My main table:

CREATE EXTERNAL TABLE user(language STRING,snapshot_time STRING,products STRUCT<id:STRING,name:STRING>,item STRUCT<quantity:ARRAY<STRUCT<name:STRING>>>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS TEXTFILE
LOCATION '/user/input/sample';

This is my main table, from which I'm trying to retrieve specific fields and insert into "user_prod_info" table. But, while inserting data using "Insert into" command, I'm facing the below error :

FAILED: SemanticException [Error 10007]: Ambiguous column reference text in q

CREATE EXTERNAL TABLE user_prod_info ( 
temp_row_num INT,
language STRING,
snapshot_time STRING,
id STRING,
prod_name STRING,
user_name STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY 't' 
NULL DEFINED as "null"
stored as textfile;

My insert command :

INSERT OVERWRITE TABLE user_prod_info
SELECT q.* FROM (
SELECT row_number() OVER (PARTITION BY products.id ORDER BY snapshot_time DESC) AS temp_row_num,
language,
snapshot_time,
products.id,
products.name,
A.name
FROM user as raw 
LATERAL VIEW EXPLODE(item.quantity) quantity as A
) q WHERE temp_row_num == 1;

This command is unable to retrieve the field from the specific table because we have two "name" fields. one is in "products" and the other is in "A".

I tried creating alias for "A.name as name1". I'm able to insert the data without errors. But, one record is storing in 3 rows with some nulls in it.

I got stuck over here. Can anyone please help me out regarding this...

you can remove the ambiguous adding an alias for one of the column names like this

INSERT OVERWRITE TABLE user_prod_info
SELECT q.* FROM (
SELECT row_number() OVER (PARTITION BY products.id ORDER BY snapshot_time DESC) AS temp_row_num,
language,
snapshot_time,
products.id,
products.name as prod_name,
A.name
FROM user as raw 
LATERAL VIEW EXPLODE(item.quantity) quantity as A
) q WHERE temp_row_num == 1;