RE: Hive - Sorting on the Partition Column data type Int . Output is Alphabetic Sort

[Arafat, Moiz]

Hi,

Here are the steps I followed . Please let me know If I did something wrong.


1)      Create table

hive> CREATE TABLE moiz_partition_test

    > (EVENT_DT STRING) partitioned by

    > (

    > PARTITION_HR INT

    > )

    >  ROW FORMAT DELIMITED

    >  FIELDS TERMINATED BY '09'

    >  location '/user/moiztcs/moiz_partition_test'

    > ;



2)      Add partitions
hive> alter table  moiz_partition_test add IF NOT EXISTS partition 
(partition_hr=0)  location '/user/moiztcs/moiz_partition_test/00';
OK
Time taken: 0.411 seconds
hive> alter table  moiz_partition_test add IF NOT EXISTS partition 
(partition_hr=1)  location '/user/moiztcs/moiz_partition_test/01';
OK
Time taken: 0.193 seconds
hive> alter table  moiz_partition_test add IF NOT EXISTS partition 
(partition_hr=2)  location '/user/moiztcs/moiz_partition_test/02';
OK
Time taken: 0.182 seconds
hive> alter table  moiz_partition_test add IF NOT EXISTS partition 
(partition_hr=10)  location '/user/moiztcs/moiz_partition_test/10';
OK
Time taken: 0.235 seconds


3)      Copy data into the directories

hadoop fs -copyFromLocal test.dat  /user/moiztcs/moiz_partition_test/00

hadoop fs -copyFromLocal test.dat  /user/moiztcs/moiz_partition_test/01

hadoop fs -copyFromLocal test.dat  /user/moiztcs/moiz_partition_test/02

hadoop fs -copyFromLocal test.dat  /user/moiztcs/moiz_partition_test/10



4)      Ran the sql

hive> select distinct partition_hr from moiz_partition_test order by 
partition_hr;

Ended Job

OK

0

1

10

2

Thanks,
Moiz
From: Stephen Sprague [mailto:sprag...@gmail.com]
Sent: Wednesday, March 12, 2014 12:55 AM
To: user@hive.apache.org
Subject: Re: Hive - Sorting on the Partition Column data type Int . Output is 
Alphabetic Sort

that makes no sense. if the column is an int it isn't going to sort like a 
string.  I smell a user error somewhere.

On Tue, Mar 11, 2014 at 6:21 AM, Arafat, Moiz 
<moiz.ara...@teamaol.com<mailto:moiz.ara...@teamaol.com>> wrote:
Hi ,

I have a table that has a partition column partition_hr . Data Type is int 
(partition_hr        int) . When i run a sort on this column the output is like 
this.

0
1
10
11
12
13
14
15
16
17
18
19
2
20
21
22
23
3
4
5
6
7
8
9

I expected the output like this  .

0
1
2
3
4
5
6
7
8
9
10
.
.
and so on.

It works fine for non-partition columns. Please advise.

Thanks,
Moiz