18、Snowflake 数据编程：脚本与 Snowpark 全解析

uran

于 2025-08-20 15:20:08 发布

阅读量48

点赞数

CC 4.0 BY-SA版权

分类专栏： Snowflake核心认证备考指南文章标签： Snowflake 数据编程脚本

本文链接：https://blog.youkuaiyun.com/uran/article/details/150577107

Snowflake核心认证备考指南专栏收录该内容

19 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

Snowflake 数据编程：脚本与 Snowpark 全解析

1. 安全数据共享与主键生成

在数据处理中，使用序列生成表的主键是常见操作。但如同安全视图一样，如果这些主键对没有访问所有底层数据权限的用户可见，他们可能会猜出数据分布。因此，应隐藏或不将序列生成的列作为函数输出，就像在安全视图中不显示它一样。

2. Snowflake 脚本概述

Snowflake 不仅支持使用 SQL 查询数据，还支持以编写过程代码块、创建存储过程和函数以及创建 API 调用的形式对数据进行编程操作。Snowflake 脚本允许在 SQL 语句不足以满足需求时编写过程逻辑，可用于编写存储过程、函数和过程代码块。

2.1 Snowflake 脚本块结构

Snowflake 脚本块由关键字分隔的部分组成，如下所示：

DECLARE
  -- (变量、游标、异常、结果集)
BEGIN
  -- (Snowflake 脚本和 SQL 语句)
EXCEPTION
  -- (处理异常的语句)
END;

各部分作用如下：
- DECLARE ：可声明将使用的变量、游标、异常或结果集，此部分可选。
- BEGIN … END ：在 BEGIN 和 END 关键字之间编写 SQL 语句和其他 Snowflake 脚本结构。
- EXCEPTION ：可编写异常处理代码，用于捕获异常，此部分可选。

最基本的 Snowflake 脚本块只需 BEGIN 和 END 关键字。例如，创建两个表 FRUIT 和 FRUIT_INVENTORY 的代码块：

BEGIN
  create table FRUIT (
    fruit_id number,
    name varchar,
    color varchar
  );
  create table FRUIT_INVENTORY (
    fruit_id number,
    inv_date date,
    amount_kg number
  );
END;

执行此块代码时，可像在 Snowsight 中执行任何 SQL 查询一样，使用所选的数据库、模式和虚拟仓库。若使用 Classic UI，需参考 Snowflake 文档了解代码格式和执行方法。

上述代码块为匿名块，可独立执行且无名称。也可在存储过程中使用块，例如：

create procedure CREATE_SAMPLE_TABLES()
  returns number
  language sql
  as
BEGIN
  create or replace table FRUIT (
    fruit_id number,
    name varchar,
    color varchar
  );
  create or replace table FRUIT_INVENTORY (
    fruit_id number,
    inv_date date,
    amount_kg number
  );
END;

执行存储过程使用 CALL 语句：

call CREATE_SAMPLE_TABLES();

2.2 变量声明

在 Snowflake 脚本中，可声明变量并定义其数据类型。变量可用于表达式、SQL 语句或作为 Snowflake 脚本结构的一部分。可通过指定确切数据类型显式定义变量的数据类型，也可提供初始值，Snowflake 将根据提供的值确定数据类型。

在块的 DECLARE 部分声明变量的语法如下：
- <variable_name> <data_type>;
- <variable_name> DEFAULT <expression>;
- <variable_name> <data_type> DEFAULT <expression>;

数据类型可以是：
- SQL 数据类型
- CURSOR
- RESULTSET
- EXCEPTION

例如，在 DECLARE 部分声明变量的代码块：

DECLARE
  weight_lb number;
  weight_kg number;
BEGIN
  weight_lb := 145;
  weight_kg := 0.453592 * weight_lb;
  return weight_kg;
END;

此例声明了两个变量 WEIGHT_LB 和 WEIGHT_KG，将 145 赋值给 WEIGHT_LB，用其进行计算并将结果赋值给 WEIGHT_KG，最后返回 WEIGHT_KG 的值。块内声明的变量不能在块外使用。

也可在 BEGIN 和 END 关键字之间使用 LET 命令声明变量，例如：

DECLARE
    weight_kg number;
BEGIN
    let weight_lb := 145;
    weight_kg := 0.453592 * weight_lb;
    return weight_kg;
END;

当不明确指定数据类型声明变量时，Snowflake 脚本会从赋值表达式推断数据类型。为避免歧义，建议明确定义数据类型。

2.3 变量赋值

使用 := 运算符为变量赋值，语法为：

<variable_name> := <expression>;

