CREATE EXTERNAL FUNCTION - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

CREATE EXTERNAL FUNCTION

根據 Amazon Redshift 建立純量使用者定義函數 (UDF)。 AWS Lambda 如需 Lambda 使用者定義函數的相關資訊,請參閱 建立純量 Lambda UDF

所需權限

以下是 CREATE EXTERNAL FUNCTION 所需的權限:

  • 超級使用者

  • 具有 CREATE [ OR REPLACE ] EXTERNAL FUNCTION 權限的使用者

語法

CREATE [ OR REPLACE ] EXTERNAL FUNCTION external_fn_name ( [data_type] [, ...] ) RETURNS data_type { VOLATILE | STABLE } LAMBDA 'lambda_fn_name' IAM_ROLE { default | ‘arn:aws:iam::<AWS 帳戶-id>:role/<role-name>’ RETRY_TIMEOUT milliseconds MAX_BATCH_ROWS count MAX_BATCH_SIZE size [ KB | MB ];

以下是 Amazon Redshift 上的機器學習語法。如需模型專屬參數的資訊,請參閱 參數

CREATE [ OR REPLACE ] EXTERNAL FUNCTION external_fn_name ( [data_type] [, ...] ) RETURNS data_type { VOLATILE | STABLE } SAGEMAKER'endpoint_name' IAM_ROLE { default | ‘arn:aws:iam::<AWS 帳戶-id>:role/<role-name>’ };

參數

OR REPLACE

該子句會指定已有相同名稱和輸入引數資料類型 (或簽章) 的函數存在時,取代現有函數。您可以將函數取代為定義一組相同資料類型的新函數。您必須是超級使用者才能取代函數。

如果您定義的函數與現有函數同名,但簽章不同,則會建立新函數。換言之,函數名稱將會過載。如需詳細資訊,請參閱 多載函數名稱

external_fn_name

外部函數的名稱。如果您指定結構描述名稱 (例如 myschema.myfunction),則會使用指定的結構描述建立函數。否則,函數會在目前結構描述中建立。如需有效名稱的相關資訊,請參閱 名稱與識別碼

我們建議您在所有 UDF 名稱前加上 f_。Amazon Redshift 會保留 UDF 名稱的 f_ 字首。透過使用 f_ 字首,您可以協助確保您的 UDF 名稱不會與目前或未來 Amazon Redshift 的任何內建 SQL 函數名稱衝突。如需詳細資訊,請參閱 命名 UDF

data_type

輸入引數的資料類型。如需詳細資訊,請參閱 資料類型

RETURNS data_type

函數所傳回值的資料類型。RETURNS 資料類型可以是任何標準 Amazon Redshift 資料類型。如需詳細資訊,請參閱 Python UDF 資料類型

VOLATILE | STABLE

通知查詢最佳化工具有關函數的波動情形。

若要得到最理想的最佳化,請將函數標示為最嚴格的有效波動類別。從最低嚴格程度開始,依嚴格程度排列的波動類別如下所示:

  • VOLATILE

  • STABLE

VOLATILE

假設引數相同,即使是針對單一陳述式中的資料列,函數也可能在後續呼叫中傳回不同的結果。查詢最佳化工具無法假設波動函數的行為。使用波動函數的查詢必須重新評估每個輸入的函數。

STABLE

假設引數相同,函數一定會針對單一陳述式內處理的連續呼叫傳回相同結果。在不同陳述式中呼叫函數時,函數可能傳回不同結果。此類別可這麼做,所以最佳化工具可以減少在單個陳述式中調用函數的次數。

請注意,如果選擇的嚴格性對函數無效,則最佳化工具可能會有根據此嚴格性而略過某些呼叫的風險。這可能會導致不正確的結果集。

Lambda UDF 目前不支援 IMMUTABLE 子句。

LAMBDA 'lambda_fn_name'

Amazon Redshift 呼叫的函數名稱。

如需建立 AWS Lambda 函數的步驟,請參閱AWS Lambda 開發人員指南中的使用主控台建立 Lambda 函數

如需 Lambda 函數所需權限的相關資訊,請參閱《AWS Lambda 開發人員指南》中的 AWS Lambda 權限

IAM_ROLE { default | ‘arn:aws:iam::<AWS 帳戶-id>:role/<role-name>

使用預設關鍵字,讓 Amazon Redshift 使用設定為預設並在執行 CREATE EXTERNAL FUNCTION 命令時與叢集關聯的 IAM 角色。

對叢集進行身分驗證和授權時所使用的 IAM 角色使用 Amazon Resource Name (ARN)。CREATE EXTERNAL FUNCTION 已獲得授權,可透過此 IAM 角色調用 Lambda 函數。如果您的叢集已附加有權調用 Lambda 函數的現有 IAM 角色,則可以替換角色的 ARN。如需詳細資訊,請參閱 設定 Lambda UDF 的授權參數

以下顯示 IAM_ROLE 參數的語法。

IAM_ROLE 'arn:aws:iam::aws-account-id:role/role-name'
RETRY_TIMEOUT milliseconds

Amazon Redshift 用於重試退避延遲的總時間 (以毫秒為單位)。

Amazon Redshift 不會針對任何失敗的查詢立即重試,而是執行退選,並在重試之間等待一段時間。然後,Amazon Redshift 會重試請求以重新執行失敗的查詢,直到所有延遲的總和等於或超過您指定的 RETRY_TIMEOUT 值為止。預設值為 20,000 毫秒。

調用 Lambda 函數時,Amazon Redshift 會針對接收到錯誤 (例如 TooManyRequestsExceptionEC2ThrottledExceptionServiceException) 的查詢重試。

您可以將 RETRY_TIMEOUT 參數設定為 0 毫秒,以防止 Lambda UDF 進行任何重試。

MAX_BATCH_ROWS 計數

Amazon Redshift 在單一批次請求中針對單一 Lambda 調用傳送的資料列數目上限。

此參數的最小值為 1。最大值為 INT_MAX 或 2,147,483,647。

此為選用參數。預設值為 INT_MAX 或 2,147,483,647。

MAX_BATCH_SIZE size [ KB | MB ]

Amazon Redshift 在單一批次請求中針對單一 Lambda 調用傳送的資料承載大小上限。

此參數的最小值為 1 KB。最大值為 5 MB。

此參數的預設值為 5 MB。

您可以選擇 KB 和 MB。如果未設定測量單位,Amazon Redshift 會預設為使用 KB。

使用須知

建立 Lambda UDF 時應考慮下列事項:

  • 輸入引數上的 Lambda 函數調用順序不是固定或可保證的。其可能會因執行查詢的執行個體而有所不同,具體取決於叢集組態。

  • 函數不能保證一次且僅一次地套用到每個輸入引數。Amazon Redshift 之間的交互 AWS Lambda 可能會導致具有相同輸入的重複呼叫。

範例

以下是使用純量 Lambda 使用者定義函數 (UDF) 的範例。

使用 Node.js Lambda 函數的純量 Lambda UDF 範例

下列範例會建立名為 exfunc_sum 的外部函數,該函數會接受兩個整數做為輸入引數。該函數會傳回作為整數輸出的總和。要呼叫的 Lambda 函數名稱為 lambda_sum。用於此 Lambda 函數的語言是 Node.js 12.x。請務必指定 IAM 角色。此範例使用 'arn:aws:iam::123456789012:user/johndoe' 做為 IAM 角色。

CREATE EXTERNAL FUNCTION exfunc_sum(INT,INT) RETURNS INT VOLATILE LAMBDA 'lambda_sum' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-Exfunc-Test';

Lambda 函數會接受請求承載並逐一查看每一列。單列中的所有值都會加入以計算該列的總和,而這會保存在回應陣列中。結果陣列中的資料列數會與請求承載中接收的資料列數相似。

JSON 回應承載必須在「結果」欄位中包含結果資料,才能由外部函數辨識。在傳送至 Lambda 函數的請求中,引數欄位會包含資料承載。在批次處理請求的情況下,資料承載中可以有多個資料列。下列 Lambda 函數會逐一查看請求資料承載中的所有資料列。也會單獨逐一查看單一資料列中的所有值。

exports.handler = async (event) => { // The 'arguments' field in the request sent to the Lambda function contains the data payload. var t1 = event['arguments']; // 'len(t1)' represents the number of rows in the request payload. // The number of results in the response payload should be the same as the number of rows received. const resp = new Array(t1.length); // Iterating over all the rows in the request payload. for (const [i, x] of t1.entries()) { var sum = 0; // Iterating over all the values in a single row. for (const y of x) { sum = sum + y; } resp[i] = sum; } // The 'results' field should contain the results of the lambda call. const response = { results: resp }; return JSON.stringify(response); };

下列範例會呼叫具有常值的外部函數。

select exfunc_sum(1,2); exfunc_sum ------------ 3 (1 row)

下列範例會建立名為 t_sum 的資料表,其中包含整數資料類型的兩個資料欄 c1 和 c2,並插入兩列資料。然後透過傳遞此資料表的資料欄名稱來呼叫外部函數。這兩個資料表資料列會在請求承載中以批次請求的形式傳送,以做為單一 Lambda 調用。

CREATE TABLE t_sum(c1 int, c2 int); INSERT INTO t_sum VALUES (4,5), (6,7); SELECT exfunc_sum(c1,c2) FROM t_sum; exfunc_sum --------------- 9 13 (2 rows)

使用 RETRY_TIMEOUT 屬性的純量 Lambda UDF 範例

在下文中,您可以找到如何在 Lambda UDF 中使用 RETRY_TIMEOUT 屬性的範例。

AWS Lambda 函數具有您可以為每個函數設置的並發限制。如需並行限制的詳細資訊,請參閱AWS Lambda 開發人員指南的管理 Lambda 函數的並行性,以及運算部落格上的管理 AWS Lambda 函數並行文章。 AWS

當 Lambda UDF 處理的要請數目超過並行限制時,新要求會收到 TooManyRequestsException 錯誤。Lambda UDF 會針對此錯誤重試,直到傳送至 Lambda 函數的請求之間的所有延遲總和等於或超過您設定的 RETRY_TIMEOUT 值為止。預設的 RETRY_TIMEOUT 值為 20,000 毫秒。

下列範例會使用名為 exfunc_sleep_3 的 Lambda 函數。該函數會接受請求承載、逐一查看每一個資料列,並將輸入轉換為大寫。然後休眠 3 秒鐘後傳回結果。用於此 Lambda 函數的語言是 Python 3.8。

結果陣列中的資料列數會與請求承載中接收的資料列數相似。JSON 回應承載必須在 results 欄位中包含結果資料,才能由外部函數辨識。在傳送至 Lambda 函數的請求中,arguments 欄位會包含資料承載。在批次處理請求的情況下,資料承載中可以有多個資料列。

此函數的並行限制會在保留並行中特別設定為 1,以示範如何使用 RETRY_TIMEOUT 屬性。當屬性設定為 1 時,Lambda 函數一次只能處理一個請求。

import json import time def lambda_handler(event, context): t1 = event['arguments'] # 'len(t1)' represents the number of rows in the request payload. # The number of results in the response payload should be the same as the number of rows received. resp = [None]*len(t1) # Iterating over all rows in the request payload. for i, x in enumerate(t1): # Iterating over all the values in a single row. for j, y in enumerate(x): resp[i] = y.upper() time.sleep(3) ret = dict() ret['results'] = resp ret_json = json.dumps(ret) return ret_json

接下來,另外兩個範例會說明 RETRY_TIMEOUT 屬性。他們每個人都會調用單一 Lambda UDF。呼叫 Lambda UDF 時,每個範例都會執行相同的 SQL 查詢,以同時從兩個並行的資料庫工作階段調用 Lambda UDF。當 UDF 處理第一個調用 Lambda UDF 的查詢時,第二個查詢會收到 TooManyRequestsException 錯誤。之所以發生此結果,是因為您在 UDF 中特別將保留並行設定為 1。如需如何為 Lambda 函數設定保留並行的相關資訊,請參閱設定保留並行

接下來的第一個範例會將 Lambda UDF 的 RETRY_TIMEOUT 屬性設定為 0 毫秒。如果 Lambda 請求從 Lambda 函數收到任何例外狀況,Amazon Redshift 不會進行任何重試。之所以發生這個結果,是因為 RETRY_TIMEOUT 屬性設定為 0。

CREATE OR REPLACE EXTERNAL FUNCTION exfunc_upper(varchar) RETURNS varchar VOLATILE LAMBDA 'exfunc_sleep_3' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-Exfunc-Test' RETRY_TIMEOUT 0;

將 RETRY_TIMEOUT 設定為 0 時,您可以從不同的資料庫工作階段執行下列兩個查詢,以查看不同的結果。

第一個使用 Lambda UDF 的 SQL 查詢成功執行。

select exfunc_upper('Varchar'); exfunc_upper -------------- VARCHAR (1 row)

同時從不同資料庫工作階段執行的第二個查詢會收到 TooManyRequestsException 錯誤。

select exfunc_upper('Varchar'); ERROR: Rate Exceeded.; Exception: TooManyRequestsException; ShouldRetry: 1 DETAIL: ----------------------------------------------- error: Rate Exceeded.; Exception: TooManyRequestsException; ShouldRetry: 1 code: 32103 context:query: 0 location: exfunc_client.cpp:102 process: padbmaster [pid=26384] -----------------------------------------------

接下來的第二個範例會將 Lambda UDF 的 RETRY_TIMEOUT 屬性設定為 3,000 毫秒。即使第二個查詢同時執行,Lambda UDF 也會重試,直到總延遲為 3,000 毫秒為止。因此,兩個查詢都會成功執行。

CREATE OR REPLACE EXTERNAL FUNCTION exfunc_upper(varchar) RETURNS varchar VOLATILE LAMBDA 'exfunc_sleep_3' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-Exfunc-Test' RETRY_TIMEOUT 3000;

將 RETRY_TIMEOUT 設定為 3,000 毫秒時,您可以從不同的資料庫工作階段執行下列兩個查詢,以查看相同的結果。

第一個執行 Lambda UDF 的 SQL 查詢成功執行。

select exfunc_upper('Varchar'); exfunc_upper -------------- VARCHAR (1 row)

第二個查詢會同時執行,Lambda UDF 也會重試,直到總延遲為 3,000 毫秒為止。

select exfunc_upper('Varchar'); exfunc_upper -------------- VARCHAR (1 row)

使用 Python Lambda 函數的純量 Lambda UDF 範例

下列範例會建立名為 exfunc_multiplication 且會乘以數字並傳回整數的外部函數。此範例包含 Lambda 回應中的成功和 error_msg 欄位。當乘法結果中有整數溢位,且 error_msg 訊息設定為 Integer multiplication overflow 時,成功欄位會設定為 false。exfunc_multiplication 函數採用三個整數作為輸入參數,並傳回總和作為整數輸出。

要呼叫的 Lambda 函數名稱為 lambda_multiplication。用於此 Lambda 函數的語言是 Python 3.8。請務必指定 IAM 角色。

CREATE EXTERNAL FUNCTION exfunc_multiplication(int, int, int) RETURNS INT VOLATILE LAMBDA 'lambda_multiplication' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-Exfunc-Test';

Lambda 函數會接受請求承載並逐一查看每一列。單列中的所有值都會相乘以計算該列的結果,而這會保存在回應清單中。此範例使用預設為 true 的布林成功值。如果資料列的乘法結果有整數溢位,則成功值會設定為 false。然後迭代循環會中斷。

建立回應承載時,如果成功值為 false,則下列 Lambda 函數會在承載中新增 error_msg 欄位。也會將錯誤訊息設定為 Integer multiplication overflow。如果成功值為 true,則結果資料會新增到結果欄位中。結果陣列中的資料列數 (如果有的話) 會與請求承載中接收的資料列數相似。

在傳送至 Lambda 函數的請求中,引數欄位會包含資料承載。在批次處理請求的情況下,資料承載中可以有多個資料列。下列 Lambda 函數會逐一查看請求資料承載中的所有資料列,並個別逐一查看單一資料列中的所有值。

import json def lambda_handler(event, context): t1 = event['arguments'] # 'len(t1)' represents the number of rows in the request payload. # The number of results in the response payload should be the same as the number of rows received. resp = [None]*len(t1) # By default success is set to 'True'. success = True # Iterating over all rows in the request payload. for i, x in enumerate(t1): mul = 1 # Iterating over all the values in a single row. for j, y in enumerate(x): mul = mul*y # Check integer overflow. if (mul >= 9223372036854775807 or mul <= -9223372036854775808): success = False break else: resp[i] = mul ret = dict() ret['success'] = success if not success: ret['error_msg'] = "Integer multiplication overflow" else: ret['results'] = resp ret_json = json.dumps(ret) return ret_json

下列範例會呼叫具有常值的外部函數。

SELECT exfunc_multiplication(8, 9, 2); exfunc_multiplication --------------------------- 144 (1 row)

下列範例會建立名為 t_multi 的資料表,其中包含整數資料類型的三個資料欄 c1、c2 和 c3。外部函數會透過傳遞此資料表的資料欄名稱來呼叫。資料會以這種方式插入,進而導致整數溢位,以顯示錯誤的傳播方式。

CREATE TABLE t_multi (c1 int, c2 int, c3 int); INSERT INTO t_multi VALUES (2147483647, 2147483647, 4); SELECT exfunc_multiplication(c1, c2, c3) FROM t_multi; DETAIL: ----------------------------------------------- error: Integer multiplication overflow code: 32004context: context: query: 38 location: exfunc_data.cpp:276 process: query2_16_38 [pid=30494] -----------------------------------------------