Search code examples
sqlhadoophivehiveqlhive-partitions

Insert data in many partitions using one insert statement

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

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

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?

Solution

  • Use dynamic partition load:

    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

    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.