CODEWITHSUNDEEP
mysqlsqlhivehiveql
Akshat Choudhary
Akshat Choudhary

Reputation: 85

Insert a value in one table if it is not present in another

I have two tables, Table1 and Table2. Table1 has only one column, let's say Id. Table2 has multiple other columns besides Id I need to write a hive query to first check if a given Id is present in the Id column of Table1 or not. If the Id is not present I need to insert it into Table2 otherwise insert null.

Eg:

Table1

----Id1------
"abcde"
"ghdis"
----------

Now I suppose I am given a value "sjsnx". The query should run through Table1 and insert "sjsnx" in Table2. If I am given "abcde" as value, the query should insert null in Table2.

Upvotes: 0

Views: 1680

Answers (2)

Rishu S
Rishu S

Reputation: 3968

You need to write a code in some programming language (maybe SHELL, Python, etc). This cannot be done in one shot using hive since your requirement needs to do two database INSERTS. Also for your input requirement, you can leverage Hive config parameter using SET values to search for IDs.

Your code will look something as below in SHELL:

Checking the first table:

 search_id='<your search id>'

 table1search_var=`hive -S -e "select id from table1 where id=${hiveconfig:db_search_id}" --hiveconfig:db_search_id=$search_id`;
 
 if [ -z "$table1search_var" ];
 then
   echo "Found Null row. Hence inserting search id into table2"
   hive -S -e "insert into table2(id) values('$search_id')"
 
 else
   echo "Found Not Null rows. Hence inserting NULL into table2"
   hive -S -e "insert into table2(id) values(null)"
 
 fi

Hope this helps :)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270081

If I understand correctly, you can use not exists to get the ids that are in table1 but not table2:

insert into table2 (id, . . . )
    select t1.id, . . . 
    from table1 t1
    where not exists (select 1 from table2 t2 where t2.id = t1.id);

The . . . is for the other columns and their values.

Upvotes: 1

Related Questions