Redshift迁移Snowflake：VALUES含子查询的INSERT语句报错如何改写

错误原因 #

Snowflake 不支持在 INSERT ... VALUES 子句中直接嵌套标量子查询作为值参数，你在 Redshift 中使用的语法属于 PostgreSQL 系兼容扩展，Snowflake 未适配该写法，因此会抛出表达式无效的编译错误。

改写方案 #

将 VALUES 子句替换为直接 SELECT 固定值+子查询的形式即可正常执行，改写后的代码如下：

insert into
    etl_audit_table
    (
        batch_id,
        batch_run_date,
        table_name,
        staging_row_count,
        dwh_load_type
    )
select
    (select batch_id from etl_cntrl.batch_history_table where batch_status='running'),
    current_date,
    'tablename',
    (select count(*) from table where lastmodifieddate between '2021-07-31 00:00:00' and '2021-08-17 00:00:00'),
    'Slow Changing Dimension'
;

补充注意事项 #

• 若子查询存在返回多行的风险，可以给标量子查询增加 LIMIT 1 限制，或者使用 MAX()/MIN() 聚合函数保证仅返回单个值，避免运行时报错
• 该写法属于 ANSI SQL 标准写法，在 Redshift、Snowflake、BigQuery 等主流数仓都可以正常运行，兼容性更好

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