AWS Athena查询正常但创建视图时报‘列名重复’错误，求原因

问题场景 #

你编写的关联三张表的SELECT查询能正常运行：

Select t1.*, t2.*, t3.*
from "analytics_poc"."stg_orderitem" as t1
INNER Join "analytics_poc"."stg_orderitemtag" as t2
ON t1.orderitemid=t2.orderitemid
LEFT Join "analytics_poc"."stg_tag" as t3
ON t3.tagid=t2.tagid

但用相同逻辑创建视图时触发错误：

CREATE OR REPLACE VIEW "CMS_orderitem_tags"
AS 
Select t1.*, t2.*, t3.*
from "analytics_poc"."stg_orderitem" as t1
INNER Join "analytics_poc"."stg_orderitemtag" as t2
ON t1.orderitemid=t2.orderitemid
LEFT Join "analytics_poc"."stg_tag" as t3
ON t3.tagid=t2.tagid

错误信息：

line 1:1: Column name 'orderitemid' specified more than once.

问题原因 #

直接执行查询时，Athena允许结果集中存在重复列名（比如t1.orderitemid和t2.orderitemid同时出现在结果里），但创建视图的规则更严格：视图必须拥有唯一的列名定义，不能存在重复的列名。你的查询里t1（stg_orderitem表）和t2（stg_orderitemtag表）都包含orderitemid字段，用*通配符会把这两个重复字段都带入视图的列定义，因此触发报错。

解决方法 #

核心是避免视图的列定义出现重复名称，两种常用方式：

1. 显式指定所有需要的列，对重复列重命名
   放弃*通配符，逐个列出字段，给重复的字段设置别名，示例：

   CREATE OR REPLACE VIEW "CMS_orderitem_tags"
   AS 
   Select 
     t1.orderitemid, 
     t1.order_id, -- 替换为实际需要的字段
     t1.item_name,
     t2.tagid as assoc_tag_id,
     t2.create_time,
     t3.tagid,
     t3.tag_name,
     t3.tag_desc
   from "analytics_poc"."stg_orderitem" as t1
   INNER Join "analytics_poc"."stg_orderitemtag" as t2
   ON t1.orderitemid=t2.orderitemid
   LEFT Join "analytics_poc"."stg_tag" as t3
   ON t3.tagid=t2.tagid
   

2. 排除重复列（Athena支持）
   使用EXCLUDE语法排除重复的字段，保留其他字段的*通配符，示例：

   CREATE OR REPLACE VIEW "CMS_orderitem_tags"
   AS 
   Select 
     t1.*,
     t2.* EXCLUDE (orderitemid), -- 排除t2中与t1重复的orderitemid
     t3.*
   from "analytics_poc"."stg_orderitem" as t1
   INNER Join "analytics_poc"."stg_orderitemtag" as t2
   ON t1.orderitemid=t2.orderitemid
   LEFT Join "analytics_poc"."stg_tag" as t3
   ON t3.tagid=t2.tagid
   

内容的提问来源于stack exchange，提问作者Ross Dickinson