Hive search using csv file

Question

I'm relatively new to Hive, so I'm not even sure of the proper terminology to use, so this may have already been addressed. Apologies if it has.

Here's my scenario; we have a large table of data for thousands of devices, keyed by serial number. I need to lookup specific variables for devices, often several hundred at a time. I know I can do a search that contains "SN=001 OR SN=002 OR SN=003.." for hundreds of entries, but that's awkward and time consuming. What I'd like to be able to do is have a csv file that contains a list of serial numbers, and perform a search that says "Give me the variables I want for all the devices in this csv file". Is that possible, and if so how do I do it? Thanks!

Answer 1

in_file

Demo

bash

cat>/tmp/myfile.txt
111
123
222
333
789

hive

create table mytable (mycol string);
insert into mytable values (123),(456),(789);

---

select  *
from    mytable
where   in_file  (mycol,'/tmp/myfile.txt')
;

---

+-------+
| mycol |
+-------+
|  123  |
|  789  |
+-------+