Big Query 中的 SQLSTATE 和 SQLCODE 等效 [英] SQLSTATE and SQLCODE equivalent in Big Query

问题描述

我正在阅读 Teradata 的存储过程文档中的 SQLSTATE 和 SQLCODE.我正在尝试将 Teradata 的存储过程迁移到云.大查询中的这些等价物是什么?

I was going through SQLSTATE and SQLCODE in Teradata's Stored Prcedures documentation. I am trying to migrate a Teradata's stored procedure to cloud. What is equivalent for these in Big query?

推荐答案

首先，不再建议使用 SQLCODE.引用 Teradata 文档:

First of all, the use of SQLCODE is no longer advised. Quoting the Teradata documentation:

SQLCODE 不符合 ANSI/ISO SQL:2011.SQLCODE 在 ANSI/ISO SQL-92 标准中已被弃用，并且未在 SQL:2011 标准中定义.ANSI/ISO SQL 委员会建议使用 SQLSTATE 编写新应用程序(请参阅 SQLSTATE(第 91 页))代替 SQLCODE.

ANSI Compliance

SQLCODE is not ANSI/ISO SQL:2011-compliant. SQLCODE was deprecated in the ANSI/ISO SQL-92 standard and is not defined in the SQL:2011 standard. The ANSI/ISO SQL committee recommends that new applications be written using SQLSTATE (see SQLSTATE on page 91) in place of SQLCODE.

有关将 SQLSTATE 映射到 BigQuery，请参阅Google 的迁移指南:

For mapping SQLSTATE to BigQuery, see Google's migration guide:

Teradata 在错误控制过程中对状态代码使用处理程序.在 BigQuery 中，错误处理是主要控制流的核心功能，类似于其他语言通过 TRY ... CATCH 块提供的功能.

Error condition handlers

Teradata uses handlers on status codes in procedures for error control. In BigQuery, error handling is a core feature of the main control flow, similar to what other languages provide with TRY ... CATCH blocks.

Teradata                  | BigQuery
--------------------------+------------------------------------
DECLARE EXIT HANDLER FOR  | BEGIN ... EXCEPTION WHEN ERROR THEN 
SQLEXCEPTION              | 
                          |
SIGNAL sqlstate           | RAISE message
                          |
DECLARE CONTINUE HANDLER  | Exception handlers that trigger for certain 
FOR SQLSTATE VALUE 23505; | error conditions are not used by BigQuery

Teradata 中的 SQLSTATE 变量类似于 @@error 系统变量.在 BigQuery 中，更常见的是使用审计日志或INFORMATION_SCHEMA 视图.

The SQLSTATE variable in Teradata is similar to the @@error system variable in BigQuery. In BigQuery, it is more common to investigate errors using audit logging or the INFORMATION_SCHEMA views.

还有也:

Teradata 错误代码和 BigQuery 错误代码不同.BigQuery 提供 REST API，主要依赖于 HTTP 状态代码和详细的错误消息.

Teradata error codes and BigQuery error codes are different. Providing a REST API, BigQuery relies primarily on HTTP status codes plus detailed error messages.

如果您的应用逻辑当前正在捕获以下错误，请尝试消除错误源，因为 BigQuery 不会返回相同的错误代码.

If your application logic is currently catching the following errors, try to eliminate the source of the error, because BigQuery will not return the same error codes.

• SQLSTATE = '02000'—未找到行"
• SQLSTATE = '21000'—基数违规(唯一索引)"
• SQLSTATE = '22000'—数据违规(数据类型)"
• SQLSTATE = '23000'—违反约束"

• SQLSTATE = '02000'—"Row not found"
• SQLSTATE = '21000'—"Cardinality violation (Unique Index)"
• SQLSTATE = '22000'—"Data violation (Data Type)"
• SQLSTATE = '23000'—"Constraint Violation"

很明显，SQLSTATE 与 BigQuery 中的任何内容都没有一一对应的关系，因此您需要根据上述信息重写一些代码.

It is clear there is no one-to-one correspondence of SQLSTATE with anything in BigQuery, so you'll need to rewrite some of your code based on the above information.

这篇关于Big Query 中的 SQLSTATE 和 SQLCODE 等效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！