I'm using ES 0.90.7 along with JDBC River 2.2.3. I'm attempting to find a
mechanic to periodically update ES from a MySQL table. I need to ensure
that I have every row from MySQL indexed into ES. Below is my test
structure:
orders table
CREATE TABLE orders (
id bigint(20) NOT NULL,
name varchar(45) DEFAULT NULL,
description text,
guid char(16) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
number int(11) unsigned DEFAULT NULL,
date datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
orders_meta table
CREATE TABLE orders_meta (
id bigint(20) NOT NULL AUTO_INCREMENT,
last_run datetime DEFAULT NULL,
last_id bigint(20) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
orders ES index
"mappings" : {
"order" : {
"properties" : {
"_id" : {
"type" : "long",
"index" : "not_analyzed"
},
"name" : {
"type" : "string"
},
"description" : {
"type" : "string"
},
"guid" : {
"type" : "string",
"index" : "not_analyzed"
},
"number" : {
"type" : "integer",
"index" : "not_analyzed"
},
"date" : {
"type" : "date"
}
}
}
}
orders JDBC river
{
"type" : "jdbc",
"jdbc" : {
"strategy" : "simple",
"poll" : "5s",
"driver" : "com.mysql.jdbc.Driver",
"url" : "jdbc:mysql://127.0.0.1:6033/test",
"user" : "username",
"password" : "password",
"sql" : "SELECT orders.id AS _id, orders.name,
orders.description, BinaryToGuid(orders.guid), orders.number,
orders.date FROM test.orders WHERE orders.id > (SELECT
orders_meta.last_id FROM test.orders_meta);",
"acksql" : "REPLACE INTO test.orders_meta (last_run, last_id)
VALUES (UTC_TIMESTAMP(), 0);"
},
"index" : {
"index" : "orders",
"type" : "order"
}
}
As you can see from the river, my thought was to keep a meta table that
tracked the last id that pulled into ES. The trouble is that I don't how
or if you can get that last (highest) _id from ES (note the 0 in the acksql
portion).
This may not be the right way to even tackle this. I'm open to ideas. My
production tables get ~2-6 million rows inserted daily. I need to update
ES relatively quickly for searching.
--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/62bc56a9-52e9-438f-8ca6-e6b22c6810e5%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
