WRITE A HIVE QUERY TO DELETE THE ROWS WHOSE NAME START WITH 'SA' ???

[#1]

@anjaneyulu dodla

if you are having acid table in hive then you can execute below query to delete only the rows that starts with SA

delete from table where name like 'SA%';

the below link describes how to create transactional tables

https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions#HiveTransactions-TableProperties

(or)

if you don't have acid enabled on the table then you can select all the rows that are not starts with SA

keep them into tmp area then again overwrite the target table with the tmp data.

step1:- create tmp table with name not starts with SA dataset:-

hive> create table <tmp_db_name>.<tmp_table_name> stored as orc as select * from <target_db_name>.<target_table_name> where name not like 'SA%'; //now we have created new tmp table and selected all the rows that are not starting name with SA

step2 Insert overwrite the target table from tmp table data:-

hive> insert overwrite table <target_db_name>.<target_table_name> select * from  <tmp_db_name>.<tmp_table_name>; //insert overwrite the target table with tmp table.

.

If the Answer helped to resolve your issue, Click on Accept button below to accept the answer, That would be great help to Community users to find solution quickly for these kind of issues.