亚马逊 Redshift 中的存储过程增强功能

Amazon Redshift 是一项完全托管的 PB 级云端数据仓库服务。借助 Amazon Redshift，您可以分析所有数据，以获得有关您的业务和客户的全面见解。它支持存储过程，在这些存储过程中，可以保存准备好的 SQL 代码，并且可以一遍又一遍地重复使用代码。

存储过程通常用于封装用于数据转换、数据验证和业务特定逻辑的逻辑。通过将多个 SQL 步骤合并到一个存储过程中，您可以创建可重复使用的代码块，这些代码块可以作为单个事务一起运行，也可以作为多个单独的事务一起运行。您还可以在 Amazon Redshift 上安排存储过程以自动处理数据。有关更多信息，请参阅 将您的存储过程引入 Amazon Redshift 。

在 Redshift 存储过程的默认原子事务模式下，对 Redshift 存储过程的调用将在调用开始时创建自己的事务，或者如果在调用存储过程之前打开显式事务，则调用该事务是现有事务的一部分。过程中的所有语句都表现得好像它们位于单个事务块中，该事务块在存储过程调用完成时结束。对另一个过程的嵌套调用被视为与任何其他 SQL 语句一样，并在与调用者相同的事务的上下文中运行。TRUNCATE、COMMIT 和 ROLLBACK 语句以及包含任意 SQL 语句的异常处理块会关闭当前事务并隐式启动新事务。这种行为可能会给从Teradata等其他系统迁移到Amazon Redshift带来挑战。

在这篇文章中，我们讨论了对非原子事务模式的 Amazon Redshift 存储过程的改进。此模式提供增强的事务控制，使您能够在存储过程中自动提交语句。

非原子交易模式

新的非原子事务模式功能对 Amazon Redshift 中的存储过程进行了三项增强：

  • 除非 DML 或 DDL 语句是显式开放事务的一部分，否则存储过程中的每条语句都将在自己的隐式事务中运行，并且将打开一个新事务来处理以下语句。如果打开了显式事务，则所有后续语句都将运行并保持未提交状态，直到运行显式事务控制命令（COMMIT 或 ROLLBACK）以结束事务。
  • 异常处理语句完成后，Amazon Redshift 不会重新引发异常。因此，提供了一个不带任何信息或异常的新 RAISE 语句，用于重新抛出异常处理块捕获的异常。这个没有任何信息或异常的 RAISE 语句只能在异常处理块中使用。
  • 此外，新的 START TRANSACTION 语句在非原子事务模式存储过程中开始显式事务。使用现有的事务控制命令（COMMIT 或 ROLLBACK）结束显式启动的事务。
    • Amazon Redshift 不支持子交易，因此，如果已经有一笔未结交易，则再次调用此语句将无济于事，也不会引发错误。
    • 如果在非原子事务模式存储过程调用结束时显式事务仍处于打开状态，则在会话中运行事务控制命令之前，该显式事务将保持打开状态。
    • 如果会话在运行事务控制命令之前断开连接，则整个事务会自动回滚。

其他限制

还对 Redshift 存储过程引入了一些限制：

  • 要嵌套存储过程调用，所有过程都必须在相同的事务模式下创建，无论它是处于原子（默认）事务模式还是新的非原子事务模式
  • 你不能在两种事务模式（原子和非原子）之间嵌套存储过程
  • 你无法为非原子事务模式存储过程设置 SECURITY DEFINER 选项或设置配置参数 选项

对游标的影响

与默认原子事务模式相比，非原子事务模式存储过程中的游标的行为会有所不同：

  • 在启动游标之前，游标语句需要一个显式的事务块，以确保游标循环的每次迭代都不会自动提交。
  • 要从非原子事务模式存储过程返回游标，在启动游标之前需要一个显式事务块。否则，当循环内的 SQL 语句自动提交时，游标将被关闭。

优点

从用户的角度来看，以下是此功能的主要优势：

  • 它提供了提升和移动 Teradata 存储过程以在 Teradata 会话模式下运行的功能。这有助于从 Teradata 和 SQL Server 等数据仓库进行无缝迁移。
  • 它使 Amazon Redshift 能够在遇到错误和异常时在存储过程中提供更灵活的操作。Amazon Redshift 现在可以在出现异常之前保留先前操作的状态。

