将存储过程迁移到 Amazon Redshift

Amazon 始终以满足客户需求为工作重点。客户强烈要求希望能在 Amazon Redshift 中使用存储过程，以便更轻松地从原有的本地数据仓库迁移现有工作负载。

为实现这一主要目标，AWS 选择实施了 PL/pqSQL 存储过程以最大程度地兼容现有的程序并简化迁移。在本博文中，我们将讨论如何以及在什么情况下可以使用存储过程提高操作效率和安全性。此外，还会说明如何通过 AWS Schema Conversion Tool 使用存储过程。

什么是存储过程？

存储过程是用户创建的对象，用于执行一组 SQL 查询和逻辑操作。存储过程存储在数据库中，只有具有相应权限的用户才能运行存储过程。

不同于用户定义的函数 (UDF)，存储过程除了 SELECT 查询外，还可以纳入数据定义语言 (DDL) 和数据操作语言 (DML)。存储过程不一定要返回值。您可以使用 PL/pgSQL 程序语言（包括循环和条件表达式）来控制逻辑流。

存储过程通常用于封装逻辑，以进行数据转换、数据验证和具体业务操作。通过将多个 SQL 步骤组合到一个存储过程，可以减少应用程序和数据库之间的往返时间。

您也可以将存储过程用于委派访问控制。例如，您可以创建存储过程来执行函数，无需授予用户基础表访问权限。

为什么要使用存储过程？

迁移到 Amazon Redshift 的许多客户都拥有在其旧数据仓库平台上使用存储过程构建的复杂数据仓库处理管道。复杂的转换和重要的聚合由存储过程定义，并在其处理的许多部分中重复使用。使用外部编程语言或新的 ETL 平台重新创建这些流程的逻辑可能会是一个大工程。使用 Amazon Redshift 存储过程可让您更快地迁移到 Amazon Redshift。

其他客户希望加强安全性并限制其数据库用户的权限。存储过程带来了新选择，让 DBA 可以执行必要的操作而不必授予过多的权限。通过存储过程中的安全定义者概念，现在可以允许用户执行原本不具有运行权限的操作。

其次，以这种方式使用存储过程有助于减轻操作负担。有经验的 DBA 能够为某些管理或维护操作定义经测试验证的流程。然后，这些 DBA 可以授权其他经验尚浅的操作人员执行流程，而无需将集群的完整超级用户权限委托给他们。

最后，在 ETL/ELT 操作的管理方式选择上，一些客户更喜欢使用存储过程来替代 Shell 脚本或复杂的编排工具。确保 Shell 脚本正确检索和解释 ETL/ELT 流程中每项操作的状态可能很困难。依靠小型数据仓库团队进行编排工具的运营和维护同样极具挑战。

存储过程允许将 ETL/ELT 逻辑步骤完全封装在编写的主过程中，从而完全成功，或完全失败但不产生任何不良影响。您可以从简单调度程序（如 cron）中放心地调用存储过程。

创建存储过程

要在 Amazon Redshift 中创建存储过程，请使用以下语法：

CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name 
  ( [ [ argname ] [ argmode ] argtype [, ...] ] )
AS $$
  procedure_body
$$ LANGUAGE plpgsql 
[ { SECURITY INVOKER | SECURITY DEFINER } ]
[ SET configuration_parameter { TO value | = value } ]

设计存储过程时，请考虑封装的功能、输入和输出参数以及安全级别。举例来说，以下内容展示了如何使用动态 SQL 编写存储过程，用于检查主键违规情况以及给定架构、表和主键列的名称：

CREATE OR REPLACE PROCEDURE check_primary_key(schema_name varchar(128),
table_name varchar(128), col_name varchar(128)) LANGUAGE plpgsql
AS $$
DECLARE
  cnt_var integer := 0;
