ClickHouse是否有控制列别名显示规则的配置项？

在ClickHouse中，多表关联时使用SELECT *默认会给列名加上表别名前缀（如A.id），目前没有全局配置项可以直接禁用这种前缀行为，你试过的asterisk_include_alias_columns和prefer_column_name_to_alias确实不针对这个场景：

• asterisk_include_alias_columns用于控制是否在SELECT *中包含表的别名列，不影响关联列的前缀；
• prefer_column_name_to_alias是控制函数/表达式优先用列名还是别名，和关联时的列前缀无关。

要让返回列名和Athena一致，最直接可靠的方法是显式指定列名而非使用SELECT *，修改你的CTE查询如下：

with T1 as (
    SELECT
        A.id AS id,
        A.path_id AS path_id,
        B.userid,
        B.DPID,
        C.LPID,
        D._id
    FROM (SELECT id, path_id FROM table1) AS A
    LEFT JOIN (SELECT id as userid, DPID FROM table2) AS B ON A.id = B.userid
    LEFT JOIN (SELECT id as LPID FROM table3) AS C ON A.path_id = C.LPID
    LEFT JOIN (SELECT _id FROM table4) AS D ON B.DPID = D._id
    WHERE DPID is not null
)
select * from T1

这样查询返回的列名就会和Athena完全一致：id, path_id, userid, DPID, LPID, _id。

如果不想修改大量的SELECT *，也可以在最终查询时通过RENAME语法调整列名，但这种方式不如显式列名清晰，示例如下：

with T1 as (
    -- 原CTE查询不变
    SELECT * FROM (SELECT id, path_id FROM table1) AS A
    LEFT JOIN (SELECT id as userid, DPID FROM table2) AS B ON A.id = B.userid
    LEFT JOIN (SELECT id as LPID FROM table3) AS C ON A.path_id = C.LPID
    LEFT JOIN (SELECT _id FROM table4) AS D ON B.DPID = D._id
    WHERE DPID is not null
)
select
    A.id AS id,
    A.path_id AS path_id,
    B.userid AS userid,
    B.DPID AS DPID,
    C.LPID AS LPID,
    D._id AS _id
from T1

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