CODEWITHSUNDEEP
sqlhivehiveqlimpala
Omar AlSaghier
Omar AlSaghier

Reputation: 340

How to UPDATE a value in hive table?

I have a flag column in Hive table that I want to update after some processing. I have tried using hive and impala using the below query but it didn't work, and got that it needs to be a kudu table while the table I have is a non-kudu table. Is there a way to update it like this query below ?

UPDATE table_name SET flag_col = 1
where [condition];

Upvotes: 5

Views: 15370

Answers (2)

yusuf hayırsever
yusuf hayırsever

Reputation: 701

Hive doesn't support updates (or deletes), but it supports INSERT INTO, so it is possible to add new rows to an existing table.

> insert overwrite table table_name 
> select *,
 case when [condition] then 1 else flag_col end as flag_col,
 from table_name 

//If you want to use you can add where// > where id <> 1;

Upvotes: 1

leftjoin
leftjoin

Reputation: 38335

Overwrite the whole table with calculated values, all other columns as is:

insert overwrite table table_name 
select col1, col2, ..., coln, 
       case when [condition] then 1 else flag_col end as flag_col,
       colO, 
       colP...
  from table_name ;

Read documentation for more details on partitioned tables, etc: https://docs.cloudera.com/documentation/enterprise/5-8-x/topics/impala_insert.html

Upvotes: 3

Related Questions