CODEWITHSUNDEEP
hadoophive
Gibbs
Gibbs

Reputation: 22956

Hive modify partitioned table data

Problem: One column value is null. It should be 'ab'. Unfortunately I have written '' instead 'ab'.

My table is partitioned table. Is there any way to change that?

I found the following way. But it seems inefficient.

  1. Create a temp table like my table
  2. Use INSERT OVERWRITE. Read data from my old table and write to new table. I am using case statement to change '' to 'ab'
  3. And then change my temp table to original table.

I am looking for a solution something like update partition and msck. Is there any way to do?

Upvotes: 3

Views: 1698

Answers (2)

franklinsijo
franklinsijo

Reputation: 18270

One possible solution would be to perform update on the table provided the column is not neither a partitioning nor bucketing column.

UPDATE tablename SET column = (CASE WHEN column = '' THEN 'ab' else column END) [WHERE expr if any];

Update: To support ACID operations on Hive 

SET hive.support.concurrency=true;
SET hive.enforce.bucketing=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
SET hive.compactor.initiator.on=true;
SET hive.compactor.worker.threads=1;

Note: works only if Hive >= 0.14

Upvotes: 1

leftjoin
leftjoin

Reputation: 38290

You can overwrite single partition in this way:

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

insert overwrite target_table partition (part_col)
select 
case when column ='' then 'ab' else column end as column ,
col2,    --select all the columns in the same order
col3,
part_col --partition column is the last one
from target_table where part_col='your_partition_value';

Upvotes: 1

Related Questions