在 Hive 中分解结构数组 [英] Explode the Array of Struct in Hive
问题描述
这是下面的 Hive 表
This is the below Hive Table
CREATE EXTERNAL TABLE IF NOT EXISTS SampleTable
(
USER_ID BIGINT,
NEW_ITEM ARRAY<STRUCT<PRODUCT_ID: BIGINT,TIMESTAMPS:STRING>>
)
这就是上表中的数据-
1015826235 [{"product_id":220003038067,"timestamps":"1340321132000"},{"product_id":300003861266,"timestamps":"1340271857000"}]
有什么方法可以在爆炸数组后从 HiveQL 获得以下输出?
Is there any way I can get the below output from the HiveQL after exploding the array?
**USER_ID** | **PRODUCT_ID** | **TIMESTAMPS**
------------+------------------+----------------
1015826235 220003038067 1340321132000
1015826235 300003861266 1340271857000
更新
我编写了这个查询来获得上述格式的输出,但它并没有以我想要的方式给我结果.
I wrote this query to get the output in the above format, but it is not giving me the result in the way I wanted to.
SELECT myTable1.myCol1,myTable2.myCol2 FROM sampletable st LATERAL VIEW
explode(st.purchased_item.product_id) myTable1 AS myCol1 LATERAL VIEW
explode(st.purchased_item.timestamps) myTable2 AS myCol2;
谁能帮助我我做错了什么?任何建议将不胜感激.
Can anyone help me what wrong I am doing? Any suggestions will be appreciated.
推荐答案
你只需要爆炸一次(结合 LATERAL VIEW).展开后,您可以使用一个新列(在我的示例中称为 prod_and_ts),该列将是 struct 类型.然后,您可以解析这个新结构列的 product_id 和 timestamps 成员来检索所需的结果.
You need to explode only once (in conjunction with LATERAL VIEW). After exploding you can use a new column (called prod_and_ts in my example) which will be of struct type. Then, you can resolve the product_id and timestamps members of this new struct column to retrieve the desired result.
SELECT
user_id,
prod_and_ts.product_id as product_id,
prod_and_ts.timestamps as timestamps
FROM
SampleTable
LATERAL VIEW explode(new_item) exploded_table as prod_and_ts;
这篇关于在 Hive 中分解结构数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!