Data not getting loaded into Partitioned Table in Hive

Question

I am trying to create partition for my Table inorder to update a value.

This is my sample data

1,Anne,Admin,50000,A
2,Gokul,Admin,50000,B
3,Janet,Sales,60000,A

I want to update Janet's Department to B.

So for doing that I created a table with Department as partition.

create external table trail (EmployeeID Int,FirstName String,Designation String,Salary Int) PARTITIONED BY (Department String) row format delimited fields terminated by "," location '/user/sreeveni/HIVE';

But while doing the above command. No data are inserted into trail table.

hive>select * from trail;                               
OK
Time taken: 0.193 seconds

hive>desc trail;                                        
OK
employeeid              int                     None                
firstname               string                  None                
designation             string                  None                
salary                  int                     None                
department              string                  None                

# Partition Information      
# col_name              data_type               comment             

department              string                  None   

Am I doing anything wrong?

UPDATE

As suggested I tried to insert data into my table

load data inpath '/user/aibladmin/HIVE' overwrite into table trail Partition(Department);

But it is showing

FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict

After setting set hive.exec.dynamic.partition.mode=nonstrict also didnt work fine.

Anything else to do.

Answer 1

Try both below properties

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

And while writing insert statement for a partitioned table make sure that you specify the partition columns at the last in select clause.