Trying to get multiple rows with the values in the array in hive by lateral view and explode

Question

I have a table with two columns and i am trying get multiple rows with the values in the array.My table is like this.

date                  users
2019-01-01       ["U00001","U00002","U00002"]

I am trying to get the output like below

date               users
2019-01-01       "U00001"
2019-01-01       "U00002"
2019-01-01       "U00003"

I am using below query

SELECT date, user FROM  table1
LATERAL VIEW  explode(users)  myTable2 AS user;

I am not able to get the output that i expected above and
my query results are showing like this.

date                 user
2019-01-01    "U00001","U00002","U00002"

my column data types are

column         data_type
date            string
user            Array

Answer 1

date and user are reserverd words in Hive, use backticks. Also (check my example) lateral view should have an alias (u), column exploded an alias user:

with your_data as (--use your table instead of this 
select stack(1, '2019-01-01', array("U00001","U00002","U00002")) as(`date`, users)
)

select t.`date`, u.`user` 
  from your_data t --use your table instead
       lateral view explode(t.users) u as `user` ;

If users is of type STRING, then remove square brackets and double-quotes, then split and explode:

with your_data as (--use your table instead of this 
select stack(1, '2019-01-01', '["U00001","U00002","U00002"]') as (`date`, users)
)

select t.`date`, u.`user` 
  from your_data t --use your table instead
       lateral view explode(split(regexp_replace(t.users,'\\[|\\]|\\"',''),',')) u as `user` ;

Result:

t.date      u.user
2019-01-01  U00001
2019-01-01  U00002
2019-01-01  U00002