17

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 :)

tharindu_DG
  • 8,900
  • 6
  • 52
  • 64
Baeumla
  • 443
  • 3
  • 6
  • 18

2 Answers2

28

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.

scalauser
  • 1,327
  • 1
  • 12
  • 34
Tanveer
  • 890
  • 12
  • 22
  • Thanks for your help :) The select: "INSERT OVERWRITE TABLE demo_tab PARTITION (land) SELECT stadt, geograph_breite, id, t.country,t.id as land FROM demo_stg t;" works! – Baeumla Jun 16 '14 at 10:58
  • I am glad it worked. And your welcome. Further if you have multiple columns to partition select should contain multiple as in the order which you specify in PARTITION clause after insert statement. Few days back I have written a blog over same @ http://exploredatascience.blogspot.in/2014/06/dynamic-partitioning-with-hive.html – Tanveer Jun 16 '14 at 11:36
  • From the doc: `dynamic partition columns must be specified last among the columns in the SELECT statement and in the same order in which they appear in the PARTITION() clause` https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions#DynamicPartitions-Syntax – zbstof Nov 05 '19 at 17:02
12

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
Floern
  • 33,559
  • 24
  • 104
  • 119
Azam Khan
  • 516
  • 5
  • 12