动态分区+在HIVE上创建AS [英] Dynamic Partitioning + CREATE AS on HIVE
问题描述
我试图用 CREATE AS
从另一个表创建一个新表,并在HiveCLI上创建动态分区。我从Hive官方维客学习,这里有这样的例子:
$ b $ pre $ CREATE TABLE T(key int,value string)
PARTITIONED BY(ds string,hr int)AS
SELECT key,value,ds,hr + 1 hr1
FROM srcpart
其中ds不为空
并且hr> 10 ;
但我收到这个错误:
FAILED:SemanticException [错误10065]:
CREATE TABLE AS SELECT命令无法为目标表指定列的列表
来源: https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions#DynamicPartitions-Syntax
由于您已经知道目标表的完整模式,请尝试先创建它并使用LOAD DATA命令填充它:
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
并且hr> 10;
注意:因为您正在执行完整的动态分区插入操作,所以需要set命令。
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.
这篇关于动态分区+在HIVE上创建AS的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!