Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # dev - Explode the Array of Struct in Hive


Copy link to this message
-
Explode the Array of Struct in Hive
Raihan Jamal 2012-07-07, 19:46
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*