Load data from MySQL TO Elasticsearch

Question

Hi I am Using the following scirp file in lostash 2.X version I have over 186000 records in MySQL database table,but while running this .conf file only one document is loading in elastic search index.

input {
        jdbc {
            jdbc_connection_string => "jdbc:mysql://localhost/elasticsearch"
            jdbc_user => "root"
            jdbc_password => "empower"
            #jdbc_validate_connection => true
            jdbc_driver_library => "/home/wtc082/Documents/com.mysql.jdbc_5.1.5.jar"
            jdbc_driver_class => "com.mysql.jdbc.Driver"
            statement => "SELECT * FROM index_part_content_local;"
            #schedule => "* * * * *"
            #codec => "json"
        }
    }

output {
    elasticsearch {
        index => "mysqltest"
        document_type => "mysqltest_type"
        document_id => "%{id}"
        hosts => "localhost:9200"
    }
}

When i use this query only one document is index

GET mysqltest/_search
{
  "query": {
    "match_all": {}
  }
}




{
  "took": 14,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "max_score": 1,
    "hits": [
      {
        "_index": "mysqltest",
        "_type": "mysqltest_type",
        "_id": "%{id}",
        "_score": 1,
        "_source": {
          "partnum": "",
          "property1": "",
          "property2": "",
          "color": "",
          "size": "",
          "dim": "",
          "thumburl": "",
          "catid": "6575",
          "subcatid": "3813",
          "termid": "31999",
          "longdesc": "<ul><li>Equipment and Parts<li>GC32-XD Parts<li>D/V Lock Plate Screw</ul>",
          "hier1desc": "Heavy Duty Tools / Equipment",
          "hier2desc": "Other Heavy Duty Equipment",
          "hier3desc": "Hose Crimping Equipment & Accessories",
          "aaiabrandid": "BBSC",
          "aaiabrandname": "Gates",
          "brandimageurl": "es-logo-sm.jpg",
          "linecode": "GAT",
          "descrp": "D/V Lock Plate Screw",
          "@version": "1",
          "@timestamp": "2016-12-20T09:16:40.075Z"
        }
      }
    ]
  }
}

I have updated the Document ,Thanks for your help in advance Val, — prasad kp
– prasad kp, Commented Dec 20, 2016 at 10:00

Val · Accepted Answer · 2016-12-20 10:00:55Z

2

Ok, as you can see the ID of your document is the verbatim value "%{id}", which means that apparently you don't have any id column in your database and all records from your database are indexed under the same document id, hence why you only see one document.

In your elasticsearch output, you need to make sure to use a field that is the primary key of your table

    document_id => "%{PRIMARY_KEY}"

Fix that and that will work.

answered Dec 20, 2016 at 10:00

Val

218k14 gold badges377 silver badges384 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

prasad kp Over a year ago

OMG...!!!!! you are god of ES Val , thanks for helping .....I wish when ever I post any question ,u s'be the one who will answer it ,eveytime. Became a huge fan of yours........

Collectives™ on Stack Overflow

Load data from MySQL TO Elasticsearch

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related