0

I'm trying create dynamic partitioning based on two columns, and load data from file which is present in the hdfs location. But while loading data into the dynamically partitioned table from staging table, the second column in the partitioning is appearing as NULL. Can somebody please help.

Staging table definition

 create external table staging_table (COL1 double,COL2 string,COL3 double,COL4 bigint,COL5 bigint,COL6 bigint) location 'hdfs://DIRECTORY_PATH_TO_DATA';

Enable dynamic partition 

set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.dynamic.partition=true;
set hive.exec.max.dynamic.partitions=1000;
set hive.exec.max.dynamic.partitions.pernode=1000;

CREATE EXTERNAL TABLE final_table(COL1 STRING,COL2 BIGINT,COL3 BIGINT,COL4 BIGINT) PARTITIONED BY (COL5 BIGINT,COL6 BIGINT) STORED AS PARQUET LOCATION 'hdfs://HDFS_PATH';

insert into tagger_table_final1 PARTITION(COL5,COL6) SELECT COL1,COL2,COL3,COL4,COL5,COL6 FROM staging_table;

Here, col5 value is inserted correctly into the final table from staging table but col6 is appearing NULL. Could somebody please help.

learner
  • 155
  • 3
  • 18
  • Without seeing the data there must be null values in col6 – betico Dec 19 '18 at 02:34
  • if it's partitioned by `col5` and `col6` you're going to have folders `col5=` and `col6=` in the table's root location. you mean, you see `col6=null` ? – mangusta Dec 19 '18 at 02:35

0 Answers0