How to iterate through csv and read row by row and assigning them in tJavaRow

Six Stars

How to iterate through csv and read row by row and assigning them in tJavaRow

I have a flow below where I am reading the csv file 

 

tFileInputDelimited_1 -> tFlowtoIterate -> tJavaFlex -> tFileInputDelimited_2 -> tJavaRow -> tFlowToIterate -> tHiveInput -> tFileOutputDelimited. 

 

I am preparing a SQL query using above flow and sending it to Hive. 

 

I have delimited data in tFileInputDelimited_1 which i wanted to iterate through and assign them in tJavaFlex and tJavaRow accordingly (i.e. to contextVariables).

 

For each row field in csv (tFileInputDelimited_1), which matches a condition(i.e. based in tFileInputDelimited_2), i want to assign to context variables accordingly. 

 

tFileInputDelimited_1:

123-D1-abc-rtey121-a1b1-1416

123-D1-abc-bd2tr-a1y14-1311

141-B1-fhf--a1fb32-utoot21b-157

141-B1-fhf--vefb32-utoot21b-157

141-B1-fhf--k191-utoot21b-157

456575-C1-21-ytuueoro-ghfyt-43

456575-C1-21-ytuueoro-ghfyt-43

4567-B1-abc-grth-a1b1-1287

 

tFileInputDelimited_2:

sno,section_name,col_1,col_2,col_3

1, section_name_1,<string_a1>,<string_a2>,<string_a3>

2, section_name_2,<string_b1>,<string_b2>,<string_b3>

 

Code in tJavaRow: 

 

 

 

String v_filter = ""

if(input_row.sno == 1 && input_row.name.equals("section_name_1")) /* conditions from tFileInputDelimited_2*/
{
    while((String)globalMap.get("row.line") != null) /*lines from tFileInputDelimited_1*/
    {
      //the below code should loop 
      if (context.AP_CODE.equals("D1") && context.I_NAME.equals("abc"))
      {
         v_filter = "AP_CODE_V =" + "'" + context.AP_CODE_V + "'"
          + " AND I_NAME_V=" + "'" + context.I_NAME_V + "'"
          + " AND ID_V =" + context.ID_V 
      } 
    } 
} 
..
..
I need output like below (i.e. for each matching condition).. AP_CODE_V = 'rtey121'
I_NAME_V = 'a1b1'
ID_V = 1416

AP_CODE_V = 'bd2tr'
I_NAME_V = 'a1y14'
ID_V = 1311

 

The Problem is that I am able print only first set. I am not able to loop through other set of values(Seems like while condition is not working). 

 

Please help me, where i am doing wrong. 

 

Note: I thought of organizing these files in JSON, so that it would be easy to parse through and iterate. but some how started with delimited files to get some quick turn-around. 

Eight Stars

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

I think we'll need to see the component linkages to help with this. The issue seems to be that globalMap.get("row.line") isn't iterating through the rows correctly: are you sending it an Iterate? Your workflow is:

tFileInputDelimited_1 -> tFlowtoIterate -> tJavaFlex -> tFileInputDelimited_2 -> tJavaRow -> tFlowToIterate -> tHiveInput -> tFileOutputDelimited.

which has other components between the tFlowtoIterate and tJavaRow. Or is the code you posted from tJavaFlex?
Six Stars

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

hi @DVSCHWAB

 

The flow is same as mentioned. 

1) tFileInputDelimited_1 -> has the test data as mentioned. I connected this to tFlowtoIterate

2) tJavaFlex -> I am assigning the above test data to context variables. (i have no other code here apart from variable assignment) 

3) tFileInputDelimited_2 -> has the data (as mentioned above) to form the SQL query template. 

4) tFlowtoIterate_1 -> connects to tJavaRow to loop the data

5) tJavarow_1 -> this is where my logic to assign test data from tFileInputDelimited_1 to template in tFileInputDelimited_2 (i.e. the code which i poisted)

 

image.png

Highlighted
Six Stars

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

Any update ? Please let me know..

 

 

CC: 

@rhall_2_0

Sixteen Stars

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

What data are you intending to loop in your tJavaRow?

Six Stars

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

Hi @rhall_2_0

 

tFileInputDelimited_1:

123-D1-abc-rtey121-a1b1-1416

123-D1-abc-bd2tr-a1y14-1311

141-B1-fhf--a1fb32-utoot21b-157

141-B1-fhf--vefb32-utoot21b-157

141-B1-fhf--k191-utoot21b-157

456575-C1-21-ytuueoro-ghfyt-43

456575-C1-21-ytuueoro-ghfyt-43

4567-B1-abc-grth-a1b1-1287

 

tFileInputDelimited_2:

sno,section_name,col_1,col_2,col_3

1, section_name_1,<string_a1>,<string_a2>,<string_a3>

2, section_name_2,<string_b1>,<string_b2>,<string_b3>

 

 

eg:- I want to loop over 2 times on tFileInputDelimited_1, if (sno == 1 && sec_name_1) from tFileInputDelimited_2 and while((context.AP_CODE.equals("D1") && context.I_NAME.equals("abc"))) //based on matching criteria from each line. (i..e D1 and abc)

i.e. 

123-D1-abc-rtey121-a1b1-1416

123-D1-abc-bd2tr-a1y14-1311

 

so that I will get output like this (i.e., the values which i need based on matching condition).. 

 

AP_CODE_V = 'rtey121'
I_NAME_V = 'a1b1'
ID_V = 1416

AP_CODE_V = 'bd2tr'
I_NAME_V = 'a1y14'
ID_V = 1311

 Unfortunately, I don't have a join condition to match between these two files. 

Similarly For, (sno == 2 && sec_name_2) and while((context.AP_CODE.equals("B1") && context.I_NAME.equals("fhf")))

AP_CODE_V = 'a1fb32'
I_NAME_V = 'utoot21b'
ID_V = 157

AP_CODE_V = 'vefb32'
I_NAME_V = 'utoot21b'
ID_V = 157

AP_CODE_V = 'k191'
I_NAME_V = 'utoot21b'
ID_V = 157
Six Stars

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

Please let me know where I am doing wrong! I am not sure if I am doing wrong in while condition, I.e to loop over tFileInputDelimited_1 (to retrieve only that row which satisfies the 2nd if condition)?

(or) should I use tJavaFlex instead of tJavarow for this purpose?
Sixteen Stars

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

OK, you need to understand the strict flow here. For every row from your first file, every row of your second file will be returned. That is how iterate links work. You cannot force subsequent rows from the first file in middle of an iteration. If you first file has two rows of data (holding values "1" and "2") and your second file has 4 rows (holding values "a", "b", "c" and "d") and if you ran them through your configuration, the rough permutations of data would be below....

 

"1", "a"

"1", "b"

"1", "c"

"1", "d"

"2", "a"

"2", "b"

"2", "c"

"2", "d"

 

I suspect you might need to use a tMap to join your data from your files and perform your logic there.

Eight Stars

Re: How to iterate through csv and read row by row and assigning them in tJavaRow

I confess that I'm not sure why your code isn't working; it looks correct to me. At this point, what I would do (if this were my project) is create a new job with only the tfileDelimited2 and tJavaRow and see if it works (output to tLogRow). If it does, add back the other components one at a time until you find the source of the problem (if it doesn't work with just the minimal components, strip the Java code down to the bare minimum to prove the functionality exists).

Best of luck,

David