0

I have a Hive temp table without any partitions which has the data required. I want to select this data and insert into another table partitioned by date. I tried following techniques with no luck.

Source table schema 

 CREATE TABLE cls_staging.cls_billing_address_em_tmp
( col1 string,
 col2 string,
col3 string);

Destination table :

CREATE TABLE cls_staging.cls_billing_address_em_tmp
    ( col1 string,
     col2 string,
    col3 string) 
    PARTITIONED BY (
       curr_date string) 
      STORED AS ORC;

Query for inserting into destination table :

insert overwrite table cls_staging.cls_billing_address_em_tmp partition (record_date)  select col1, col2, col3, FROM_UNIXTIME(UNIX_TIMESTAMP()) from myDB.mytbl;

ERROR

Dynamic partition strict mode requires at least one static partition column

2nd

insert overwrite table cls_staging.cls_billing_address_em_tmp partition (record_date = FROM_UNIXTIME(UNIX_TIMESTAMP()))  select col1, col2, col3 from myDB.mytbl;

ERROR :

cannot recognize input near 'FROM_UNIXTIME' '(' 'UNIX_TIMESTAMP'
mazaneicha
  • 8,794
  • 4
  • 33
  • 52
Saawan
  • 363
  • 6
  • 24

1 Answers1

2

1st Switch-on dynamic partitioning and non-strict mode: 

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

insert overwrite table cls_staging.cls_billing_address_em_tmp partition (record_date)  
select col1, col2, col3, current_timestamp from myDB.mytbl;

2nd: Do not use unix_timestamp() for this purpose, because it will generate many different timestamps, use current_timestamp constant, read this: https://stackoverflow.com/a/58081191/2700344

leftjoin
  • 36,950
  • 8
  • 57
  • 116