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 59DF1F3D2 for ; Mon, 8 Apr 2013 13:38:45 +0000 (UTC) Received: (qmail 51628 invoked by uid 500); 8 Apr 2013 13:38:43 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 51181 invoked by uid 500); 8 Apr 2013 13:38:43 -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 51144 invoked by uid 99); 8 Apr 2013 13:38:41 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 08 Apr 2013 13:38:41 +0000 X-ASF-Spam-Status: No, hits=-2.8 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_HI,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of steffen.lutter@sap.com designates 155.56.66.98 as permitted sender) Received: from [155.56.66.98] (HELO smtpgw.sap-ag.de) (155.56.66.98) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 08 Apr 2013 13:38:37 +0000 From: "LUTTER, Steffen" To: "user@hive.apache.org" Subject: RE: Syntax for filters on timstamp data type Thread-Topic: Syntax for filters on timstamp data type Thread-Index: Ac4yBWXQTcx7e2YeTUCpGyv3n4Zjwv//4ocA///Y7BCAAFFyAP/7XcDg Date: Mon, 8 Apr 2013 13:38:13 +0000 Message-ID: <02C37907F518FB4482AF3EDED498DBCF28E2C605@DEWDFEMB10A.global.corp.sap> References: <02C37907F518FB4482AF3EDED498DBCF28E2C12A@DEWDFEMB10A.global.corp.sap> <02C37907F518FB4482AF3EDED498DBCF28E2C165@DEWDFEMB10A.global.corp.sap> In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.21.40.39] Content-Type: multipart/alternative; boundary="_000_02C37907F518FB4482AF3EDED498DBCF28E2C605DEWDFEMB10Aglob_" MIME-Version: 1.0 X-Virus-Checked: Checked by ClamAV on apache.org --_000_02C37907F518FB4482AF3EDED498DBCF28E2C605DEWDFEMB10Aglob_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi Mark, Correct, I just did some tests and the cast is the way to go. While for com= parison operations (equal, diff, ...) implicit casts work, this is not the = case for the IN clause. I think it should, as eventually this just translat= es to a disjunction of comparisons so it should be the same. Anyway, I have a working solution now. For the record I paste two working e= xample queries below. Thanks a lot for your help !!! Steffen Example 1: SELECT * FROM table1 WHERE datecol =3D CAST('2009-01-17 00:00= :00' AS timestamp) Example 2: SELECT * FROM table1 WHERE datecol IN (CAST ('2009-01-11 00:00:= 00' AS timestamp), CAST ('2009-01-08 00:00:00' AS timestamp) ) From: Mark Grover [mailto:grover.markgrover@gmail.com] Sent: 05 April 2013 18:43 To: user@hive.apache.org Subject: Re: Syntax for filters on timstamp data type Steffan, One thing that may be different is that equal can cast operands to make equ= als work but that may not be true for IN. FWIW, this is me just speculating= , I haven't looked at the code just yet. Perhaps, you could explicit casting to get around this? On Fri, Apr 5, 2013 at 7:36 AM, LUTTER, Steffen > wrote: Equal, not equal, less than, less or equal, greater than, greater or equal = all work. Also the function execution in the IN clause seems to work, as th= e error message states that the result type is bigint. Following the error = message, it expects the input as timestamp, but I couldn't find a syntax to= express timestamps in HiveQL. Two questions remain: 1) How to express timestamps in HiveQL? 2) Why doesn't the IN clause support comparisons between timestamp and= bigint, if "equal" and so on does? Thanks for any thought in this, Steffen From: Nitin Pawar [mailto:nitinpawar432@gmail.com] Sent: 05 April 2013 16:11 To: user@hive.apache.org Subject: Re: Syntax for filters on timstamp data type I am not sure IN clause supports executing functions in the query did it fail when you tried less than greater than type On Fri, Apr 5, 2013 at 7:36 PM, LUTTER, Steffen > wrote: Hi, I have a question regarding filters on timestamps. The syntax seems to be U= NIX_TIMESTAMP('yyyy-MM-dd hh:mm:ss'), is there another way to express a dat= etime type? The problem is that I get an exception when using the IN = syntax, while the equal comparison works without problems. Example: SELECT * FROM table1 WHERE datecol IN ( UNIX_TIMESTAMP('2009-01-0= 5 00:00:00'),UNIX_TIMESTAMP('2009-01-10 00:00:00') ) Throws exception: Caused by: java.sql.SQLException: Query returned non-zero code: 10014, caus= e: FAILED: SemanticException [Error 10014]: Line 5:21 Wrong arguments ''200= 9-01-10 00:00:00'': The arguments for IN should be the same type! Types are= : {timestamp IN (bigint, bigint)} at org.apache.hadoop.hive.jdbc.HiveStatement.executeQuery(HiveStatem= ent.java:189) at org.apache.hadoop.hive.jdbc.HiveStatement.execute(HiveStatement.j= ava:127) at com.sap.connectivity.cs.java.drivers.jdbc.JDBCAPI$Statement.execu= te(JDBCAPI.java:1648) at com.sap.connectivity.cs.java.drivers.jdbc.JDBCDriver.prepare(JDBC= Driver.java:1760) ... 15 more Following query works: SELECT * FROM table1 WHERE datecol =3D UNIX_TIMESTAMP('2009-01-17 00:00:= 00') Is there another syntax for datetime types? Could it be a bug in the filter= "IN " operation? Thanks in advance, Steffen -- Nitin Pawar --_000_02C37907F518FB4482AF3EDED498DBCF28E2C605DEWDFEMB10Aglob_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi Mark,

 <= /p>

