Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 6D10A110E3 for ; Sun, 24 Aug 2014 18:57:17 +0000 (UTC) Received: (qmail 22675 invoked by uid 500); 24 Aug 2014 18:57:14 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 22603 invoked by uid 500); 24 Aug 2014 18:57:14 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 22590 invoked by uid 99); 24 Aug 2014 18:57:14 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 24 Aug 2014 18:57:14 +0000 X-ASF-Spam-Status: No, hits=2.2 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_NEUTRAL X-Spam-Check-By: apache.org Received-SPF: neutral (athena.apache.org: local policy) Received: from [209.85.219.52] (HELO mail-oa0-f52.google.com) (209.85.219.52) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 24 Aug 2014 18:57:10 +0000 Received: by mail-oa0-f52.google.com with SMTP id o6so10030974oag.39 for ; Sun, 24 Aug 2014 11:56:49 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to:content-type; bh=3DSJupqOnEpvgNk6e8jrm0baq9OXWJjcU6rOggz6VcQ=; b=MlE17keMPUertbtPQuMUkOVd3QSWJlgxw2jf7+3Ip0KRISx50qIPHlSLLBhpvV7yZg 0b8x9lOWjCO/mtJbuJbfp9OsF44oz4D7FyiLpHoC4xt8A44mXOnKcwXBKyAsI8Caf7A8 I6zOkjJE5hxSo8y9PpIVerHpcAbCgYnF4DM+DBQqsHO6hSvy8FLoUMA36fGa8as6ymYs c4YOqXATF+7y9IZAr6wMNGD6WLonr0Fscr+SM/LW7rZRZrTwpeLoMP6tjPpuZtSi+IhX P924wJMuHj+/hdoEHxvMfTfUgiR6bba53X8aY8PjhrRD/FD4jRkXuHmz0IgC0GjEYIYI 7w7g== X-Gm-Message-State: ALoCoQl7kR0BowlSLEISmRKzSzKlo00E7Jbmd//YTO6NUP4KeNrTL+BqusNeaPYegZ7Ulg/43OB7 MIME-Version: 1.0 X-Received: by 10.60.74.232 with SMTP id x8mr3635oev.84.1408906609067; Sun, 24 Aug 2014 11:56:49 -0700 (PDT) Received: by 10.76.102.43 with HTTP; Sun, 24 Aug 2014 11:56:48 -0700 (PDT) In-Reply-To: References: Date: Sun, 24 Aug 2014 11:56:48 -0700 Message-ID: Subject: Re: List of dates as arguments From: Bala Krishna Gangisetty To: user@hive.apache.org Content-Type: multipart/alternative; boundary=001a113483b4f4a224050164a14d X-Virus-Checked: Checked by ClamAV on apache.org --001a113483b4f4a224050164a14d Content-Type: text/plain; charset=UTF-8 The query can be modified further with one variable with the of date_sub() UDF function, available by default. Suppose currentdate is the argument whose value is current date, the query will look like: SELECT * FROM *table_name* WHERE *date_column* BETWEEN date_sub(*${hiveconf:currentdate}, 15)* AND *${hiveconf:currentdate}* However, the query comes with an overhead of UDF execution. --Bala G. On Sun, Aug 24, 2014 at 11:25 AM, Bala Krishna Gangisetty < bala@altiscale.com> wrote: > Here is my understanding on your requirements. Let me know if I am missing > something. You, > > a) would like to run a query daily to find top 10 products in the past 15 > days > b) would like to pass dates dynamically as arguments to HIVE query > > Given the requirement a), passing just two variables(startdate and > enddate) to HIVE query will suffice to achieve the requirement b). > > Assuming startdate and enddate variables are passed to HIVE query, the > query will look like below. > > SELECT * FROM *table_name* WHERE *date_column* BETWEEN > *${hiveconf:startdate}* AND *${hiveconf:enddate}* > > Note, values for startdate and enddate must be enclosed in ' '. > > Hope this helps. > > --Bala G. > > > On Sun, Aug 24, 2014 at 12:57 AM, Nitin Pawar > wrote: > >> I am not sure if you can transform array from shell to java, you may want >> to write your own custom UDF for that >> >> if these are continuous dates, then you can have less than greater than >> comparison >> >> >> On Sun, Aug 24, 2014 at 12:39 PM, karthik Srivasthava < >> karthiksrivasthava@gmail.com> wrote: >> >>> Nitin, >>> Teja >>> >>> Thank you.. I exactly need what Teja suggested... i need list of dates >>> between start date and end date.... >>> >>> >>> On Sun, Aug 24, 2014 at 2:05 AM, Teja Kunapareddy < >>> tejakunapareddy@gmail.com> wrote: >>> >>>> Thanks Nithin For your reply.. I can get start date and end date,. But >>>> can i get all the dates with in START DATE AND END DATE.??? . so that my >>>> query looks something like this >>>> >>>> "Select a, b, c from table_x where date in (${hiveconf:LIST_OF >>>> DATES})" >>>> >>>> >>>> On 24 August 2014 01:18, Nitin Pawar wrote: >>>> >>>>> with your shell script calculate your start date and end date >>>>> hive $HIVEPARAMS -hiveconf startdate="$var1" -hiveconf >>>>> enddate="$var2" >>>>> >>>>> also set in ..hiverc >>>>> set hive.variable.substitute=true; >>>>> >>>>> >>>>> On Sun, Aug 24, 2014 at 10:19 AM, karthik Srivasthava < >>>>> karthiksrivasthava@gmail.com> wrote: >>>>> >>>>>> As my raw-data table is partitioned by date.. i want to get data to >>>>>> run a query every days to find top 10 products in last 15 days . >>>>>> >>>>>> How to pass list of dates dynamically as arguments in hive query >>>>>> using hiveconf? >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> Nitin Pawar >>>>> >>>> >>>> >>> >> >> >> -- >> Nitin Pawar >> > > --001a113483b4f4a224050164a14d Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
The query can be modified further with one variable with t= he of date_sub() UDF function, available by default.=C2=A0

Suppose currentdate is the argument whose value is current date, the que= ry will look like:
=C2=A0
SELECT * FROM=C2=A0table_name=C2=A0WHERE=C2=A0date_column=C2=A0BETWEEN=C2=A0date_sub(${hiveconf:currentdate}, 15)=C2=A0AND =C2=A0${hiveconf:curre= ntdate}

However, the query comes with = an overhead of UDF execution.=C2=A0

--Bala G.


On Sun, A= ug 24, 2014 at 11:25 AM, Bala Krishna Gangisetty <bala@altiscale.com&= gt; wrote:
Here is my understanding on= your requirements. Let me know if I am missing something. You,
a) would like to run a query daily to find top 10 products in th= e past 15 days
b) would like to pass dates dynamically as argumen= ts to HIVE query

Given the requirement a), passing just two variab= les(startdate and enddate) to HIVE query will suffice to achieve the requir= ement b).=C2=A0

Assuming startdate and enddate var= iables are passed to HIVE query, the query will look like below.

SELECT * FROM table_name WHERE date_column BETWEEN ${hiveconf:startdate} AND =C2=A0${hiveconf:enddate}=

Note, values for startdate and enddate must be en= closed in ' '.=C2=A0

Hope this helps.

--Bala G.
=

On Sun, Aug 24, 2014 at 12:57 AM, Nitin = Pawar <nitinpawar432@gmail.com> wrote:
I am not sure if you can tr= ansform array from shell to java, you may want to write your own custom UDF= for that=C2=A0

if these are continuous dates, then you can have less than g= reater than comparison=C2=A0


On Sun, Aug 24, 2014 at 12:39 PM, karthik Srivasthava = <karth= iksrivasthava@gmail.com> wrote:
Nitin,
Teja
<= br>
Thank you.. I exactly need what Teja suggested... i need list= of dates between start date and end date....=C2=A0


On Sun, Aug 24, 2014 at 2:05 AM, Teja Kunapareddy <tejakunapareddy= @gmail.com> wrote:
Thanks Nithin For your reply.. I can get start d= ate and end date,. But can i get all the dates with in START DATE AND END D= ATE.??? . so that my query looks something like this

"Select =C2=A0a, b, c from table_x where= date in =C2=A0(${hiveconf:LIST_OF DATES})" =C2=A0=C2=A0


On 24 August 2014 01:18, Nitin Pawar <nitinpawar432@gmail.com= > wrote:
with your shell scrip= t calculate your start date and end date=C2=A0
hive $HIVEPARAMS -hiveconf startdate=3D"$var1" =C2=A0-hiveconf e= nddate=3D"$var2"=C2=A0

also set in ..hiverc=C2=A0
set hive.variable.substitute=3Dtrue;


On Sun, Aug 24, 2014 at 10:19 AM, karthik Srivasthava <ka= rthiksrivasthava@gmail.com> wrote:
As my raw-data table is partitioned = by date.. i want to get data to run a query every days to find top 10 produ= cts in last 15 days .

How to pass list of dates dynamically as arguments in h= ive query using hiveconf?





--
Nitin Pawar





<= /div>--
Nitin Pawar


--001a113483b4f4a224050164a14d--