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 中创建存储过程，请使用以下语法：

SQL

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 编写存储过程，用于检查主键违规情况以及给定架构、表和主键列的名称：

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 后，存储过程将以过程拥有者的权限执行。

本文转载自AWS技术博客。

原文链接：https://amazonaws-china.com/cn/blogs/china/big-data-bringing-your-stored-procedures-to-amazon-redshift/

创作场景

将存储过程迁移到 Amazon Redshift（一）