Correct, I= just did some tests and the cast is the way to go. While for comparison op= erations (equal, diff, …) implicit casts work, this is not the case for the IN clause. I think it should, as eventually this just tra= nslates to a disjunction of comparisons so it should be the same.

 = ;

Anyway, I = have a working solution now. For the record I paste two working example que= ries below.

 = ;

Thanks a l= ot for your help !!!

 = ;

Steffen

 = ;

Example 1:= SELECT * FROM  table1 WHERE datecol  =3D  CAST('2009-01-17 = 00:00:00' AS timestamp)

Example 2:= SELECT * FROM  table1 WHERE datecol IN (CAST ('2009-01-11 00:00:00' A= S timestamp), CAST ('2009-01-08 00:00:00' AS timestamp) )=

 = ;

From: Mark Grover [mailto:grover.markgrover@gmail.com]
Sent: 05 April 2013 18:43
To: user@hive.apache.org
Subject: Re: Syntax for filters on timstamp data type

 

Steffan,

One thing that may be different is that equal can ca= st operands to make equals work but that may not be true for IN. FWIW, this= is me just speculating, I haven't looked at the code just yet.<= /p>

 

Perhaps, you could ex= plicit casting to get around this?

On Fri, Apr 5, 2013 at 7:36 AM, LUTTER, Steffen <= steffen.lutter@= sap.com> wrote:

Equal, not equal, less t= han, less or equal, greater than, greater or equal all work. Also the function execution in the IN clause seems to work, as the error m= essage states that the result type is bigint. Following the error message, = it expects the input as timestamp, but I couldn’t find a syntax to ex= press timestamps in HiveQL.

 =

Two questions remain:

 =

1)      How to express timestamps = in HiveQL?

2)      Why doesn’t the IN c= lause support comparisons between timestamp and bigint, if “equalR= 21; and so on does?

 =

Thanks for any thought i= n this,

 =

Steffen

 =

From: Nitin Pawar [mailto:nitinpawar432@gmail.com]
Sent: 05 April 2013 16:11
To: user@h= ive.apache.org
Subject: Re: Syntax for filters on timstamp data type

 

I am not sure IN clause supports executing functions in the query&= nbsp;

 

did it fail when you tried  less than greater than type =

 

On Fri, Apr 5, 2013 at 7:36 PM, LUTTER, Steffen <steffen.lutter@sap.com>= ; wrote:

Hi,=

 

I have a question reg= arding filters on timestamps. The syntax seems to be UNIX_TIMESTAMP('yyyy-M= M-dd hh:mm:ss'), is there another way to express a datetime type? The problem is that I get an exception when using= the IN <list> syntax, while the equal comparison works without probl= ems.

 

Example: SELECT * FROM  table1 WHERE datecol IN ( UNIX_TIMESTAMP= ('2009-01-05 00:00:00'),UNIX_TIMESTAMP('2009-01-10 00:00:00')  )

 

Throws exception:

 

Caused by: java.sql.SQLException: Query returned non-zero code: 10014, cause: FAILE= D: SemanticException [Error 10014]: Line 5:21 Wrong arguments ''2009-01-10 = 00:00:00'': The arguments for IN should be the same type! Types are: {times= tamp IN (bigint, bigint)}

       at org.apache.hadoop.= hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:189)

       at org.apache.hadoop.= hive.jdbc.HiveStatement.execute(HiveStatement.java:127)

       at com.sap.connectivi= ty.cs.java.drivers.jdbc.JDBCAPI$Statement.execute(JDBCAPI.java:1648)=

       at com.sap.connectivi= ty.cs.java.drivers.jdbc.JDBCDriver.prepare(JDBCDriver.java:1760)

       ... 15 more

 

Following query works= :

 

SELECT * FROM  table1 WHERE datecol  =3D  U= NIX_TIMESTAMP('2009-01-17 00:00:00')

 

Is there another synt= ax for datetime types? Could it be a bug in the filter “IN <list&g= t;” operation?

 

Thanks in advance,

 

Steffen

 



 

--
Nitin Pawar

 

--_000_02C37907F518FB4482AF3EDED498DBCF28E2C605DEWDFEMB10Aglob_--