Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

How can we create dynamic partitions in Hive external table.?

avatar
Super Collaborator

Hi, i created an external table in HIVE with 150 columns. i have a .csv file for each day , and eventually i will have to load data for 4 years.

i just loaded one month worth of files which turned into 2mill rows.

i now like to partition the table by date (which first column in the table and file).

1.how can i partion on existing table and 2. can i do it dynamically when i load other files.

Regards,Sai

4 REPLIES 4

avatar
Explorer

Hi @Saikrishna Tarapareddy

1. You can add partitions to existing table using ALTER TABLE <table_name> ADD PARTITION (date=' ') LOCATION '/path'

2. For dynamic partitioning:

--Set following two properties for your Hive session:

SET hive.exec.dynamic.partition=true; SET hive.exec.dynamic.partition.mode=nonstrict;

--Create your staging table without Partition

create external table table_name (col_01,col_02) row format delimited fields terminated by ",";

--load data to your Hive Table:

load data local inpath '/path' into table table_name;

--Create production table with the columns you want to partition upon. In this case you want to partition upon date column:

create external table table_name (col_01,col_02) partitioned by (date_part string) row format delimited fields terminated by ',';

insert overwrite table table_name partition(date_part) select col_01,col_02 from staging_table;

--Verify data is correctly populated in partition:

hive> select * from table_name where date_part="20170111";

Regards,

Anubhav

avatar
Super Collaborator

Hi @aawasthi,

suppose i have an external table "test_display" which points to '/user/test/display' which already have 30 files one for each day. and i can query the table. when i created the table i did not specify any partition.

now like you mentioned above i tried to execute the command below

ALTER TABLE test_display ADD PARTITION (moat_date='2017-01-05') LOCATION '/user/test/display'

Error occurred executing hive query: Error while compiling statement: FAILED: ValidationFailureSemanticException table is not partitioned but partition spec exists: {moat_date=2017-01-05}

do i have to manually create the folder 2017-01-05 under '/user/test/display'

avatar
Explorer

Your create table statement should define partition name.

CREATE EXTERNAL TABLE table_name ( ... ) PARTITIONED BY (moat_date STRING);

avatar
Contributor

Hi @Saikrishna Tarapareddy

Can you provide output of below?

1. create table def

hive> Show create table test_display

2. Files in the hdfs location

hadoop fs -ls <table_display table hdfs location>