You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hive.apache.org by Raihan Jamal <ja...@gmail.com> on 2012/07/07 21:46:33 UTC
Explode the Array of Struct in 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>>
)
And this is the data in the above table-
1015826235
[{"product_id":220003038067,"timestamps":"1340321132000"},{"product_id":300003861266,"timestamps":"1340271857000"}]
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
**Updated**
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 in my Query?
*Raihan Jamal*