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
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.