ユーザー定義型

ユーザー定義型 は、既存の:doc:` Snowflakeデータ型</sql-reference-data-types>`に基づく新しいデータ型です。たとえば、人の年齢の列を定義し、最大で3桁の数字を含み、小数点以下の数字を含まないように値を制限するとします。``NUMBER(3,0)``に対応する``age``という名前のデータ型を定義できます。

ユーザー定義型は、列定義、関数とプロシージャ定義、キャスト式など、型を使用できるすべての場所で使用できるスキーマレベルのオブジェクトです。

ユーザー定義型により、スキーマのメンテナンスを簡素化し、データ品質を向上させることができます。ユーザー定義型を一度定義してから、複数のオブジェクトで使用できます。

また、フィールドに複数の列やテーブルを使用する代わりに、ユーザー定義型を使用して、関連するデータフィールドを単一の論理列にグループ化することもできます。たとえば、住所用のデータ型として、通り（番地）、市区町村、都道府県、ZIP番号の各フィールドを持つ:ref:`構造化されたOBJECT型<label-structured_types_specifying_object>`を定義できます。

ユーザー定義型に必要な権限

スキーマのユーザー定義型を作成するには、そのスキーマに対するCREATETYPE権限が付与されたロールを使用する必要があります。

詳細については、:ref:`ユーザー定義型のアクセス制御要件<label-access_control_privileges_type>`を参照してください。

ユーザー定義型の一般的な使用上の注意

  • ユーザー定義型の定義を変更するには、削除してから再作成します。

    ユーザー定義型の定義を変更する場合

    • 対象の型を使用するテーブル列を直接操作するSQLステートメントは、SELECTステートメントやDMLステートメントなどのエラーを返す可能性があります。ただし対象の型を使用するテーブル列で直接動作しないSQLステートメントは、正常に実行されます。たとえば、テーブルに``typed_column``という名前のユーザー定義型列が含まれていても、SELECTステートメントのSELECTリストで他の列を指定していれば、そのSELECTステートメントは通常どおり実行されます。問題を修正するには、基になるSnowflake型を使用するようにSQLステートメントを修正します。

    • 対象の型を使用する関数およびストアドプロシージャの呼び出しを行うと、エラーが返されます。問題を修正するには、関数とストアドプロシージャを削除して再作成します。

  • :doc:`/sql-reference/sql/alter-table-column`コマンドにより、列のデータ型をユーザー定義型から互換性のある:doc:`Snowflakeデータ型</sql-reference-data-types>`へ、またはSnowflakeデータ型からユーザー定義型へ変更できます。

  • :doc:`/sql-reference/functions/object_construct`関数や:ref:`OBJECT定数<label-object_constant>`を使用して、ユーザー定義型の列に挿入するオブジェクトを構築する場合は、その結果を該当のユーザー定義型にキャストしてください。

    例については、 テーブル列でのユーザー定義型の使用 をご参照ください。

  • :doc:`集合演算子</sql-reference/operators-query>`（たとえば、UNION、INTERSECT、EXCEPT）や:doc:`条件式関数</sql-reference/expressions-conditional>`（たとえば、CASE、IFF、COALESCE、NVLなど）が、ユーザー定義型の値として解決される式を評価する場合、Snowflakeはオペランドの基となるベース型を使用して、共通の型を決定します。デフォルトでは、結果のデータ型はこのベース型です。結果をユーザー定義型の値にする場合は、最終式をユーザー定義型に明示的にキャストします。

    ユーザー定義型が集合操作または条件式関数で使用される場合は、以下の規則が適用されます。

    • ユーザー定義型はベース型とは異なりますが、式の型解決では、ベース型を強制して共通の型を見つけます。

    • ブランチまたはオペランドが単一のSnowflake型に解決される場合（たとえば、VARCHARまたはNUMBER）、それが結果の型です。

    • ユーザー定義型を保持したり、ユーザー定義型の値である結果を生成したりするには、:samp:`CAST({expr} AS {user-defined type})`や:samp:`{expr}::{user-defined type}`を使用して式全体を:ref:`キャスト<label-user_defined_type_casting_explicit>`します。

    • 互換性のないベース型（たとえば、VARCHARおよびNUMBER）は通常の:doc:`強制ルール</sql-reference/data-type-conversion>`に従います。共通のベース型が存在しない場合、エラーが返されます。

    例については、 ユーザー定義型での集合演算子と条件式関数の使用 をご参照ください。

  • 関数の:ref:`オーバーロード<label-procedure_function_name_overloading>`において、ユーザー定義型と、互換性のあるSnowflakeデータ型を使用することが許可されています。つまり、関数の引数型にはユーザー定義型を指定でき、同じ名前の関数の引数型には互換性のあるSnowflakeデータ型を指定できます。

  • ユーザー定義型を、SQLユーザー定義関数（UDF）やSnowflake ScriptingストアドプロシージャのRETURN型として指定する場合、UDFまたはストアドプロシージャの本体で、戻り値を、そのユーザー定義型に明示的にキャストする必要があります。

  • SQL以外の言語（PythonまたはJavaなど）で記述された、UDFまたはプロシージャの引数または戻り値として、ユーザー定義型を使用する場合、ユーザー定義型はそのベース型と同じように扱われます。

  • :doc:`スキーマエボリューション</user-guide/data-load-schema-evolution>`はユーザー定義型ではサポートされていません。

