Snowflake中CONCAT内嵌套SELECT语句报错问题排查

原始查询 #

with table1 as 
(
    select
        schema_name,
        schema_owner,
        created,
        last_altered
    from 
        information_schema.schemata
), secondtable as
(
    select 
        schema_name as InternalName 
    from 
        table1
)
select * 
from concat('"ITCAPTURE".'(select * from secondtable),'"VW_SLA_KPI"')

错误信息 #

SQL compilation error: syntax error line 8 at position 20 unexpected '('. syntax error line 8 at position 78 unexpected ')'

问题原因 #

1. CONCAT函数调用语法错误：你使用concat时参数传递完全不符合规则，函数的多个参数必须用逗号分隔，但你直接将子查询(select * from secondtable)写在字符串后，没有逗号分隔，这直接触发了语法报错。
2. 静态SQL不支持动态表名拼接：Snowflake的常规静态SQL无法通过字符串拼接生成表名后直接用于from子句，这种动态引用数据库对象的需求必须通过动态SQL（比如EXECUTE IMMEDIATE语句）实现。
3. 子查询返回逻辑问题：即使语法修正，select * from secondtable如果返回多条schema名称，拼接出的字符串也不是合法表名，无法直接用于查询。

修正方案 #

场景1：仅查询单个指定schema下的视图 #

如果secondtable只会返回一条InternalName，可以用变量配合动态SQL执行：

SET target_schema = (SELECT InternalName FROM secondtable LIMIT 1);

EXECUTE IMMEDIATE CONCAT(
    'SELECT * FROM "ITCAPTURE".', '"', $target_schema, '"', '."VW_SLA_KPI"'
);

场景2：批量查询多个schema下的同名称视图 #

如果要查询所有符合条件的schema下的VW_SLA_KPI视图，可以生成动态UNION ALL语句批量执行：

DECLARE
    sql_stmt STRING;
BEGIN
    SELECT LISTAGG(
        CONCAT('SELECT * FROM "ITCAPTURE".', '"', InternalName, '"', '."VW_SLA_KPI"'),
        ' UNION ALL '
    ) INTO sql_stmt
    FROM secondtable;

    EXECUTE IMMEDIATE sql_stmt;
END;

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