变量也可用于 SQL 语句，此时需在变量名前加冒号。例如，将 WEIGHT_KG 变量的值插入表中：

insert into FRUIT_INVENTORY (amount_kg) values (:weight_kg);

变量还可作为对象名称，如表名，此时需用 IDENTIFIER 关键字括起来。例如，从 TABLE_NAME 变量存储的表中选择数据：

select sum(amount_kg) from identifier(:table_name);

若在表达式或使用 RETURN 等 Snowflake 脚本语言元素时使用变量，无需在变量前加冒号。例如，返回 WEIGHT_KG 变量的值：

return weight_kg;

当代码块用于存储过程时，可使用作为参数传递给过程的变量。例如，创建基于重量转换的存储过程：

create procedure CONVERT_WEIGHT(weight_lb number)
  returns number
  language sql
  as
DECLARE
  weight_kg number;
BEGIN
  weight_kg := 0.453592 * weight_lb;
  return weight_kg;
END;

由于 WEIGHT_LB 变量作为存储过程的参数，无需在代码块中声明。使用时无需冒号前缀。执行存储过程时，使用 CALL 语句并提供输入参数的值：

call CONVERT_WEIGHT(145);

2.4 分支和循环

Snowflake 脚本提供各种分支和循环语句，包括 IF 和 CASE 语句用于分支，以及 FOR、WHILE、REPEAT 和 LOOP 语句用于过程逻辑中的循环。

2.4.1 IF 语句示例

BEGIN
  let weight_lb := 114;
  IF (weight_lb < 100) THEN
    return 'less than 100';
  ELSEIF (weight_lb = 100) THEN
    return 'exactly 100';
  ELSE
    return 'more than 100';
  END IF;
END;

IF 语句的行为与许多编程语言中的 IF 结构类似，IF 关键字后的表达式必须包含在括号中。

2.4.2 CASE 语句示例

BEGIN
  let fruit_name := 'banana';
  CASE (fruit_name)
    WHEN 'apple' THEN
      return 'red';
    WHEN 'banana' THEN
      return 'yellow';
    WHEN 'avocado' THEN
      return 'green';
    ELSE
      return 'unknown';
  END;
END;

CASE 语句类似于 IF 语句，但可有多个条件。Snowflake 会执行第一个值与表达式匹配的 WHEN 子句。

2.4.3 FOR 循环示例

基于计数器的 FOR 循环执行五次：

DECLARE
  counter integer default 0;
  maximum_count integer default 5;
BEGIN
  FOR i in 1 to maximum_count do
    counter := counter + 1;
  END FOR;
  return counter;
END;

在循环结构中，可使用 BREAK 命令提前终止循环，使用 CONTINUE 命令跳过当前迭代的剩余语句并继续下一次迭代。

2.5 游标使用

在 Snowflake 脚本块中，可使用 INTO 子句将 SELECT 语句返回的值赋给变量。例如：

DECLARE
  v_id integer;
  v_name varchar;
  v_color varchar;
BEGIN
  select fruit_id, name, color
    into v_id, v_name, v_color
    from FRUIT
    where fruit_id = 1;
END;

此语法仅在 SELECT 语句返回单行时有效，因此查询中有过滤单个 FRUIT_ID 的 WHERE 条件。

当要处理 SELECT 语句返回的多行数据时，可使用游标逐行迭代查询结果。使用游标步骤如下：
1. 在块的 DECLARE 部分声明游标，提供游标将执行的查询。
2. 在代码块的主要部分（BEGIN 和 END 关键字之间），执行 OPEN 命令打开游标、执行查询并将结果加载到游标中，然后执行 FETCH 命令逐行获取并使用循环命令处理，处理完后执行 CLOSE 命令关闭游标。
3. 或者，在代码块的主要部分使用基于游标的 FOR 循环迭代结果集，使用时无需显式打开和关闭游标。

例如，使用之前创建的 FRUIT 和 FRUIT_INVENTORY 表插入数据：

insert into FRUIT values
  (1, 'apple', 'red'),
  (2, 'banana', 'yellow'),
  (3, 'avocado', 'green');
insert into FRUIT_INVENTORY values
  (1, sysdate(), 110),
  (2, sysdate(), 80),
  (3, sysdate(), 145);

声明游标并使用基于游标的 FOR 循环处理：

DECLARE
  c1 cursor for
    select amount_kg from FRUIT_INVENTORY;
  total_amount number default 0;
BEGIN
  FOR record in c1 do
    total_amount := total_amount + record.amount_kg;
  END FOR;
  return total_amount;
END;

