1
%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

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Rex
  • 11
  • 1
  • I am not sure if you can pass functions in partition clause. Why dont you create a new column in `LINEITEM` table(or create a view on off of ) and try this `INSERT INTO NEWPARTITIONING partition(yyyy_L_shipdt)) select * from LINEITEM;` In case dynamic partitioning, you need to ensure last column in source should match to the partitiong. – Koushik Roy Oct 02 '20 at 06:10

1 Answers1

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).

leftjoin
  • 36,950
  • 8
  • 57
  • 116