Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP

Moving a table into Elasticsearch

Jörg Prante edited this page · 2 revisions

A table from a JDBC data source can be moved into Elasticsearch.

For moving, we use a simple "select *" (star) query. Star queries are the simplest variant of selecting data from a database. They dump tables into Elasticsearch row-by-row.

For example, this river

curl -XPUT 'localhost:9200/_river/my_jdbc_river/_meta' -d '{
    "type" : "jdbc",
    "jdbc" : {
        "url" : "jdbc:mysql://localhost:3306/test",
        "user" : "",
        "password" : "",
        "sql" : "select * from orders"
    }
}'

and this table

mysql> select * from orders;
+----------+-----------------+---------+----------+---------------------+
| customer | department      | product | quantity | created             |
+----------+-----------------+---------+----------+---------------------+
| Big      | American Fruits | Apples  |        1 | 0000-00-00 00:00:00 |
| Large    | German Fruits   | Bananas |        1 | 0000-00-00 00:00:00 |
| Huge     | German Fruits   | Oranges |        2 | 0000-00-00 00:00:00 |
| Good     | German Fruits   | Apples  |        2 | 2012-06-01 00:00:00 |
| Bad      | English Fruits  | Oranges |        3 | 2012-06-01 00:00:00 |
+----------+-----------------+---------+----------+---------------------+
5 rows in set (0.00 sec)

will result into the following JSON objects

id=0 {"product":"Apples","created":null,"department":"American Fruits","quantity":1,"customer":"Big"}
id=1 {"product":"Bananas","created":null,"department":"German Fruits","quantity":1,"customer":"Large"}
id=2 {"product":"Oranges","created":null,"department":"German Fruits","quantity":2,"customer":"Huge"}
id=3 {"product":"Apples","created":1338501600000,"department":"German Fruits","quantity":2,"customer":"Good"}
id=4 {"product":"Oranges","created":1338501600000,"department":"English Fruits","quantity":3,"customer":"Bad"}

The JSON objects are flat, the id of the documents is generated automatically.

Something went wrong with that request. Please try again.