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!

Highlighted
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"))
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)