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

Switch to Threaded View
Hive, mail # user - Explode of Array in Hive


Copy link to this message
-
Explode of Array in Hive
Raihan Jamal 2012-07-07, 22:16
Hi Everyone,

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*