@anjaneyulu dodlaif 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.