BEGIN
  SELECT INTO cnt_var count(*) from pg_table_def where schemaname = schema_name and
  tablename = table_name and "column" = col_name;
  IF cnt_var = 0 THEN
    RAISE EXCEPTION 'Input table or column does not exist.';
  END IF;

  DROP TABLE IF EXISTS duplicates;
  EXECUTE
    $_$ CREATE TEMP TABLE duplicates as
    SELECT $_$|| col_name ||$_$, count(*) as counter
    FROM $_$|| table_name ||$_$
    GROUP BY 1
    HAVING count(*) > 1
    ORDER BY counter desc $_$;
  SELECT INTO cnt_var COUNT(*) FROM duplicates;
  IF cnt_var = 0
    THEN RAISE INFO 'No duplicates found';
    DROP TABLE IF EXISTS duplicates;
  ELSE
    RAISE INFO 'Duplicates exist for % value(s) in column %', cnt, col_name;
    RAISE INFO 'Check tmp table "duplicates" for duplicated values';
  END IF;
END;
$$;

如需详细了解可在存储过程内使用的 SQL 查询和控制流逻辑的种类，请参阅在 Amazon Redshift 中创建存储过程。

调用存储过程

只能使用 CALL 命令调用存储过程。该命令会提取过程名称和输入参数值。CALL 命令不能是任何常规查询的一部分。例如，下面的内容展示了调用之前创建的存储过程的方法：

db=# call check_primary_key('public', 'bar', 'b');
INFO:  Duplicates exist for 1 value(s) in column b
INFO:  Check tmp table "duplicates" for duplicated values

Amazon Redshift 存储过程调用可通过输出参数或结果集返回结果。同时还支持嵌套和递归调用。有关详细信息，请参阅 CALL 命令。

如何使用安全定义者过程

现在您已了解如何创建和调用存储过程，接下来将向您介绍有关安全性的更多信息。创建存储过程时，您作为存储过程的拥有者（创建者），是唯一能调用或执行该过程的角色。您可以将 EXECUTE 权限授予其他用户或组，使他们能够执行该存储过程。获得 EXECUTE 权限并不意味着调用者自动可以访问存储过程中引用的所有数据库对象（表、视图等）。

以用户张三创建的过程 sp_insert_customers 为例。该过程包含 INSERT 语句，该语句写入到张三为拥有者的表客户。如果张三向用户李四授予 EXECUTE 权限，李四也无法对表客户执行 INSERT 操作，除非张三明确向李四授予客户的 INSERT 权限。

但是，有时可以允许李四调用存储过程，但不授予他客户的 INSERT 权限。为此，张三需要在创建该过程时将 SECURITY 属性设置为 DEFINER，然后授予李四 EXECUTE 权限。如此一来，当李四调用 sp_insert_customers 时，存储过程将以张三的权限执行该操作，并且无需该表的 INSERT 权限，李四就可以向客户中插入内容。

如果在创建存储过程时未指定安全属性，默认情况下该属性的值会设置为 INVOKER。这意味着存储过程会以过程调用者用户的权限执行。将安全属性显式设置为 DEFINER 后，存储过程将以过程拥有者的权限执行。

在 Amazon Redshift 中使用存储过程的最佳实践

以下是一些使用存储过程的最佳实践。

确保已在源代码控制工具中捕获了存储过程。

如果您计划将存储过程用作数据处理的关键元素，那么还需建立一套实践，将所有存储过程更改都提交给源代码控制系统。

您还可以考虑定义特定用户（即重要存储过程的拥有者），并实现存储过程创建和修改过程自动化。

您可以使用以下命令检索现有存储过程的源代码：

SHOW procedure_name;

考虑每个存储过程的安全范围和调用者

默认情况下，存储过程运行需要存储过程调用者用户的权限。使用 SECURITY DEFINER 属性可以让存储过程使用其他权限运行。例如，从重要的表中显式撤消对 DELETE 的访问权限，然后定义一个存储过程。该存储过程用于在检查安全列表后执行删除操作。

使用 SECURITY DEFINER 时，请注意以下事项：

请将用于执行存储过程的 EXECUTE 权限授予特定用户，而非授予 PUBLIC。这样可以确保存储过程不会被一般用户误用。
如果可能，请使用架构名称限制存储过程访问的所有数据库对象。例如，使用 myschema.mytable，而非 mytable。
请在使用 SET 选项创建存储过程时设置 search_path。这样可以防止其他架构中的同名对象受到重要存储过程的影响。

使用基于集合的逻辑，避免手动循环大型数据集

在存储过程中操作数据时，请尽可能继续使用基于集合的常规 SQL 命令，例如 INSERT、UPDATE 和 DELETE。

