MySQL 动态分区 + 在 HIVE 上创建 AS
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21477855/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamic Partitioning + CREATE AS on HIVE
提问by Adriano Foschi
I'm trying to create a new table from another table with CREATE ASand dynamic Partitioning on HiveCLI. I'm learning from Hive official wiki where there is this example:
我正在尝试CREATE AS使用 HiveCLI 上的动态分区从另一个表创建一个新表。我正在从 Hive 官方 wiki 学习，那里有这个例子：
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
失败：语义异常 [错误 10065]：
CREATE TABLE AS SELECT 命令无法指定目标表的列列表
Source: https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions#DynamicPartitions-Syntax
来源：https: //cwiki.apache.org/confluence/display/Hive/DynamicPartitions#DynamicPartitions-Syntax
回答by Simplefish
Since you already know the full schema of the target table, try creating it first and the populating it with a LOAD DATA command:
由于您已经知道目标表的完整架构，请先尝试创建它并使用 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
And hr>10;
Note: the set command is needed since you are performing a full dynamic partition insert.
注意：由于您正在执行完整的动态分区插入，因此需要 set 命令。
回答by Deb
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 Createstatement use: CREATE TABLE T like srcpart;
在上面的代码中，代替Create语句使用：CREATE TABLE T like srcpart;
In case the partitioning is similar.
如果分区相似。
