sql - hive -如何从类型为列表的表中读取列

Question

我有一个名为customer的Hive表，该表有一个名为cust_id的列表类型列，具有以下值:cust_id

[123,234,456,567]

[345,457,67]    

[89,23,34]    

现在，我只想在我的选择查询中读取此特定列cust_id，它可以将所有这些列表值提供为该列cust_id的以下单独值:

cust_id

123

234

456

567

345

457

67

89

23

34

基本上，我想从该表中获取所有 cust_id的值作为一列，以在其他查​​询的where存在或where in子句中使用这些值。
对此的解决方案将是高度赞赏的。

Answer 1

AFAIK这是您在 hive 手册中寻找的。

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.

例如

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

完整示例:

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