Dynamic separation + CREATE AS on HIVE

Question

I am trying to create a new table from another table using CREATE AS and dynamic partitioning on HiveCLI. I am studying the official Hive 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 got this error:

FAILED: SemanticException [Error 10065]:

CREATE TABLE AS SELECT command cannot specify column list for target table

Source: https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions#DynamicPartitions-Syntax

Answer 1

Since you already know the full schema of the target table, try creating it first and populating it with the 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 necessary since you are performing a full dynamic insertion of partitions.

Answer 2

 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; 

In the above code, instead of the Create statement, use: CREATE TABLE T like srcpart ;

In case the partition is similar.