语法

新的可选关键字 NONATOMIC 已添加到存储过程定义语法中，如以下代码所示： 

CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name
( [ [ argname ] [ argmode ] argtype [, ...] ] )
[ NONATOMIC ]
AS $$
procedure_body
$$ LANGUAGE plpgsql

这个可选关键字在非原子事务模式下创建存储过程。如果您未指定关键字，则创建存储过程时的默认原子模式将是事务模式。

NONATOMIC 表示过程中的每个 DML 和 DDL 语句都将被隐式提交。

如果没有非原子模式，则该过程将在调用开始时创建自己的事务，或者如果在调用之前打开了显式事务，则该过程将成为现有事务的一部分。存储过程中的每条语句都将属于这一笔事务。

非原子模式示例

让我们考虑一下客户联系表 c ustcontac ts ，它存储了客户的主要和次要联系人电话号码： 

CREATE table custcontacts(
custid int4 not null,
primaryphone char(10),
secondaryphone char(10));

我们插入了三条没有联系值的样本客户记录： 

INSERT INTO custcontacts VALUES (101, 'xxxxxxxxxx', 'xxxxxxxxxx');
INSERT INTO custcontacts VALUES (102, 'xxxxxxxxxx', 'xxxxxxxxxx');
INSERT INTO custcontacts VALUES (103, 'xxxxxxxxxx', 'xxxxxxxxxx');

您需要创建一个存储过程来更新主要和辅助电话号码。要求是，如果由于某种原因对辅助联系号码的更新失败，则不要回滚对主要联系号码的更新。

你可以通过使用 NONATOMIC 关键字创建存储过程来实现这一点。NONATOMIC 关键字确保存储过程中的每条语句都在自己的隐式事务块中运行。因此，如果备用电话的 UPDATE 语句失败，则它不会回滚对主电话进行的数据更新。参见以下代码： 

CREATE PROCEDURE sp_update_custcontacts(cid int4,pphone char(15),sphone char(15)) NONATOMIC AS
$$
BEGIN
UPDATE custcontacts SET primaryphone=pphone WHERE custid=cid;
UPDATE custcontacts SET secondaryphone=sphone WHERE custid=cid;
END;
$$
LANGUAGE plpgsql;

现在，让我们调用传递超过 10 位数的辅助电话号码的存储过程，由于长度不正确，这将在 辅助电话 更新语句中失败： 

call sp_update_custcontacts(101,'1234567890','345443345324');

前面的过程调用将成功更新主电话号码。辅助电话号码更新失败。但是，由于存储过程定义中有 N ON ATOMIC 子句，primary phone 更新不会回滚，因为它在自己的隐式事务块中运行。 

select * from custcontacts;

custcontacts | primaryphone | secondaryphone
-------------+---------------+---------------
101 | 1234567890 | XXXXXXXXXX
102 | XXXXXXXXXX | XXXXXXXXXX
103 | XXXXXXXXXX | XXXXXXXXXX

非原子模式下的异常处理

根据原子或非原子模式，存储过程中的异常处理方式有所不同：

  • 原子（默认） — 总是会重新引发异常
  • 非原子性 — 异常已得到处理，你可以选择是否重新提高

让我们继续前面的例子来说明非原子模式下的异常处理。

创建下表来记录存储过程引发的异常： 

CREATE TABLE procedure_log
(log_timestamp timestamp, procedure_name varchar(100), error_message varchar(255));

现在更新 sp_u pdate_custcontacts () 程序来处理异常 。 请注意，我们将在过程定义中添加一个 EXCEPTION 块。如果出现异常，它会在 pro cedure_log 表中插入一条记录。 

CREATE OR REPLACE PROCEDURE sp_update_custcontacts(cid int4,pphone char(15),sphone char(15)) NONATOMIC AS
$$
BEGIN
UPDATE custcontacts SET primaryphone=pphone WHERE custid=cid;
UPDATE custcontacts SET secondaryphone=sphone WHERE custid=cid;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO procedure_log VALUES (getdate(), 'sp_update_custcontacts', sqlerrm);
END;
$$
LANGUAGE plpgsql;

现在再创建一个存储过程，它将调用前面的过程。它还有一个 EXCEPTION 块，并在出现异常时在 p rocedure_log 表中插入一条记录。 