存储过程提供了新的控制结构，例如 FOR 和 WHILE 循环。这些控制结构在迭代少量项目（例如表列表）时很有用。但是，应避免使用循环结构替换基于集合的 SQL 操作。例如，通过迭代数百万个值以逐一更新这些值，这种做法低效且缓慢。

注意 REFCURSOR 限制，使用临时表处理较大的结果集

结果集从存储过程返回时，可以作为 REFCURSOR 返回或者使用临时表返回。 REFCURSOR 是内存中的数据结构，在许多情况下它是最简单的选择。

但是，每个存储过程最多只能有一个 REFCURSOR。有时您需要返回多个结果集，或者与多个子过程的结果进行交互，或者返回数百万（或更多）个结果行。在这些情况下，建议您将结果定向到临时表，并返回对临时表的引用作为存储过程的输出。

简化存储过程，对复杂流程进行嵌套处理

请尝试使每个存储过程的逻辑尽可能简单。通过简化，您可以最大限度地提高灵活性，并使存储过程更加易于理解。

当您优化和改进存储过程的代码时，代码可能会变得复杂化。当遇到冗长而复杂的存储过程时，通常可以简化操作，方法是将子元素移到从原始过程调用的单独过程。

使用 AWS Schema Conversion Tool 迁移存储过程

在 Amazon Redshift 宣布支持存储过程后，AWS 还改进了 AWS Schema Conversion Tool，此工具现可将旧数据仓库存储过程转换为 Amazon Redshift 存储过程。

AWS SCT 现已支持将 Microsoft SQL Server 数据仓库存储过程转换为 Amazon Redshift 存储过程。

版本 627 之后，AWS SCT 现在可以将 Microsoft SQL Server 数据仓库存储过程转换为 Amazon Redshift 存储过程。以下是 AWS SCT 中的操作步骤：

新建一个 OLAP 项目，用于将 SQL Server 数据仓库 (DW) 转换到 Amazon Redshift。
连接 SQL Server DW 和 Amazon Redshift 终端节点。
取消选中源树中的所有节点。
打开架构的上下文菜单（右键菜单）。
打开存储过程节点的上下文菜单（右键菜单），然后选择转换脚本（操作类似转换数据库对象）。
（可选）您也可以选择查看评估报告并应用转换。

以下是 SQL Server DW 存储过程转换的示例：

小结

Amazon Redshift 存储过程支持功能现已在所有 AWS 区域正式发布。我们希望您和我们一样对于能够在 Amazon Redshift 中运行存储过程感到兴奋。

借助 Amazon Redshift 和 AWS Schema Conversion Tool 对存储过程的支持，您现在可以将存储过程迁移到 Amazon Redshift，无需使用其他语言或框架进行编码。这一功能减少了迁移的工作量。我们希望更多本地客户可以利用 Amazon Redshift 并迁移到云中，以实现数据库自由。

关于作者

Joe Harris 是 AWS 的高级 Redshift 数据库工程师，专注于 Redshift 性能问题研究他从事各种平台数据分析和数据仓库构建工作已有二十年之久。在加入 AWS 之前，自 2013 年 Redshift 发布时，他就一直是 Redshift 客户，同时也是 Redshift 论坛中的杰出贡献者。

Abhinav Singh 是 AWS 的一名数据库工程师。他负责数据库迁移项目的设计和开发工作，并就数据库迁移项目为客户提供指导和技术帮助，助力客户使用 AWS 提高解决方案的价值。

Entong Shen 在 Amazon Redshift 查询处理团队担任高级软件工程师。他从事 MPP 数据库相关工作已 7 年有余，专注于查询优化、统计信息和 SQL 语言功能研究。业余时间，他喜欢听着各种流派的音乐在花园中当一名快乐的园丁。

Vinay 是 Amazon Web Services 的 Amazon Redshift 首席产品经理。此前，他曾在 Teradata 担任过产品部门高级总监，在 Hortonworks 担任过产品总监。在 Hortonworks，他推出了 Data Science、Spark、Zeppelin 和 Security 领域的产品。工作之余，Vinay 喜欢做瑜伽或外出远足。

Sushim Mitra 是 Amazon Redshift 查询处理团队的一名软件开发工程师。他主要负责处理查询优化问题、SQL 语言功能和数据库安全性。工作之余，他喜欢阅读世界各地的小说。

亚马逊AWS官方博客