You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by ilhami Kalkan <il...@intellica.net> on 2014/08/12 13:22:30 UTC
hive query with in statement
Hi all,
I have a problem with IN statement in HiveQL. My table "cdr", column
"calldate" which type is "date". First query is successfully return:
select * from cdr where calldate = '2014-05-02';
But when query with IN statement,
select * from cdr where calldate in ( '2014-08-11','2014-05-02');
it returns below exception:
Error: Error while processing statement: FAILED: SemanticException
[Error 10014]: Line 1:38 Wrong arguments ''20014-03-02'': The arguments
for IN should be the same type! Types are: {date IN (string, string)}
(state=42000,code=10014)
How can I handle this?
Thanks.
Hive version 0.12
Re: hive query with in statement
Posted by Seeling Cheung <se...@yahoo.com>.
>>if I use calldate with cast operator it works: select * from table1 where cast(calldate as string) in ( '2014-08-11','2014-05-02');
As one other user, Tuong, suggested, it would be better to use cast in this following way:
select * from table1 where calldate in (
cast( '2014-08-11' as date), cast('2014-05-02' as date));
This provides 2 benefits:
- correct semantic comparison for date values
- better performance as the cast is only done for the 2 literal values, not for every row of calldate
________________________________
From: ilhami Kalkan <il...@intellica.net>
To: user@hive.apache.org
Sent: Wednesday, August 13, 2014 6:03 AM
Subject: Re: hive query with in statement
Hi Kevin,
I'm using 0.12 version and IN statement works fine except this
situation:
select * from table1 where callhour in (1,2,3,4); --> success
(callhour type: int)
select * from table1 where name in ('foo1','foo2','foo3','foo4');
--> success
(name type: string)
select * from table1 where calldate = '2014-08-11'; --> success
(calldate type:date)
select * from table1 where calldate < '2014-08-11'; -->
success
(calldate type:date)
select * from table1 where calldate in (
'2014-08-11','2014-05-02'); -->error
(calldate type:date)
Error: Error while processing statement: FAILED: SemanticException
[Error 10014]: Line 1:38 Wrong arguments ''20014-03-02'': The
arguments
for IN should be the same type! Types are: {date IN (string,
string)}
(state=42000,code=10014)
if I use calldate with cast operator it works:
select * from table1 where cast(calldate as string) in (
'2014-08-11','2014-05-02');
On 13-08-2014 15:49, Kevin Weiler wrote:
This is a relatively old stack overflow post. I’m not sure what version you guys are using, but IN seems to work just fine for me.
--
Kevin Weiler
IT
IMC Financial Markets | 233 S. Wacker Drive, Suite 4300 | Chicago, IL 60606 | http://imc-chicago.com/
Phone: +1 312-204-7439 | Fax: +1 312-244-3301 | E-Mail: kevin.weiler@imc-chicago.com
On Aug 13, 2014, at 12:58 AM, Sreenath <sr...@gmail.com> wrote:
Hi,
>
>
>hive doesn't support IN clause. you might want to check out http://stackoverflow.com/questions/7677333/how-to-write-subquery-and-use-in-clause-in-hive
>
>
>
>On 12 August 2014 17:07, ilhami Kalkan <il...@hotmail.com> wrote:
>
>Hi all,
>>I have a problem with IN statement in HiveQL. My
table "cdr", column
>>"calldate" which type is "date". First query is
successfully return:
>>select * from cdr where calldate = '2014-05-02';
>>
>>But when query with IN statement,
>>
>>select * from cdr where calldate in (
'2014-08-11','2014-05-02');
>>
>>it returns below exception:
>>
>>Error: Error while processing statement: FAILED:
SemanticException
>>[Error 10014]: Line 1:38 Wrong arguments
''20014-03-02'': The arguments
>>for IN should be the same type! Types are: {date IN
(string, string)}
>>(state=42000,code=10014)
>>
>>How can I handle this?
>>Thanks.
>>
>>Hive version 0.12
>>
>>
>>
>>
>>
>>
>>
>>
>
>
>
>
--
>
>Sreenath S Kamath
>Bangalore
>Ph No:+91-9590989106
________________________________
The information in this e-mail is intended only for the person
or entity to which it is addressed.
It may contain confidential and /or privileged material. If
someone other than the intended recipient should receive this
e-mail, he / she shall not be entitled to read, disseminate,
disclose or duplicate it.
If you receive this e-mail unintentionally, please inform us
immediately by "reply" and then delete it from your system.
Although this information has been compiled with great care,
neither IMC Financial Markets & Asset Management nor any of
its related entities shall accept any responsibility for any
errors, omissions or other inaccuracies in this information or
for the consequences thereof, nor shall it be bound in any way
by the contents of this e-mail or its attachments. In the event
of incomplete or incorrect transmission, please return the
e-mail to the sender and permanently delete this message and any
attachments.
Messages and attachments are scanned for all known viruses.
Always scan attachments before opening them.
Re: hive query with in statement
Posted by ilhami Kalkan <il...@intellica.net>.
Hi Kevin,
I'm using 0.12 version and IN statement works fine except this situation:
select * from table1 where callhour in (1,2,3,4); --> success
(callhour type: int)
select * from table1 where name in ('foo1','foo2','foo3','foo4'); -->
success
(name type: string)
select * from table1 where calldate = '2014-08-11'; --> success
(calldate type:date)
select * from table1 where calldate < '2014-08-11'; --> success
(calldate type:date)
select * from table1 where calldate in ( '2014-08-11','2014-05-02');
-->error
(calldate type:date)
Error: Error while processing statement: FAILED: SemanticException
[Error 10014]: Line 1:38 Wrong arguments ''20014-03-02'': The arguments
for IN should be the same type! Types are: {date IN (string, string)}
(state=42000,code=10014)
if I use calldate with cast operator it works:
select * from table1 where cast(calldate as string) in (
'2014-08-11','2014-05-02');
On 13-08-2014 15:49, Kevin Weiler wrote:
> This is a relatively old stack overflow post. I’m not sure what
> version you guys are using, but IN seems to work just fine for me.
>
> --
> *Kevin Weiler*
>
> IT
>
> IMC Financial Markets | 233 S. Wacker Drive, Suite 4300 | Chicago, IL
> 60606 | http://imc-chicago.com/
> Phone: +1 312-204-7439 | Fax: +1 312-244-3301 | E-Mail:
> _kevin.weiler@imc-chicago.com <ma...@imc-chicago.com>_
>
> On Aug 13, 2014, at 12:58 AM, Sreenath <sreenaths1923@gmail.com
> <ma...@gmail.com>> wrote:
>
>> Hi,
>>
>> hive doesn't support IN clause. you might want to check out
>> http://stackoverflow.com/questions/7677333/how-to-write-subquery-and-use-in-clause-in-hive
>>
>>
>> On 12 August 2014 17:07, ilhami Kalkan <ilhami1985@hotmail.com
>> <ma...@hotmail.com>> wrote:
>>
>> Hi all,
>> I have a problem with IN statement in HiveQL. My table "cdr", column
>> "calldate" which type is "date". First query is successfully return:
>> select * from cdr where calldate = '2014-05-02';
>>
>> But when query with IN statement,
>>
>> select * from cdr where calldate in ( '2014-08-11','2014-05-02');
>>
>> it returns below exception:
>>
>> Error: Error while processing statement: FAILED: SemanticException
>> [Error 10014]: Line 1:38 Wrong arguments ''20014-03-02'': The
>> arguments
>> for IN should be the same type! Types are: {date IN (string, string)}
>> (state=42000,code=10014)
>>
>> How can I handle this?
>> Thanks.
>>
>> Hive version 0.12
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> --
>> Sreenath S Kamath
>> Bangalore
>> Ph No:+91-9590989106 <tel:%2B91-9590989106>
>
>
> ------------------------------------------------------------------------
>
> The information in this e-mail is intended only for the person or
> entity to which it is addressed.
>
> It may contain confidential and /or privileged material. If someone
> other than the intended recipient should receive this e-mail, he / she
> shall not be entitled to read, disseminate, disclose or duplicate it.
>
> If you receive this e-mail unintentionally, please inform us
> immediately by "reply" and then delete it from your system. Although
> this information has been compiled with great care, neither IMC
> Financial Markets & Asset Management nor any of its related entities
> shall accept any responsibility for any errors, omissions or other
> inaccuracies in this information or for the consequences thereof, nor
> shall it be bound in any way by the contents of this e-mail or its
> attachments. In the event of incomplete or incorrect transmission,
> please return the e-mail to the sender and permanently delete this
> message and any attachments.
>
> Messages and attachments are scanned for all known viruses. Always
> scan attachments before opening them.
Re: hive query with in statement
Posted by Kevin Weiler <Ke...@imc-chicago.com>.
This is a relatively old stack overflow post. I’m not sure what version you guys are using, but IN seems to work just fine for me.
--
Kevin Weiler
IT
IMC Financial Markets | 233 S. Wacker Drive, Suite 4300 | Chicago, IL 60606 | http://imc-chicago.com/
Phone: +1 312-204-7439 | Fax: +1 312-244-3301 | E-Mail: kevin.weiler@imc-chicago.com<ma...@imc-chicago.com>
On Aug 13, 2014, at 12:58 AM, Sreenath <sr...@gmail.com>> wrote:
Hi,
hive doesn't support IN clause. you might want to check out http://stackoverflow.com/questions/7677333/how-to-write-subquery-and-use-in-clause-in-hive
On 12 August 2014 17:07, ilhami Kalkan <il...@hotmail.com>> wrote:
Hi all,
I have a problem with IN statement in HiveQL. My table "cdr", column
"calldate" which type is "date". First query is successfully return:
select * from cdr where calldate = '2014-05-02';
But when query with IN statement,
select * from cdr where calldate in ( '2014-08-11','2014-05-02');
it returns below exception:
Error: Error while processing statement: FAILED: SemanticException
[Error 10014]: Line 1:38 Wrong arguments ''20014-03-02'': The arguments
for IN should be the same type! Types are: {date IN (string, string)}
(state=42000,code=10014)
How can I handle this?
Thanks.
Hive version 0.12
--
Sreenath S Kamath
Bangalore
Ph No:+91-9590989106<tel:%2B91-9590989106>
________________________________
The information in this e-mail is intended only for the person or entity to which it is addressed.
It may contain confidential and /or privileged material. If someone other than the intended recipient should receive this e-mail, he / she shall not be entitled to read, disseminate, disclose or duplicate it.
If you receive this e-mail unintentionally, please inform us immediately by "reply" and then delete it from your system. Although this information has been compiled with great care, neither IMC Financial Markets & Asset Management nor any of its related entities shall accept any responsibility for any errors, omissions or other inaccuracies in this information or for the consequences thereof, nor shall it be bound in any way by the contents of this e-mail or its attachments. In the event of incomplete or incorrect transmission, please return the e-mail to the sender and permanently delete this message and any attachments.
Messages and attachments are scanned for all known viruses. Always scan attachments before opening them.
Re: hive query with in statement
Posted by Sreenath <sr...@gmail.com>.
Hi,
hive doesn't support IN clause. you might want to check out
http://stackoverflow.com/questions/7677333/how-to-write-subquery-and-use-in-clause-in-hive
On 12 August 2014 17:07, ilhami Kalkan <il...@hotmail.com> wrote:
> Hi all,
> I have a problem with IN statement in HiveQL. My table "cdr", column
> "calldate" which type is "date". First query is successfully return:
> select * from cdr where calldate = '2014-05-02';
>
> But when query with IN statement,
>
> select * from cdr where calldate in ( '2014-08-11','2014-05-02');
>
> it returns below exception:
>
> Error: Error while processing statement: FAILED: SemanticException
> [Error 10014]: Line 1:38 Wrong arguments ''20014-03-02'': The arguments
> for IN should be the same type! Types are: {date IN (string, string)}
> (state=42000,code=10014)
>
> How can I handle this?
> Thanks.
>
> Hive version 0.12
>
>
>
>
>
>
>
>
--
Sreenath S Kamath
Bangalore
Ph No:+91-9590989106
hive query with in statement
Posted by ilhami Kalkan <il...@hotmail.com>.
Hi all,
I have a problem with IN statement in HiveQL. My table "cdr", column
"calldate" which type is "date". First query is successfully return:
select * from cdr where calldate = '2014-05-02';
But when query with IN statement,
select * from cdr where calldate in ( '2014-08-11','2014-05-02');
it returns below exception:
Error: Error while processing statement: FAILED: SemanticException
[Error 10014]: Line 1:38 Wrong arguments ''20014-03-02'': The arguments
for IN should be the same type! Types are: {date IN (string, string)}
(state=42000,code=10014)
How can I handle this?
Thanks.
Hive version 0.12
hive query with in statement
Posted by ilhami Kalkan <il...@intellica.net>.
Hi all,
I have a problem with IN statement in HiveQL. My table "cdr", column
"calldate" which type is "date". First query is successfully return:
select * from cdr where calldate = '2014-05-02';
But when query with IN statement,
select * from cdr where calldate in ( '2014-08-11','2014-05-02');
it returns below exception:
Error: Error while processing statement: FAILED: SemanticException
[Error 10014]: Line 1:38 Wrong arguments ''20014-03-02'': The arguments
for IN should be the same type! Types are: {date IN (string, string)}
(state=42000,code=10014)
How can I handle this?
Thanks.
Hive version 0.12
Re: hive query with in statement
Posted by "Tuong Tr." <ir...@yahoo.com>.
Ilhami,
You probably better off casting the arguments for a couple of benefits:
select * from cdr where calldate in (cast('2014-08-11' as date),cast('2014-05-02' as date))
1. Comparison is done in proper type semantics, and bad argument to IN will raise proper error as opposed to producing incorrect result.
2. Hive *should* do the casting once for the IN arguments per query execution.
Casting calldate column will be done for every row which in big data will have some perf impact.
Respectfully,
Tuong
________________________________
From: ilhami Kalkan <il...@intellica.net>
To: user@hive.apache.org
Sent: Wednesday, August 13, 2014 1:14 AM
Subject: Re: hive query with in statement
Thanks Navis it works.
On 13-08-2014 09:03, Navis류승우 wrote:
Could you try "cast(calldate as string)"?
>
>
>Thanks,
>Navis
>
>
>
>
>
>2014-08-12 20:22 GMT+09:00 ilhami Kalkan <il...@intellica.net>:
>
>Hi all,
>>I have a problem with IN statement in HiveQL. My table
"cdr", column "calldate" which type is "date". First
query is successfully return:
>>select * from cdr where calldate = '2014-05-02';
>>
>>But when query with IN statement,
>>
>>select * from cdr where calldate in (
'2014-08-11','2014-05-02');
>>
>>it returns below exception:
>>
>>Error: Error while processing statement: FAILED:
SemanticException [Error 10014]: Line 1:38 Wrong
arguments ''20014-03-02'': The arguments for IN should
be the same type! Types are: {date IN (string,
string)} (state=42000,code=10014)
>>
>>How can I handle this?
>>Thanks.
>>
>>Hive version 0.12
>>
>>
>>
>>
>
Re: hive query with in statement
Posted by ilhami Kalkan <il...@intellica.net>.
Thanks Navis it works.
On 13-08-2014 09:03, Navis류승우 wrote:
> Could you try "cast(calldate as string)"?
>
> Thanks,
> Navis
>
>
> 2014-08-12 20:22 GMT+09:00 ilhami Kalkan <ilhami.kalkan@intellica.net
> <ma...@intellica.net>>:
>
> Hi all,
> I have a problem with IN statement in HiveQL. My table "cdr",
> column "calldate" which type is "date". First query is
> successfully return:
> select * from cdr where calldate = '2014-05-02';
>
> But when query with IN statement,
>
> select * from cdr where calldate in ( '2014-08-11','2014-05-02');
>
> it returns below exception:
>
> Error: Error while processing statement: FAILED: SemanticException
> [Error 10014]: Line 1:38 Wrong arguments ''20014-03-02'': The
> arguments for IN should be the same type! Types are: {date IN
> (string, string)} (state=42000,code=10014)
>
> How can I handle this?
> Thanks.
>
> Hive version 0.12
>
>
>
>
Re: hive query with in statement
Posted by Navis류승우 <na...@nexr.com>.
Could you try "cast(calldate as string)"?
Thanks,
Navis
2014-08-12 20:22 GMT+09:00 ilhami Kalkan <il...@intellica.net>:
> Hi all,
> I have a problem with IN statement in HiveQL. My table "cdr", column
> "calldate" which type is "date". First query is successfully return:
> select * from cdr where calldate = '2014-05-02';
>
> But when query with IN statement,
>
> select * from cdr where calldate in ( '2014-08-11','2014-05-02');
>
> it returns below exception:
>
> Error: Error while processing statement: FAILED: SemanticException [Error
> 10014]: Line 1:38 Wrong arguments ''20014-03-02'': The arguments for IN
> should be the same type! Types are: {date IN (string, string)}
> (state=42000,code=10014)
>
> How can I handle this?
> Thanks.
>
> Hive version 0.12
>
>
>
>