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

Anonymous — Sat, 16 Nov 2024 10:32:38 GMT

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

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

Anonymous — Thu, 30 Jun 2016 09:41:20 GMT

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);

topic [resolved] Extracting earliest load_date's data from Hive tables - ISSUE in Talend Studio

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

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