CREATE PROCEDURE sp_update_customer() NONATOMIC AS
$$
BEGIN
-- Let us assume you have additional staments here to update other fields. For this example, ommitted them for simplifiction.
-- Nested call to update contacts
call sp_update_custcontacts(101,'1234567890','345443345324');
EXCEPTION
WHEN OTHERS THEN
INSERT INTO procedure_log VALUES (getdate(), 'sp_update_customer', sqlerrm);
END;
$$
LANGUAGE plpgsql;

让我们调用我们创建的父过程： 

call sp_update_customer();

这反过来会调用 sp_u pdate_cu stcontacts () 程序。 内部程序 sp_update_custcontacts () 将失败，因为我们正在使用无效值更新辅助电话。 该控件将进入 sp_update_custcontacts () 过程的异常区块，并在 procedure_ log 表中进行插入。

但是，它不会在非原子模式下重新引发异常。 因此，父进程 sp_update_customer () 不会获得 sp_update_c ustcontacts () 过程传递的 异常。 该控件不会进入 sp_update_cu stomer () 过程的异常块。

如果您查询 procesu re_log 表，则只会看到一个条目，说明由 sp_u pdate_ custcontacts () 过程 处理的错误： 

select * from procedure_log;

Procedure Log Output

现在使用 RAISE 语句重新定义 sp_update_custcontacts () 程序 ： 

CREATE PROCEDURE sp_update_custcontacts(cid int4,pphone char(15),sphone char(15)) NONATOMIC AS
$$
BEGIN
UPDATE custcontacts SET primaryphone=pphone WHERE custid=cid;
UPDATE custcontacts SET secondaryphone=sphone WHERE custid=cid;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO procedure_log VALUES (getdate(), 'sp_update_custcontacts', sqlerrm);
RAISE;
END;
$$
LANGUAGE plpgsql;

让我们再次调用父存储过程 sp_update_customer ()： 

call sp_update_customer();

现在，内部进程 sp_update_custcontacts () 将在其自己的异常块中处理异常后，将向父进程 sp_update_custom er () 重新引发异常。 然后，控件将到达父过程中的 EXCEPTION 块，并将另一条记录插入到 procedu re_log 表中。

如果你现在查询 procesu re_log 表，你会看到两个条目：一个是内部过程 sp_update_custcontacts () 的条目，另一个来自父过程 sp_update_customer () 。 这表明内部过程中的 RAISE 语句重新引发了异常。 

select * from procedure_log;

Procedure log output

非原子模式下的显式 START 事务语句

您可以发出 START TRANSACTION 语句来启动存储过程中的事务块。它将在存储过程中打开一个新事务。有关示例，请参阅 非原子模式存储过程事务管理 。

结论

在这篇文章中，我们讨论了对非原子事务模式的 Redshift 存储过程的增强，它提供了增强的事务控制，使您能够在存储过程中自动提交语句。这种模式还可以更轻松地从其他系统（例如Teradata）迁移到Amazon Redshift。试试这些增强功能，并在评论中告诉我们你的体验。

作者简介

Milind Oke 是一名来自纽约的数据仓库专家解决方案架构师。他构建数据仓库解决方案已有 15 年以上，专门研究亚马逊 Redshift。

Satesh Sonti 是一位来自亚特兰大的高级分析专家解决方案架构师，专门构建企业数据平台、数据仓库和分析解决方案。他在为全球银行和保险客户构建数据资产和领导复杂数据平台项目方面拥有超过17年的经验。

基兰·钦塔 是亚马逊 Redshift 的软件开发经理。他在查询处理、SQL 语言、数据安全和性能方面领导着一支强大的团队。Kiran 热衷于提供与客户业务应用程序无缝集成、具有适当易用性和性能的产品。在业余时间，他喜欢阅读和打网球。

刘惠臣 是亚马逊 Redshift 查询处理团队的软件开发工程师。她专注于查询优化、统计和 SQL 语言功能。在业余时间，她喜欢徒步旅行和摄影。

*前述特定亚马逊云科技生成式人工智能相关的服务仅在亚马逊云科技海外区域可用，亚马逊云科技中国仅为帮助您发展海外业务和/或了解行业前沿技术选择推荐该服务。