CREATE EXTERNAL FUNCTION - Amazon Redshift

CREATE EXTERNAL FUNCTION

Amazon Redshift 用の AWS Lambda に基づいてスカラーユーザー定義関数 (UDF) を作成します。Lambda のユーザー定義関数の詳細については、スカラー Lambda UDF の作成 を参照してください。

Syntax

CREATE [ OR REPLACE ] EXTERNAL FUNCTION external_fn_name ( [data_type] [, ...] ) RETURNS data_type { VOLATILE | STABLE | IMMUTABLE } LAMBDA 'lambda_fn_name' IAM_ROLE 'iam-role-arn' RETRY_TIMEOUT milliseconds;

次は、Amazon Redshift での機械学習向けの構文です。モデル固有のパラメータの詳細については、「Parameters」を参照してください。

CREATE [ OR REPLACE ] EXTERNAL FUNCTION external_fn_name ( [data_type] [, ...] ) RETURNS data_type { VOLATILE | STABLE | IMMUTABLE } SAGEMAKER'endpoint_name' IAM_ROLE 'iam-role-arn';

Parameters

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 | IMMUTABLE

関数の変動率についてのクエリオプティマイザを報告する句。

関数の最適な変動率の分類を厳正に設定してラベルを付けることで、最高の最適化が得られます。ただし、変動幅が厳正すぎると、オプティマイザはその一部の呼び出しを誤って省略してしまい、よって不正確な結果セットを報告することになってしまいます。厳正度の順に、低度の厳正度から変動率を分類すると、以下のようになります。

  • 変動性

    同じ引数が入っている場合、単一のステートメントに含まれる行であっても、関数は連続の呼び出しに異なる結果を返すことがあります。クエリオプティマイザは、変動的な関数の動作について想定することはできません。変動性の関数を使用するクエリは、各入力行で関数を再評価する必要があります。

  • 安定

    同じ引数が入っている場合、関数が単一のステートメント内で処理されるすべての行に対して同じ結果を返すことが保証されます。異なるステートメントから呼び出された場合、関数が異なる結果を返すことがあります。この分類は、単一のステートメント内でのステートメントへの 1 回の呼び出しにおける関数の複数の呼び出しを最適化するオプティマイザを可能にします。

  • 不変

    同じ引数が入っている場合、関数は常に永遠に同じ結果を返します。クエリが定数引数の IMMUTABLE 関数を呼び出すと、オプティマイザは関数を前評価します。

LAMBDA 'lambda_fn_name'

Amazon Redshift が呼び出す関数の名前。

AWS Lambda 関数を作成する手順については、AWS Lambda デベロッパーガイドの「コンソールで Lambda 関数を作成する」を参照してください。

Lambda 関数に必要なアクセス許可の詳細については、AWS Lambda デベロッパーガイドの「AWS Lambda アクセス許可」を参照してください。

IAM_ROLE 'iam-role-arn'

クラスターが認証と許可に使用する AWS Identity and Access Management IAM ロールの Amazon リソースネーム (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 の再試行を防ぐことができます。

Examples

以下は、スカラー Lambda ユーザー定義関数 (UDF) の使用例を示します。

Node.js Lambda 関数を使用したスカラー Lambda UDF の例

次の例では、入力引数として 2 つの整数を受け取る exfunc_sum という外部関数を作成します。この関数は、合計を整数出力として返します。呼び出される Lambda 関数の名前は lambda_sum です。この Lambda 関数に使用される言語は Node.js 12.x です。IAM ロールを必ず指定してください。この例では、IAM ロールとして 'arn:aws:iam::123456789012:user/johndoe' を使用しています。STABLE オプションを使用することをお勧めします。

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

Lambda 関数はリクエストペイロードを受け取り、各行を繰り返して処理します。単一の行ですべての値が加算されて、その行の合計が計算され、応答配列に保存されます。結果配列の行数は、リクエストペイロードで受信した行数と似ています。

JSON 応答ペイロードは、外部関数によって認識されるために、['results' (結果)] フィールドに結果データを持っている必要があります。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)

