CODEWITHSUNDEEP
hadoopmapreducehivepartition
USB
USB

Reputation: 6139

Data not getting loaded into Partitioned Table in Hive

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.

Upvotes: 12

Views: 52466

Answers (7)

user26429157
user26429157

Reputation: 1

Check if your source is provided with the partitioning column data for dynamic partitioning or any spell checks in column names of partitioning.

Upvotes: 0

Elad Gazit
Elad Gazit

Reputation: 19

just set those 2 properties BEFORE you getOrCreate() the spark session:

SparkSession
    .builder
    .config(new SparkConf())
    .appName(appName)
    .enableHiveSupport()
    .config("hive.exec.dynamic.partition","true")
    .config("hive.exec.dynamic.partition.mode", "nonstrict")
    .getOrCreate()

Upvotes: 2

denimmonkey
denimmonkey

Reputation: 21

Can you try running MSCK REPAIR TABLE table_name;

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-RecoverPartitions(MSCKREPAIRTABLE)

Upvotes: 1

Adelin
Adelin

Reputation: 18951

I ran into the same problem and yes these two properties are needed. However, I used JDBC driver with Scala to set these properties before executing Hive statements. The problem, however, was that I was executing a bunch of properties (SET statements) in one execution statement like this 

     conn = DriverManager.getConnection(conf.get[String]("hive.jdbc.url"))
     conn.createStatement().execute(
"SET spark.executor.memory = 2G;
SET hive.exec.dynamic.partition.mode = nonstrict; 
SET hive.other.statements =blabla  ;")

For some reason, the driver was not able to interpret all these as separate statements, so I needed to execute each one of them separately. 

  conn = DriverManager.getConnection(conf.get[String]("hive.jdbc.url"))
    conn.createStatement().execute("SET spark.executor.memory = 2G;")
    conn.createStatement().execute("SET hive.exec.dynamic.partition.mode=nonstrict;") 
   conn.createStatement().execute("SET hive.other.statements =blabla  ;")

Upvotes: 0

user3484461
user3484461

Reputation: 1133

Try the following:

Start by creating the table:

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

Create a directory in hdfs with partition name : 

$ hadoop fs -mkdir /user/rocky/HIVE/department=50000

Create a local file abc.txt by filtering records having department equal to 50000:

$ cat abc.txt 
1,Anne,Admin,50000,A
2,Gokul,Admin,50000,B

Put it into HDFS:

$ hadoop fs -put /home/yarn/abc.txt /user/rocky/HIVE/department=50000

Now alter the table:

ALTER TABLE test23 ADD PARTITION(department=50000);

And check the result:

select * from test23 ;

Upvotes: 3

gattamaneni harsha
gattamaneni harsha

Reputation: 71

You cannot directly insert data(Hdfs File) into a Partitioned hive table. First you need to create a normal table, then you will insert that table data into partitioned table.

set hive.exec.dynamic.partition.mode=strict means when ever you are populating hive table it must have at least one static partition column.

set hive.exec.dynamic.partition.mode=nonstrict In this mode you don't need any static partition column.

Upvotes: 4

Kiran teja Avvaru
Kiran teja Avvaru

Reputation: 364

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. 

Upvotes: 25

Related Questions