0

I'm trying to insert data into a Hive table with partition, the partition condition is yesterday's date in yyyyMMdd format, and I want to do that dynamically so I'm generating it using a query. The date query works fine in my other select statement, however when inserting it's throwing an error like this:

Error picture

Could you guys help me? Thank you and have a nice day.

QuangTM
  • 3
  • 3
  • here is my statement: INSERT OVERWRITE TABLE dwh_vts.staging_f_vts_sale_revenue PARTITION(date=DATE_FORMAT(date_sub(CURRENT_DATE,1),'yyyyMMdd')) VALUES ('N350','10','4500000.000000'),('T280','21','3760000.000000'); – QuangTM Aug 17 '21 at 04:35
  • Hive does not support functions in partition specification, read this answer:https://stackoverflow.com/a/64171676/2700344 – leftjoin Aug 17 '21 at 07:23

1 Answers1

0

You can create a view to load data or tweak your sql to do it. Make sure you have this date column as last column and partitioned by this column in table.

INSERT OVERWRITE TABLE dwh_vts.staging_f_vts_sale_revenue PARTITION(`date`) 
SELECT 'N350','10','4500000.000000',DATE_FORMAT(date_sub(CURRENT_DATE,1),'yyyyMMdd')
union
SELECT 'T280','21','3760000.000000',,DATE_FORMAT(date_sub(CURRENT_DATE,1),'yyyyMMdd')

Or you can put above SQL into a view and then insert overwrite from the view.

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33