在插入配置单元表时面临不明确的列引用错误

Question

我的主表：

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';

这是我的主表，我试图从中检索特定字段并插入到"user_prod_info"表中。但是，当使用"插入"命令插入数据时，我面临以下错误：

失败：SemanticException[错误10007]：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;

我的插入命令：

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;

此命令无法从特定表中检索字段，因为我们有两个"名称"字段。一个是在"产品"中，另一个在"A"中。

我尝试为"A.name as name1"创建别名。我可以毫无错误地插入数据。但是，一条记录存储在3行中，其中有一些空值

我被困在这里了。有人能帮我解决这个问题吗。。。

Answer 1

您可以删除不明确的为类似的列名之一添加别名

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;

Answer 2

因为在两个命令中使用了相同的名称"temp_row_num"。因此，请在第二个命令中将该名称命名为"temp_row_num_new"。完整的第二个查询如下：

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_new,
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_new == 1;