次の例では、整数データ型の 2 つの列 c1 と c2 を持つ t_sum というテーブルを作成し、2 行のデータを挿入します。次に、このテーブルの列名を渡すことにより、外部の関数が呼び出されます。2 つのテーブル行は、リクエストペイロードのバッチリクエストで単一の 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 コンピューティングブログの記事「Managing AWS Lambda Function Concurrency」を参照してください。

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 フィールドには、データペイロードが含まれています。バッチリクエストの場合、データペイロードに複数の行が表示されることがあります。

この関数の同時実行制限は、RETRY_TIMEOUT 属性の使用法を示すために、予約済み同時実行で特に 1 に設定されています。属性が 1 に設定されている場合、Lambda 関数は一度に 1 つのリクエストしか処理できません。

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 属性の追加例を 2 つ示します。それぞれ単一の Lambda UDF を呼び出します。Lambda UDF を呼び出す間、各例は同じ SQL クエリを実行して、2 つの同時実行データベースセッションから同時に Lambda UDF を呼び出します。Lambda UDF を呼び出す最初のクエリが UDF によって処理されている場合、2 番目のクエリは 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 STABLE LAMBDA 'exfunc_sleep_3' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-Exfunc-Test' RETRY_TIMEOUT 0;

RETRY_TIMEOUT を 0 に設定すると、別々のデータベースセッションから次の 2 つのクエリを実行して、異なる結果を確認できます。

Lambda UDF を使用する最初の SQL クエリが正常に実行されます。

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

別のデータベースセッションから同時に実行される 2 番目のクエリは、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] -----------------------------------------------

次の 2 番目の例では、Lambda UDF の RETRY_TIMEOUT 属性を 3,000 ミリ秒に設定します。2 番目のクエリが同時に実行された場合でも、Lambda UDF は遅延の合計が 3,000 ミリ秒になるまで再試行します。したがって、両方のクエリが正常に実行されます。

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

RETRY_TIMEOUT を 3,000 ミリ秒に設定すると、別々のデータベースセッションから次の 2 つのクエリを実行して、同じ結果を確認できます。

Lambda UDF を実行する最初の SQL クエリが正常に実行されます。

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

2 番目のクエリは同時に実行され、Lambda UDF は遅延の合計が 3,000 ミリ秒になるまで再試行します。

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

Python Lambda 関数を使用したスカラー Lambda UDF の例

次の例では、exfunc_multiplication という名前の外部関数を作成します。この関数は、数値を乗算して整数を返します。この例では、Lambda 応答に success フィールドと error_msg フィールドが組み込まれています。乗算結果に整数オーバーフローがあり、error_msg メッセージが Integer multiplication overflow に設定されている場合、成功フィールドは false に設定されます。exfunc_multiplication 関数は、入力引数として 3 つの整数を取り、その合計を整数出力として返します。

呼び出される Lambda 関数の名前は lambda_multiplication です。この Lambda 関数に使用される言語は Python 3.8 です。IAM ロールを必ず指定してください。

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

Lambda 関数はリクエストペイロードを受け取り、各行を繰り返して処理します。1 つの行ですべての値が乗算され、その行の結果が計算されます。この結果は、応答リストに保存されます。この例では、デフォルトで true に設定されているブールの成功値を使用します。行の乗算結果に整数オーバーフローがある場合、成功値は false に設定されます。その後、反復ループが中断されます。

応答ペイロードの作成中に、成功値が false の場合、次の Lambda 関数がペイロードに error_msg フィールドを追加します。また、エラーメッセージを Integer multiplication overflow に設定します。成功値が true の場合、結果データが結果フィールドに追加されます。結果配列の行数は、存在する場合、リクエストペイロードで受信した行数と似ています。

Lambda 関数に送信されるリクエストの引数フィールドには、データペイロードが含まれます。バッチリクエストの場合、データペイロードに複数の行が存在する可能性があります。次の Lambda 関数は、リクエストデータペイロードのすべての行を繰り返し処理し、1 つの行内のすべての値を個別に繰り返し処理します。

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 = Falsebreakelse: 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)

次の例では、整数データ型の 3 つの列 c1、c2、および c3 を持つ t_multi という名前のテーブルを作成します。外部関数は、このテーブルの列名を渡すことによって呼び出されます。データは、エラーがどのように伝播されるかを示す整数オーバーフローを引き起こすような方法で挿入されます。

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] -----------------------------------------------