You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Edward Capriolo <ed...@gmail.com> on 2011/03/17 13:59:23 UTC

Re: We had this wierd behvior

On Thursday, March 17, 2011, Guy Doulberg <Gu...@conduit.com> wrote:
> Hey guys, I have a hive partitioned table. First I ran a query that look like this:Select count(*) From tableWhere field like '%bla%' and (partition>'10' and partition < '20') For this query I gotSome records let's say 640 When I ran this query Select count(*) From tableWhere field like '%bla%' and (partition>'5' and partition < '20') I got none….. Assume that I have partitions 5-20. What am I missing here? Thanks gut

Is your table partitioned by int or string?

'5' is not less then '20' when you are treating them as strings. Don't
put single quotes around numbers

RE: We had this wierd behvior

Posted by Guy Doulberg <Gu...@conduit.com>.
Okay thanks

-----Original Message-----
From: Edward Capriolo [mailto:edlinuxguru@gmail.com] 
Sent: Thursday, March 17, 2011 4:08 PM
To: user@hive.apache.org
Cc: Guy Doulberg
Subject: Re: We had this wierd behvior

2011/3/17 Guy Doulberg <Gu...@conduit.com>:
> Strings
>
> I actually simplified the scenario so I could the question,
> Our partitions are actually string of dates with hour
>
> So the query was actually
> Partition >= '20110301_20' and Partition <= '2011030223'
>
> Still using a single quote wouldn't be advised?
>
>
> Thanks,
>
> -----Original Message-----
> From: Edward Capriolo [mailto:edlinuxguru@gmail.com]
> Sent: Thursday, March 17, 2011 2:59 PM
> To: user@hive.apache.org
> Subject: Re: We had this wierd behvior
>
> On Thursday, March 17, 2011, Guy Doulberg <Gu...@conduit.com> wrote:
>> Hey guys, I have a hive partitioned table. First I ran a query that look like this:Select count(*) From tableWhere field like '%bla%' and (partition>'10' and partition < '20') For this query I gotSome records let's say 640 When I ran this query Select count(*) From tableWhere field like '%bla%' and (partition>'5' and partition < '20') I got none….. Assume that I have partitions 5-20. What am I missing here? Thanks gut
>
> Is your table partitioned by int or string?
>
> '5' is not less then '20' when you are treating them as strings. Don't
> put single quotes around numbers
>

If you put single quotes around numbers it is converted to a string literal.

 '5' != 5

How did you define your partitions?

CREATE TABLE ... PARTITIONED BY ( int date)
OR
CREATE TABLE ... PARTITIONED BY ( string date)

If your did the former the sorting / comparison rules are numerical.
If you did the latter the sorting / comparison rules are lexicographical.

The best way to explain this is just to follow one rule.
Do not put single quotes around numbers and this will work as expected.

Re: We had this wierd behvior

Posted by Edward Capriolo <ed...@gmail.com>.
2011/3/17 Guy Doulberg <Gu...@conduit.com>:
> Strings
>
> I actually simplified the scenario so I could the question,
> Our partitions are actually string of dates with hour
>
> So the query was actually
> Partition >= '20110301_20' and Partition <= '2011030223'
>
> Still using a single quote wouldn't be advised?
>
>
> Thanks,
>
> -----Original Message-----
> From: Edward Capriolo [mailto:edlinuxguru@gmail.com]
> Sent: Thursday, March 17, 2011 2:59 PM
> To: user@hive.apache.org
> Subject: Re: We had this wierd behvior
>
> On Thursday, March 17, 2011, Guy Doulberg <Gu...@conduit.com> wrote:
>> Hey guys, I have a hive partitioned table. First I ran a query that look like this:Select count(*) From tableWhere field like '%bla%' and (partition>'10' and partition < '20') For this query I gotSome records let's say 640 When I ran this query Select count(*) From tableWhere field like '%bla%' and (partition>'5' and partition < '20') I got none….. Assume that I have partitions 5-20. What am I missing here? Thanks gut
>
> Is your table partitioned by int or string?
>
> '5' is not less then '20' when you are treating them as strings. Don't
> put single quotes around numbers
>

If you put single quotes around numbers it is converted to a string literal.

 '5' != 5

How did you define your partitions?

CREATE TABLE ... PARTITIONED BY ( int date)
OR
CREATE TABLE ... PARTITIONED BY ( string date)

If your did the former the sorting / comparison rules are numerical.
If you did the latter the sorting / comparison rules are lexicographical.

The best way to explain this is just to follow one rule.
Do not put single quotes around numbers and this will work as expected.

RE: We had this wierd behvior

Posted by Guy Doulberg <Gu...@conduit.com>.
Strings

I actually simplified the scenario so I could the question,
Our partitions are actually string of dates with hour

So the query was actually
Partition >= '20110301_20' and Partition <= '2011030223'

Still using a single quote wouldn't be advised?


Thanks,

-----Original Message-----
From: Edward Capriolo [mailto:edlinuxguru@gmail.com] 
Sent: Thursday, March 17, 2011 2:59 PM
To: user@hive.apache.org
Subject: Re: We had this wierd behvior

On Thursday, March 17, 2011, Guy Doulberg <Gu...@conduit.com> wrote:
> Hey guys, I have a hive partitioned table. First I ran a query that look like this:Select count(*) From tableWhere field like '%bla%' and (partition>'10' and partition < '20') For this query I gotSome records let's say 640 When I ran this query Select count(*) From tableWhere field like '%bla%' and (partition>'5' and partition < '20') I got none….. Assume that I have partitions 5-20. What am I missing here? Thanks gut

Is your table partitioned by int or string?

'5' is not less then '20' when you are treating them as strings. Don't
put single quotes around numbers