Hive dynamic partitioning

Question

I'm trying to create a partitioned table using dynamic partitioning, but i'm facing an issue. I'm running Hive 0.12 on Hortonworks Sandbox 2.0.

set hive.exec.dynamic.partition=true;
INSERT OVERWRITE TABLE demo_tab PARTITION (land)
SELECT stadt, geograph_breite, id, t.country
FROM demo_stg t;

however it does not work.. I'm getting an Error.

Here is the Query to create the table demo_stg:

create table demo_stg
(
    country STRING,
    stadt STRING,
    geograph_breite FLOAT,
    id INT
    )
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\073";

And demo_tab:

CREATE TABLE demo_tab 
(
    stadt STRING,
    geograph_breite FLOAT,
    id INT
)
PARTITIONED BY (land STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\073";

• The table demo_stg is also filled with data, so it's not empty.

Thanks for help :)

Answer 1

Partition column needs to be the last column in select query.

And one more thing other than setting the partition to true you need to set mode to nonstrict:

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

Answer 2

You need to modify your select:

set hive.exec.dynamic.partition=true;
INSERT OVERWRITE TABLE demo_tab PARTITION (land)
SELECT stadt, geograph_breite, id, t.country
FROM demo_stg t;

I am not sure to which column on your demo staging you want to perform partitioning or which column in demo corresponds to land. But whatever is the column it should be present as the last column in select say your demo table column name is id so your select should be written as:

INSERT OVERWRITE TABLE demo_tab PARTITION (land)
SELECT stadt, geograph_breite, id, t.country,t.id as land
FROM demo_stg t;

I think this should work.