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.

score 23 · Answer 1 · edited May 20 '16 at 09:15

23

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.

edited May 20 '16 at 09:15

Emma Burrows

4,734
1
20
24

answered Apr 15 '15 at 09:18

Kiran teja Avvaru

334
3
10

im trying to `insert into select` to a partition table. The data is only 13k records. After using the above setting the query runs. but i get memory run our issue. Wonder, for a fairly small data it should not run out of memory. – sjd Jan 24 '19 at 05:27

score 3 · Answer 2 · edited Mar 09 '17 at 12:02

3

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.

edited Mar 09 '17 at 12:02

sureshvv

4,234
1
26
32

answered Mar 09 '17 at 07:17

gattamaneni harsha

61
2

2

How do you create a static partition column? – am6sigma Jul 03 '20 at 11:10

score 2 · Answer 3 · edited Feb 21 '19 at 15:51

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 ;

score 1 · Answer 4 · edited Nov 03 '21 at 16:03

1

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()

edited Nov 03 '21 at 16:03

Suraj Rao

29,388
11
94
103

answered Nov 03 '21 at 15:55

Elad Gazit

9
2

score 0 · Answer 5 · answered Nov 15 '19 at 13:38

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  ;")

score 0 · Answer 6 · answered Jan 03 '20 at 08:33

0

Can you try running MSCK REPAIR TABLE table_name;

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

answered Jan 03 '20 at 08:33

denimmonkey

11
3

Data not getting loaded into Partitioned Table in Hive

6 Answers6