# \[Ann\] JDBC River Plugin for ElasticSearch **URL:** \ **Category:** Elasticsearch\ **Created:** [June 16, 2012, 8:59pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132 "2012-06-16T20:59:18Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [June 16, 2012, 8:59pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/1 "2012-06-16T20:59:18Z")
Hi, I'd like to announce a JDBC river implementation. It can be found at [https://github.com/jprante/elasticsearch-river-jdbc](https://github.com/jprante/elasticsearch-river-jdbc) I hope it is useful for all of you who need to index data from SQL databases into ElasticSearch. Suggestions, corrections, improvements are welcome! ## Introduction The Java Database Connection (JDBC) river allows to select data from JDBC sources for indexing into ElasticSearch. It is implemented as an Elasticsearch plugin. The relational data is internally transformed into structured JSON objects for ElasticSearch schema-less indexing. Setting it up is as simple as executing something like the following against ElasticSearch: curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ "type" : "jdbc", "jdbc" : { "driver" : "com.mysql.jdbc.Driver", "url" : "jdbc:mysql://localhost:3306/test", "user" : "", "password" : "", "sql" : "select \* from orders", } }' This HTTP PUT statement will create a river named `my_jdbc_river` that fetches all the rows from the `orders` table in the MySQL database `test` at `localhost`. You have to install the JDBC driver jar of your favorite database manually into the `plugins` directory where the jar file of the JDBC river plugin resides. By default, the JDBC river re-executes the SQL statement on a regular basis (60 minutes). In case of a failover, the JDBC river will automatically be restarted on another ElasticSearch node, and continue indexing. Many JDBC rivers can run in parallel. Each river opens one thread to select the data. ## Installation In order to install the plugin, simply run: `bin/plugin -install jprante/elasticsearch-river-jdbc/1.0.0`. ## Log example of river creation [2012-06-16 18:50:10,035][INFO][cluster.metadata] [Anomaly] [\_river] update\_mapping [my\_jdbc\_river] (dynamic) [2012-06-16 18:50:10,046][INFO][river.jdbc] [Anomaly] [jdbc][my\_jdbc\_river] starting JDBC connector: URL [jdbc:mysql://localhost:3306/test], driver [com.mysql.jdbc.Driver], sql [select - from orders], indexing to [jdbc]/[jdbc], poll [1h] [2012-06-16 18:50:10,129][INFO][cluster.metadata] [Anomaly] [jdbc] creating index, cause [api], shards [5]/[1], mappings [] [2012-06-16 18:50:10,353][INFO][cluster.metadata] [Anomaly] [\_river] update\_mapping [my\_jdbc\_river] (dynamic) [2012-06-16 18:50:10,714][INFO][river.jdbc] [Anomaly] [jdbc][my\_jdbc\_river] got 5 rows [2012-06-16 18:50:10,719][INFO][river.jdbc] [Anomaly] [jdbc][my\_jdbc\_river] next run, waiting 1h, URL [jdbc:mysql://localhost:3306/test] driver [com.mysql.jdbc.Driver] sql [select \* from orders] # Configuration The SQL statements used for selecting can be configured as follows. ## Star query Star queries are the simplest variant of selecting data. They can be used to dump tables into ElasticSearch. curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ "type" : "jdbc", "jdbc" : { "driver" : "com.mysql.jdbc.Driver", "url" : "jdbc:mysql://localhost:3306/test", "user" : "", "password" : "", "sql" : "select \* from orders" } }' For example 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) The JSON objects are flat, the `id` of the documents is generated automatically, it is the row number. 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"} ## Labeled columns In SQL, each column may be labeled with a name. This name is used by the JDBC river to JSON object construction. curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ "type" : "jdbc", "jdbc" : { "driver" : "com.mysql.jdbc.Driver", "url" : "jdbc:mysql://localhost:3306/test", "user" : "", "password" : "", "sql" : "select products.name as "product.name", orders.customer as "product.customer.name", orders.quantity \* products.price as "product.customer.bill" from products, orders where products.name = orders.product" } }' In this query, the columns selected are described as `product.name`, `product.customer.name`, and `product.customer.bill`. mysql\> select products.name as "product.name", orders.customer as "product.customer", orders.quantity \* products.price as "product.customer.bill" from products, orders where products.name = orders.product ; +--------------+------------------+-----------------------+ | product.name | product.customer | product.customer.bill | +--------------+------------------+-----------------------+ | Apples | Big | 1 | | Bananas | Large | 2 | | Oranges | Huge | 6 | | Apples | Good | 2 | | Oranges | Bad | 9 | +--------------+------------------+-----------------------+ 5 rows in set, 5 warnings (0.00 sec) The JSON objects are id=0 {"product":{"name":"Apples","customer":{"bill":1.0,"name":"Big"}}} id=1 {"product":{"name":"Bananas","customer":{"bill":2.0,"name":"Large"}}} id=2 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} id=3 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} id=4 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} There are three column labels with an underscore as prefix that are mapped to the Elasticsearch index/type/id. \_id \_type \_index ## Structured objects One of the advantage of SQL queries is the join operation. From many tables, new tuples can be formed. curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ "type" : "jdbc", "jdbc" : { "driver" : "com.mysql.jdbc.Driver", "url" : "jdbc:mysql://localhost:3306/test", "user" : "", "password" : "", "sql" : "select "relations" as "\_index", orders.customer as "\_id", orders.customer as "contact.customer", employees.name as "contact.employee" from orders left join employees on employees.department = orders.department" } }' For example, these rows from SQL mysql\> select "relations" as "\_index", orders.customer as "\_id", orders.customer as "contact.customer", employees.name as "contact.employee" from orders left join employees on employees.department = orders.department; +-----------+-------+------------------+------------------+ | \_index | \_id | contact.customer | contact.employee | +-----------+-------+------------------+------------------+ | relations | Big | Big | Smith | | relations | Large | Large | Müller | | relations | Large | Large | Meier | | relations | Large | Large | Schulze | | relations | Huge | Huge | Müller | | relations | Huge | Huge | Meier | | relations | Huge | Huge | Schulze | | relations | Good | Good | Müller | | relations | Good | Good | Meier | | relations | Good | Good | Schulze | | relations | Bad | Bad | Jones | +-----------+-------+------------------+------------------+ 11 rows in set (0.00 sec) will generate fewer JSON objects for the index `relations`. index=relations id=Big {"contact":{"employee":"Smith","customer":"Big"}} index=relations id=Large {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Large"}} index=relations id=Huge {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Huge"}} index=relations id=Good {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Good"}} index=relations id=Bad {"contact":{"employee":"Jones","customer":"Bad"}} Note how the `employee` column is collapsed into a JSON array. The repeated occurence of the `_id` column controls how values are folded into arrays for making use of the ElasticSearch JSON data model. ## Bind parameter Bind parameters are useful for selecting rows according to a matching condition where the match criteria is not known beforehand. For example, only rows matching certain conditions can be indexed into ElasticSearch. curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ "type" : "jdbc", "jdbc" : { "driver" : "com.mysql.jdbc.Driver", "url" : "jdbc:mysql://localhost:3306/test", "user" : "", "password" : "", "sql" : "select products.name as "product.name", orders.customer as "product.customer.name", orders.quantity \* products.price as "product.customer.bill" from products, orders where products.name = orders.product and orders.quantity \* products.price \> ?", "params: [5.0] } }' Example result id=0 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} ## Time-based selecting Because the JDBC river is running repeatedly, time-based selecting is useful. The current time is represented by the parameter value `$now`. In this example, all rows beginning with a certain date up to now are selected. curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ "type" : "jdbc", "jdbc" : { "driver" : "com.mysql.jdbc.Driver", "url" : "jdbc:mysql://localhost:3306/test", "user" : "", "password" : "", "sql" : "select products.name as "product.name", orders.customer as "product.customer.name", orders.quantity \* products.price as "product.customer.bill" from products, orders where products.name = orders.product and orders.created between ? - 14 and ?", "params: [2012-06-01", "$now"] } }' Example result: id=0 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} ## Index Each river can index into a specified index. Example: curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ "type" : "jdbc", "jdbc" : { "driver" : "com.mysql.jdbc.Driver", "url" : "jdbc:mysql://localhost:3306/test", "user" : "", "password" : "", "sql" : "select \* from orders", }, "index" : { "index" : "jdbc", "type" : "jdbc" } }' ## Bulk indexing Bulk indexing is automatically used in order to speed up the indexing process. Each SQL result set will be indexed by a single bulk if the bulk size is not specified. A bulk size can be defined, also a maximum size of active bulk requests to cope with high load situations. A bulk timeout defines the time period after which bulk feeds continue. curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ "type" : "jdbc", "jdbc" : { "driver" : "com.mysql.jdbc.Driver", "url" : "jdbc:mysql://localhost:3306/test", "user" : "", "password" : "", "sql" : "select \* from orders", }, "index" : { "index" : "jdbc", "type" : "jdbc", "bulk\_size" : 100, "max\_bulk\_requests" : 30, "bulk\_timeout" : "60s" } }' ## Stopping/deleting the river curl -XDELETE 'localhost:9200/\_river/my\_jdbc\_river/' Best regards,, Jörg ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [June 16, 2012, 11:30pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/2 "2012-06-16T23:30:24Z")
Hi Jörg ! That's really a cool new feature ! Thanks for sharing. I think that you will get many users (and many questions) for it. 😉 David 😉 Twitter : @dadoonet / @elasticsearchfr Le 16 juin 2012 à 22:59, Jörg Prante [joergprante@gmail.com](mailto:joergprante@gmail.com) a écrit : > Hi, > > I'd like to announce a JDBC river implementation. > > It can be found at [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) > > I hope it is useful for all of you who need to index data from SQL databases into Elasticsearch. > > Suggestions, corrections, improvements are welcome! > > ## Introduction > > The Java Database Connection (JDBC) river allows to select data from JDBC sources for indexing into Elasticsearch. > > It is implemented as an Elasticsearch plugin. > > The relational data is internally transformed into structured JSON objects for Elasticsearch schema-less indexing. > > Setting it up is as simple as executing something like the following against Elasticsearch: > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders", > } > }' > > This HTTP PUT statement will create a river named `my_jdbc_river` > that fetches all the rows from the `orders` table in the MySQL database > `test` at `localhost`. > > You have to install the JDBC driver jar of your favorite database manually into > the `plugins` directory where the jar file of the JDBC river plugin resides. > > By default, the JDBC river re-executes the SQL statement on a regular basis (60 minutes). > > In case of a failover, the JDBC river will automatically be restarted > on another Elasticsearch node, and continue indexing. > > Many JDBC rivers can run in parallel. Each river opens one thread to select > the data. > > ## Installation > > In order to install the plugin, simply run: `bin/plugin -install jprante/elasticsearch-river-jdbc/1.0.0`. > > ## Log example of river creation > > [2012-06-16 18:50:10,035][INFO][cluster.metadata] [Anomaly] [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > [2012-06-16 18:50:10,046][INFO][river.jdbc] [Anomaly] [jdbc][my\_jdbc\_river] starting JDBC connector: URL [jdbc:mysql://localhost:3306/test], driver [com.mysql.jdbc.Driver], sql [select \* from orders], indexing to [jdbc]/[jdbc], poll [1h] > [2012-06-16 18:50:10,129][INFO][cluster.metadata] [Anomaly] [jdbc] creating index, cause [api], shards [5]/[1], mappings > [2012-06-16 18:50:10,353][INFO][cluster.metadata] [Anomaly] [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > [2012-06-16 18:50:10,714][INFO][river.jdbc] [Anomaly] [jdbc][my\_jdbc\_river] got 5 rows > [2012-06-16 18:50:10,719][INFO][river.jdbc] [Anomaly] [jdbc][my\_jdbc\_river] next run, waiting 1h, URL [jdbc:mysql://localhost:3306/test] driver [com.mysql.jdbc.Driver] sql [select \* from orders] > > # Configuration > > The SQL statements used for selecting can be configured as follows. > > ## Star query > > Star queries are the simplest variant of selecting data. They can be used > to dump tables into Elasticsearch. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders" > } > }' > > For example > > 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) > > The JSON objects are flat, the `id` > of the documents is generated automatically, it is the row number. > > 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"} > > ## Labeled columns > > In SQL, each column may be labeled with a name. This name is used by the JDBC river to JSON object construction. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select products.name as "product.name", orders.customer as "product.customer.name", orders.quantity \* products.price as "product.customer.bill" from products, orders where products.name = orders.product" > } > }' > > In this query, the columns selected are described as `product.name`, > `product.customer.name`, and `product.customer.bill`. > > mysql\> select products.name as "product.name", orders.customer as "product.customer", orders.quantity \* products.price as "product.customer.bill" from products, orders where products.name = orders.product ; > +--------------+------------------+-----------------------+ > | product.name | product.customer | product.customer.bill | > +--------------+------------------+-----------------------+ > | Apples | Big | 1 | > | Bananas | Large | 2 | > | Oranges | Huge | 6 | > | Apples | Good | 2 | > | Oranges | Bad | 9 | > +--------------+------------------+-----------------------+ > 5 rows in set, 5 warnings (0.00 sec) > > The JSON objects are > > id=0 {"product":{"name":"Apples","customer":{"bill":1.0,"name":"Big"}}} > id=1 {"product":{"name":"Bananas","customer":{"bill":2.0,"name":"Large"}}} > id=2 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > id=3 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > id=4 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > There are three column labels with an underscore as prefix > that are mapped to the Elasticsearch index/type/id. > > \_id > \_type > \_index > > ## Structured objects > > One of the advantage of SQL queries is the join operation. From many tables, new tuples can be formed. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select "relations" as "\_index", orders.customer as "\_id", orders.customer as "contact.customer", employees.name as "contact.employee" from orders left join employees on employees.department = orders.department" > } > }' > > For example, these rows from SQL > > mysql\> select "relations" as "\_index", orders.customer as "\_id", orders.customer as "contact.customer", employees.name as "contact.employee" from orders left join employees on employees.department = orders.department; > +-----------+-------+------------------+------------------+ > | \_index | \_id | contact.customer | contact.employee | > +-----------+-------+------------------+------------------+ > | relations | Big | Big | Smith | > | relations | Large | Large | Müller | > | relations | Large | Large | Meier | > | relations | Large | Large | Schulze | > | relations | Huge | Huge | Müller | > | relations | Huge | Huge | Meier | > | relations | Huge | Huge | Schulze | > | relations | Good | Good | Müller | > | relations | Good | Good | Meier | > | relations | Good | Good | Schulze | > | relations | Bad | Bad | Jones | > +-----------+-------+------------------+------------------+ > 11 rows in set (0.00 sec) > > will generate fewer JSON objects for the index `relations`. > > index=relations id=Big {"contact":{"employee":"Smith","customer":"Big"}} > index=relations id=Large {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Large"}} > index=relations id=Huge {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Huge"}} > index=relations id=Good {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Good"}} > index=relations id=Bad {"contact":{"employee":"Jones","customer":"Bad"}} > > Note how the `employee` column is collapsed into a JSON array. The repeated occurence of the `_id` column > controls how values are folded into arrays for making use of the Elasticsearch JSON data model. > > ## Bind parameter > > Bind parameters are useful for selecting rows according to a matching condition > where the match criteria is not known beforehand. > > For example, only rows matching certain conditions can be indexed into > Elasticsearch. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select products.name as "product.name", orders.customer as "product.customer.name", orders.quantity \* products.price as "product.customer.bill" from products, orders where products.name = orders.product and orders.quantity \* products.price \> ?", > "params: [5.0] > } > }' > > Example result > > id=0 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > ## Time-based selecting > > Because the JDBC river is running repeatedly, time-based selecting is useful. > The current time is represented by the parameter value `$now`. > > In this example, all rows beginning with a certain date up to now are selected. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select products.name as "product.name", orders.customer as "product.customer.name", orders.quantity \* products.price as "product.customer.bill" from products, orders where products.name = orders.product and orders.created between ? - 14 and ?", > "params: [2012-06-01", "$now"] > } > }' > > Example result: > > id=0 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > ## Index > > Each river can index into a specified index. Example: > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders", > }, > "index" : { > "index" : "jdbc", > "type" : "jdbc" > } > }' > > ## Bulk indexing > > Bulk indexing is automatically used in order to speed up the indexing process. > > Each SQL result set will be indexed by a single bulk if the bulk size is not specified. > > A bulk size can be defined, also a maximum size of active bulk requests to cope with high load situations. > A bulk timeout defines the time period after which bulk feeds continue. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders", > }, > "index" : { > "index" : "jdbc", > "type" : "jdbc", > "bulk\_size" : 100, > "max\_bulk\_requests" : 30, > "bulk\_timeout" : "60s" > } > }' > > ## Stopping/deleting the river > > curl -XDELETE 'localhost:9200/\_river/my\_jdbc\_river/' > > Best regards,, > > Jörg ---
**Author:** ![otisg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/otisg/32/492_2.png) [@otisg](https://discuss.elastic.co/u/otisg)\ **Post date:** [June 17, 2012, 4:26am UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/3 "2012-06-17T04:26:21Z")
Like David said - I hope you are ready for the avalanche of questions.... at least I think that's what will happen based on what happened when Solr got DataImportHandler a few years ago. And speaking of DIH, how does one handle deletion of DB rows and how does one select+index incrementally (only rows that changed since last river run)? ## Thanks, Otis Search Analytics - [Cloud Monitoring Tools & Services | Sematext](http://sematext.com/search-analytics/index.html) Scalable Performance Monitoring - [Sematext Monitoring | Infrastructure Monitoring Service](http://sematext.com/spm/index.html) On Saturday, June 16, 2012 4:59:18 PM UTC-4, Jörg Prante wrote: > Hi, > > I'd like to announce a JDBC river implementation. > > It can be found at [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) > > I hope it is useful for all of you who need to index data from SQL > databases into Elasticsearch. > > Suggestions, corrections, improvements are welcome! > > ## Introduction > > The Java Database Connection (JDBC) river allows to select data from JDBC > sources for indexing into Elasticsearch. > > It is implemented as an Elasticsearch plugin. > > The relational data is internally transformed into structured JSON objects > for Elasticsearch schema-less indexing. > > Setting it up is as simple as executing something like the following > against Elasticsearch: > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders", > } > }' > > This HTTP PUT statement will create a river named `my_jdbc_river` > that fetches all the rows from the `orders` table in the MySQL database > `test` at `localhost`. > > You have to install the JDBC driver jar of your favorite database manually > into > the `plugins` directory where the jar file of the JDBC river plugin > resides. > > By default, the JDBC river re-executes the SQL statement on a regular > basis (60 minutes). > > In case of a failover, the JDBC river will automatically be restarted > on another Elasticsearch node, and continue indexing. > > Many JDBC rivers can run in parallel. Each river opens one thread to select > the data. > > ## Installation > > In order to install the plugin, simply run: `bin/plugin -install jprante/elasticsearch-river-jdbc/1.0.0`. > > ## Log example of river creation > > [2012-06-16 18:50:10,035][INFO][cluster.metadata] [Anomaly] > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > [2012-06-16 18:50:10,046][INFO][river.jdbc] [Anomaly] > [jdbc][my\_jdbc\_river] starting JDBC connector: URL > [jdbc:mysql://localhost:3306/test], driver [com.mysql.jdbc.Driver], sql [select > > - from orders], indexing to [jdbc]/[jdbc], poll [1h] > [2012-06-16 18:50:10,129][INFO][cluster.metadata] [Anomaly] > [jdbc] creating index, cause [api], shards [5]/[1], mappings > [2012-06-16 18:50:10,353][INFO][cluster.metadata] [Anomaly] > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > [2012-06-16 18:50:10,714][INFO][river.jdbc] [Anomaly] > [jdbc][my\_jdbc\_river] got 5 rows > [2012-06-16 18:50:10,719][INFO][river.jdbc] [Anomaly] > [jdbc][my\_jdbc\_river] next run, waiting 1h, URL > [jdbc:mysql://localhost:3306/test] driver [com.mysql.jdbc.Driver] sql > [select \* from orders] > > # Configuration > > The SQL statements used for selecting can be configured as follows. > > ## Star query > > Star queries are the simplest variant of selecting data. They can be used > to dump tables into Elasticsearch. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders" > } > }' > > For example > > 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) > > The JSON objects are flat, the `id` > of the documents is generated automatically, it is the row number. > > 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"} > > ## Labeled columns > > In SQL, each column may be labeled with a name. This name is used by the > JDBC river to JSON object construction. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select products.name as "product.name", > orders.customer as "product.customer.name", orders.quantity \* > products.price as "product.customer.bill" from products, orders where > products.name = orders.product" > } > }' > > In this query, the columns selected are described as `product.name`, > `product.customer.name`, and `product.customer.bill`. > > mysql\> select products.name as "product.name", orders.customer as > "product.customer", orders.quantity \* products.price as > "product.customer.bill" from products, orders where products.name = > orders.product ; > +--------------+------------------+-----------------------+ > | product.name | product.customer | product.customer.bill | > +--------------+------------------+-----------------------+ > | Apples | Big | 1 | > | Bananas | Large | 2 | > | Oranges | Huge | 6 | > | Apples | Good | 2 | > | Oranges | Bad | 9 | > +--------------+------------------+-----------------------+ > 5 rows in set, 5 warnings (0.00 sec) > > The JSON objects are > > id=0 {"product":{"name":"Apples","customer":{"bill":1.0,"name":"Big"}}} > id=1 {"product":{"name":"Bananas","customer":{"bill":2.0,"name":"Large"}}} > id=2 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > id=3 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > id=4 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > There are three column labels with an underscore as prefix > that are mapped to the Elasticsearch index/type/id. > > \_id > \_type > \_index > > ## Structured objects > > One of the advantage of SQL queries is the join operation. From many > tables, new tuples can be formed. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select "relations" as "\_index", orders.customer as > "\_id", orders.customer as "contact.customer", employees.name as > "contact.employee" from orders left join employees on > employees.department = orders.department" > } > }' > > For example, these rows from SQL > > mysql\> select "relations" as "\_index", orders.customer as "\_id", > orders.customer as "contact.customer", employees.name as > "contact.employee" from orders left join employees on employees.department > = orders.department; > +-----------+-------+------------------+------------------+ > | \_index | \_id | contact.customer | contact.employee | > +-----------+-------+------------------+------------------+ > | relations | Big | Big | Smith | > | relations | Large | Large | Müller | > | relations | Large | Large | Meier | > | relations | Large | Large | Schulze | > | relations | Huge | Huge | Müller | > | relations | Huge | Huge | Meier | > | relations | Huge | Huge | Schulze | > | relations | Good | Good | Müller | > | relations | Good | Good | Meier | > | relations | Good | Good | Schulze | > | relations | Bad | Bad | Jones | > +-----------+-------+------------------+------------------+ > 11 rows in set (0.00 sec) > > will generate fewer JSON objects for the index `relations`. > > index=relations id=Big {"contact":{"employee":"Smith","customer":"Big"}} > index=relations id=Large > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Large"}} > index=relations id=Huge > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Huge"}} > index=relations id=Good > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Good"}} > index=relations id=Bad {"contact":{"employee":"Jones","customer":"Bad"}} > > Note how the `employee` column is collapsed into a JSON array. The > repeated occurence of the `_id` column > controls how values are folded into arrays for making use of the > Elasticsearch JSON data model. > > ## Bind parameter > > Bind parameters are useful for selecting rows according to a matching > condition > where the match criteria is not known beforehand. > > For example, only rows matching certain conditions can be indexed into > Elasticsearch. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select products.name as "product.name", > orders.customer as "product.customer.name", orders.quantity \* > products.price as "product.customer.bill" from products, orders where > products.name = orders.product and orders.quantity \* products.price \> ?", > "params: [5.0] > } > }' > > Example result > > id=0 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > ## Time-based selecting > > Because the JDBC river is running repeatedly, time-based selecting is > useful. > The current time is represented by the parameter value `$now`. > > In this example, all rows beginning with a certain date up to now are > selected. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select products.name as "product.name", > orders.customer as "product.customer.name", orders.quantity \* > products.price as "product.customer.bill" from products, orders where > products.name = orders.product and orders.created between ? - 14 and ?", > "params: [2012-06-01", "$now"] > } > }' > > Example result: > > id=0 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > ## Index > > Each river can index into a specified index. Example: > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders", > }, > "index" : { > "index" : "jdbc", > "type" : "jdbc" > } > }' > > ## Bulk indexing > > Bulk indexing is automatically used in order to speed up the indexing > process. > > Each SQL result set will be indexed by a single bulk if the bulk size is > not specified. > > A bulk size can be defined, also a maximum size of active bulk requests to > cope with high load situations. > A bulk timeout defines the time period after which bulk feeds continue. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders", > }, > "index" : { > "index" : "jdbc", > "type" : "jdbc", > "bulk\_size" : 100, > "max\_bulk\_requests" : 30, > "bulk\_timeout" : "60s" > } > }' > > ## Stopping/deleting the river > > curl -XDELETE 'localhost:9200/\_river/my\_jdbc\_river/' > > Best regards,, > > Jörg ---
**Author:** ![karmi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/karmi/32/44951_2.png) [@karmi](https://discuss.elastic.co/u/karmi)\ **Post date:** [June 17, 2012, 7:00am UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/4 "2012-06-17T07:00:47Z")
Hi Jörg, the interface/configuration looks great, I love the way you use `SELECT x as y` (“labeled columns”) to construct the JSON. Karel On Saturday, June 16, 2012 10:59:18 PM UTC+2, Jörg Prante wrote: > Hi, > > I'd like to announce a JDBC river implementation. > > It can be found at [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) > > I hope it is useful for all of you who need to index data from SQL > databases into Elasticsearch. > > Suggestions, corrections, improvements are welcome! > > ## Introduction > > The Java Database Connection (JDBC) river allows to select data from JDBC > sources for indexing into Elasticsearch. > > It is implemented as an Elasticsearch plugin. > > The relational data is internally transformed into structured JSON objects > for Elasticsearch schema-less indexing. > > Setting it up is as simple as executing something like the following > against Elasticsearch: > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders", > } > }' > > This HTTP PUT statement will create a river named `my_jdbc_river` > that fetches all the rows from the `orders` table in the MySQL database > `test` at `localhost`. > > You have to install the JDBC driver jar of your favorite database manually > into > the `plugins` directory where the jar file of the JDBC river plugin > resides. > > By default, the JDBC river re-executes the SQL statement on a regular > basis (60 minutes). > > In case of a failover, the JDBC river will automatically be restarted > on another Elasticsearch node, and continue indexing. > > Many JDBC rivers can run in parallel. Each river opens one thread to select > the data. > > ## Installation > > In order to install the plugin, simply run: `bin/plugin -install jprante/elasticsearch-river-jdbc/1.0.0`. > > ## Log example of river creation > > [2012-06-16 18:50:10,035][INFO][cluster.metadata] [Anomaly] > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > [2012-06-16 18:50:10,046][INFO][river.jdbc] [Anomaly] > [jdbc][my\_jdbc\_river] starting JDBC connector: URL > [jdbc:mysql://localhost:3306/test], driver [com.mysql.jdbc.Driver], sql [select > > - from orders], indexing to [jdbc]/[jdbc], poll [1h] > [2012-06-16 18:50:10,129][INFO][cluster.metadata] [Anomaly] > [jdbc] creating index, cause [api], shards [5]/[1], mappings > [2012-06-16 18:50:10,353][INFO][cluster.metadata] [Anomaly] > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > [2012-06-16 18:50:10,714][INFO][river.jdbc] [Anomaly] > [jdbc][my\_jdbc\_river] got 5 rows > [2012-06-16 18:50:10,719][INFO][river.jdbc] [Anomaly] > [jdbc][my\_jdbc\_river] next run, waiting 1h, URL > [jdbc:mysql://localhost:3306/test] driver [com.mysql.jdbc.Driver] sql > [select \* from orders] > > # Configuration > > The SQL statements used for selecting can be configured as follows. > > ## Star query > > Star queries are the simplest variant of selecting data. They can be used > to dump tables into Elasticsearch. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders" > } > }' > > For example > > 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) > > The JSON objects are flat, the `id` > of the documents is generated automatically, it is the row number. > > 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"} > > ## Labeled columns > > In SQL, each column may be labeled with a name. This name is used by the > JDBC river to JSON object construction. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select products.name as "product.name", > orders.customer as "product.customer.name", orders.quantity \* > products.price as "product.customer.bill" from products, orders where > products.name = orders.product" > } > }' > > In this query, the columns selected are described as `product.name`, > `product.customer.name`, and `product.customer.bill`. > > mysql\> select products.name as "product.name", orders.customer as > "product.customer", orders.quantity \* products.price as > "product.customer.bill" from products, orders where products.name = > orders.product ; > +--------------+------------------+-----------------------+ > | product.name | product.customer | product.customer.bill | > +--------------+------------------+-----------------------+ > | Apples | Big | 1 | > | Bananas | Large | 2 | > | Oranges | Huge | 6 | > | Apples | Good | 2 | > | Oranges | Bad | 9 | > +--------------+------------------+-----------------------+ > 5 rows in set, 5 warnings (0.00 sec) > > The JSON objects are > > id=0 {"product":{"name":"Apples","customer":{"bill":1.0,"name":"Big"}}} > id=1 {"product":{"name":"Bananas","customer":{"bill":2.0,"name":"Large"}}} > id=2 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > id=3 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > id=4 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > There are three column labels with an underscore as prefix > that are mapped to the Elasticsearch index/type/id. > > \_id > \_type > \_index > > ## Structured objects > > One of the advantage of SQL queries is the join operation. From many > tables, new tuples can be formed. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select "relations" as "\_index", orders.customer as > "\_id", orders.customer as "contact.customer", employees.name as > "contact.employee" from orders left join employees on > employees.department = orders.department" > } > }' > > For example, these rows from SQL > > mysql\> select "relations" as "\_index", orders.customer as "\_id", > orders.customer as "contact.customer", employees.name as > "contact.employee" from orders left join employees on employees.department > = orders.department; > +-----------+-------+------------------+------------------+ > | \_index | \_id | contact.customer | contact.employee | > +-----------+-------+------------------+------------------+ > | relations | Big | Big | Smith | > | relations | Large | Large | Müller | > | relations | Large | Large | Meier | > | relations | Large | Large | Schulze | > | relations | Huge | Huge | Müller | > | relations | Huge | Huge | Meier | > | relations | Huge | Huge | Schulze | > | relations | Good | Good | Müller | > | relations | Good | Good | Meier | > | relations | Good | Good | Schulze | > | relations | Bad | Bad | Jones | > +-----------+-------+------------------+------------------+ > 11 rows in set (0.00 sec) > > will generate fewer JSON objects for the index `relations`. > > index=relations id=Big {"contact":{"employee":"Smith","customer":"Big"}} > index=relations id=Large > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Large"}} > index=relations id=Huge > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Huge"}} > index=relations id=Good > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Good"}} > index=relations id=Bad {"contact":{"employee":"Jones","customer":"Bad"}} > > Note how the `employee` column is collapsed into a JSON array. The > repeated occurence of the `_id` column > controls how values are folded into arrays for making use of the > Elasticsearch JSON data model. > > ## Bind parameter > > Bind parameters are useful for selecting rows according to a matching > condition > where the match criteria is not known beforehand. > > For example, only rows matching certain conditions can be indexed into > Elasticsearch. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select products.name as "product.name", > orders.customer as "product.customer.name", orders.quantity \* > products.price as "product.customer.bill" from products, orders where > products.name = orders.product and orders.quantity \* products.price \> ?", > "params: [5.0] > } > }' > > Example result > > id=0 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > ## Time-based selecting > > Because the JDBC river is running repeatedly, time-based selecting is > useful. > The current time is represented by the parameter value `$now`. > > In this example, all rows beginning with a certain date up to now are > selected. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select products.name as "product.name", > orders.customer as "product.customer.name", orders.quantity \* > products.price as "product.customer.bill" from products, orders where > products.name = orders.product and orders.created between ? - 14 and ?", > "params: [2012-06-01", "$now"] > } > }' > > Example result: > > id=0 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > ## Index > > Each river can index into a specified index. Example: > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders", > }, > "index" : { > "index" : "jdbc", > "type" : "jdbc" > } > }' > > ## Bulk indexing > > Bulk indexing is automatically used in order to speed up the indexing > process. > > Each SQL result set will be indexed by a single bulk if the bulk size is > not specified. > > A bulk size can be defined, also a maximum size of active bulk requests to > cope with high load situations. > A bulk timeout defines the time period after which bulk feeds continue. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders", > }, > "index" : { > "index" : "jdbc", > "type" : "jdbc", > "bulk\_size" : 100, > "max\_bulk\_requests" : 30, > "bulk\_timeout" : "60s" > } > }' > > ## Stopping/deleting the river > > curl -XDELETE 'localhost:9200/\_river/my\_jdbc\_river/' > > Best regards,, > > Jörg ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [June 18, 2012, 1:39pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/5 "2012-06-18T13:39:04Z")
Sorry, in 1.0.0, there was a glitch, the data did not get indexed. Fix version 1.0.1 just released. Jörg On Sunday, June 17, 2012 9:00:47 AM UTC+2, Karel Minařík wrote: > Hi Jörg, > > the interface/configuration looks great, I love the way you use `SELECT x as y` (“labeled columns”) to construct the JSON. > > Karel > > On Saturday, June 16, 2012 10:59:18 PM UTC+2, Jörg Prante wrote: > > > Hi, > > > > I'd like to announce a JDBC river implementation. > > > > It can be found at [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) > > > > I hope it is useful for all of you who need to index data from SQL > > databases into Elasticsearch. > > > > Suggestions, corrections, improvements are welcome! > > > > ## Introduction > > > > The Java Database Connection (JDBC) river allows to select data from JDBC > > sources for indexing into Elasticsearch. > > > > It is implemented as an Elasticsearch plugin. > > > > The relational data is internally transformed into structured JSON > > objects for Elasticsearch schema-less indexing. > > > > Setting it up is as simple as executing something like the following > > against Elasticsearch: > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select \* from orders", > > } > > }' > > > > This HTTP PUT statement will create a river named `my_jdbc_river` > > that fetches all the rows from the `orders` table in the MySQL database > > `test` at `localhost`. > > > > You have to install the JDBC driver jar of your favorite database > > manually into > > the `plugins` directory where the jar file of the JDBC river plugin > > resides. > > > > By default, the JDBC river re-executes the SQL statement on a regular > > basis (60 minutes). > > > > In case of a failover, the JDBC river will automatically be restarted > > on another Elasticsearch node, and continue indexing. > > > > Many JDBC rivers can run in parallel. Each river opens one thread to > > select > > the data. > > > > ## Installation > > > > In order to install the plugin, simply run: `bin/plugin -install jprante/elasticsearch-river-jdbc/1.0.0`. > > > > ## Log example of river creation > > > > [2012-06-16 18:50:10,035][INFO][cluster.metadata] [Anomaly] > > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > > [2012-06-16 18:50:10,046][INFO][river.jdbc] [Anomaly] > > [jdbc][my\_jdbc\_river] starting JDBC connector: URL > > [jdbc:mysql://localhost:3306/test], driver [com.mysql.jdbc.Driver], sql [select > > > > - from orders], indexing to [jdbc]/[jdbc], poll [1h] > > [2012-06-16 18:50:10,129][INFO][cluster.metadata] [Anomaly] > > [jdbc] creating index, cause [api], shards [5]/[1], mappings > > [2012-06-16 18:50:10,353][INFO][cluster.metadata] [Anomaly] > > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > > [2012-06-16 18:50:10,714][INFO][river.jdbc] [Anomaly] > > [jdbc][my\_jdbc\_river] got 5 rows > > [2012-06-16 18:50:10,719][INFO][river.jdbc] [Anomaly] > > [jdbc][my\_jdbc\_river] next run, waiting 1h, URL > > [jdbc:mysql://localhost:3306/test] driver [com.mysql.jdbc.Driver] sql > > [select \* from orders] > > > > # Configuration > > > > The SQL statements used for selecting can be configured as follows. > > > > ## Star query > > > > Star queries are the simplest variant of selecting data. They can be used > > to dump tables into Elasticsearch. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select \* from orders" > > } > > }' > > > > For example > > > > 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) > > > > The JSON objects are flat, the `id` > > of the documents is generated automatically, it is the row number. > > > > 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"} > > > > ## Labeled columns > > > > In SQL, each column may be labeled with a name. This name is used by the > > JDBC river to JSON object construction. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select products.name as "product.name", > > orders.customer as "product.customer.name", orders.quantity \* > > products.price as "product.customer.bill" from products, orders where > > products.name = orders.product" > > } > > }' > > > > In this query, the columns selected are described as `product.name`, > > `product.customer.name`, and `product.customer.bill`. > > > > mysql\> select products.name as "product.name", orders.customer as > > "product.customer", orders.quantity \* products.price as > > "product.customer.bill" from products, orders where products.name = > > orders.product ; > > +--------------+------------------+-----------------------+ > > | product.name | product.customer | product.customer.bill | > > +--------------+------------------+-----------------------+ > > | Apples | Big | 1 | > > | Bananas | Large | 2 | > > | Oranges | Huge | 6 | > > | Apples | Good | 2 | > > | Oranges | Bad | 9 | > > +--------------+------------------+-----------------------+ > > 5 rows in set, 5 warnings (0.00 sec) > > > > The JSON objects are > > > > id=0 {"product":{"name":"Apples","customer":{"bill":1.0,"name":"Big"}}} > > id=1 {"product":{"name":"Bananas","customer":{"bill":2.0,"name":"Large"}}} > > id=2 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > > id=3 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > > id=4 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > > > There are three column labels with an underscore as prefix > > that are mapped to the Elasticsearch index/type/id. > > > > \_id > > \_type > > \_index > > > > ## Structured objects > > > > One of the advantage of SQL queries is the join operation. From many > > tables, new tuples can be formed. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select "relations" as "\_index", orders.customer as > > "\_id", orders.customer as "contact.customer", employees.name as > > "contact.employee" from orders left join employees on > > employees.department = orders.department" > > } > > }' > > > > For example, these rows from SQL > > > > mysql\> select "relations" as "\_index", orders.customer as "\_id", > > orders.customer as "contact.customer", employees.name as > > "contact.employee" from orders left join employees on employees.department > > = orders.department; > > +-----------+-------+------------------+------------------+ > > | \_index | \_id | contact.customer | contact.employee | > > +-----------+-------+------------------+------------------+ > > | relations | Big | Big | Smith | > > | relations | Large | Large | Müller | > > | relations | Large | Large | Meier | > > | relations | Large | Large | Schulze | > > | relations | Huge | Huge | Müller | > > | relations | Huge | Huge | Meier | > > | relations | Huge | Huge | Schulze | > > | relations | Good | Good | Müller | > > | relations | Good | Good | Meier | > > | relations | Good | Good | Schulze | > > | relations | Bad | Bad | Jones | > > +-----------+-------+------------------+------------------+ > > 11 rows in set (0.00 sec) > > > > will generate fewer JSON objects for the index `relations`. > > > > index=relations id=Big {"contact":{"employee":"Smith","customer":"Big"}} > > index=relations id=Large > > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Large"}} > > index=relations id=Huge > > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Huge"}} > > index=relations id=Good > > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Good"}} > > index=relations id=Bad {"contact":{"employee":"Jones","customer":"Bad"}} > > > > Note how the `employee` column is collapsed into a JSON array. The > > repeated occurence of the `_id` column > > controls how values are folded into arrays for making use of the > > Elasticsearch JSON data model. > > > > ## Bind parameter > > > > Bind parameters are useful for selecting rows according to a matching > > condition > > where the match criteria is not known beforehand. > > > > For example, only rows matching certain conditions can be indexed into > > Elasticsearch. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select products.name as "product.name", > > orders.customer as "product.customer.name", orders.quantity \* > > products.price as "product.customer.bill" from products, orders where > > products.name = orders.product and orders.quantity \* products.price \> ?", > > "params: [5.0] > > } > > }' > > > > Example result > > > > id=0 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > > > ## Time-based selecting > > > > Because the JDBC river is running repeatedly, time-based selecting is > > useful. > > The current time is represented by the parameter value `$now`. > > > > In this example, all rows beginning with a certain date up to now are > > selected. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select products.name as "product.name", > > orders.customer as "product.customer.name", orders.quantity \* > > products.price as "product.customer.bill" from products, orders where > > products.name = orders.product and orders.created between ? - 14 and ?", > > "params: [2012-06-01", "$now"] > > } > > }' > > > > Example result: > > > > id=0 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > > > ## Index > > > > Each river can index into a specified index. Example: > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select \* from orders", > > }, > > "index" : { > > "index" : "jdbc", > > "type" : "jdbc" > > } > > }' > > > > ## Bulk indexing > > > > Bulk indexing is automatically used in order to speed up the indexing > > process. > > > > Each SQL result set will be indexed by a single bulk if the bulk size is > > not specified. > > > > A bulk size can be defined, also a maximum size of active bulk requests > > to cope with high load situations. > > A bulk timeout defines the time period after which bulk feeds continue. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select \* from orders", > > }, > > "index" : { > > "index" : "jdbc", > > "type" : "jdbc", > > "bulk\_size" : 100, > > "max\_bulk\_requests" : 30, > > "bulk\_timeout" : "60s" > > } > > }' > > > > ## Stopping/deleting the river > > > > curl -XDELETE 'localhost:9200/\_river/my\_jdbc\_river/' > > > > Best regards,, > > > > Jörg ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [June 18, 2012, 1:57pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/6 "2012-06-18T13:57:11Z")
For future versions, I am thinking about some incremental update/delete options. I don't know which one will work best. Selected rows from SQL do not have an identity, as required for objects. This leads to the requirement to store object identities, manage these identities (in a different index?), and compare the last saved state of indexed objects with the current state. This is quite resource expensive. An internal Elasticsearch river index could be used as a persistent state memory. More elegant is another option, to let the SQL DB provide object identities. This is possible by using the "\_id" column label, so Elasticsearch river indexing will simply overwrite old objects with new objects. Deletion of objects could be performed in two variants. First, true deletion, i.e. Elasticsearch uses something like delete by query based upon a list of ids. How to maintain such a list of ids is the question: either the SQL DB provides a separate table or an extra SQL request that gives back an id list with timestamps or markers (so the SQL DB must manage additional object identities), or Elasticsearch serves as a persistent state memory (maybe with the help of the version feature?). And second, another variant of deleting object is not to delete documents, but to mark documents as deleted, e.g. "{ "deleted":true }. This is a more straightforward process together with data updates. A housekeeper job could run periodically to clean such deleted documents from the index. Best regards, Jörg On Sunday, June 17, 2012 6:26:21 AM UTC+2, Otis Gospodnetic wrote: > Like David said - I hope you are ready for the avalanche of questions.... > at least I think that's what will happen based on what happened when Solr > got DataImportHandler a few years ago. > > And speaking of DIH, how does one handle deletion of DB rows and how does > one select+index incrementally (only rows that changed since last river > run)? > > ## Thanks, Otis > > Search Analytics - [Cloud Monitoring Tools & Services | Sematext](http://sematext.com/search-analytics/index.html) > Scalable Performance Monitoring - [Sematext Monitoring | Infrastructure Monitoring Service](http://sematext.com/spm/index.html) > > On Saturday, June 16, 2012 4:59:18 PM UTC-4, Jörg Prante wrote: > > > Hi, > > > > I'd like to announce a JDBC river implementation. > > > > It can be found at [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) > > > > I hope it is useful for all of you who need to index data from SQL > > databases into Elasticsearch. > > > > Suggestions, corrections, improvements are welcome! > > > > ## Introduction > > > > The Java Database Connection (JDBC) river allows to select data from JDBC > > sources for indexing into Elasticsearch. > > > > It is implemented as an Elasticsearch plugin. > > > > The relational data is internally transformed into structured JSON > > objects for Elasticsearch schema-less indexing. > > > > Setting it up is as simple as executing something like the following > > against Elasticsearch: > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select \* from orders", > > } > > }' > > > > This HTTP PUT statement will create a river named `my_jdbc_river` > > that fetches all the rows from the `orders` table in the MySQL database > > `test` at `localhost`. > > > > You have to install the JDBC driver jar of your favorite database > > manually into > > the `plugins` directory where the jar file of the JDBC river plugin > > resides. > > > > By default, the JDBC river re-executes the SQL statement on a regular > > basis (60 minutes). > > > > In case of a failover, the JDBC river will automatically be restarted > > on another Elasticsearch node, and continue indexing. > > > > Many JDBC rivers can run in parallel. Each river opens one thread to > > select > > the data. > > > > ## Installation > > > > In order to install the plugin, simply run: `bin/plugin -install jprante/elasticsearch-river-jdbc/1.0.0`. > > > > ## Log example of river creation > > > > [2012-06-16 18:50:10,035][INFO][cluster.metadata] [Anomaly] > > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > > [2012-06-16 18:50:10,046][INFO][river.jdbc] [Anomaly] > > [jdbc][my\_jdbc\_river] starting JDBC connector: URL > > [jdbc:mysql://localhost:3306/test], driver [com.mysql.jdbc.Driver], sql [select > > > > - from orders], indexing to [jdbc]/[jdbc], poll [1h] > > [2012-06-16 18:50:10,129][INFO][cluster.metadata] [Anomaly] > > [jdbc] creating index, cause [api], shards [5]/[1], mappings > > [2012-06-16 18:50:10,353][INFO][cluster.metadata] [Anomaly] > > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > > [2012-06-16 18:50:10,714][INFO][river.jdbc] [Anomaly] > > [jdbc][my\_jdbc\_river] got 5 rows > > [2012-06-16 18:50:10,719][INFO][river.jdbc] [Anomaly] > > [jdbc][my\_jdbc\_river] next run, waiting 1h, URL > > [jdbc:mysql://localhost:3306/test] driver [com.mysql.jdbc.Driver] sql > > [select \* from orders] > > > > # Configuration > > > > The SQL statements used for selecting can be configured as follows. > > > > ## Star query > > > > Star queries are the simplest variant of selecting data. They can be used > > to dump tables into Elasticsearch. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select \* from orders" > > } > > }' > > > > For example > > > > 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) > > > > The JSON objects are flat, the `id` > > of the documents is generated automatically, it is the row number. > > > > 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"} > > > > ## Labeled columns > > > > In SQL, each column may be labeled with a name. This name is used by the > > JDBC river to JSON object construction. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select products.name as "product.name", > > orders.customer as "product.customer.name", orders.quantity \* > > products.price as "product.customer.bill" from products, orders where > > products.name = orders.product" > > } > > }' > > > > In this query, the columns selected are described as `product.name`, > > `product.customer.name`, and `product.customer.bill`. > > > > mysql\> select products.name as "product.name", orders.customer as > > "product.customer", orders.quantity \* products.price as > > "product.customer.bill" from products, orders where products.name = > > orders.product ; > > +--------------+------------------+-----------------------+ > > | product.name | product.customer | product.customer.bill | > > +--------------+------------------+-----------------------+ > > | Apples | Big | 1 | > > | Bananas | Large | 2 | > > | Oranges | Huge | 6 | > > | Apples | Good | 2 | > > | Oranges | Bad | 9 | > > +--------------+------------------+-----------------------+ > > 5 rows in set, 5 warnings (0.00 sec) > > > > The JSON objects are > > > > id=0 {"product":{"name":"Apples","customer":{"bill":1.0,"name":"Big"}}} > > id=1 {"product":{"name":"Bananas","customer":{"bill":2.0,"name":"Large"}}} > > id=2 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > > id=3 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > > id=4 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > > > There are three column labels with an underscore as prefix > > that are mapped to the Elasticsearch index/type/id. > > > > \_id > > \_type > > \_index > > > > ## Structured objects > > > > One of the advantage of SQL queries is the join operation. From many > > tables, new tuples can be formed. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select "relations" as "\_index", orders.customer as > > "\_id", orders.customer as "contact.customer", employees.name as > > "contact.employee" from orders left join employees on > > employees.department = orders.department" > > } > > }' > > > > For example, these rows from SQL > > > > mysql\> select "relations" as "\_index", orders.customer as "\_id", > > orders.customer as "contact.customer", employees.name as > > "contact.employee" from orders left join employees on employees.department > > = orders.department; > > +-----------+-------+------------------+------------------+ > > | \_index | \_id | contact.customer | contact.employee | > > +-----------+-------+------------------+------------------+ > > | relations | Big | Big | Smith | > > | relations | Large | Large | Müller | > > | relations | Large | Large | Meier | > > | relations | Large | Large | Schulze | > > | relations | Huge | Huge | Müller | > > | relations | Huge | Huge | Meier | > > | relations | Huge | Huge | Schulze | > > | relations | Good | Good | Müller | > > | relations | Good | Good | Meier | > > | relations | Good | Good | Schulze | > > | relations | Bad | Bad | Jones | > > +-----------+-------+------------------+------------------+ > > 11 rows in set (0.00 sec) > > > > will generate fewer JSON objects for the index `relations`. > > > > index=relations id=Big {"contact":{"employee":"Smith","customer":"Big"}} > > index=relations id=Large > > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Large"}} > > index=relations id=Huge > > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Huge"}} > > index=relations id=Good > > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Good"}} > > index=relations id=Bad {"contact":{"employee":"Jones","customer":"Bad"}} > > > > Note how the `employee` column is collapsed into a JSON array. The > > repeated occurence of the `_id` column > > controls how values are folded into arrays for making use of the > > Elasticsearch JSON data model. > > > > ## Bind parameter > > > > Bind parameters are useful for selecting rows according to a matching > > condition > > where the match criteria is not known beforehand. > > > > For example, only rows matching certain conditions can be indexed into > > Elasticsearch. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select products.name as "product.name", > > orders.customer as "product.customer.name", orders.quantity \* > > products.price as "product.customer.bill" from products, orders where > > products.name = orders.product and orders.quantity \* products.price \> ?", > > "params: [5.0] > > } > > }' > > > > Example result > > > > id=0 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > > > ## Time-based selecting > > > > Because the JDBC river is running repeatedly, time-based selecting is > > useful. > > The current time is represented by the parameter value `$now`. > > > > In this example, all rows beginning with a certain date up to now are > > selected. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select products.name as "product.name", > > orders.customer as "product.customer.name", orders.quantity \* > > products.price as "product.customer.bill" from products, orders where > > products.name = orders.product and orders.created between ? - 14 and ?", > > "params: [2012-06-01", "$now"] > > } > > }' > > > > Example result: > > > > id=0 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > > > ## Index > > > > Each river can index into a specified index. Example: > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select \* from orders", > > }, > > "index" : { > > "index" : "jdbc", > > "type" : "jdbc" > > } > > }' > > > > ## Bulk indexing > > > > Bulk indexing is automatically used in order to speed up the indexing > > process. > > > > Each SQL result set will be indexed by a single bulk if the bulk size is > > not specified. > > > > A bulk size can be defined, also a maximum size of active bulk requests > > to cope with high load situations. > > A bulk timeout defines the time period after which bulk feeds continue. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select \* from orders", > > }, > > "index" : { > > "index" : "jdbc", > > "type" : "jdbc", > > "bulk\_size" : 100, > > "max\_bulk\_requests" : 30, > > "bulk\_timeout" : "60s" > > } > > }' > > > > ## Stopping/deleting the river > > > > curl -XDELETE 'localhost:9200/\_river/my\_jdbc\_river/' > > > > Best regards,, > > > > Jörg ---
**Author:** ![phoenix](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/phoenix/32/1319_2.png) [@phoenix](https://discuss.elastic.co/u/phoenix)\ **Post date:** [June 18, 2012, 4:09pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/7 "2012-06-18T16:09:27Z")
Really nice ! I'm waiting to see the update process. I think to options for object id should be available : using db id of object (but how to manage compound ids), or let elasticsearch generate one (but how to manage update/insert). Maybe letting the user provide a primary key comparator may be nice, allowing to let elasticsearch handle the id generation but recognize when an object is being updated rather than inserted. Frederic Le samedi 16 juin 2012 22:59:18 UTC+2, Jörg Prante a écrit : > Hi, > > I'd like to announce a JDBC river implementation. > > It can be found at [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) > > I hope it is useful for all of you who need to index data from SQL > databases into Elasticsearch. > > Suggestions, corrections, improvements are welcome! > > ## Introduction > > The Java Database Connection (JDBC) river allows to select data from JDBC > sources for indexing into Elasticsearch. > > It is implemented as an Elasticsearch plugin. > > The relational data is internally transformed into structured JSON objects > for Elasticsearch schema-less indexing. > > Setting it up is as simple as executing something like the following > against Elasticsearch: > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders", > } > }' > > This HTTP PUT statement will create a river named `my_jdbc_river` > that fetches all the rows from the `orders` table in the MySQL database > `test` at `localhost`. > > You have to install the JDBC driver jar of your favorite database manually > into > the `plugins` directory where the jar file of the JDBC river plugin > resides. > > By default, the JDBC river re-executes the SQL statement on a regular > basis (60 minutes). > > In case of a failover, the JDBC river will automatically be restarted > on another Elasticsearch node, and continue indexing. > > Many JDBC rivers can run in parallel. Each river opens one thread to select > the data. > > ## Installation > > In order to install the plugin, simply run: `bin/plugin -install jprante/elasticsearch-river-jdbc/1.0.0`. > > ## Log example of river creation > > [2012-06-16 18:50:10,035][INFO][cluster.metadata] [Anomaly] > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > [2012-06-16 18:50:10,046][INFO][river.jdbc] [Anomaly] > [jdbc][my\_jdbc\_river] starting JDBC connector: URL > [jdbc:mysql://localhost:3306/test], driver [com.mysql.jdbc.Driver], sql [select > > - from orders], indexing to [jdbc]/[jdbc], poll [1h] > [2012-06-16 18:50:10,129][INFO][cluster.metadata] [Anomaly] > [jdbc] creating index, cause [api], shards [5]/[1], mappings > [2012-06-16 18:50:10,353][INFO][cluster.metadata] [Anomaly] > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > [2012-06-16 18:50:10,714][INFO][river.jdbc] [Anomaly] > [jdbc][my\_jdbc\_river] got 5 rows > [2012-06-16 18:50:10,719][INFO][river.jdbc] [Anomaly] > [jdbc][my\_jdbc\_river] next run, waiting 1h, URL > [jdbc:mysql://localhost:3306/test] driver [com.mysql.jdbc.Driver] sql > [select \* from orders] > > # Configuration > > The SQL statements used for selecting can be configured as follows. > > ## Star query > > Star queries are the simplest variant of selecting data. They can be used > to dump tables into Elasticsearch. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders" > } > }' > > For example > > 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) > > The JSON objects are flat, the `id` > of the documents is generated automatically, it is the row number. > > 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"} > > ## Labeled columns > > In SQL, each column may be labeled with a name. This name is used by the > JDBC river to JSON object construction. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select products.name as "product.name", > orders.customer as "product.customer.name", orders.quantity \* > products.price as "product.customer.bill" from products, orders where > products.name = orders.product" > } > }' > > In this query, the columns selected are described as `product.name`, > `product.customer.name`, and `product.customer.bill`. > > mysql\> select products.name as "product.name", orders.customer as > "product.customer", orders.quantity \* products.price as > "product.customer.bill" from products, orders where products.name = > orders.product ; > +--------------+------------------+-----------------------+ > | product.name | product.customer | product.customer.bill | > +--------------+------------------+-----------------------+ > | Apples | Big | 1 | > | Bananas | Large | 2 | > | Oranges | Huge | 6 | > | Apples | Good | 2 | > | Oranges | Bad | 9 | > +--------------+------------------+-----------------------+ > 5 rows in set, 5 warnings (0.00 sec) > > The JSON objects are > > id=0 {"product":{"name":"Apples","customer":{"bill":1.0,"name":"Big"}}} > id=1 {"product":{"name":"Bananas","customer":{"bill":2.0,"name":"Large"}}} > id=2 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > id=3 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > id=4 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > There are three column labels with an underscore as prefix > that are mapped to the Elasticsearch index/type/id. > > \_id > \_type > \_index > > ## Structured objects > > One of the advantage of SQL queries is the join operation. From many > tables, new tuples can be formed. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select "relations" as "\_index", orders.customer as > "\_id", orders.customer as "contact.customer", employees.name as > "contact.employee" from orders left join employees on > employees.department = orders.department" > } > }' > > For example, these rows from SQL > > mysql\> select "relations" as "\_index", orders.customer as "\_id", > orders.customer as "contact.customer", employees.name as > "contact.employee" from orders left join employees on employees.department > = orders.department; > +-----------+-------+------------------+------------------+ > | \_index | \_id | contact.customer | contact.employee | > +-----------+-------+------------------+------------------+ > | relations | Big | Big | Smith | > | relations | Large | Large | Müller | > | relations | Large | Large | Meier | > | relations | Large | Large | Schulze | > | relations | Huge | Huge | Müller | > | relations | Huge | Huge | Meier | > | relations | Huge | Huge | Schulze | > | relations | Good | Good | Müller | > | relations | Good | Good | Meier | > | relations | Good | Good | Schulze | > | relations | Bad | Bad | Jones | > +-----------+-------+------------------+------------------+ > 11 rows in set (0.00 sec) > > will generate fewer JSON objects for the index `relations`. > > index=relations id=Big {"contact":{"employee":"Smith","customer":"Big"}} > index=relations id=Large > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Large"}} > index=relations id=Huge > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Huge"}} > index=relations id=Good > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Good"}} > index=relations id=Bad {"contact":{"employee":"Jones","customer":"Bad"}} > > Note how the `employee` column is collapsed into a JSON array. The > repeated occurence of the `_id` column > controls how values are folded into arrays for making use of the > Elasticsearch JSON data model. > > ## Bind parameter > > Bind parameters are useful for selecting rows according to a matching > condition > where the match criteria is not known beforehand. > > For example, only rows matching certain conditions can be indexed into > Elasticsearch. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select products.name as "product.name", > orders.customer as "product.customer.name", orders.quantity \* > products.price as "product.customer.bill" from products, orders where > products.name = orders.product and orders.quantity \* products.price \> ?", > "params: [5.0] > } > }' > > Example result > > id=0 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > ## Time-based selecting > > Because the JDBC river is running repeatedly, time-based selecting is > useful. > The current time is represented by the parameter value `$now`. > > In this example, all rows beginning with a certain date up to now are > selected. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select products.name as "product.name", > orders.customer as "product.customer.name", orders.quantity \* > products.price as "product.customer.bill" from products, orders where > products.name = orders.product and orders.created between ? - 14 and ?", > "params: [2012-06-01", "$now"] > } > }' > > Example result: > > id=0 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > ## Index > > Each river can index into a specified index. Example: > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders", > }, > "index" : { > "index" : "jdbc", > "type" : "jdbc" > } > }' > > ## Bulk indexing > > Bulk indexing is automatically used in order to speed up the indexing > process. > > Each SQL result set will be indexed by a single bulk if the bulk size is > not specified. > > A bulk size can be defined, also a maximum size of active bulk requests to > cope with high load situations. > A bulk timeout defines the time period after which bulk feeds continue. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders", > }, > "index" : { > "index" : "jdbc", > "type" : "jdbc", > "bulk\_size" : 100, > "max\_bulk\_requests" : 30, > "bulk\_timeout" : "60s" > } > }' > > ## Stopping/deleting the river > > curl -XDELETE 'localhost:9200/\_river/my\_jdbc\_river/' > > Best regards,, > > Jörg ---
**Author:** ![Curt\_Hu](https://avatars.discourse-cdn.com/v4/letter/c/ebca7d/32.png) [@Curt\_Hu](https://discuss.elastic.co/u/Curt_Hu)\ **Post date:** [June 19, 2012, 3:14pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/8 "2012-06-19T15:14:02Z")
Hi, I wonder after loading some data into Elastic, how to query the indices, do we have some examples? Thanks ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [June 20, 2012, 8:47pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/9 "2012-06-20T20:47:20Z")
Right now, I'm heading towards the versioning feature of Elasticsearch to get used by the JDBC river. Basic idea is, let the river first run, and set version of all indexed ES documents to 1, then save this version to a river internal index, before waiting for the next run. Subsequent river runs are updates and will use version 2,3 ... and so on. And, at the end of each cycle, the documents in the JDBC index with version of "version - 1" (one lower than the current version) are deleted, making only the current state of the SQL DB being indexed and searchable. Best regards, Jörg On Monday, June 18, 2012 6:09:27 PM UTC+2, Frederic Esnault wrote: > Really nice ! > > I'm waiting to see the update process. > I think to options for object id should be available : using db id of > object (but how to manage compound ids), or let elasticsearch generate one > (but how to manage update/insert). > Maybe letting the user provide a primary key comparator may be nice, > allowing to let elasticsearch handle the id generation but recognize when > an object is being updated rather than inserted. > > Frederic ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [June 21, 2012, 9:52am UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/10 "2012-06-21T09:52:23Z")
Hi Jörg, Have you seen this thread : [https://groups.google.com/forum/?hl=fr&fromgroups#!topic/elasticsearch/A4791hc\_x2Q](https://groups.google.com/forum/?hl=fr&fromgroups#!topic/elasticsearch/A4791hc_x2Q) ? You can not sort on \_version. I don't know if you can search on \_version ? If not, that means that you will have to make a scan & scroll on each doc on ES side to detect the version ? It could be a huge overhead for the river... David. Le mercredi 20 juin 2012 22:47:20 UTC+2, Jörg Prante a écrit : > Right now, I'm heading towards the versioning feature of Elasticsearch to > get used by the JDBC river. > > Basic idea is, let the river first run, and set version of all indexed ES > documents to 1, then save this version to a river internal index, before > waiting for the next run. Subsequent river runs are updates and will use > version 2,3 ... and so on. And, at the end of each cycle, the documents in > the JDBC index with version of "version - 1" (one lower than the current > version) are deleted, making only the current state of the SQL DB being > indexed and searchable. > > Best regards, > > Jörg > > On Monday, June 18, 2012 6:09:27 PM UTC+2, Frederic Esnault wrote: > > > Really nice ! > > > > I'm waiting to see the update process. > > I think to options for object id should be available : using db id of > > object (but how to manage compound ids), or let elasticsearch generate one > > (but how to manage update/insert). > > Maybe letting the user provide a primary key comparator may be nice, > > allowing to let elasticsearch handle the id generation but recognize when > > an object is being updated rather than inserted. > > > > Frederic ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [June 22, 2012, 12:57pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/11 "2012-06-22T12:57:27Z")
Hi, a new version of the JDBC river is available. Version 1.1.0 comes with an update/delete feature and fixes an issue with the JDBC username. The update/delete feature comes with an overhead of scanning through the index when the SQL DB has changed data relevant to the JSON object ID set. More information at [https://github.com/jprante/elasticsearch-river-jdbc](https://github.com/jprante/elasticsearch-river-jdbc) Best regards, Jörg ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [June 22, 2012, 1:01pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/12 "2012-06-22T13:01:53Z")
Yes, unfortunately it is a huge overhead. Improvements are welcome. There is one option not implemented, where the SQL DB could manage updates/deletes in a special table on its own. This option would enforce structural changes at the side of the SQL DB which is often not possible. Consider DBA team opinions being different from the search engineer team opinions. Best regards, Jörg On Thursday, June 21, 2012 11:52:23 AM UTC+2, David Pilato wrote: > Hi Jörg, > > Have you seen this thread : > [Rediriger vers Google Groupes](https://groups.google.com/forum/?hl=fr&fromgroups#!topic/elasticsearch/A4791hc_x2Q) > ? > > You can not sort on \_version. I don't know if you can search on \_version ? > If not, that means that you will have to make a scan & scroll on each doc > on ES side to detect the version ? It could be a huge overhead for the > river... > > David. > > Le mercredi 20 juin 2012 22:47:20 UTC+2, Jörg Prante a écrit : > > > Right now, I'm heading towards the versioning feature of Elasticsearch to > > get used by the JDBC river. > > > > Basic idea is, let the river first run, and set version of all indexed ES > > documents to 1, then save this version to a river internal index, before > > waiting for the next run. Subsequent river runs are updates and will use > > version 2,3 ... and so on. And, at the end of each cycle, the documents in > > the JDBC index with version of "version - 1" (one lower than the current > > version) are deleted, making only the current state of the SQL DB being > > indexed and searchable. > > > > Best regards, > > > > Jörg > > > > On Monday, June 18, 2012 6:09:27 PM UTC+2, Frederic Esnault wrote: > > > > > Really nice ! > > > > > > I'm waiting to see the update process. > > > I think to options for object id should be available : using db id of > > > object (but how to manage compound ids), or let elasticsearch generate one > > > (but how to manage update/insert). > > > Maybe letting the user provide a primary key comparator may be nice, > > > allowing to let elasticsearch handle the id generation but recognize when > > > an object is being updated rather than inserted. > > > > > > Frederic ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [June 22, 2012, 1:27pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/13 "2012-06-22T13:27:32Z")
Yes. I think that it could be a nice option (not mandatory) for those that can provide such a table. For others, there will always have some overhead... As you said, it will be a debate between teams without the same interest. David. Le 22 juin 2012 à 15:01, "Jörg Prante" [joergprante@gmail.com](mailto:joergprante@gmail.com) a écrit : > Yes, unfortunately it is a huge overhead. Improvements are welcome. > > There is one option not implemented, where the SQL DB could manage > updates/deletes in a special table on its own. This option would enforce > structural changes at the side of the SQL DB which is often not possible. > Consider DBA team opinions being different from the search engineer team > opinions. > > Best regards, > > Jörg > > On Thursday, June 21, 2012 11:52:23 AM UTC+2, David Pilato wrote: > > > > Hi Jörg, > > > > Have you seen this thread : > > [Rediriger vers Google Groupes](https://groups.google.com/forum/?hl=fr&fromgroups#!topic/elasticsearch/A4791hc_x2Q) > > ? > > [https://groups.google.com/forum/?hl=fr&fromgroups#!topic/elasticsearch/A4791hc\_x2Q](https://groups.google.com/forum/?hl=fr&fromgroups#!topic/elasticsearch/A4791hc_x2Q) > > > > You can not sort on \_version. I don't know if you can search on \_version > > ? > > If not, that means that you will have to make a scan & scroll on each doc > > on ES side to detect the version ? It could be a huge overhead for the > > river... > > > > David. > > > > Le mercredi 20 juin 2012 22:47:20 UTC+2, Jörg Prante a écrit : > > \> \> \> Right now, I'm heading towards the versioning feature of > > \> \> \> Elasticsearch to get used by the JDBC river. > > > > > ``` > > > Basic idea is, let the river first run, and set version of all > > > > > > ``` > > > > > > indexed ES documents to 1, then save this version to a river internal > > > index, before waiting for the next run. Subsequent river runs are updates > > > and will use version 2,3 ... and so on. And, at the end of each cycle, the > > > documents in the JDBC index with version of "version - 1" (one lower than > > > the current version) are deleted, making only the current state of the SQL > > > DB being indexed and searchable. > > > > > > ``` > > > Best regards, > > > > > > Jörg > > > > > > On Monday, June 18, 2012 6:09:27 PM UTC+2, Frederic Esnault wrote: > > > > > > > Really nice ! > > > > > > ``` > > > > > > > ``` > > > > I'm waiting to see the update process. > > > > I think to options for object id should be available : using db > > > > > > > > ``` > > > > > > > > id of object (but how to manage compound ids), or let elasticsearch > > > > generate one (but how to manage update/insert). > > > > Maybe letting the user provide a primary key comparator may be > > > > nice, allowing to let elasticsearch handle the id generation but > > > > recognize when an object is being updated rather than inserted. > > > > > > > > ``` > > > > Frederic > > > > > > > > > > > > > > > > > > > > > > ``` [https://groups.google.com/forum/?hl=fr&fromgroups#!topic/elasticsearch/A4791hc\_x2Q](https://groups.google.com/forum/?hl=fr&fromgroups#!topic/elasticsearch/A4791hc_x2Q) -- David Pilato [http://dev.david.pilato.fr/](http://dev.david.pilato.fr/) Twitter : @dadoonet ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [June 23, 2012, 4:38pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/14 "2012-06-23T16:38:34Z")
Yes, I am thinking about a handshake mechanism so the SQL DB gets noticed about successful Elasticsearch river runs, with three separate channels: new objects, modified objects, and deleted objects. Each channel will provide the ID sets so updating them in ES is straightforward. It will require additional SQL statement configuration of the river and a special "object indexing table" at the SQL DB side. Best regards, Jörg On Friday, June 22, 2012 3:27:32 PM UTC+2, David Pilato wrote: > Yes. I think that it could be a nice option (not mandatory) for those > that can provide such a table. > > For others, there will always have some overhead... As you said, it will > be a debate between teams without the same interest. ---
**Author:** ![dgabm](https://avatars.discourse-cdn.com/v4/letter/d/96bed5/32.png) [@dgabm](https://discuss.elastic.co/u/dgabm)\ **Post date:** [January 25, 2013, 10:50am UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/15 "2013-01-25T10:50:47Z")
Hi Jörg, 1)I' m using a Oracle XE server on a win7 x64, where I've done: 1.1)unzip elasticsearch-0.20.2.zip 1.2)copy ojdbc14.jar to %ES\_HOME%/lib/ojdbc14.jar 1.3)./bin/plugin -url [http://bit.ly/U75w1N](http://bit.ly/U75w1N) -install river-jdbc 1. Within oracle squirrel client: 2.1)Create Table ORDERS( ORDER\_ID Number(7) Primary Key, description VARCHAR2(100)); Create Table OPTIONS( OPTION\_ID Number(7) Primary Key, order\_id Number(7), option\_name VARCHAR2(50), CONSTRAINT fk\_order FOREIGN KEY (order\_id) REFERENCES ORDERS(order\_id)) INSERT INTO ORDERS(ORDER\_ID, description) values(1, '1st order'); INSERT INTO OPTIONS(OPTION\_ID, ORDER\_ID, option\_name) values(1, 1, '1st order option1'); INSERT INTO OPTIONS(OPTION\_ID, ORDER\_ID, option\_name) values(2, 1, '1st order option2'); 2.2)sqplus-\> SQL\> select \* from orders; ORDER\_ID DESCRIPTION * * * ``` 1 1st order ``` SQL\> select \* from options; OPTION\_ID ORDER\_ID OPTION\_NAME * * * ``` 1 1 1st order option1 2 1 1st order option2 ``` SQL\> select 'order' as "\_index", ord.order\_id as "\_id",ord.order\_id as "order.oId",option\_name as "order.options" from orders ord inner join options opt on ord.order\_id = opt.order\_id; \_index \_id order.oId order.options * * * order 1 1 1st order option1 order 1 1 1st order option2 1. On a ubuntu VM box, hosted on the same win7 x64, I executed: ubuntu@ubuntu-VirtualBox:~$ uname -a Linux ubuntu-VirtualBox 3.2.0-24-generic-pae #37-Ubuntu SMP Wed Apr 25 10:47:59 UTC 2012 i686 i686 i386 GNU/Linux ubuntu@ubuntu-VirtualBox:~$ curl -XPUT '192.168.56.1:9200/\_river/my\_jdbc\_river/\_meta' -d '{ "type" : "jdbc", "jdbc" : { "driver" : "oracle.jdbc.OracleDriver", "url" : "jdbc:oracle:thin:@localhost:1521/XE", "user" : "", "password" : "", "poll" : "10s", "sql" : "select \u0027order\u0027 as "\_index", ord.order\_id as "\_id",ord.order\_id as "order.oId",option\_name as "order.option" from orders ord inner join options opt on ord.order\_id = opt.order\_id" } }' 4)I'm searching for: ubuntu@ubuntu-VirtualBox:~$ curl -XGET '192.168.56.1:9200/order/jdbc/\_search?pretty' { "took" : 2, "timed\_out" : false, "\_shards" : { "total" : 5, "successful" : 5, "failed" : 0 }, "hits" : { "total" : 1, "max\_score" : 1.0, "hits" : [ { "\_index" : "order", "\_type" : "jdbc", "\_id" : "1", "\_score" : 1.0, "\_source" : {"order":{"oId":1,"option":"1st order option2"},"\_id":1,"\_index":"order"} } ] } Obviously is fetching only the 2nd record from the one-to-many relation, the "1st order option1" is missing...where I've done wrong? 5)But I would like to have the following result: "\_score" : 1.0, "\_source" : {"order":{"oId":1,"options":["1st order option1","1st order option2"]},"\_id":1,"\_index":"order"} that should be an equivalent result from your example: index=relations id=Good {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Good"}} Can you please help me?with any hint? Thanks GM ---
**Author:** ![Vasanth](https://avatars.discourse-cdn.com/v4/letter/v/85e7bf/32.png) [@Vasanth](https://discuss.elastic.co/u/Vasanth)\ **Post date:** [June 28, 2013, 1:48pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/16 "2013-06-28T13:48:58Z")
Hi I would like to introduce myself to this site...I am beginner to this topic so kindly get me into this to become a good.. 1.First of all i need to know abt few lines regd Elastic Search like can we use it for all kind of java application (WebApplication and desktop application) If so clarify me how ? 2.I could in get proper vision how to integrate this elastic search in my java swing application.(JDBC Appl) 3.What is meant by curl here ? How to Use this ? what are the necessary to be initiated ? Note : Give me one small example how ths Elastic search adapted. Can i develop the same in Eclipse ? How ?Available sw or plugins ? Rds, Vasanth ---
**Author:** ![Santosh\_B](https://avatars.discourse-cdn.com/v4/letter/s/ea666f/32.png) [@Santosh\_B](https://discuss.elastic.co/u/Santosh_B)\ **Post date:** [July 28, 2014, 11:27am UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/17 "2014-07-28T11:27:56Z")
Hi, Its a very good feature. I was trying to use JDBC driver to import from hive/Impala but it never works whereas mysql connector works perfectly fine. Is it something it was specifically designed to work for mysql,MSSQl...and few of them or any other databases which supports JDBC. Thanks, Santosh B On Sunday, 17 June 2012 02:29:18 UTC+5:30, Jörg Prante wrote: > Hi, > > I'd like to announce a JDBC river implementation. > > It can be found at [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) > > I hope it is useful for all of you who need to index data from SQL > databases into Elasticsearch. > > Suggestions, corrections, improvements are welcome! > > ## Introduction > > The Java Database Connection (JDBC) river allows to select data from JDBC > sources for indexing into Elasticsearch. > > It is implemented as an Elasticsearch plugin. > > The relational data is internally transformed into structured JSON objects > for Elasticsearch schema-less indexing. > > Setting it up is as simple as executing something like the following > against Elasticsearch: > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders", > } > }' > > This HTTP PUT statement will create a river named `my_jdbc_river` > that fetches all the rows from the `orders` table in the MySQL database > `test` at `localhost`. > > You have to install the JDBC driver jar of your favorite database manually > into > the `plugins` directory where the jar file of the JDBC river plugin > resides. > > By default, the JDBC river re-executes the SQL statement on a regular > basis (60 minutes). > > In case of a failover, the JDBC river will automatically be restarted > on another Elasticsearch node, and continue indexing. > > Many JDBC rivers can run in parallel. Each river opens one thread to select > the data. > > ## Installation > > In order to install the plugin, simply run: `bin/plugin -install jprante/elasticsearch-river-jdbc/1.0.0`. > > ## Log example of river creation > > [2012-06-16 18:50:10,035][INFO][cluster.metadata] [Anomaly] > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > [2012-06-16 18:50:10,046][INFO][river.jdbc] [Anomaly] > [jdbc][my\_jdbc\_river] starting JDBC connector: URL > [jdbc:mysql://localhost:3306/test], driver [com.mysql.jdbc.Driver], sql [select > > - from orders], indexing to [jdbc]/[jdbc], poll [1h] > [2012-06-16 18:50:10,129][INFO][cluster.metadata] [Anomaly] > [jdbc] creating index, cause [api], shards [5]/[1], mappings > [2012-06-16 18:50:10,353][INFO][cluster.metadata] [Anomaly] > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > [2012-06-16 18:50:10,714][INFO][river.jdbc] [Anomaly] > [jdbc][my\_jdbc\_river] got 5 rows > [2012-06-16 18:50:10,719][INFO][river.jdbc] [Anomaly] > [jdbc][my\_jdbc\_river] next run, waiting 1h, URL > [jdbc:mysql://localhost:3306/test] driver [com.mysql.jdbc.Driver] sql > [select \* from orders] > > # Configuration > > The SQL statements used for selecting can be configured as follows. > > ## Star query > > Star queries are the simplest variant of selecting data. They can be used > to dump tables into Elasticsearch. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders" > } > }' > > For example > > 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) > > The JSON objects are flat, the `id` > of the documents is generated automatically, it is the row number. > > 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"} > > ## Labeled columns > > In SQL, each column may be labeled with a name. This name is used by the > JDBC river to JSON object construction. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select products.name as "product.name", > orders.customer as "product.customer.name", orders.quantity \* > products.price as "product.customer.bill" from products, orders where > products.name = orders.product" > } > }' > > In this query, the columns selected are described as `product.name`, > `product.customer.name`, and `product.customer.bill`. > > mysql\> select products.name as "product.name", orders.customer as > "product.customer", orders.quantity \* products.price as > "product.customer.bill" from products, orders where products.name = > orders.product ; > +--------------+------------------+-----------------------+ > | product.name | product.customer | product.customer.bill | > +--------------+------------------+-----------------------+ > | Apples | Big | 1 | > | Bananas | Large | 2 | > | Oranges | Huge | 6 | > | Apples | Good | 2 | > | Oranges | Bad | 9 | > +--------------+------------------+-----------------------+ > 5 rows in set, 5 warnings (0.00 sec) > > The JSON objects are > > id=0 {"product":{"name":"Apples","customer":{"bill":1.0,"name":"Big"}}} > id=1 {"product":{"name":"Bananas","customer":{"bill":2.0,"name":"Large"}}} > id=2 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > id=3 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > id=4 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > There are three column labels with an underscore as prefix > that are mapped to the Elasticsearch index/type/id. > > \_id > \_type > \_index > > ## Structured objects > > One of the advantage of SQL queries is the join operation. From many > tables, new tuples can be formed. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select "relations" as "\_index", orders.customer as > "\_id", orders.customer as "contact.customer", employees.name as > "contact.employee" from orders left join employees on > employees.department = orders.department" > } > }' > > For example, these rows from SQL > > mysql\> select "relations" as "\_index", orders.customer as "\_id", > orders.customer as "contact.customer", employees.name as > "contact.employee" from orders left join employees on employees.department > = orders.department; > +-----------+-------+------------------+------------------+ > | \_index | \_id | contact.customer | contact.employee | > +-----------+-------+------------------+------------------+ > | relations | Big | Big | Smith | > | relations | Large | Large | Müller | > | relations | Large | Large | Meier | > | relations | Large | Large | Schulze | > | relations | Huge | Huge | Müller | > | relations | Huge | Huge | Meier | > | relations | Huge | Huge | Schulze | > | relations | Good | Good | Müller | > | relations | Good | Good | Meier | > | relations | Good | Good | Schulze | > | relations | Bad | Bad | Jones | > +-----------+-------+------------------+------------------+ > 11 rows in set (0.00 sec) > > will generate fewer JSON objects for the index `relations`. > > index=relations id=Big {"contact":{"employee":"Smith","customer":"Big"}} > index=relations id=Large > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Large"}} > index=relations id=Huge > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Huge"}} > index=relations id=Good > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Good"}} > index=relations id=Bad {"contact":{"employee":"Jones","customer":"Bad"}} > > Note how the `employee` column is collapsed into a JSON array. The > repeated occurence of the `_id` column > controls how values are folded into arrays for making use of the > Elasticsearch JSON data model. > > ## Bind parameter > > Bind parameters are useful for selecting rows according to a matching > condition > where the match criteria is not known beforehand. > > For example, only rows matching certain conditions can be indexed into > Elasticsearch. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select products.name as "product.name", > orders.customer as "product.customer.name", orders.quantity \* > products.price as "product.customer.bill" from products, orders where > products.name = orders.product and orders.quantity \* products.price \> ?", > "params: [5.0] > } > }' > > Example result > > id=0 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > ## Time-based selecting > > Because the JDBC river is running repeatedly, time-based selecting is > useful. > The current time is represented by the parameter value `$now`. > > In this example, all rows beginning with a certain date up to now are > selected. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select products.name as "product.name", > orders.customer as "product.customer.name", orders.quantity \* > products.price as "product.customer.bill" from products, orders where > products.name = orders.product and orders.created between ? - 14 and ?", > "params: [2012-06-01", "$now"] > } > }' > > Example result: > > id=0 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > ## Index > > Each river can index into a specified index. Example: > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders", > }, > "index" : { > "index" : "jdbc", > "type" : "jdbc" > } > }' > > ## Bulk indexing > > Bulk indexing is automatically used in order to speed up the indexing > process. > > Each SQL result set will be indexed by a single bulk if the bulk size is > not specified. > > A bulk size can be defined, also a maximum size of active bulk requests to > cope with high load situations. > A bulk timeout defines the time period after which bulk feeds continue. > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/test", > "user" : "", > "password" : "", > "sql" : "select \* from orders", > }, > "index" : { > "index" : "jdbc", > "type" : "jdbc", > "bulk\_size" : 100, > "max\_bulk\_requests" : 30, > "bulk\_timeout" : "60s" > } > }' > > ## Stopping/deleting the river > > curl -XDELETE 'localhost:9200/\_river/my\_jdbc\_river/' > > Best regards,, > > Jörg -- 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](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/17dc1eed-3a7c-4b7c-992d-bd0c65b2e3ff%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/17dc1eed-3a7c-4b7c-992d-bd0c65b2e3ff%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [July 28, 2014, 12:46pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/18 "2014-07-28T12:46:32Z")
From the docs [https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients](https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients) I conclude that Hive2 is not a JDBC Type 4 driver. Only JDBC Type 4 drivers are supported by JDBC plugin. JDBC Type 4 does no longer need Class.forName. Jörg On Mon, Jul 28, 2014 at 1:27 PM, Santosh B [contactsantoshb@gmail.com](mailto:contactsantoshb@gmail.com) wrote: > Hi, > Its a very good feature. > I was trying to use JDBC driver to import from hive/Impala but it never > works whereas mysql connector works perfectly fine. > Is it something it was specifically designed to work for mysql,MSSQl...and > few of them or any other databases which supports JDBC. > > Thanks, > Santosh B > > On Sunday, 17 June 2012 02:29:18 UTC+5:30, Jörg Prante wrote: > > > Hi, > > > > I'd like to announce a JDBC river implementation. > > > > It can be found at [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) > > > > I hope it is useful for all of you who need to index data from SQL > > databases into Elasticsearch. > > > > Suggestions, corrections, improvements are welcome! > > > > ## Introduction > > > > The Java Database Connection (JDBC) river allows to select data from JDBC > > sources for indexing into Elasticsearch. > > > > It is implemented as an Elasticsearch plugin. > > > > The relational data is internally transformed into structured JSON > > objects for Elasticsearch schema-less indexing. > > > > Setting it up is as simple as executing something like the following > > against Elasticsearch: > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select \* from orders", > > } > > }' > > > > This HTTP PUT statement will create a river named `my_jdbc_river` > > that fetches all the rows from the `orders` table in the MySQL database > > `test` at `localhost`. > > > > You have to install the JDBC driver jar of your favorite database > > manually into > > the `plugins` directory where the jar file of the JDBC river plugin > > resides. > > > > By default, the JDBC river re-executes the SQL statement on a regular > > basis (60 minutes). > > > > In case of a failover, the JDBC river will automatically be restarted > > on another Elasticsearch node, and continue indexing. > > > > Many JDBC rivers can run in parallel. Each river opens one thread to > > select > > the data. > > > > ## Installation > > > > In order to install the plugin, simply run: `bin/plugin -install jprante/elasticsearch-river-jdbc/1.0.0`. > > > > ## Log example of river creation > > > > [2012-06-16 18:50:10,035][INFO][cluster.metadata] [Anomaly] > > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > > [2012-06-16 18:50:10,046][INFO][river.jdbc] [Anomaly] > > [jdbc][my\_jdbc\_river] starting JDBC connector: URL > > [jdbc:mysql://localhost:3306/test], driver [com.mysql.jdbc.Driver], sql [select > > > > - from orders], indexing to [jdbc]/[jdbc], poll [1h] > > [2012-06-16 18:50:10,129][INFO][cluster.metadata] [Anomaly] > > [jdbc] creating index, cause [api], shards [5]/[1], mappings > > [2012-06-16 18:50:10,353][INFO][cluster.metadata] [Anomaly] > > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > > [2012-06-16 18:50:10,714][INFO][river.jdbc] [Anomaly] > > [jdbc][my\_jdbc\_river] got 5 rows > > [2012-06-16 18:50:10,719][INFO][river.jdbc] [Anomaly] > > [jdbc][my\_jdbc\_river] next run, waiting 1h, URL > > [jdbc:mysql://localhost:3306/test] driver [com.mysql.jdbc.Driver] sql > > [select \* from orders] > > > > # Configuration > > > > The SQL statements used for selecting can be configured as follows. > > > > ## Star query > > > > Star queries are the simplest variant of selecting data. They can be used > > to dump tables into Elasticsearch. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select \* from orders" > > } > > }' > > > > For example > > > > 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) > > > > The JSON objects are flat, the `id` > > of the documents is generated automatically, it is the row number. > > > > 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"} > > > > ## Labeled columns > > > > In SQL, each column may be labeled with a name. This name is used by the > > JDBC river to JSON object construction. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select products.name as "product.name", > > orders.customer as "product.customer.name", orders.quantity \* > > products.price as "product.customer.bill" from products, orders where > > products.name = orders.product" > > } > > }' > > > > In this query, the columns selected are described as `product.name`, > > `product.customer.name`, and `product.customer.bill`. > > > > mysql\> select products.name as "product.name", orders.customer as > > "product.customer", orders.quantity \* products.price as > > "product.customer.bill" from products, orders where products.name = > > orders.product ; > > +--------------+------------------+-----------------------+ > > | product.name | product.customer | product.customer.bill | > > +--------------+------------------+-----------------------+ > > | Apples | Big | 1 | > > | Bananas | Large | 2 | > > | Oranges | Huge | 6 | > > | Apples | Good | 2 | > > | Oranges | Bad | 9 | > > +--------------+------------------+-----------------------+ > > 5 rows in set, 5 warnings (0.00 sec) > > > > The JSON objects are > > > > id=0 {"product":{"name":"Apples","customer":{"bill":1.0,"name":"Big"}}} > > id=1 {"product":{"name":"Bananas","customer":{"bill":2.0,"name":" > > Large"}}} > > id=2 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > > id=3 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > > id=4 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > > > There are three column labels with an underscore as prefix > > that are mapped to the Elasticsearch index/type/id. > > > > \_id > > \_type > > \_index > > > > ## Structured objects > > > > One of the advantage of SQL queries is the join operation. From many > > tables, new tuples can be formed. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select "relations" as "\_index", orders.customer as > > "\_id", orders.customer as "contact.customer", employees.name as > > "contact.employee" from orders left join employees on > > employees.department = orders.department" > > } > > }' > > > > For example, these rows from SQL > > > > mysql\> select "relations" as "\_index", orders.customer as "\_id", > > orders.customer as "contact.customer", employees.name as > > "contact.employee" from orders left join employees on employees.department > > = orders.department; > > +-----------+-------+------------------+------------------+ > > | \_index | \_id | contact.customer | contact.employee | > > +-----------+-------+------------------+------------------+ > > | relations | Big | Big | Smith | > > | relations | Large | Large | Müller | > > | relations | Large | Large | Meier | > > | relations | Large | Large | Schulze | > > | relations | Huge | Huge | Müller | > > | relations | Huge | Huge | Meier | > > | relations | Huge | Huge | Schulze | > > | relations | Good | Good | Müller | > > | relations | Good | Good | Meier | > > | relations | Good | Good | Schulze | > > | relations | Bad | Bad | Jones | > > +-----------+-------+------------------+------------------+ > > 11 rows in set (0.00 sec) > > > > will generate fewer JSON objects for the index `relations`. > > > > index=relations id=Big {"contact":{"employee":"Smith","customer":"Big"}} > > index=relations id=Large {"contact":{"employee":[" > > Müller","Meier","Schulze"],"customer":"Large"}} > > index=relations id=Huge {"contact":{"employee":[" > > Müller","Meier","Schulze"],"customer":"Huge"}} > > index=relations id=Good {"contact":{"employee":[" > > Müller","Meier","Schulze"],"customer":"Good"}} > > index=relations id=Bad {"contact":{"employee":"Jones","customer":"Bad"}} > > > > Note how the `employee` column is collapsed into a JSON array. The > > repeated occurence of the `_id` column > > controls how values are folded into arrays for making use of the > > Elasticsearch JSON data model. > > > > ## Bind parameter > > > > Bind parameters are useful for selecting rows according to a matching > > condition > > where the match criteria is not known beforehand. > > > > For example, only rows matching certain conditions can be indexed into > > Elasticsearch. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select products.name as "product.name", > > orders.customer as "product.customer.name", orders.quantity \* > > products.price as "product.customer.bill" from products, orders where > > products.name = orders.product and orders.quantity \* products.price \> ?", > > "params: [5.0] > > } > > }' > > > > Example result > > > > id=0 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}} > > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > > > ## Time-based selecting > > > > Because the JDBC river is running repeatedly, time-based selecting is > > useful. > > The current time is represented by the parameter value `$now`. > > > > In this example, all rows beginning with a certain date up to now are > > selected. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select products.name as "product.name", > > orders.customer as "product.customer.name", orders.quantity \* > > products.price as "product.customer.bill" from products, orders where > > products.name = orders.product and orders.created between ? - 14 and ?", > > "params: [2012-06-01", "$now"] > > } > > }' > > > > Example result: > > > > id=0 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > > > ## Index > > > > Each river can index into a specified index. Example: > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select \* from orders", > > }, > > "index" : { > > "index" : "jdbc", > > "type" : "jdbc" > > } > > }' > > > > ## Bulk indexing > > > > Bulk indexing is automatically used in order to speed up the indexing > > process. > > > > Each SQL result set will be indexed by a single bulk if the bulk size is > > not specified. > > > > A bulk size can be defined, also a maximum size of active bulk requests > > to cope with high load situations. > > A bulk timeout defines the time period after which bulk feeds continue. > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select \* from orders", > > }, > > "index" : { > > "index" : "jdbc", > > "type" : "jdbc", > > "bulk\_size" : 100, > > "max\_bulk\_requests" : 30, > > "bulk\_timeout" : "60s" > > } > > }' > > > > ## Stopping/deleting the river > > > > curl -XDELETE 'localhost:9200/\_river/my\_jdbc\_river/' > > > > Best regards,, > > > > Jörg > > > > -- > > 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](mailto:elasticsearch+unsubscribe@googlegroups.com). > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/17dc1eed-3a7c-4b7c-992d-bd0c65b2e3ff%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/17dc1eed-3a7c-4b7c-992d-bd0c65b2e3ff%40googlegroups.com) > > [https://groups.google.com/d/msgid/elasticsearch/17dc1eed-3a7c-4b7c-992d-bd0c65b2e3ff%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/17dc1eed-3a7c-4b7c-992d-bd0c65b2e3ff%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- 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](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHRZw-sFaPrR6VmJqNDFUtRMTYuKHA6h1\_sukWZsZWWMA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHRZw-sFaPrR6VmJqNDFUtRMTYuKHA6h1_sukWZsZWWMA%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Santosh\_B](https://avatars.discourse-cdn.com/v4/letter/s/ea666f/32.png) [@Santosh\_B](https://discuss.elastic.co/u/Santosh_B)\ **Post date:** [July 28, 2014, 1:59pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/19 "2014-07-28T13:59:37Z")
Thanks a lot for sharing.... On Mon, Jul 28, 2014 at 6:16 PM, [joergprante@gmail.com](mailto:joergprante@gmail.com) \< [joergprante@gmail.com](mailto:joergprante@gmail.com)\> wrote: > From the docs > > [HiveServer2 Clients - Apache Hive - Apache Software Foundation](https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients) > > I conclude that Hive2 is not a JDBC Type 4 driver. Only JDBC Type 4 > drivers are supported by JDBC plugin. JDBC Type 4 does no longer need > Class.forName. > > Jörg > > On Mon, Jul 28, 2014 at 1:27 PM, Santosh B [contactsantoshb@gmail.com](mailto:contactsantoshb@gmail.com) > wrote: > > > Hi, > > Its a very good feature. > > I was trying to use JDBC driver to import from hive/Impala but it never > > works whereas mysql connector works perfectly fine. > > Is it something it was specifically designed to work for > > mysql,MSSQl...and few of them or any other databases which supports JDBC. > > > > Thanks, > > Santosh B > > > > On Sunday, 17 June 2012 02:29:18 UTC+5:30, Jörg Prante wrote: > > > > > Hi, > > > > > > I'd like to announce a JDBC river implementation. > > > > > > It can be found at [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) > > > > > > I hope it is useful for all of you who need to index data from SQL > > > databases into Elasticsearch. > > > > > > Suggestions, corrections, improvements are welcome! > > > > > > ## Introduction > > > > > > The Java Database Connection (JDBC) river allows to select data from > > > JDBC sources for indexing into Elasticsearch. > > > > > > It is implemented as an Elasticsearch plugin. > > > > > > The relational data is internally transformed into structured JSON > > > objects for Elasticsearch schema-less indexing. > > > > > > Setting it up is as simple as executing something like the following > > > against Elasticsearch: > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > "type" : "jdbc", > > > "jdbc" : { > > > "driver" : "com.mysql.jdbc.Driver", > > > "url" : "jdbc:mysql://localhost:3306/test", > > > "user" : "", > > > "password" : "", > > > "sql" : "select \* from orders", > > > } > > > }' > > > > > > This HTTP PUT statement will create a river named `my_jdbc_river` > > > that fetches all the rows from the `orders` table in the MySQL database > > > `test` at `localhost`. > > > > > > You have to install the JDBC driver jar of your favorite database > > > manually into > > > the `plugins` directory where the jar file of the JDBC river plugin > > > resides. > > > > > > By default, the JDBC river re-executes the SQL statement on a regular > > > basis (60 minutes). > > > > > > In case of a failover, the JDBC river will automatically be restarted > > > on another Elasticsearch node, and continue indexing. > > > > > > Many JDBC rivers can run in parallel. Each river opens one thread to > > > select > > > the data. > > > > > > ## Installation > > > > > > In order to install the plugin, simply run: `bin/plugin -install jprante/elasticsearch-river-jdbc/1.0.0`. > > > > > > ## Log example of river creation > > > > > > [2012-06-16 18:50:10,035][INFO][cluster.metadata] [Anomaly] > > > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > > > [2012-06-16 18:50:10,046][INFO][river.jdbc] [Anomaly] > > > [jdbc][my\_jdbc\_river] starting JDBC connector: URL > > > [jdbc:mysql://localhost:3306/test], driver [com.mysql.jdbc.Driver], sql [select > > > > > > - from orders], indexing to [jdbc]/[jdbc], poll [1h] > > > [2012-06-16 18:50:10,129][INFO][cluster.metadata] [Anomaly] > > > [jdbc] creating index, cause [api], shards [5]/[1], mappings > > > [2012-06-16 18:50:10,353][INFO][cluster.metadata] [Anomaly] > > > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > > > [2012-06-16 18:50:10,714][INFO][river.jdbc] [Anomaly] > > > [jdbc][my\_jdbc\_river] got 5 rows > > > [2012-06-16 18:50:10,719][INFO][river.jdbc] [Anomaly] > > > [jdbc][my\_jdbc\_river] next run, waiting 1h, URL > > > [jdbc:mysql://localhost:3306/test] driver [com.mysql.jdbc.Driver] sql > > > [select \* from orders] > > > > > > # Configuration > > > > > > The SQL statements used for selecting can be configured as follows. > > > > > > ## Star query > > > > > > Star queries are the simplest variant of selecting data. They can be used > > > to dump tables into Elasticsearch. > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > "type" : "jdbc", > > > "jdbc" : { > > > "driver" : "com.mysql.jdbc.Driver", > > > "url" : "jdbc:mysql://localhost:3306/test", > > > "user" : "", > > > "password" : "", > > > "sql" : "select \* from orders" > > > } > > > }' > > > > > > For example > > > > > > 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) > > > > > > The JSON objects are flat, the `id` > > > of the documents is generated automatically, it is the row number. > > > > > > 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"} > > > > > > ## Labeled columns > > > > > > In SQL, each column may be labeled with a name. This name is used by the > > > JDBC river to JSON object construction. > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > "type" : "jdbc", > > > "jdbc" : { > > > "driver" : "com.mysql.jdbc.Driver", > > > "url" : "jdbc:mysql://localhost:3306/test", > > > "user" : "", > > > "password" : "", > > > "sql" : "select products.name as "product.name", > > > orders.customer as "product.customer.name", orders.quantity \* > > > products.price as "product.customer.bill" from products, orders where > > > products.name = orders.product" > > > } > > > }' > > > > > > In this query, the columns selected are described as `product.name`, > > > `product.customer.name`, and `product.customer.bill`. > > > > > > mysql\> select products.name as "product.name", orders.customer as > > > "product.customer", orders.quantity \* products.price as > > > "product.customer.bill" from products, orders where products.name = > > > orders.product ; > > > +--------------+------------------+-----------------------+ > > > | product.name | product.customer | product.customer.bill | > > > +--------------+------------------+-----------------------+ > > > | Apples | Big | 1 | > > > | Bananas | Large | 2 | > > > | Oranges | Huge | 6 | > > > | Apples | Good | 2 | > > > | Oranges | Bad | 9 | > > > +--------------+------------------+-----------------------+ > > > 5 rows in set, 5 warnings (0.00 sec) > > > > > > The JSON objects are > > > > > > id=0 {"product":{"name":"Apples","customer":{"bill":1.0,"name":"Big"}}} > > > id=1 {"product":{"name":"Bananas","customer":{"bill":2.0,"name":" > > > Large"}}} > > > id=2 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":" > > > Huge"}}} > > > id=3 {"product":{"name":"Apples","customer":{"bill":2.0,"name":" > > > Good"}}} > > > id=4 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}} > > > > > > There are three column labels with an underscore as prefix > > > that are mapped to the Elasticsearch index/type/id. > > > > > > \_id > > > \_type > > > \_index > > > > > > ## Structured objects > > > > > > One of the advantage of SQL queries is the join operation. From many > > > tables, new tuples can be formed. > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > "type" : "jdbc", > > > "jdbc" : { > > > "driver" : "com.mysql.jdbc.Driver", > > > "url" : "jdbc:mysql://localhost:3306/test", > > > "user" : "", > > > "password" : "", > > > "sql" : "select "relations" as "\_index", orders.customer as > > > "\_id", orders.customer as "contact.customer", employees.name as > > > "contact.employee" from orders left join employees on > > > employees.department = orders.department" > > > } > > > }' > > > > > > For example, these rows from SQL > > > > > > mysql\> select "relations" as "\_index", orders.customer as "\_id", > > > orders.customer as "contact.customer", employees.name as > > > "contact.employee" from orders left join employees on employees.department > > > = orders.department; > > > +-----------+-------+------------------+------------------+ > > > | \_index | \_id | contact.customer | contact.employee | > > > +-----------+-------+------------------+------------------+ > > > | relations | Big | Big | Smith | > > > | relations | Large | Large | Müller | > > > | relations | Large | Large | Meier | > > > | relations | Large | Large | Schulze | > > > | relations | Huge | Huge | Müller | > > > | relations | Huge | Huge | Meier | > > > | relations | Huge | Huge | Schulze | > > > | relations | Good | Good | Müller | > > > | relations | Good | Good | Meier | > > > | relations | Good | Good | Schulze | > > > | relations | Bad | Bad | Jones | > > > +-----------+-------+------------------+------------------+ > > > 11 rows in set (0.00 sec) > > > > > > will generate fewer JSON objects for the index `relations`. > > > > > > index=relations id=Big {"contact":{"employee":"Smith","customer":"Big"}} > > > index=relations id=Large {"contact":{"employee":[" > > > Müller","Meier","Schulze"],"customer":"Large"}} > > > index=relations id=Huge {"contact":{"employee":[" > > > Müller","Meier","Schulze"],"customer":"Huge"}} > > > index=relations id=Good {"contact":{"employee":[" > > > Müller","Meier","Schulze"],"customer":"Good"}} > > > index=relations id=Bad {"contact":{"employee":"Jones" > > > ,"customer":"Bad"}} > > > > > > Note how the `employee` column is collapsed into a JSON array. The > > > repeated occurence of the `_id` column > > > controls how values are folded into arrays for making use of the > > > Elasticsearch JSON data model. > > > > > > ## Bind parameter > > > > > > Bind parameters are useful for selecting rows according to a matching > > > condition > > > where the match criteria is not known beforehand. > > > > > > For example, only rows matching certain conditions can be indexed into > > > Elasticsearch. > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > "type" : "jdbc", > > > "jdbc" : { > > > "driver" : "com.mysql.jdbc.Driver", > > > "url" : "jdbc:mysql://localhost:3306/test", > > > "user" : "", > > > "password" : "", > > > "sql" : "select products.name as "product.name", > > > orders.customer as "product.customer.name", orders.quantity \* > > > products.price as "product.customer.bill" from products, orders where > > > products.name = orders.product and orders.quantity \* products.price \> > > > ?", > > > "params: [5.0] > > > } > > > }' > > > > > > Example result > > > > > > id=0 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":" > > > Huge"}}} > > > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":" > > > Bad"}}} > > > > > > ## Time-based selecting > > > > > > Because the JDBC river is running repeatedly, time-based selecting is > > > useful. > > > The current time is represented by the parameter value `$now`. > > > > > > In this example, all rows beginning with a certain date up to now are > > > selected. > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > "type" : "jdbc", > > > "jdbc" : { > > > "driver" : "com.mysql.jdbc.Driver", > > > "url" : "jdbc:mysql://localhost:3306/test", > > > "user" : "", > > > "password" : "", > > > "sql" : "select products.name as "product.name", > > > orders.customer as "product.customer.name", orders.quantity \* > > > products.price as "product.customer.bill" from products, orders where > > > products.name = orders.product and orders.created between ? - 14 and ?", > > > "params: [2012-06-01", "$now"] > > > } > > > }' > > > > > > Example result: > > > > > > id=0 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}} > > > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":" > > > Bad"}}} > > > > > > ## Index > > > > > > Each river can index into a specified index. Example: > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > "type" : "jdbc", > > > "jdbc" : { > > > "driver" : "com.mysql.jdbc.Driver", > > > "url" : "jdbc:mysql://localhost:3306/test", > > > "user" : "", > > > "password" : "", > > > "sql" : "select \* from orders", > > > }, > > > "index" : { > > > "index" : "jdbc", > > > "type" : "jdbc" > > > } > > > }' > > > > > > ## Bulk indexing > > > > > > Bulk indexing is automatically used in order to speed up the indexing > > > process. > > > > > > Each SQL result set will be indexed by a single bulk if the bulk size is > > > not specified. > > > > > > A bulk size can be defined, also a maximum size of active bulk requests > > > to cope with high load situations. > > > A bulk timeout defines the time period after which bulk feeds continue. > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > "type" : "jdbc", > > > "jdbc" : { > > > "driver" : "com.mysql.jdbc.Driver", > > > "url" : "jdbc:mysql://localhost:3306/test", > > > "user" : "", > > > "password" : "", > > > "sql" : "select \* from orders", > > > }, > > > "index" : { > > > "index" : "jdbc", > > > "type" : "jdbc", > > > "bulk\_size" : 100, > > > "max\_bulk\_requests" : 30, > > > "bulk\_timeout" : "60s" > > > } > > > }' > > > > > > ## Stopping/deleting the river > > > > > > curl -XDELETE 'localhost:9200/\_river/my\_jdbc\_river/' > > > > > > Best regards,, > > > > > > Jörg > > > > > > -- > > > 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](mailto:elasticsearch+unsubscribe@googlegroups.com). > > > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/17dc1eed-3a7c-4b7c-992d-bd0c65b2e3ff%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/17dc1eed-3a7c-4b7c-992d-bd0c65b2e3ff%40googlegroups.com) > > [https://groups.google.com/d/msgid/elasticsearch/17dc1eed-3a7c-4b7c-992d-bd0c65b2e3ff%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/17dc1eed-3a7c-4b7c-992d-bd0c65b2e3ff%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > -- > You received this message because you are subscribed to a topic in the > Google Groups "elasticsearch" group. > To unsubscribe from this topic, visit > [https://groups.google.com/d/topic/elasticsearch/TonBKhpdjsA/unsubscribe](https://groups.google.com/d/topic/elasticsearch/TonBKhpdjsA/unsubscribe). > To unsubscribe from this group and all its topics, send an email to > [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHRZw-sFaPrR6VmJqNDFUtRMTYuKHA6h1\_sukWZsZWWMA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHRZw-sFaPrR6VmJqNDFUtRMTYuKHA6h1_sukWZsZWWMA%40mail.gmail.com) > [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHRZw-sFaPrR6VmJqNDFUtRMTYuKHA6h1\_sukWZsZWWMA%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHRZw-sFaPrR6VmJqNDFUtRMTYuKHA6h1_sukWZsZWWMA%40mail.gmail.com?utm_medium=email&utm_source=footer) > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- 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](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAG%2Bs7e3arvL%2B9p3AM9FeT0fTHh7P\_-\_m1wZwzLQaMk76ex1\_Ow%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAG%2Bs7e3arvL%2B9p3AM9FeT0fTHh7P_-_m1wZwzLQaMk76ex1_Ow%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Madhavan\_Ramachandra](https://avatars.discourse-cdn.com/v4/letter/m/a88e57/32.png) [@Madhavan\_Ramachandra](https://discuss.elastic.co/u/Madhavan_Ramachandra)\ **Post date:** [August 1, 2014, 5:11pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132/20 "2014-08-01T17:11:26Z")
Hi JP I setup a single node ES 1.2.1 on windows 2008 R2. Need to know the JDBC river version that support to import data from Microsoft SQL Server 2012. Regards Madhavan.TR On Monday, July 28, 2014 8:59:45 AM UTC-5, Santosh B wrote: > Thanks a lot for sharing.... > > On Mon, Jul 28, 2014 at 6:16 PM, [joerg...@gmail.com](mailto:joerg...@gmail.com) \ \< > [joerg...@gmail.com](mailto:joerg...@gmail.com) \\> wrote: > > > From the docs > > > > [HiveServer2 Clients - Apache Hive - Apache Software Foundation](https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients) > > > > I conclude that Hive2 is not a JDBC Type 4 driver. Only JDBC Type 4 > > drivers are supported by JDBC plugin. JDBC Type 4 does no longer need > > Class.forName. > > > > Jörg > > > > On Mon, Jul 28, 2014 at 1:27 PM, Santosh B \<[contact...@gmail.com](mailto:contact...@gmail.com) > > \\> wrote: > > > > > Hi, > > > Its a very good feature. > > > I was trying to use JDBC driver to import from hive/Impala but it never > > > works whereas mysql connector works perfectly fine. > > > Is it something it was specifically designed to work for > > > mysql,MSSQl...and few of them or any other databases which supports JDBC. > > > > > > Thanks, > > > Santosh B > > > > > > On Sunday, 17 June 2012 02:29:18 UTC+5:30, Jörg Prante wrote: > > > > > > > Hi, > > > > > > > > I'd like to announce a JDBC river implementation. > > > > > > > > It can be found at [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) > > > > > > > > I hope it is useful for all of you who need to index data from SQL > > > > databases into Elasticsearch. > > > > > > > > Suggestions, corrections, improvements are welcome! > > > > > > > > ## Introduction > > > > > > > > The Java Database Connection (JDBC) river allows to select data from > > > > JDBC sources for indexing into Elasticsearch. > > > > > > > > It is implemented as an Elasticsearch plugin. > > > > > > > > The relational data is internally transformed into structured JSON > > > > objects for Elasticsearch schema-less indexing. > > > > > > > > Setting it up is as simple as executing something like the following > > > > against Elasticsearch: > > > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > > "type" : "jdbc", > > > > "jdbc" : { > > > > "driver" : "com.mysql.jdbc.Driver", > > > > "url" : "jdbc:mysql://localhost:3306/test", > > > > "user" : "", > > > > "password" : "", > > > > "sql" : "select \* from orders", > > > > } > > > > }' > > > > > > > > This HTTP PUT statement will create a river named `my_jdbc_river` > > > > that fetches all the rows from the `orders` table in the MySQL database > > > > `test` at `localhost`. > > > > > > > > You have to install the JDBC driver jar of your favorite database > > > > manually into > > > > the `plugins` directory where the jar file of the JDBC river plugin > > > > resides. > > > > > > > > By default, the JDBC river re-executes the SQL statement on a regular > > > > basis (60 minutes). > > > > > > > > In case of a failover, the JDBC river will automatically be restarted > > > > on another Elasticsearch node, and continue indexing. > > > > > > > > Many JDBC rivers can run in parallel. Each river opens one thread to > > > > select > > > > the data. > > > > > > > > ## Installation > > > > > > > > In order to install the plugin, simply run: `bin/plugin -install jprante/elasticsearch-river-jdbc/1.0.0`. > > > > > > > > ## Log example of river creation > > > > > > > > [2012-06-16 18:50:10,035][INFO][cluster.metadata] [Anomaly] > > > > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > > > > [2012-06-16 18:50:10,046][INFO][river.jdbc] [Anomaly] > > > > [jdbc][my\_jdbc\_river] starting JDBC connector: URL > > > > [jdbc:mysql://localhost:3306/test], driver [com.mysql.jdbc.Driver], > > > > sql [select \* from orders], indexing to [jdbc]/[jdbc], poll [1h] > > > > [2012-06-16 18:50:10,129][INFO][cluster.metadata] [Anomaly] > > > > [jdbc] creating index, cause [api], shards [5]/[1], mappings > > > > [2012-06-16 18:50:10,353][INFO][cluster.metadata] [Anomaly] > > > > [\_river] update\_mapping [my\_jdbc\_river] (dynamic) > > > > [2012-06-16 18:50:10,714][INFO][river.jdbc] [Anomaly] > > > > [jdbc][my\_jdbc\_river] got 5 rows > > > > [2012-06-16 18:50:10,719][INFO][river.jdbc] [Anomaly] > > > > [jdbc][my\_jdbc\_river] next run, waiting 1h, URL > > > > [jdbc:mysql://localhost:3306/test] driver [com.mysql.jdbc.Driver] sql > > > > [select \* from orders] > > > > > > > > # Configuration > > > > > > > > The SQL statements used for selecting can be configured as follows. > > > > > > > > ## Star query > > > > > > > > Star queries are the simplest variant of selecting data. They can be > > > > used > > > > to dump tables into Elasticsearch. > > > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > > "type" : "jdbc", > > > > "jdbc" : { > > > > "driver" : "com.mysql.jdbc.Driver", > > > > "url" : "jdbc:mysql://localhost:3306/test", > > > > "user" : "", > > > > "password" : "", > > > > "sql" : "select \* from orders" > > > > } > > > > }' > > > > > > > > For example > > > > > > > > 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) > > > > > > > > The JSON objects are flat, the `id` > > > > of the documents is generated automatically, it is the row number. > > > > > > > > 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"} > > > > > > > > ## Labeled columns > > > > > > > > In SQL, each column may be labeled with a name. This name is used by > > > > the JDBC river to JSON object construction. > > > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > > "type" : "jdbc", > > > > "jdbc" : { > > > > "driver" : "com.mysql.jdbc.Driver", > > > > "url" : "jdbc:mysql://localhost:3306/test", > > > > "user" : "", > > > > "password" : "", > > > > "sql" : "select products.name as "product.name", > > > > orders.customer as "product.customer.name", orders.quantity \* > > > > products.price as "product.customer.bill" from products, orders where > > > > products.name = orders.product" > > > > } > > > > }' > > > > > > > > In this query, the columns selected are described as `product.name`, > > > > `product.customer.name`, and `product.customer.bill`. > > > > > > > > mysql\> select products.name as "product.name", orders.customer as > > > > "product.customer", orders.quantity \* products.price as > > > > "product.customer.bill" from products, orders where products.name = > > > > orders.product ; > > > > +--------------+------------------+-----------------------+ > > > > | product.name | product.customer | product.customer.bill | > > > > +--------------+------------------+-----------------------+ > > > > | Apples | Big | 1 | > > > > | Bananas | Large | 2 | > > > > | Oranges | Huge | 6 | > > > > | Apples | Good | 2 | > > > > | Oranges | Bad | 9 | > > > > +--------------+------------------+-----------------------+ > > > > 5 rows in set, 5 warnings (0.00 sec) > > > > > > > > The JSON objects are > > > > > > > > id=0 {"product":{"name":"Apples","customer":{"bill":1.0,"name":"Big"}}} > > > > id=1 {"product":{"name":"Bananas","customer":{"bill":2.0,"name":" > > > > Large"}}} > > > > id=2 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":" > > > > Huge"}}} > > > > id=3 {"product":{"name":"Apples","customer":{"bill":2.0,"name":" > > > > Good"}}} > > > > id=4 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":" > > > > Bad"}}} > > > > > > > > There are three column labels with an underscore as prefix > > > > that are mapped to the Elasticsearch index/type/id. > > > > > > > > \_id > > > > \_type > > > > \_index > > > > > > > > ## Structured objects > > > > > > > > One of the advantage of SQL queries is the join operation. From many > > > > tables, new tuples can be formed. > > > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > > "type" : "jdbc", > > > > "jdbc" : { > > > > "driver" : "com.mysql.jdbc.Driver", > > > > "url" : "jdbc:mysql://localhost:3306/test", > > > > "user" : "", > > > > "password" : "", > > > > "sql" : "select "relations" as "\_index", orders.customer as > > > > "\_id", orders.customer as "contact.customer", employees.name as > > > > "contact.employee" from orders left join employees on > > > > employees.department = orders.department" > > > > } > > > > }' > > > > > > > > For example, these rows from SQL > > > > > > > > mysql\> select "relations" as "\_index", orders.customer as "\_id", > > > > orders.customer as "contact.customer", employees.name as > > > > "contact.employee" from orders left join employees on employees.department > > > > = orders.department; > > > > +-----------+-------+------------------+------------------+ > > > > | \_index | \_id | contact.customer | contact.employee | > > > > +-----------+-------+------------------+------------------+ > > > > | relations | Big | Big | Smith | > > > > | relations | Large | Large | Müller | > > > > | relations | Large | Large | Meier | > > > > | relations | Large | Large | Schulze | > > > > | relations | Huge | Huge | Müller | > > > > | relations | Huge | Huge | Meier | > > > > | relations | Huge | Huge | Schulze | > > > > | relations | Good | Good | Müller | > > > > | relations | Good | Good | Meier | > > > > | relations | Good | Good | Schulze | > > > > | relations | Bad | Bad | Jones | > > > > +-----------+-------+------------------+------------------+ > > > > 11 rows in set (0.00 sec) > > > > > > > > will generate fewer JSON objects for the index `relations`. > > > > > > > > index=relations id=Big {"contact":{"employee":"Smith" > > > > ,"customer":"Big"}} > > > > index=relations id=Large {"contact":{"employee":[" > > > > Müller","Meier","Schulze"],"customer":"Large"}} > > > > index=relations id=Huge {"contact":{"employee":[" > > > > Müller","Meier","Schulze"],"customer":"Huge"}} > > > > index=relations id=Good {"contact":{"employee":[" > > > > Müller","Meier","Schulze"],"customer":"Good"}} > > > > index=relations id=Bad {"contact":{"employee":"Jones" > > > > ,"customer":"Bad"}} > > > > > > > > Note how the `employee` column is collapsed into a JSON array. The > > > > repeated occurence of the `_id` column > > > > controls how values are folded into arrays for making use of the > > > > Elasticsearch JSON data model. > > > > > > > > ## Bind parameter > > > > > > > > Bind parameters are useful for selecting rows according to a matching > > > > condition > > > > where the match criteria is not known beforehand. > > > > > > > > For example, only rows matching certain conditions can be indexed into > > > > Elasticsearch. > > > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > > "type" : "jdbc", > > > > "jdbc" : { > > > > "driver" : "com.mysql.jdbc.Driver", > > > > "url" : "jdbc:mysql://localhost:3306/test", > > > > "user" : "", > > > > "password" : "", > > > > "sql" : "select products.name as "product.name", > > > > orders.customer as "product.customer.name", orders.quantity \* > > > > products.price as "product.customer.bill" from products, orders where > > > > products.name = orders.product and orders.quantity \* products.price \> > > > > ?", > > > > "params: [5.0] > > > > } > > > > }' > > > > > > > > Example result > > > > > > > > id=0 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":" > > > > Huge"}}} > > > > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":" > > > > Bad"}}} > > > > > > > > ## Time-based selecting > > > > > > > > Because the JDBC river is running repeatedly, time-based selecting is > > > > useful. > > > > The current time is represented by the parameter value `$now`. > > > > > > > > In this example, all rows beginning with a certain date up to now are > > > > selected. > > > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > > "type" : "jdbc", > > > > "jdbc" : { > > > > "driver" : "com.mysql.jdbc.Driver", > > > > "url" : "jdbc:mysql://localhost:3306/test", > > > > "user" : "", > > > > "password" : "", > > > > "sql" : "select products.name as "product.name", > > > > orders.customer as "product.customer.name", orders.quantity \* > > > > products.price as "product.customer.bill" from products, orders where > > > > products.name = orders.product and orders.created between ? - 14 and > > > > ?", > > > > "params: [2012-06-01", "$now"] > > > > } > > > > }' > > > > > > > > Example result: > > > > > > > > id=0 {"product":{"name":"Apples","customer":{"bill":2.0,"name":" > > > > Good"}}} > > > > id=1 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":" > > > > Bad"}}} > > > > > > > > ## Index > > > > > > > > Each river can index into a specified index. Example: > > > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > > "type" : "jdbc", > > > > "jdbc" : { > > > > "driver" : "com.mysql.jdbc.Driver", > > > > "url" : "jdbc:mysql://localhost:3306/test", > > > > "user" : "", > > > > "password" : "", > > > > "sql" : "select \* from orders", > > > > }, > > > > "index" : { > > > > "index" : "jdbc", > > > > "type" : "jdbc" > > > > } > > > > }' > > > > > > > > ## Bulk indexing > > > > > > > > Bulk indexing is automatically used in order to speed up the indexing > > > > process. > > > > > > > > Each SQL result set will be indexed by a single bulk if the bulk size > > > > is not specified. > > > > > > > > A bulk size can be defined, also a maximum size of active bulk requests > > > > to cope with high load situations. > > > > A bulk timeout defines the time period after which bulk feeds continue. > > > > > > > > curl -XPUT 'localhost:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > > > "type" : "jdbc", > > > > "jdbc" : { > > > > "driver" : "com.mysql.jdbc.Driver", > > > > "url" : "jdbc:mysql://localhost:3306/test", > > > > "user" : "", > > > > "password" : "", > > > > "sql" : "select \* from orders", > > > > }, > > > > "index" : { > > > > "index" : "jdbc", > > > > "type" : "jdbc", > > > > "bulk\_size" : 100, > > > > "max\_bulk\_requests" : 30, > > > > "bulk\_timeout" : "60s" > > > > } > > > > }' > > > > > > > > ## Stopping/deleting the river > > > > > > > > curl -XDELETE 'localhost:9200/\_river/my\_jdbc\_river/' > > > > > > > > Best regards,, > > > > > > > > Jörg > > > > > > > > -- > > > > 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 [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > > > > > To view this discussion on the web visit > > > [https://groups.google.com/d/msgid/elasticsearch/17dc1eed-3a7c-4b7c-992d-bd0c65b2e3ff%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/17dc1eed-3a7c-4b7c-992d-bd0c65b2e3ff%40googlegroups.com) > > > [https://groups.google.com/d/msgid/elasticsearch/17dc1eed-3a7c-4b7c-992d-bd0c65b2e3ff%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/17dc1eed-3a7c-4b7c-992d-bd0c65b2e3ff%40googlegroups.com?utm_medium=email&utm_source=footer) > > > . > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > -- > > You received this message because you are subscribed to a topic in the > > Google Groups "elasticsearch" group. > > To unsubscribe from this topic, visit > > [https://groups.google.com/d/topic/elasticsearch/TonBKhpdjsA/unsubscribe](https://groups.google.com/d/topic/elasticsearch/TonBKhpdjsA/unsubscribe). > > To unsubscribe from this group and all its topics, send an email to > > [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHRZw-sFaPrR6VmJqNDFUtRMTYuKHA6h1\_sukWZsZWWMA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHRZw-sFaPrR6VmJqNDFUtRMTYuKHA6h1_sukWZsZWWMA%40mail.gmail.com) > > [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHRZw-sFaPrR6VmJqNDFUtRMTYuKHA6h1\_sukWZsZWWMA%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHRZw-sFaPrR6VmJqNDFUtRMTYuKHA6h1_sukWZsZWWMA%40mail.gmail.com?utm_medium=email&utm_source=footer) > > . > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- 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](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/dba6831c-9b0b-475a-9348-d9df6f716379%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/dba6831c-9b0b-475a-9348-d9df6f716379%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). [Next page](https://discuss.elastic.co/t/ann-jdbc-river-plugin-for-elasticsearch/8132.md?page=2)