One Star

[resolved] Extracting earliest load_date's data from Hive tables - ISSUE

Hi All,
I need to extract the data from hive table based on the 'earliest' date coming from the data in one of the columns called 'Load_Date' in the same hive table.
For ex:- if the Load_Date' has the values like 01jan2016,01feb2016,03mar2016 etc...I need only extract the data for 01jan2016.(i.e. where Load_Date = '01Jan2016' which is the min(Load_Date) from hive table)
I tried with couple of options by mentioning in subquery in Hive sql editor in 'tHiveInput' component
I am using  tHiveConnection and tHiveInput components to extract the data from Hive table and I tried below couple of options and its not working:-
1) By using sub query in where cluase in sql editor in Hiveinput component :-
Select coli1,col2,......Load_Date 
FROM 
tl.agents_updates
where
agents_updates.Load_Date = select min(Load_Date) as min_Load_Date from tl.agents_updates
2) I tried getting min(Load_Date) using aggregator follwed by tHiveInput componet and writing that value in to the context variable (minloaddate) using 'tAggregateRow' and 'tJavaRow' components.
and in another flow/subjob tried mentioning where clause in the thiveinput component like 
where agents_updates.load_date = '"+context.minloaddate+"'"
the context variable in where clause above is not substituting with any value (which is loaded by tjavarow) ,hence getting zero rows!
Could you help me on this to resolve the same and any urgent help will be appreciated.
Please see the attached screenshot for the job design.
Thanks
1 ACCEPTED SOLUTION

Accepted Solutions
One Star

Re: [resolved] Extracting earliest load_date's data from Hive tables - ISSUE

Manage to resolve this issue as the value of min date is not converting (double to string) properly when i pass to the where clause.
I had to convert double to integer and then integer to string.
On tjavarow,i did the below and its working fine.

int aloadtime=Integer.valueOf(row2.loadtime.intValue());
context.minloadtime=Integer.toString(aloadtime);
System.out.println("my context variable value" +context.minloadtime);
1 REPLY
One Star

Re: [resolved] Extracting earliest load_date's data from Hive tables - ISSUE

Manage to resolve this issue as the value of min date is not converting (double to string) properly when i pass to the where clause.
I had to convert double to integer and then integer to string.
On tjavarow,i did the below and its working fine.

int aloadtime=Integer.valueOf(row2.loadtime.intValue());
context.minloadtime=Integer.toString(aloadtime);
System.out.println("my context variable value" +context.minloadtime);