也可使用 RESULTSET_FROM_CURSOR 函数和 TABLE 关键字从游标返回表：

DECLARE
  c2 cursor for
    select FRUIT.name, FRUIT_INVENTORY.amount_kg
    from FRUIT_INVENTORY
    inner join FRUIT
      on FRUIT.fruit_id = FRUIT_INVENTORY.fruit_id;
BEGIN
  open c2;
  return TABLE(resultset_from_cursor(c2));
END;

2.6 受影响的行数和查询 ID

每次执行 DML 命令后，Snowflake 脚本会设置以下全局变量：
- SQLROWCOUNT ：最后一个 DML 语句影响的行数。
- SQLFOUND ：若最后一个 DML 语句影响了一行或多行，则为 TRUE。
- SQLNOTFOUND ：若最后一个 DML 语句未影响任何行，则为 TRUE。

例如，更新 FRUIT_INVENTORY 表中的记录并返回 SQLROWCOUNT 的值：

BEGIN
  update FRUIT_INVENTORY
    set amount_kg = 175 where fruit_id = '2';
  return SQLROWCOUNT;
END;

此例中，一行被更新，返回值为 1。同样，也可返回 SQLFOUND 变量的值，应为 TRUE。

另一个有用的全局变量是 SQLID，它包含最后执行的查询的 ID。例如：

BEGIN
  update FRUIT_INVENTORY
    set amount_kg = 150 where fruit_id = '3';
  return SQLID;
END;

此例从代码块返回 UPDATE 语句的查询 ID。

2.7 异常处理

与许多编程语言类似，Snowflake 脚本允许在脚本块中引发异常。异常可能由多种原因引起，如数据库错误、脚本代码语法错误、数据类型转换错误等。

由于 Snowflake 脚本块可以嵌套，异常处理程序也可以嵌套。当在脚本块中引发异常时，Snowflake 按以下优先级查找异常处理程序：
1. 若发生异常的块有自己的异常处理程序来捕获异常，则执行该处理程序。
2. 若该块没有自己的异常处理程序，则执行包含当前块的块中的异常处理程序。
3. 异常处理嵌套继续，遵循脚本块的嵌套层次结构。每次发生错误时，若当前块中没有异常处理程序，异常将向外传递一层，直到找到合适的异常处理程序或到达最外层。

使用 EXCEPTION 子句编写异常处理程序，可使用 WHEN 子句处理单个异常。Snowflake 脚本中一些常见的内置异常包括：
- STATEMENT_ERROR ：执行语句时出错引发。
- EXPRESSION_ERROR ：与表达式相关的错误引发，如尝试将表达式的值赋给不兼容数据类型的变量。

每个异常返回的几个变量提供了有关内置异常的额外信息：
- SQLCODE ：表示错误代码的整数。
- SQLERRM ：包含错误消息的字符串。
- SQLSTATE ：基于 ANSI SQL 标准的代码。

使用 OTHER 关键字处理未命名的其他异常。例如，尝试从 FRUIT 表中选择多行并将结果存储在变量中，此语句会失败，可在添加的 EXCEPTION 部分捕获异常：

DECLARE
  v_name varchar;
BEGIN
  select name
  into v_name
  from FRUIT;
  return v_name;
EXCEPTION
  WHEN statement_error THEN
    return object_construct('Error type', 'Statement error',
      'SQLCODE', sqlcode, 'SQLERRM', sqlerrm,
      'SQLSTATE', sqlstate);
  WHEN expression_error THEN
    return object_construct('Error type', 'Expression error',
      'SQLCODE', sqlcode, 'SQLERRM', sqlerrm,
      'SQLSTATE', sqlstate);
  WHEN other THEN
    return object_construct('Error type', 'Other error',
      'SQLCODE', sqlcode, 'SQLERRM', sqlerrm,
      'SQLSTATE', sqlstate);
END;

以下是一个 mermaid 格式的流程图，展示了 Snowflake 脚本块的基本结构：

graph TD;
    A[开始] --> B{DECLARE部分};
    B -- 可选 --> C[声明变量、游标等];
    B -- 无 --> D[BEGIN部分];
    C --> D;
    D --> E[编写SQL和脚本语句];
    E --> F{EXCEPTION部分};
    F -- 可选 --> G[编写异常处理代码];
    F -- 无 --> H[END部分];
    G --> H;
    H --> I[结束];

通过以上内容，我们详细了解了 Snowflake 脚本的各个方面，包括脚本块结构、变量声明与赋值、分支和循环、游标使用、全局变量以及异常处理等。这些功能为在 Snowflake 中进行数据编程提供了强大的支持。

