0
votes

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.

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

  2. 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';

  1. 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?

1
Consider using static partitioning. Create a table, alter table to create partition and then load data into specific partitionsBala
Bala is it possible for you to help with commands for creating partition as per my requirement in point 3 and sqoop command to populate as per point 4 ? I have tried lots of options and didn't find anything working.SqlEnthu

1 Answers

1
votes

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

enter image description here

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