# How to transform SQL columns using JDBC River? **URL:** \ **Category:** Elasticsearch\ **Created:** [August 4, 2014, 9:50pm UTC](https://discuss.elastic.co/t/how-to-transform-sql-columns-using-jdbc-river/19083 "2014-08-04T21:50:16Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Christopher\_Ambler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christopher_ambler/32/1353_2.png) [@Christopher\_Ambler](https://discuss.elastic.co/u/Christopher_Ambler)\ **Post date:** [August 4, 2014, 9:50pm UTC](https://discuss.elastic.co/t/how-to-transform-sql-columns-using-jdbc-river/19083/1 "2014-08-04T21:50:16Z")
I would like to use the JDBC river to consume a queue implemented in an MSSQL server. I have been given a stored procedure to call that hands me the top 100 items in the queue. I'm given a number of columns as output, and the names of the columns do not correspond to the names of the fields in Elasticsearch. Can anyone give me some adult supervision on how I can (or even IF I CAN) use the JDBC river to call this SP and properly transform the column names, as they're coming out of the SP's result set, into the proper field names in my index? -- 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/45ff2a25-6e36-4edd-a849-b5bba0f60449%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/45ff2a25-6e36-4edd-a849-b5bba0f60449%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:** [August 4, 2014, 10:26pm UTC](https://discuss.elastic.co/t/how-to-transform-sql-columns-using-jdbc-river/19083/2 "2014-08-04T22:26:13Z")
I have started to implement stored procedure calls. They are not complete in JDBC plugin. At the moment it is an undocumented (incomplete) feature that can register field names to callable statement result parameters. You hit the nail - how to map result set output to field names is not done yet. Because I don't know MS SQL, it will not be easy to assist, I hope this is JDBC compliant. The stored procedures I can test are MySQL stored procedures like described here: > **[Using Stored Procedures (The Java™ Tutorials \> JDBC...](https://docs.oracle.com/javase/tutorial/jdbc/basics/storedprocedures.html#calling_javadb_mysql)** > > This JDBC Java tutorial describes how to use JDBC API to create, insert into, update, and query tables. You will also learn how to use simple and prepared statements, stored procedures and perform transactions Jörg On Mon, Aug 4, 2014 at 11:50 PM, Christopher Ambler [chris@insiderhouse.com](mailto:chris@insiderhouse.com) wrote: > I would like to use the JDBC river to consume a queue implemented in an > MSSQL server. > > I have been given a stored procedure to call that hands me the top 100 > items in the queue. I'm given a number of columns as output, and the names > of the columns do not correspond to the names of the fields in > Elasticsearch. > > Can anyone give me some adult supervision on how I can (or even IF I CAN) > use the JDBC river to call this SP and properly transform the column names, > as they're coming out of the SP's result set, into the proper field names > in my index? > > -- > 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/45ff2a25-6e36-4edd-a849-b5bba0f60449%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/45ff2a25-6e36-4edd-a849-b5bba0f60449%40googlegroups.com) > [https://groups.google.com/d/msgid/elasticsearch/45ff2a25-6e36-4edd-a849-b5bba0f60449%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/45ff2a25-6e36-4edd-a849-b5bba0f60449%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/CAKdsXoGn63REWuQRzdbbXyONL3%3D-GFFQdq0Az\_gmk5wxbJgK1A%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGn63REWuQRzdbbXyONL3%3D-GFFQdq0Az_gmk5wxbJgK1A%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Christopher\_Ambler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christopher_ambler/32/1353_2.png) [@Christopher\_Ambler](https://discuss.elastic.co/u/Christopher_Ambler)\ **Post date:** [August 4, 2014, 10:49pm UTC](https://discuss.elastic.co/t/how-to-transform-sql-columns-using-jdbc-river/19083/3 "2014-08-04T22:49:21Z")
Well, I can surely help test it out as it becomes ready for consumption, given a little guidance on usage (being undocumented and all :-)). But yeah, mapping will be key. Specifically, I have a column coming out of the SP (the first column, called "domain") that will need to be mapped to the \_id field. Normally, I'd "SELECT domain AS \_id ..." but with an SP, of course, life is more difficult. On Monday, August 4, 2014 3:26:20 PM UTC-7, Jörg Prante wrote: > At the moment it is an undocumented (incomplete) feature that can register > field names to callable statement result parameters. You hit the nail - how > to map result set output to field names is not done yet. -- 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/eac8c53b-d7a0-4b30-bc62-173a517d9f42%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/eac8c53b-d7a0-4b30-bc62-173a517d9f42%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:** [August 5, 2014, 10:24am UTC](https://discuss.elastic.co/t/how-to-transform-sql-columns-using-jdbc-river/19083/4 "2014-08-05T10:24:03Z")
I have started work on stored procedures > > > Support for stored procedures added with tests for MySQL. The tests are taken fr…om > http://docs.oracle.com/javase/tutorial/jdbc/basics/storedprocedures.html > > The out parameters can not be renamed to Elasticsearch field names right now, this will be done as a followup. It works with MySQL, but assigning renamed field names for Elasticsearch is still an open issue. Jörg On Tue, Aug 5, 2014 at 12:49 AM, Christopher Ambler [chris@insiderhouse.com](mailto:chris@insiderhouse.com) wrote: > Well, I can surely help test it out as it becomes ready for consumption, > given a little guidance on usage (being undocumented and all :-)). But > yeah, mapping will be key. Specifically, I have a column coming out of the > SP (the first column, called "domain") that will need to be mapped to the > \_id field. > > Normally, I'd "SELECT domain AS \_id ..." but with an SP, of course, life > is more difficult. > > On Monday, August 4, 2014 3:26:20 PM UTC-7, Jörg Prante wrote: > > > At the moment it is an undocumented (incomplete) feature that can > > register field names to callable statement result parameters. You hit the > > nail - how to map result set output to field names is not done yet. > > -- > 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/eac8c53b-d7a0-4b30-bc62-173a517d9f42%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/eac8c53b-d7a0-4b30-bc62-173a517d9f42%40googlegroups.com) > [https://groups.google.com/d/msgid/elasticsearch/eac8c53b-d7a0-4b30-bc62-173a517d9f42%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/eac8c53b-d7a0-4b30-bc62-173a517d9f42%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/CAKdsXoF1skGEDyrB0XxFQ9ZjmiwXJe-uLbZS7r0BKMDBq4NUqg%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoF1skGEDyrB0XxFQ9ZjmiwXJe-uLbZS7r0BKMDBq4NUqg%40mail.gmail.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:** [August 5, 2014, 9:50pm UTC](https://discuss.elastic.co/t/how-to-transform-sql-columns-using-jdbc-river/19083/5 "2014-08-05T21:50:40Z")
Just released - stored procedures are available in JDBC plugin 1.3.0.4 > **[GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-jdbc)** > > JDBC importer for Elasticsearch. Contribute to jprante/elasticsearch-jdbc development by creating an account on GitHub. Jörg On Tue, Aug 5, 2014 at 12:24 PM, [joergprante@gmail.com](mailto:joergprante@gmail.com) \< [joergprante@gmail.com](mailto:joergprante@gmail.com)\> wrote: > I have started work on stored procedures > > [first step to support stored procedures · jprante/elasticsearch-jdbc@247a6f5 · GitHub](https://github.com/jprante/elasticsearch-river-jdbc/commit/247a6f5c029da5725cb47e3b970698326c37a22e) > > It works with MySQL, but assigning renamed field names for Elasticsearch > is still an open issue. > > Jörg > > On Tue, Aug 5, 2014 at 12:49 AM, Christopher Ambler \< > [chris@insiderhouse.com](mailto:chris@insiderhouse.com)\> wrote: > > > Well, I can surely help test it out as it becomes ready for consumption, > > given a little guidance on usage (being undocumented and all :-)). But > > yeah, mapping will be key. Specifically, I have a column coming out of the > > SP (the first column, called "domain") that will need to be mapped to the > > \_id field. > > > > Normally, I'd "SELECT domain AS \_id ..." but with an SP, of course, life > > is more difficult. > > > > On Monday, August 4, 2014 3:26:20 PM UTC-7, Jörg Prante wrote: > > > > > At the moment it is an undocumented (incomplete) feature that can > > > register field names to callable statement result parameters. You hit the > > > nail - how to map result set output to field names is not done yet. > > > > -- > > 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/eac8c53b-d7a0-4b30-bc62-173a517d9f42%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/eac8c53b-d7a0-4b30-bc62-173a517d9f42%40googlegroups.com) > > [https://groups.google.com/d/msgid/elasticsearch/eac8c53b-d7a0-4b30-bc62-173a517d9f42%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/eac8c53b-d7a0-4b30-bc62-173a517d9f42%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/CAKdsXoFN-pYEgGxKrevO%2BwAmYKCv73pVDPYrq%2Bn%2Bm7sYXu5Ybg%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoFN-pYEgGxKrevO%2BwAmYKCv73pVDPYrq%2Bn%2Bm7sYXu5Ybg%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Christopher\_Ambler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christopher_ambler/32/1353_2.png) [@Christopher\_Ambler](https://discuss.elastic.co/u/Christopher_Ambler)\ **Post date:** [August 6, 2014, 6:03am UTC](https://discuss.elastic.co/t/how-to-transform-sql-columns-using-jdbc-river/19083/6 "2014-08-06T06:03:16Z")
Awesome! Getting this on my priority to-do list to try out! On Tuesday, August 5, 2014 2:50:47 PM UTC-7, Jörg Prante wrote: > Just released - stored procedures are available in JDBC plugin 1.3.0.4 > > [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc/) > > 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/bca95414-be9b-4e19-8038-b5eeacc8ae8f%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/bca95414-be9b-4e19-8038-b5eeacc8ae8f%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![mlashch](https://avatars.discourse-cdn.com/v4/letter/m/8797f3/32.png) [@mlashch](https://discuss.elastic.co/u/mlashch)\ **Post date:** [November 12, 2014, 11:16am UTC](https://discuss.elastic.co/t/how-to-transform-sql-columns-using-jdbc-river/19083/7 "2014-11-12T11:16:24Z")
Hi Christopher, Did you have a chance to try it out? I'm getting exceptions like the following. [2014-11-12 13:18:54,489][WARN][river.jdbc.SimpleRiverSource] can't register out parameter 1 of type varchar [2014-11-12 13:18:54,489][WARN][river.jdbc.SimpleRiverSource] can't register out parameter 0 of type int ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 12:50am UTC](https://discuss.elastic.co/t/how-to-transform-sql-columns-using-jdbc-river/19083/8 "2017-07-06T00:50:32Z")