Ask Your Question
1

How can i query on a database (RDBMS) to see the result ex: Counts or aggregation query.

asked 2017-10-23 08:25:52 -0500

Roh gravatar image

updated 2017-10-25 18:12:38 -0500

metadaddy gravatar image

I explored StreamSets to get the data to HDFS through JDBC Multitable consumer and the JDBC consumer. I usually use the sqoop eval command to run my queries and see the result, I wanted to do that in StreamSets. How can I run the query and see the result without having it stored somewhere?

Example queries i want to run :

select count (*) from schemaName.TableName;

select to_date(timestamp), count (*) from schemaName.TableName group by 1 order 1;

Based on @jeffs answer below i did this: image description

image description

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
2

answered 2017-10-23 09:54:49 -0500

jeff gravatar image

updated 2017-10-23 15:51:11 -0500

You should be able to use the JDBC Lookup Processor stage to accomplish this. It will run as a separate stage (after your origin). If you want to use the table name from the separate JDBC table origin, you can look to the jdbc.tables attribute.

To add more detail, the SQL Query itself should probably have some kind of parameter for the table you want, like select count(*) from ${record:attribute('jdbc.tables')}. That example will basically get the row count for the table from which the record was generated. You then shouldn't need to use Column Mappings unless you want to output the result of your query (i.e. the single synthetic column that contains the count result) to a different field.

edit flag offensive delete link more

Comments

Thanks Jeff, Please see my updated question. After setup the JDBC lookup where can i see the data ? Tried doing the capture but no luck.

Roh gravatar imageRoh ( 2017-10-23 10:29:32 -0500 )edit

Updated my answer with hopefully more detail. You should do a Preview to see how all the pieces fit together.

jeff gravatar imagejeff ( 2017-10-23 14:11:05 -0500 )edit

I understand the lookup part, but is there a way to see the results on the stream sets UI itself? other than doing a preview record?

Roh gravatar imageRoh ( 2017-10-24 10:51:42 -0500 )edit

Well, preview is intended to show you sample data across all the stages as a sanity check (i.e. confirming each step is doing what it's supposed to). Is that not what you're trying to do? We also have snapshot functionality on a running pipeline. Those are the two mechanisms for seeing live data.

jeff gravatar imagejeff ( 2017-10-24 12:35:03 -0500 )edit

I want to run the queries and see the output as it will display in some database developer application ex:oracle SQL developer, When I configured the pipeline as displaying in the question snapshot is not capturing the output as it does with the simple select query. Something like sqoop eval I need

Roh gravatar imageRoh ( 2017-10-26 13:40:02 -0500 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-10-23 08:25:52 -0500

Seen: 726 times

Last updated: Oct 25 '17