如何利用存储过程将数据库迁移上云？

发布于: Oct 30, 2022

数据库迁移上云这项技术已经十分成熟，然而不少客户又提出了新的要求。Amazon 始终以满足客户需求为工作重点。客户强烈要求希望能在 Amazon Redshift 中使用存储过程，以便更轻松地从原有的本地数据仓库迁移现有工作负载。
为实现这一主要目标，Amazon Web Services 选择实施了 PL/pqSQL 存储过程以最大程度地兼容现有的程序并简化迁移。在本博文中，我们将讨论如何以及在什么情况下可以使用存储过程提高操作效率和安全性。此外，还会说明如何通过 Amazon 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 plpgsqlAS $$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 命令。

相关文章

跨区域全局数据库---Amazon Aurora Global Database

返回上一级云数据库文章 >>