copy data from one table to another partitioning table

Question

%hive
INSERT INTO NEWPARTITIONING partition(year(L_SHIPDATE)) select * from LINEITEM;

I want to copy the data from line item to the partitioning table NEWPARTITIONING but I got the following error:

line 1:54 cannot recognize input near ')' 'select' '*' in statement.

Don't understand why this error occurs. Can anyone give me some ideas

Answer 1

Hive supports DYNAMIC or STATIC partition loading.

Partition specification allows only column name or column list (for dynamic partition load), if you need function, then calculate it in the select, see example below:

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

insert into table NEWPARTITIONING partition (partition_column)
select i.col1,
       ...
       i.colN,
       year(L_SHIPDATE) as partition_column --Partition should be the last in column list
  from LINEITEM i 

Or you can specify static partition in the form partition(partition_column='value'), in this case you do not need to select partition expression:

insert into table NEWPARTITIONING partition (partition_column='2020-01-01')
select i.col1,
       ...
       i.colN
  from LINEITEM i 
 where year(L_SHIPDATE)  = '2020-01-01' 

In both cases - STATIC and DYNAMIC, Hive does not support functions in partition specification. Functions can be calculated in the query (dynamic load) or calculated in a wrapper shell and passed as a parameter to the script (for static partition).