Since the UDF unix_timestamp() is a non-deterministic function, Hive query planner doesn't run partition pruning based the 'dt' column value. If your table is partitioned by 'dt' column, the query would end up scanning entire table. It is ideal to compute the required date value dynamically in a shell script if you are working against partitioned columns. If your version of Hive doesn't support variable substitution based on hiveconf, you can achieve the same as shown below hive -e "SELECT .... FROM <table> where dt=$date_var;" Thanks,Venkatesh
From: [email protected] To: [email protected] Subject: RE: (Get the current date -1) in Hive Date: Mon, 6 Aug 2012 23:26:46 +0000 In the case here it literally is taking the UNIX timestamp, formatting it in yyyy-mm-dd format and then subtracting the specified integer (in this case 1) Sent from my Lumia 900 From: ext Techy Teck Sent: 8/6/2012 3:37 PM To: [email protected] Subject: Re: (Get the current date -1) in Hive Thanks Carla for the suggestion, I am currently using Hive 0.6 and that Hive version doesn't supports variable substitution with hiveconf variable, so that is the reason I was looking for some other alternative- So you are saying basically, If I add your suggestion in my query like below- select * from lip_data_quality where dt = 'date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1)'; Then the above query will be interpreted as like below- select * from lip_data_quality where dt = '2012-08-05'; Am I right? And what does date_sub do here? I am not familiar with that. Correct me if I am wrong. On Mon, Aug 6, 2012 at 12:23 PM, <[email protected]> wrote: If you are just using it in a query, you can do this: date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1) I generally do my date calculations in a shell script and pass them in with a hiveconf variable. Carla -----Original Message----- From: ext Yue Guan [mailto:[email protected]] Sent: Monday, August 06, 2012 15:00 To: [email protected] Subject: Re: (Get the current date -1) in Hive guess you can use sub_date, but you have to get today by some outside script. On 08/06/2012 02:10 PM, Techy Teck wrote: > Is there any way to get the current date -1 in Hive means yesterdays > date always? > > >
