动态分区CTAS [英] CTAS with Dynamic Partition

问题描述

我想将包含文本格式的现有表更改为orc格式.我能够通过以下方式做到这一点:(1)以orc格式手动创建具有分区的表，然后，(2)使用INSERT OVERWRITE语句填充表.

I want to change an existing table, that contains text format, into orc format. I was able to do it by: (1) creating a table in orc format manually having the partitions and then, (2) using the INSERT OVERWRITE statement to populate the table.

我正在尝试为此使用CTAS(创建表... AS选择...)语句.有什么办法可以在CTAS语句中包括动态分区?因此，如果我的文本数据集具有多个分区(例如:年和月)，我可以直接在CTAS语句中指出吗?

I am trying to use CTAS (Create Table... AS Select...) statement for this. Is there any way I can include the dynamic partitioning with CTAS statement? So, if my text data set has multiple partitions (for example: year and month), can I point this in CTAS statement directly?

格式可能是这样的:

CREATE TABLE TEST_TABLE
STORED AS ORC
WITH PARTITION(year, month)
LOCATION '/<my_location>'
tblproperties ("orc.compress"="SNAPPY")
AS SELECT * FROM <existing_table>;

请问有什么主意吗?

注意:我对CTAS语句感兴趣的原因是:使用此语句，我真的不需要指出每个列的名称.但是，如果我创建一个基于orc的表，然后手动填充它，则在创建表时必须指出所有列.没关系但是，如果我现有的表中包含很多列，则不是一个好主意.

Note: The reason I'm interested for CTAS statement is as: using this statement, I don't really need to point out each and every columns name. But if I create an orc based table and then populate it manually, I had to indicate all the columns while creating the table. This is okay; but, not a good idea if my existing table contains a lot of columns.

推荐答案

不支持

hive> create table t partitioned by (p int) as select 1 as i;

失败:SemanticException [错误10068]:
CREATE-TABLE-AS-SELECT不支持目标表中的分区

FAILED: SemanticException [Error 10068]:
CREATE-TABLE-AS-SELECT does not support partitioning in the target table

这篇关于动态分区CTAS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！