How to transform SQL columns using JDBC River?

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/45ff2a25-6e36-4edd-a849-b5bba0f60449%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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:

http://docs.oracle.com/javase/tutorial/jdbc/basics/storedprocedures.html#calling_javadb_mysql

Jörg

On Mon, Aug 4, 2014 at 11:50 PM, Christopher Ambler 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.
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?utm_medium=email&utm_source=footer
.
For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGn63REWuQRzdbbXyONL3%3D-GFFQdq0Az_gmk5wxbJgK1A%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/eac8c53b-d7a0-4b30-bc62-173a517d9f42%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

I have started work on stored procedures

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
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.
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?utm_medium=email&utm_source=footer
.

For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAKdsXoF1skGEDyrB0XxFQ9ZjmiwXJe-uLbZS7r0BKMDBq4NUqg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Just released - stored procedures are available in JDBC plugin 1.3.0.4

Jörg

On Tue, Aug 5, 2014 at 12:24 PM, joergprante@gmail.com <
joergprante@gmail.com> wrote:

I have started work on stored procedures

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> 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.
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?utm_medium=email&utm_source=footer
.

For more options, visit 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.
To view this discussion on the web visit 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.

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

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/bca95414-be9b-4e19-8038-b5eeacc8ae8f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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