1

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?

leftjoin
  • 36,950
  • 8
  • 57
  • 116
JaviOverflow
  • 1,434
  • 2
  • 14
  • 31

1 Answers1

2

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.

leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • Is X in table_A? – DachuanZhao Jan 25 '22 at 11:23
  • @DachuanZhao yes. X is a column name in table_A. and the same name is used as partition col name in table_B, but it can be different one in table B. the values taken as table B partition values are from the last column in the select, no matter how it is named, it can be expression also – leftjoin Jan 25 '22 at 11:47