Partitioning in Hive table with multiple values?

Question

I want to create a simple hive partitioned table and have a sqoop import command to populate it.

1.Table have say 4 columns, ID, col1, col2, col3.

2. One of the column say col2 is int type and contains values 1 to 10 only.

3. I need to partition table based on col2 column with 1 to 5 value data should be in one partition and rest in another.

I am currently trying this which doesnt work: Alter table tblname add partition (col2=1,col2=2,col2=3,col2=4,col2=5) location 'Part1';

4. Once done i need to populate this table with sqoop import from my sql server.

I have tried many ways but not able to do it. Can anyone please help?

Answer 1

Create a partitioned table and manually add a partition e.g. 1_to_3

create table ptable(name string) partitioned by (id string);
alter table ptable add partition (id='1_to_3');

show partitions ptable;
+------------+--+
| partition  |
+------------+--+
| id=1_to_3  |
+------------+--+

I know that I should load data from department table into this partition, if department id 1 or 2 or 3.

insert into ptable partition(id = '1_to_3') select department_name from departments where department_id between 1 and 3;

See screenshot

select * from ptable;
+------------------+------------+--+
|   ptable.name    | ptable.id  |
+------------------+------------+--+
| Marketing        | 1_to_3     |
| Finance          | 1_to_3     |
| Human Resources  | 1_to_3     |
+------------------+------------+--+

You may need to add another partition to hold other values e.g department_id > 3