Hive Insert overwrite into Dynamic partition external table from a raw external table failed with null pointer exception.,

Question

I have a raw external table with four columns- Table 1 :

create external table external_partitioned_rawtable
(age_bucket String,country_destination String,gender string,population_in_thousandsyear int)
row format delimited fields terminated by '\t'
lines terminated by '\n' location '/user/HadoopUser/hive'

I want a external table with partitions from Country_destination and gender.Table -2

create external table external_partitioned
(age_bucket String,population_in_thousandsyear int)
partitioned by(country_destination String,gender String)
row format delimited fields terminated by '\t'
lines terminated by '\n';

Insert Overwrite is failing with null pointer exception-

insert overwrite  table  external_partitioned partition(country_destination,gender) <br>
select (age_bucket,population_in_thousandsyear,country_destination,gender) <br>
from external_partitioned_rawtable;

FAILED: NullPointerException null

Answer 1

For dynamic partition insertion, before executing the INSERT statement you have to execute two properties of hive:

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

then execute insert statement(which I have modified)

insert overwrite  table  external_partitioned partition(country_destination,gender) 
select age_bucket,population_in_thousandsyear,country_destination,gender 
from external_partitioned_rawtable;

I hope this help you!!!