How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

Six Stars

How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

Hi, 

 

I have a job design as below. 

 

tFileInputDelimited -> tFlowtoIterate -> tJavaFlex ->(Iterate) tFileInputDelimited -> tJavaRow 

I am preparing the SQL queries dynamically from a file using the above flow and the till here the flow is working fine. Now, I want to pass the output from tJavaRow as batch(i.e. all the list of queries) to tHiveInput or tHiveRow. 

 

I tried.. 

..-> tJavaRow -> tForEach -> (OnComponentOk) -> tHiveConnection -> tHiveRow. 

The above flow is not working for me. I am sure, I am doing something wrong here. Please correct or suggest how to proceed further. 

Eleven Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

link for each with iterate not with OnComponentOh

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Six Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

hi @fdenis

 

I tried, but its not working. Below is my job_design snapshot. 

 

job_snapshot.jpgAlso, as i need to send all the queries at once to a Hive session, will tHiveRow work ?  As i read, tHiveRow accepts one query at a time. I may be wrong. Please correct me.

Eleven Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

what exactly is your requirement?
there is a lot of trouble on your graph!

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Six Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

@fdenis,

 

As i already explained, I have list of queries generated as an output from tJavaRow component. 

i.e. eg:-

select * from emp where emp_id = "121"

select * from dept where dept_name "XYZ"

..

and so on..

 

I need send those queries as batch (i.e all queries at once to the Hive session) and retrieve the result. 

 

 

Eleven Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

ok so remove tforeach.
remove OnSubjobOk link from connection
add tPreJob link with onSubjobOk to connection
and a row link(orange) from tJavaRow to thiveInput
propagate output data of tJavaRow to OutputLink
and try

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Six Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

job_snapshot_1.jpg

 

I did the changes, as above. 

 

  • From tPrejob, I only have "OnComponentOk" link available. 
  • From tJavaRow, i only have "OnComponentOk" link available to connect to tHiveInput.
  • Under the Query field in tHiveInput i have given the (i.e.variable from tJavaRow) variable. "outputRow.final_query" (i.e. this is the variable which holds the list of queries). 

I am getting an error - "output_row cannot be resolved to a variable". 

 

Eleven Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

when you right click on tJavaRow you must have row > main.
on tJavarow click on edit schema ... show me
what is your code into tjavarow?

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Six Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

I have tJavaRow (row->main) .. but i am not able to connect to tHiveInput as main

Eleven Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

use tflow to iterate
tJavaRow --> row --> tFlowToIterate --> iterate --> THiveInput

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Eleven Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

did you success on your task?

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Six Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

@fdenis

Sorry for the delay in reply. Partially Yes. I was able to pass params from tJavaRow to tHiveRow. Use below syntax..

 

(String)globalMap.get("row4.name")

Passing-a-value-from-a-parent-Job-to-a-child-Job

 

I am trying to send them as a batch (i.e. multiple queries at once). I am working on these and will reply soon. We can close the thread once that is working fine.

 

 

Six Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

I am able to send a list of queries to tHiveInput. But, as i see the resultset in the tLogRow. tHiveInput returns only first column of the table. I am not seeing complete recordset/row. 

 

Is there any additional component should use (or) Should i use some other hivecomponent instead of tHiveInput ? 

Eleven Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

warning:
define your globalMap variable into tFlowToIterate then use this variables in THiveInput.

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Six Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

Is it in the "Dynamic Settings" of tFlowtoIterate ? 

Like below.. ?

 

Name  & Code 

final_query   = ((String)globalMap.get("row4.final_query"))
Highlighted
Eleven Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

not in Dynamic Settings
on Basic setting uncheck use default and add:
key=final_query value=final_query

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Six Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

@fdenis

I did the same as above and also added the value "final_query"  (with & without quotes) in the Query field of tHiveInput. I see an error while running.. 

 

Error Line: 2341
Detail Message: final_query cannot be resolved to a variable

Eleven Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

use:

(String)globalMap.get("final_query")

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Six Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

@fdenis

This is working, but as i said in my ealier post. I am only getting data of first column in the Result Set but not the entire recordset. 

 

So, I added a new component "tParseRecordSet" and choose "final_query" for Prev.Comp.Column List drop-down.

 

And under Attribute table, i added as below..

 

Column: final_query

Value : "final_query"

 

And i see error as below.. 

Exception in component tParseRecordSet_1

java.lang.ClassCastException: java.lang.String cannot be cast to java.sql.ResultSet

 

The datatype of "final_query" column in tHiveInput is "Object" 

What is the data-type should i mention under tParseRecordSet to convert Object type to ResultSet ? (i.e. to display entire recordset)

 

Six Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

@fdenis

Please let me know

Eleven Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

it's a bit tricky, can you post your actual job with the configuration of your components?

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Six Stars

Re: How to pass tJavaRow output (i.e.sql queries) as a batch to tHiveInput or tHiveRow

@fdenis

Sure. Will do. But let me try to figure out a way, if not i will post the complete information here. 

 

Btw, I was able to concatenate all the SELECT columns for all the queries and able to write it to tFileOutputDelimited. Hence we can close this thread.

Tags (1)

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.