Hive – How to read a column from a table which is of type list

I have a Hive table named customer, which has a column named cust_id of list type, with following values: cust_id

[123,234,456,567]

[345,457,67]    

[89,23,34]    

Now I want to read only this specific column cust_id in my select query, which can give all these list values as following separate values of this column cust_id:

cust_id

123

234

456

567

345

457

67

89

23

34

Basically I want to fetch all the values of cust_id from this table as one column, to use these values in the where exists or where in clause of my other query. A solution for this would be highly appreciated.

Answer

AFAIK this is what you are looking for from hive manual..

Lateral view is used in conjunction with user-defined table generating functions such as explode(). As mentioned in Built-in Table-Generating Functions, a UDTF generates zero or more output rows for each input row.

for example

SELECT cust_id
FROM mytable LATERAL VIEW explode(cust_id) mytab AS cust_id;

Full example :

drop table customer_tab;
create table customer_tab ( cust_id array<String>);

INSERT INTO table customer_tab select array('123','234','456','567');

INSERT INTO table customer_tab select array('345','457','67');

INSERT INTO table customer_tab select array('89','23','34');

select * from customer_tab;
--      customer_tab.cust_id
--      ["123","234","456","567"]
--      ["345","457","67"]
--      ["89","23","34"]

SELECT mytab.cust_id
FROM customer_tab LATERAL VIEW explode(cust_id) mytab AS cust_id;


mytab.cust_id
123
234
456
567
345
457
67
89
23
34