Dynamic Partitioning + CREATE AS on HIVE

database-partitioning, hadoop, hive, mysql, sql

Solution

Since you already know the full schema of the target table, try creating it first and the populating it with a LOAD DATA command:

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

CREATE TABLE T (key int, value string) 
PARTITIONED BY (ds string, hr int);

INSERT OVERWRITE TABLE T PARTITION(ds, hr) 
SELECT key, value, ds, hr+1 AS hr 
   FROM srcpart 
   WHERE ds is not null 
   And hr>10;

Note: the set command is needed since you are performing a full dynamic partition insert.

Problem

I'm trying to create a new table from another table with `CREATE AS` and dynamic Partitioning on HiveCLI. I'm learning from Hive official wiki where there is this example: ``` CREATE TABLE T (key int, value string) PARTITIONED BY (ds string, hr int) AS SELECT key, value, ds, hr+1 hr1 FROM srcpart WHERE ds is not null And hr>10; ``` But I received this error: FAILED: SemanticException [Error 10065]: CREATE TABLE AS SELECT command cannot specify the list of columns for the target table Source: https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions#DynamicPartitions-Syntax

Original source