snowflake-cloud-data-platform - 雪花条件码 : adding new column(idempotent script)

Question

假设我们有一个包含以下数据的表:

CREATE TABLE tab(i INT PRIMARY KEY);
INSERT INTO tab(i) VALUES(1),(2),(3);
SELECT * FROM tab;

现在我的目标是创建 SQL 脚本，将一个新列添加到现有表中:

ALTER TABLE IF EXISTS tab ADD COLUMN col VARCHAR(10);

一切都按预期工作。除了我希望能够多次运行脚本但效果应该只发生一次( idempotence )。

如果我尝试再次运行它，我会得到:

SQL compilation error: column COL already exists

通常我会使用以下方法之一:

a) 使用控制结构 中频 在执行查询之前检查元数据表:

-- (T-SQL)
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
              WHERE TABLE_NAME='TAB' AND COLUMN_NAME = 'COL')
BEGIN
   ALTER TABLE tab ADD col VARCHAR(10);
END;

db<>fiddle demo

我在 Snowflake 的文档中没有找到 IF 语句。

b) 支持 IF NOT EXISTS 的 SQL 方言句法:

-- PostgreSQL
ALTER TABLE IF EXISTS tab ADD COLUMN IF NOT EXISTS col VARCHAR(10);

db<>fiddle demo

大多数雪花 SQL 命令包含 IF EXISTS/ OR REPLACE子句，这意味着它的编写方式允许多次运行脚本。

我正在考虑使用如下代码:

CREATE OR REPLACE TABLE tab
AS
SELECT i, CAST(NULL AS VARCHAR(10)) AS col
FROM tab;

另一方面，这种方法会导致不必要的表创建并且不保留元数据(如主键)。

有没有办法在雪花上达到类似的效果？最好使用条件代码(以添加列为例)。

Answer 1

你可以使用这样的东西。如果列已经存在，它会报告添加失败，但它会处理错误，因此它不会干扰 sql 脚本的执行:

create or replace procedure SafeAddColumn(tableName string, columnName string, columnType string)
returns string
language JavaScript
as
$$
    var sql_command = "ALTER TABLE IF EXISTS " + TABLENAME + " ADD COLUMN " + COLUMNNAME + " " + COLUMNTYPE + ";";
    var strOut;
    try {
        var stmt = snowflake.createStatement( {sqlText: sql_command} );
        var resultSet = stmt.execute();
        while (resultSet.next())  {
            strOut = resultSet.getColumnValue(1);
        }
    }
    catch (err)  {
        strOut = "Failed: " + err;   // Return a success/error indicator.
    }
    return strOut;
$$;

CREATE OR REPLACE TABLE tab(i INT PRIMARY KEY);
INSERT INTO tab(i) VALUES(1),(2),(3);
SELECT * FROM tab;

call SafeAddColumn('tab', 'col', 'varchar(10)');
select * from tab;
call SafeAddColumn('tab', 'col', 'varchar(10)');