Hi Venkatesh, So my shell script should be like this right?
*#!/bin/bash* *HADOOP_HOME=/home/hadoop/latest* *export HADOOP_HOME* *JAVA_HOME=/usr/jdk/latest* *export JAVA_HOME* *HIVE_OPTS="$HIVE_OPTS -hiveconf mapred.job.queue.name=hdmi-technology"* *export HIVE_OPTS* *DATE_YEST=`TZ=GMT+48 date +%Y%m%d`* *echo $DATE_YEST* *hive -S -e "SELECT * FROM REALTIME WHERE dt='$DATE_YEST' LIMIT 5"* On Tue, Aug 7, 2012 at 1:28 PM, Venkatesh Kavuluri <vkavul...@outlook.com>wrote: > 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: carla.stae...@nokia.com > To: user@hive.apache.org > 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: user@hive.apache.org > 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, <carla.stae...@nokia.com> 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:pipeha...@gmail.com] > Sent: Monday, August 06, 2012 15:00 > To: user@hive.apache.org > 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? > > > > > > > > >
