Adding partition manaully

certifications
#1

Hello Guys,
i need help in adding partition manually.
i am able to create dynamic partition but i am stuck with manual partitioning.

i am using categories table, from the big_data lab.
and trying to partition by department id.

here is my syntax.

create table category(
> id int,
> name string)
> partitioned by (cat_id int)
> ;

OK
Time taken: 0.334 seconds
hive> alter table category add partition (cat_id = 2)
> ;
OK
Time taken: 0.461 seconds
hive> insert overwrite table category partition(cat_id = 2)
> select * from categories where category_department_id = 2
> ;
FAILED: SemanticException [Error 10044]: Line 1:23 Cannot insert into target table because column number/types are different ‘2’: Table insclause-0 has 2 columns, but query has 3 columns.

hive> describe formatted category;
OK

col_name data_type comment

id int
name string

Partition Information

col_name data_type comment

cat_id int

Detailed Table Information

Database: avi_hive
Owner: abinashparida
CreateTime: Sun Jan 15 10:37:13 EST 2017
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/avi_hive.db/category
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1484494633

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.35 seconds, Fetched: 32 row(s).

please help.
thank you.

#2

@Avinash_Parida
I don’t think an explicit Alter statement is needed since CREATE TABLE itself defined with a Partitioned Key.

CREATE TABLE email2dgk_retail_db.category
(
category_id INT,
category_name STRING
)
partitioned by (cat_dept_id INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

insert overwrite table email2dgk_retail_db.category partition(cat_dept_id=2)
Select category_id, category_name from email2dgk_retail_db.categories
where category_department_id=2;

insert into table email2dgk_retail_db.category partition(cat_dept_id=3)
Select category_id, category_name from email2dgk_retail_db.categories
where category_department_id=3;

HDFS Directories :

[email2dgk@gw01 ~]$ hadoop fs -ls hdfs://nn01.itversity.com:8020/apps/hive/warehouse/email2dgk_retail_db.db/category/
Found 2 items
drwxrwxrwx - email2dgk hdfs 0 2017-01-15 12:10 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/email2dgk_retail_db.db/category/cat_dept_id=2
drwxrwxrwx - email2dgk hdfs 0 2017-01-15 12:12 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/email2dgk_retail_db.db/category/cat_dept_id=3

1 Like
#3

that works , thanks :slight_smile: