CODEWITHSUNDEEP
hadoophivebigdatahiveql
Green
Green

Reputation: 597

Hive - facing challenge's in Dynamic partition error

Can any one guide me where I am doing mistake while doing dynamic partition.

--Staging table:

    create table staging_peopledata
    (
    firstname string, 
    secondname string, 
    salary float, 
    country string
    state string
    ) 
    row format delimited fields terminated by ',' lines terminated by '\n';

--Data for Staging table:

    John,David,30000,RUS,tnRUS
    John,David,30000,RUS,tnRUS
    Mary,David,5000,AUS,syAUS
    Mary,David,5000,AUS,syAUS
    Mary,David,5000,AUS,weAUS
    Pierre,Cathey,6000,RUS,kaRUS
    Pierre,Cathey,6000,RUS,kaRUS
    Ahmed,Talib,11000,US,bcUS
    Ahmed,Talib,11000,US,onUS
    Ahmed,Talib,11000,US,onUS
    kris,David,80000,UK,lnUK
    kris,David,80000,UK,soUK

--Production table:

    create table Production_peopledata
    (
    firstname string, 
    lastname string, 
    salary float) 
    partitioned by (country string, state string) 
    row format delimited fields terminated by ',' lines terminated by '\n';

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

    insert overwrite table Production_peopledata 
    partition(country,state) 
    select firstname, secondname, salary, country, state from staging_peopledata;

If i execute the above command I am getting error as below.

    FAILED: SemanticException [Error 10096]: Dynamic partition strict mode
     requires atleast one static partition column. To turn this off set
     hive.exec.dynamic.partition.mode=nonstrict

Can any one tell me where I am doing the mistake.

Upvotes: 0

Views: 1731

Answers (4)

Akshay Gaigole
Akshay Gaigole

Reputation: 1

Once again try to do Set hive.exec.dynamic.partition.mode=nonstrict Sometimes in hive it happens even if you set this property it considers strict mode hence I suggest you to set this property once again

Upvotes: 0

Azam Khan
Azam Khan

Reputation: 539

as per the error it seems that mode in still strict, for dynamic partitioning it need to be set to non strict use below command

hive>set hive.exec.dynamic.partition.mode=nonstrict;

Upvotes: 0

Sonu
Sonu

Reputation: 732

You need to set below properties:

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

The column name want to partition on should not be part of the table definition. As the partition column is dynamically generated. While filling the data in the partitioned table the partitioned column should come from the source table.

Let's say we have EMP and EMP1 tables. EMP1 is the partitioned table which will get the data from the EMP table. Initially both of these tables are same. So first we need to create a partitioned column i.e. salpart. Then we will add this column in the source table which is EMP. After successful run we can see the partitioned files in user/hive/warehouse location. The above explanation is implemented as below: 

load data local inpath '/home/cloudera/myemployeedata.txt' overwrite into table emp;

CREATE TABLE IF NOT EXISTS emp ( eid int, name String,
salary String, destination String,salpart string)
COMMENT "Employee details"
ROW FORMAT DELIMITED
FIELDS TERMINATED BY "\t"
LINES TERMINATED BY "\n"
STORED AS TEXTFILE;

CREATE TABLE IF NOT EXISTS emp1 ( eid int, name String,
salary String, destination String)
COMMENT "Employee details"
partitioned by (salpart string)  {this column will values will come from a seperate table }
ROW FORMAT DELIMITED
FIELDS TERMINATED BY "\t"
LINES TERMINATED BY "\n"
STORED AS TEXTFILE;

Dynamic Partition:
set hive.exec.dynamic.partition=true;  
set hive.exec.dynamic.partition.mode=nonstrict;  

insert overwrite table emp1 partition(salpart) select eid,name,salary,destination,salpart from emp;

Upvotes: 1

user5223001
user5223001

Reputation: 26

Can you please run below command on Hive Shell.

hive>set hive.exec.dynamic.partition.mode=nonstrict;

Upvotes: 1

Related Questions