使用一条insert语句在多个分区中插入数据 [英] Insert data in many partitions using one insert statement

问题描述

我有表A和表B，其中B是使用名为X的字段对A进行分区的表.

I have table A and table B, where B is the partitioned table of A using a field called X.

当我要将数据从A插入到B时，通常执行以下语句:

When I want to insert data from A to B, I usually execute the following statement:

INSERT INTO TABLE B PARTITION(X=x) SELECT <columnsFromA> FROM A WHERE X=x

现在我要实现的是能够插入X范围，比如说x1，x2，x3 ...如何在一个语句中实现呢?

Now what I want to achieve is being able to insert a range of X, let's say x1, x2, x3... How can I achieve this in one single statement?

推荐答案

使用动态分区负载:

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

INSERT OVERWRITE TABLE table_B PARTITION(X)
select 
col_1,
col_2,
...
col_N,
X --partition column is the last one
 from 
      table_A
where X in ('x1', 'x2', 'x3'); --filter here

或如果A和B中的列顺序相同，则使用select * from table_A.分区列(X)应该是最后一个.

Or use select * from table_A if the order of columns in A and B is the same. Partition column (X) should be the last one.

这篇关于使用一条insert语句在多个分区中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！