Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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
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*
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB