CODEWITHSUNDEEP
hivepartition
tonyibm
tonyibm

Reputation: 651

HIVE "show partitions" command do not show correct partitions

i have a partition table with dynamic partitions, partition fields are nationality and birthdate,

when i use select * from emp_new where nationality='China', i get the following three records,

+---------------+--------------+--------------+------------------+----------------------+--------------------+--+
| emp_new.name  | emp_new.sex  | emp_new.age  |   emp_new.job    | emp_new.nationality  | emp_new.birthdate  |
+---------------+--------------+--------------+------------------+----------------------+--------------------+--+
| Tony          | M            | 34           | IT specialist    | China                | 198202             |
| Katrina       | F            | 33           | IT specialist    | China                | 198408             |
| Cathy         | F            | 30           | IT specialist    | China                | 198704             |

but when i run show partitions emp_new partition(nationality='China'), i get the following results:

+-------------------------------------+--+
|              partition              |
+-------------------------------------+--+
| nationality=China/birthdate=198408  |
| nationality=China/birthdate=198202  |
| nationality=China/birthdate=198704  |
| nationality=China/birthdate=197509  |
| nationality=China/birthdate=196704  |
| nationality=China/birthdate=197805  |
| nationality=China/birthdate=198201  |
| nationality=China/birthdate=197701  |
| nationality=China/birthdate=196708  |
+-------------------------------------+--+

Actually, I loaded the data into this table with static and dynamic partitions(nationality='China', birthdate) earlier, then truncated the table and reload with dynamic partitions (nationality, birthdate) later.

I don't understand why the old partitions are still there.

Upvotes: 0

Views: 8219

Answers (2)

tonyibm
tonyibm

Reputation: 651

i know the reason, i need to drop the partition after truncate the table, thanks

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44971

Truncate deletes the table's data files.
It does not delete the partitions definitions from the metastore.
It does not delete the file system directories.

Demo

hive> create table mytable (i int) partitioned by (p int);
OK

hive> insert into mytable partition (p) values (1,10),(2,10),(3,20),(4,30),(5,30),(6,30);
OK

hive> select * from mytable;
OK
mytable.i   mytable.p
1   10
2   10
3   20
4   30
5   30
6   30


hive> show partitions mytable;
OK
partition
p=10
p=20
p=30

hive> !tree ../local_db/mytable;
../local_db/mytable
├── p=10
│   └── 000000_0
├── p=20
│   └── 000000_0
└── p=30
    └── 000000_0

3 directories, 3 files

hive> truncate table mytable;
OK

hive> select * from mytable;
OK
mytable.i   mytable.p

hive> show partitions mytable;
OK
partition
p=10
p=20
p=30

hive> !tree ../local_db/mytable;
../local_db/mytable
├── p=10
├── p=20
└── p=30

3 directories, 0 files

Upvotes: 3

Related Questions