SQL UDFs のトラブルシューティング

このトピックでは、 SQL UDFs （ユーザー定義関数）に関するトラブルシューティング情報を提供します。

トラブルシューティング

ヒント

マスキングポリシー で SQL UDF を使用する場合は、列、 UDF、およびマスキングポリシーのデータ型が一致していることを確認します。

エラーメッセージ： Unsupported subquery type

原因:

UDF にクエリ式が含まれている場合、UDF は サブクエリ として機能できます。サブクエリに列名が渡される場合、サブクエリは 相関サブクエリ として機能できます。相関サブクエリが相関サブクエリのSnowflakeルールに違反している場合、ユーザーはエラーメッセージ Unsupported subquery type を受け取ります。以下の例は、無効な相関サブクエリ、および UDF が同様の無効な相関サブクエリのように機能する方法を示しています。

テーブルのペアを作成し、それらにデータをロードします。

CREATE TABLE stores (store_ID INTEGER, city VARCHAR);
CREATE TABLE employee_sales (employee_ID INTEGER, store_ID INTEGER, sales NUMERIC(10,2), 
    sales_date DATE);
INSERT INTO stores (store_ID, city) VALUES 
    (1, 'Winnipeg'),
    (2, 'Toronto');
INSERT INTO employee_sales (employee_ID, store_ID, sales, sales_date) VALUES 
    (1001, 1, 9000.00, '2020-01-27'),
    (1002, 1, 2000.00, '2020-01-27'),
    (2001, 2, 6000.00, '2020-01-27'),
    (2002, 2, 4000.00, '2020-01-27'),
    (2002, 2, 5000.00, '2020-01-28')
    ;

次の SQL ステートメントには、Snowflakeルールに従わない相関サブクエリが含まれています。このコードは Unsupported subquery type エラーを引き起こします。

SELECT employee_ID,
       store_ID,
       (SELECT city FROM stores WHERE stores.store_ID = employee_sales.store_ID)
    FROM employee_sales;

以下のコードは、上に示したものと同様の相関サブクエリを作成する方法で、サブクエリのような UDF を作成して呼び出します。

CREATE FUNCTION subquery_like_udf(X INT)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS
    $$
        SELECT city FROM stores WHERE stores.store_ID = X
    $$;

SELECT employee_ID, subquery_like_udf(employee_sales.store_ID)
    FROM employee_sales;
解決策#1:

UDF にクエリ式が含まれている場合は、 サブクエリ のルールに準拠した方法でのみ UDF を呼び出します。

たとえば、次のステートメントは、列名ではなく定数を使用して UDF を呼び出すため、UDF は相関サブクエリのように機能しません。

SELECT subquery_like_udf(1);
+----------------------+
| SUBQUERY_LIKE_UDF(1) |
|----------------------|
| Winnipeg             |
+----------------------+
解決策#2:

場合によっては、同じ目的を別の方法で達成するために UDF を書き直すことができます。1つの行を返すように静的に決定できる場合は、相関サブクエリが許可されます。次の UDF は集計関数を使用しているため、1行のみを返します。

CREATE FUNCTION subquery_like_udf_2(X INT)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS
    $$
        SELECT ANY_VALUE(city) FROM stores WHERE stores.store_ID = X
    $$;

SELECT employee_ID, sales_date, subquery_like_udf_2(employee_sales.store_ID)
    FROM employee_sales
    ORDER BY employee_ID, sales_date;
+-------------+------------+----------------------------------------------+
| EMPLOYEE_ID | SALES_DATE | SUBQUERY_LIKE_UDF_2(EMPLOYEE_SALES.STORE_ID) |
|-------------+------------+----------------------------------------------|
|        1001 | 2020-01-27 | Winnipeg                                     |
|        1002 | 2020-01-27 | Winnipeg                                     |
|        2001 | 2020-01-27 | Toronto                                      |
|        2002 | 2020-01-27 | Toronto                                      |
|        2002 | 2020-01-28 | Toronto                                      |
+-------------+------------+----------------------------------------------+