Hive: Fatal error when trying to create dynamic partitions

Question

    create table MY_DATA0(session_id STRING, userid BIGINT,date_time STRING, ip STRING, URL STRING ,country STRING, state STRING, city STRING) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES 
TERMINATED BY '\n' STORED AS TEXTFILE ;

    LOAD DATA INPATH '/inputhive' OVERWRITE INTO TABLE MY_DATA0;

    create table part0(session_id STRING, userid BIGINT,date_time STRING, ip STRING, URL STRING) partitioned by (country STRING, state STRING, city STRING) 

    clustered by (userid) into 256 buckets ROW FORMAT DELIMITED FIELDS 
    TERMINATED BY ',' LINES TERMINATED BY '\n' STORED AS TEXTFILE ;

    \insert overwrite table part0 partition(country, state, city) select session_id, userid, date_time,ip, url, country, state,city from my_data0;

Overview of my dataset:

{60A191CB-B3CA-496E-B33B-0ACA551DD503},1331582487,2012-03-12 13:01:27,66.91.193.75,http://www.acme.com/SH55126545/VD55179433,United States,Hauula,Hawaii

{365CC356-7822-8A42-51D2-B6396F8FC5BF},1331584835,2012-03-12 13:40:35,173.172.214.24,http://www.acme.com/SH55126545/VD55179433,United States,El Paso,Texas

When I run the last insert script I get an error as :

java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveFatalException: [Error 20004]: Fatal error occurred when node tried to create too many dynamic partitions. The maximum number of dynamic partitions is controlled by hive.exec.max.dynamic.partitions and hive.exec.max.dynamic.partitions.pernode. Maximum was set to: 100

PS:

I have set this two properties:

hive.exec.dynamic.partition.mode::nonstrict

hive.enforce.bucketing::true

Answer 1

Try setting those properties to higher values.

SET hive.exec.max.dynamic.partitions=100000;
SET hive.exec.max.dynamic.partitions.pernode=100000;