3. 总结与操作要点回顾

3.1 关键知识点总结

知识点	描述
安全数据共享	注意主键生成序列的可见性，避免无权限用户猜测数据分布，可隐藏或不将其作为函数输出。
Snowflake 脚本块	由 DECLARE、BEGIN … END、EXCEPTION 部分组成，最基本只需 BEGIN 和 END。可用于创建匿名块或存储过程。
变量声明	可在 DECLARE 部分或 BEGIN 和 END 之间使用 LET 命令声明，可显式或隐式定义数据类型，建议显式定义避免歧义。
变量赋值	使用 `:=` 运算符，在 SQL 语句中使用变量需加冒号，作为对象名时用 IDENTIFIER 关键字。
分支和循环	提供 IF、CASE 语句用于分支，FOR、WHILE、REPEAT 和 LOOP 语句用于循环，IF 后表达式需括号。
游标使用	处理多行数据时，可在 DECLARE 部分声明，在 BEGIN 和 END 之间操作，也可用基于游标的 FOR 循环。
全局变量	SQLROWCOUNT 记录 DML 影响行数，SQLFOUND 和 SQLNOTFOUND 反映是否有行受影响，SQLID 记录最后查询 ID。
异常处理	可嵌套，使用 EXCEPTION 子句和 WHEN 子句处理，有内置异常和自定义异常，用 OTHER 处理未命名异常。

3.2 操作步骤梳理

3.2.1 创建存储过程

定义存储过程名称、返回类型和语言。
在 as 关键字后编写脚本块，可包含变量声明、SQL 语句等。
使用 CALL 语句执行存储过程并提供参数（如有）。

示例代码：

create procedure CONVERT_WEIGHT(weight_lb number)
  returns number
  language sql
  as
DECLARE
  weight_kg number;
BEGIN
  weight_kg := 0.453592 * weight_lb;
  return weight_kg;
END;

call CONVERT_WEIGHT(145);

3.2.2 使用游标处理多行数据

在 DECLARE 部分声明游标，指定查询语句。
在 BEGIN 和 END 之间，可选择使用 OPEN、FETCH、CLOSE 命令操作，或使用基于游标的 FOR 循环。

示例代码：

DECLARE
  c1 cursor for
    select amount_kg from FRUIT_INVENTORY;
  total_amount number default 0;
BEGIN
  FOR record in c1 do
    total_amount := total_amount + record.amount_kg;
  END FOR;
  return total_amount;
END;

3.2.3 异常处理

在脚本块中添加 EXCEPTION 部分。
使用 WHEN 子句处理不同类型的异常，可使用内置异常或自定义异常。
用 OTHER 关键字处理未命名的异常。

示例代码：

DECLARE
  v_name varchar;
BEGIN
  select name
  into v_name
  from FRUIT;
  return v_name;
EXCEPTION
  WHEN statement_error THEN
    return object_construct('Error type', 'Statement error',
      'SQLCODE', sqlcode, 'SQLERRM', sqlerrm,
      'SQLSTATE', sqlstate);
  WHEN expression_error THEN
    return object_construct('Error type', 'Expression error',
      'SQLCODE', sqlcode, 'SQLERRM', sqlerrm,
      'SQLSTATE', sqlstate);
  WHEN other THEN
    return object_construct('Error type', 'Other error',
      'SQLCODE', sqlcode, 'SQLERRM', sqlerrm,
      'SQLSTATE', sqlstate);
END;

3.3 操作流程可视化

以下是一个 mermaid 格式的流程图，展示了使用 Snowflake 脚本进行数据处理的一般流程：

graph LR;
    A[开始] --> B[定义需求];
    B --> C{是否需要存储过程};
    C -- 是 --> D[创建存储过程];
    C -- 否 --> E[编写脚本块];
    D --> F[声明变量和游标];
    E --> F;
    F --> G[编写 SQL 语句和逻辑];
    G --> H{是否需要异常处理};
    H -- 是 --> I[添加 EXCEPTION 部分];
    H -- 否 --> J[执行脚本];
    I --> J;
    J --> K{是否使用游标};
    K -- 是 --> L[操作游标];
    K -- 否 --> M[完成操作];
    L --> M;
    M --> N[结束];

通过上述总结和梳理，我们对 Snowflake 脚本和 Snowpark 的相关知识有了更清晰的认识，能够更熟练地运用这些技术进行数据编程和处理。无论是处理简单的数据查询，还是构建复杂的存储过程和异常处理机制，都可以依据这些操作要点和流程来实现。希望这些内容能帮助你在实际应用中更好地发挥 Snowflake 的强大功能。