ユーザー定義型のキャスト

ユーザー定義型は、明示的なキャストと暗黙的なキャスト（強制）の両方を含む、:doc:`データ型の変換</sql-reference/data-type-conversion>`をサポートしています。

ユーザー定義型との間の明示的なキャスト

ユーザー定義の型の値は、そのベース型の値と同じデータ型にキャストできます。たとえば、NUMBER型に基づく``age``という名前のユーザー定義型を作成します。

CREATE TYPE age AS NUMBER(3,0);

ユーザー定義型のベース型に値をキャストできる場合は、値をユーザー定義型にキャストできます。たとえば、値``10``は、NUMBER型にキャストできるため、この値を``age``型にキャストできます。

SELECT 10::age;

ユーザー定義型の基本型がそのデータ型にキャストできる場合は、ユーザー定義型の値を別のデータ型にキャストできます。たとえば、NUMBERの値はVARCHAR型にキャストできるため、ユーザー定義型の値``age``の値``10``は、VARCHAR型にキャストできます。

SELECT 10::age::VARCHAR;

ユーザー定義型の強制

ユーザー定義型の値は、そのベース型へと型強制されます。したがって、すべての操作では、ベース型と同じように動作します。たとえば、NUMBER型に基づく``age``という名前のユーザー定義型を作成し、``age``型の2つの列からなるテーブルを作成します。

CREATE TYPE age AS NUMBER(3,0);

CREATE TABLE test_age_udf(a age, b age);

テーブルに値を挿入します。

INSERT INTO test_age_udf VALUES (10, 20);

次の例では、テーブル値に対して加算演算を実行します。Snowflakeは、操作を完了するために、``age``の値をNUMBER型の値へと型強制します。例では、結果のデータ型を示すために、:doc:`/sql-reference/functions/system_typeof`関数を使用しています。

SELECT a + b AS result,
       SYSTEM$TYPEOF(a + b) AS type
  FROM test_age_udf;

+--------+------------------+
| RESULT | TYPE             |
|--------+------------------|
|     30 | NUMBER(4,0)[SB1] |
+--------+------------------+

ユーザー定義のデータ型の例

次の例は、ユーザー定義型を使用する方法を示しています。

テーブル列でのユーザー定義型の使用

