动态分区+在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屋！