次の例では、``address``という名前のユーザー定義型を作成し、その型をテーブルで使用します。

  1. 住所情報を保存するために、:ref:`構造化されたOBJECT型<label-structured_types_specifying_object>`に基づくユーザー定義型を作成します。

    CREATE TYPE address AS OBJECT(
  street VARCHAR(100),
  city VARCHAR(50),
  state_abbr CHAR(2),
  zip_code CHAR(10)
);

  2. 住所などのお客様情報を保存するテーブルを作成します。

    CREATE TABLE customers_udt_test (
  cust_id VARCHAR NOT NULL,
  cust_name VARCHAR(100),
  cust_address address
);

  3. テーブルに行を挿入します。その際、:ref:`OBJECT定数<label-object_constant>`を``address``型にキャストして、``cust_address``列の値を指定してください。

    INSERT INTO customers_udt_test (cust_id, cust_name, cust_address)
  SELECT
    '1000',
    'Example1 Inc',
    {
      'street': '101 Snow Street',
      'city': 'San Francisco',
      'state_abbr': 'CA',
      'zip_code': '94102'
    }::address;

  4. テーブルに行を挿入します。その際、:doc:`/sql-reference/functions/object_construct`関数を呼び出し、その戻り値を``address``型にキャストして``cust_address``列の値を指定してください。

    INSERT INTO customers_udt_test (cust_id, cust_name, cust_address)
  SELECT
    '1001',
    'Example2 Inc',
    OBJECT_CONSTRUCT(
      'street', '555 Polar Bear Street',
      'city', 'New York',
      'state_abbr', 'NY',
      'zip_code', '10001'
    )::address;

  5. テーブルに行を挿入します。その際、OBJECT定数を、``address``型のベース型であるOBJECT型にキャストして、``cust_address``列の値を指定してください。通常は、OBJECT定数をユーザー定義型にキャストする方が簡単ですが、この例ではOBJECT定数がユーザー定義型に型強制される様子を示しています。

    INSERT INTO customers_udt_test (cust_id, cust_name, cust_address)
  SELECT
    '1002',
    'Example3 Inc',
    {
      'street': '909 Flake Street',
      'city': 'Seattle',
      'state_abbr': 'WA',
      'zip_code': '98109'
    }::OBJECT(
         street VARCHAR(100),
         city VARCHAR(50),
         state_abbr CHAR(2),
         zip_code CHAR(10));

  6. 挿入された行を表示するには、テーブルをクエリします。

    SELECT * FROM customers_udt_test;

    +---------+--------------+--------------------------------------+
| CUST_ID | CUST_NAME    | CUST_ADDRESS                         |
|---------+--------------+--------------------------------------|
| 1000    | Example1 Inc | {                                    |
|         |              |   "street": "101 Snow Street",       |
|         |              |   "city": "San Francisco",           |
|         |              |   "state_abbr": "CA",                |
|         |              |   "zip_code": "94102"                |
|         |              | }                                    |
| 1001    | Example2 Inc | {                                    |
|         |              |   "street": "555 Polar Bear Street", |
|         |              |   "city": "New York",                |
|         |              |   "state_abbr": "NY",                |
|         |              |   "zip_code": "10001"                |
|         |              | }                                    |
| 1002    | Example3 Inc | {                                    |
|         |              |   "street": "909 Flake Street",      |
|         |              |   "city": "Seattle",                 |
|         |              |   "state_abbr": "WA",                |
|         |              |   "zip_code": "98109"                |
|         |              | }                                    |
+---------+--------------+--------------------------------------+

  7. テーブルをクエリし、コロン演算子を使用して、``address``データの``zip_code``の値のみを表示します。

    SELECT cust_id,
       cust_name,
       cust_address:zip_code
  FROM customers_udt_test;

    +---------+--------------+-----------------------+
| CUST_ID | CUST_NAME    | CUST_ADDRESS:ZIP_CODE |
|---------+--------------+-----------------------|
| 1000    | Example1 Inc | 94102                 |
| 1001    | Example2 Inc | 10001                 |
| 1002    | Example3 Inc | 98109                 |
+---------+--------------+-----------------------+

ユーザー定義型での集合演算子と条件式関数の使用

集合演算子</sql-reference/operators-query>`や:doc:`条件式関数</sql-reference/expressions-conditional>`が、Snowflake型とユーザー定義型の値を評価する場合、それらの型は互換性があり、かつ単一の型に型強制可能である必要があります。ユーザー定義の型に明示的にキャストされない限り、結果の出力はSnowflakeベース型になります。詳細については、 :ref:`label-user_defined_type_usage_notes をご参照ください。

このセクションの例では、ユーザー定義型を用いた集合演算子および条件式を使用します。まず、さまざまなベース型でいくつかのユーザー定義型を作成します。

CREATE TYPE us_zipcode AS VARCHAR;
CREATE TYPE uk_postcode AS VARCHAR;
CREATE TYPE positive_integer AS INTEGER;
CREATE TYPE positive_number AS NUMBER;

次のクエリはIFF関数を呼び出します。この呼び出しでは、``us_zipcode``ユーザー定義型の値と、互換性のあるSnowflake型の値を評価します。このクエリは:doc:`/sql-reference/functions/system_typeof`関数を用いることで、結果がSnowflakeのベース型VARCHARであることを示しています。

SELECT IFF(TRUE, '90210'::us_zipcode, '10006') AS result,
       SYSTEM$TYPEOF(IFF(TRUE, '90210'::us_zipcode, '10006')) AS type;

+--------+-------------------------+
| RESULT | TYPE                    |
|--------+-------------------------|
| 90210  | VARCHAR(134217728)[LOB] |
+--------+-------------------------+

次のクエリは前のクエリと同じですが、結果を``us_zipcode``ユーザー定義型にキャストします。

SELECT IFF(TRUE, '90210'::us_zipcode, '10006')::us_zipcode AS result,
       SYSTEM$TYPEOF(IFF(TRUE, '90210'::us_zipcode, '10006')::us_zipcode) AS type;

+--------+-------------------------------+
| RESULT | TYPE                          |
|--------+-------------------------------|
| 90210  | MYDB.MYSCHEMA.US_ZIPCODE[LOB] |
+--------+-------------------------------+

次のクエリには、互換性のある異なるユーザー定義型を評価し、Snowflakeのベース型で値を返す、CASE式が含まれています。

SELECT CASE
         WHEN TRUE THEN 'SW1A 0AA'::uk_postcode
           ELSE '90210'::us_zipcode
         END AS result,
       SYSTEM$TYPEOF(CASE
         WHEN TRUE THEN 'SW1A 0AA'::uk_postcode
           ELSE '90210'::us_zipcode
         END) AS type;

+----------+-------------------------+
| RESULT   | TYPE                    |
|----------+-------------------------|
| SW1A 0AA | VARCHAR(134217728)[LOB] |
+----------+-------------------------+

次のクエリは前のクエリと同じですが、結果を``uk_postcode``ユーザー定義型にキャストします。

SELECT CAST(CASE
         WHEN TRUE THEN 'SW1A 0AA'::uk_postcode
           ELSE '90210'::us_zipcode
         END AS uk_postcode) AS result,
       SYSTEM$TYPEOF(CAST(CASE
         WHEN TRUE THEN 'SW1A 0AA'::uk_postcode
           ELSE '90210'::us_zipcode
         END AS uk_postcode)) AS type;

+----------+--------------------------------------------+
| RESULT   | TYPE                                       |
|----------+--------------------------------------------|
| SW1A 0AA | MYDB.MYSCHEMA.UK_POSTCODE[LOB]             |
+----------+--------------------------------------------+

次のクエリには、互換性のある異なるユーザー定義型を評価し、Snowflakeのベース型で値を返す、COALESCE式が含まれています。

SELECT COALESCE(
         5::positive_integer,
         10::positive_number) AS result,
       SYSTEM$TYPEOF(COALESCE(
         5::positive_integer,
         10::positive_number)) AS type;

+--------+-------------------+
| RESULT | TYPE              |
|--------+-------------------|
|      5 | NUMBER(38,0)[SB1] |
+--------+-------------------+

次のクエリは前のクエリと同じですが、結果を``positive_number``ユーザー定義型にキャストします。

SELECT CAST(COALESCE(
         5::positive_integer,
         10::positive_number
       ) AS positive_number) AS result,
       SYSTEM$TYPEOF(CAST(COALESCE(
         5::positive_integer,
         10::positive_number
       ) AS positive_number)) AS type;

+--------+------------------------------------+
| RESULT | TYPE                               |
|--------+------------------------------------|
|      5 | MYDB.MYSCHEMA.POSITIVE_NUMBER[SB1] |
+--------+------------------------------------+