Types définis par l’utilisateur

Vous pouvez définir des types définis par l’utilisateur, qui sont de nouveaux types de données basés sur des Types de données Snowflake existants. Par exemple, supposons que vous souhaitiez définir une colonne pour l’âge d’une personne et que vous souhaitiez restreindre les valeurs pour inclure des nombres comportant au maximum trois chiffres et aucun chiffre après la virgule. Vous pouvez définir un type de données nommé age qui correspond à NUMBER(3,0).

Un type défini par l’utilisateur est un objet de niveau schéma qui peut être utilisé à tous les endroits où les types peuvent être utilisés, y compris les définitions de colonnes, les définitions de fonctions et de procédures et les expressions de conversion.

Les types définis par l’utilisateur peuvent simplifier la maintenance des schémas et améliorer la qualité des données. Vous pouvez définir une fois un type défini par l’utilisateur, puis l’utiliser dans plusieurs objets.

Vous pouvez également utiliser des types définis par l’utilisateur pour regrouper des champs de données connexes dans une seule colonne logique, au lieu d’utiliser plusieurs colonnes ou tables pour les champs. Par exemple, vous pouvez définir un type de données pour les adresses qui est un type OBJECT structuré avec des champs pour l’adresse, la ville, l’état et le code ZIP.

Privilèges requis pour les types définis par l’utilisateur

Pour créer un type défini par l’utilisateur dans un schéma, vous devez utiliser un rôle qui a reçu le privilège CREATE TYPE sur ce schéma.

Pour plus d’informations, voir les:ref:exigences de contrôle d’accès pour les types définis par l’utilisateur <label-access_control_privileges_type>.

Notes générales sur l’utilisation des types définis par l’utilisateur

  • Pour modifier la définition d’un type défini par l’utilisateur, vous devez le supprimer et le recréer.

    Si vous modifiez la définition d’un type défini par l’utilisateur :

    • Les instructions SQL qui opèrent directement sur des colonnes de table utilisant le type peuvent renvoyer des erreurs, notamment des instructions SELECT et des instructions DML. Cependant, les instructions SQL qui n’opèrent pas directement sur les colonnes de table qui utilisent le type s’exécutent normalement. Par exemple, si une table contient une colonne dont le type est défini par l’utilisateur nommée typed_column et si une instruction SELECT spécifie d’autres colonnes dans sa liste SELECT, l’instruction SELECT s’exécute normalement. Pour corriger le problème, vous pouvez consulter les instructions SQL pour utiliser les types Snowflake sous-jacents.

    • Les appels aux fonctions et procédures stockées qui utilisent le type renvoient des erreurs. Pour corriger le problème, supprimez et recréez les fonctions et les procédures stockées.

  • La commande ALTER TABLE … ALTER COLUMN peut remplacer le type de données d’une colonne dont le type est défini par l’utilisateur par un type de données Snowflake compatible ou remplacer un type de données Snowflake par un type défini par l’utilisateur.

  • Lorsque vous créez un objet à insérer dans une colonne dont le type est défini par l’utilisateur en utilisant la fonction OBJECT_CONSTRUCT ou une constante OBJECT, convertissez le résultat en type défini par l’utilisateur.

    Pour des exemples, voir Utilisation d’un type défini par l’utilisateur pour une colonne de table.

  • Lorsque des opérateurs d’ensemble (par exemple,UNION ,INTERSECT,EXCEPT) ou des fonctions d’expressions conditionnelles (par exemple,CASE,IFF,COALESCE,NVL, etc.) évaluent une expression qui se résout en une valeur d’un type défini par l’utilisateur, Snowflake détermine un type commun en utilisant les types de base sous-jacents des opérandes. Par défaut, le type de données du résultat est ce type de base. Si vous voulez que le résultat soit une valeur d’un type défini par l’utilisateur, convertissez explicitement l’expression finale en type défini par l’utilisateur.

    Les règles suivantes s’appliquent lorsque des types définis par l’utilisateur sont utilisés dans des opérations définies ou des fonctions d’expressions conditionnelles :

    • Les types définis par l’utilisateur sont distincts de leurs types de base, mais, dans la résolution des types d’expressions, ils reprennent leurs types de base pour trouver un type commun.

    • Si les branches ou les opérandes se résolvent en un seul type Snowflake (par exemple,VARCHAR ouNUMBER), c’est le type de résultat.

    • Pour conserver un type défini par l’utilisateur ou produire un résultat qui est une valeur d’un type défini par l’utilisateur,:ref:convertissez <label-user_defined_type_casting_explicit> l’expression entière en utilisant CAST(expr AS user-defined type) ou expr::user-defined type.

    • Les types de base incompatibles (par exemple, VARCHAR etNUMBER) suivent les règles de conversion normales. S’il n’existe aucun type de base commun, une erreur est renvoyée.

    Pour des exemples, voir Utilisation d’opérateurs d’ensemble et de fonctions d’expressions conditionnelles avec des types définis par l’utilisateur.

  • L’utilisation de types définis par l’utilisateur et de types de données Snowflake compatibles pour la surcharge de fonctions est autorisée. Autrement dit, vous pouvez spécifier un type défini par l’utilisateur pour un type d’argument de fonction et vous pouvez spécifier un type de données Snowflake compatible pour un type d’argument d’une fonction portant le même nom.

  • Si un type défini par l’utilisateur est spécifié comme type RETURN d’une fonction SQL définie par l’utilisateur (UDF) ou d’une procédure stockée Snowflake Scripting, la valeur de retour doit être explicitement convertie en type défini par l’utilisateur dans le corps de l’UDF ou de la procédure stockée.

  • Lorsqu’un type défini par l’utilisateur est utilisé comme argument ou valeur de retour pour une UDF ou une procédure écrite dans un langage autre queSQL (comme Python ou Java), le type défini par l’utilisateur est traité de la même manière que son type de base.

  • L’évolution du schéma n’est pas pris en charge pour les types définis par l’utilisateur.

Conversion des types définis par l’utilisateur

Les types définis par l’utilisateur prennent en charge la conversion de type de données, y compris la conversion explicite et la conversion implicite (coercition) :

Conversion explicite vers et depuis des types définis par l’utilisateur

Une valeur de type définie par l’utilisateur peut être convertie dans le même type de données que la valeur de son type de base. Par exemple, créez un type défini par l’utilisateur nommé age qui est basé sur le type NUMBER :

CREATE TYPE age AS NUMBER(3,0);

Une valeur peut être convertie en un type défini par l’utilisateur si la valeur peut être convertie en type de base du type défini par l’utilisateur. Par exemple, la valeur``10`` peut être converti en type NUMBER pour que vous puissiez convertir la valeur en type age :

SELECT 10::age;

Une valeur de type défini par l’utilisateur peut être convertie en un type de données différent si le type de base du type défini par l’utilisateur peut être converti en ce type de données. Par exemple, une valeur NUMBER peut être convertie en type VARCHAR, donc la valeur 10 du type défini par l’utilisateur age peut être convertie en type VARCHAR :

SELECT 10::age::VARCHAR;

Conversion forcée des types définis par l’utilisateur

Une valeur de type définie par l’utilisateur doit se convertir dans son type de base. Par conséquent, dans toutes les opérations, elle se comporte comme son type de base. Par exemple, créez un type défini par l’utilisateur nommé age qui est basé sur le type NUMBER et une table avec deux colonnes de type age :

CREATE TYPE age AS NUMBER(3,0);

CREATE TABLE test_age_udf(a age, b age);

Insérer des valeurs dans la table :

INSERT INTO test_age_udf VALUES (10, 20);

L’exemple suivant effectue une opération d’ajout sur les valeurs de la table, et Snowflake contraint les valeurs age en valeurs de type NUMBER pour terminer l’opération. L’exemple utilise la fonction SYSTEM$TYPEOF pour afficher le type de données du résultat :

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

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

Exemples de types de données définis par l’utilisateur

Les exemples suivants vous montrent comment utiliser des types définis par l’utilisateur :

Utilisation d’un type défini par l’utilisateur pour une colonne de table

Dans l’exemple suivant, vous créez un type défini par l’utilisateur nommé address, et utilise ensuite le type dans une table :

  1. Créez un type défini par l’utilisateur basé sur un type OBJECT structuré pour stocker des informations d’adresse :

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

  2. Créez une table qui stocke les informations sur le client, y compris l’adresse :

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

  3. Insérez une ligne dans la table et spécifiez la valeur de la colonne cust_address en convertissant une constante OBJECT en type 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. Insérez une ligne dans la table et spécifiez la valeur de la colonne cust_address en appelant la fonction OBJECT_CONSTRUCT et en convertissant la valeur de retour en type 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. Insérez une ligne dans la table et spécifiez la valeur de la colonne``cust_address`` en convertissant une constante OBJECT en type OBJECT, qui est le type de base du type address. Il est généralement plus facile de convertir une constante OBJECT en type défini par l’utilisateur, mais cet exemple montre que la constante OBJECT est contrainte à prendre le type défini par l’utilisateur :

    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. Pour afficher les lignes insérées, interrogez la table :

    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. Interrogez la table et utilisez l’opérateur deux-points pour afficher uniquement les valeurs zip_code dans les données address :

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

Utilisation d’opérateurs d’ensemble et de fonctions d’expressions conditionnelles avec des types définis par l’utilisateur

Lorsque les opérateurs d’ensemble ou les fonctions d’expressions conditionnelles évaluent les valeurs des types Snowflake et des types définis par l’utilisateur, les types doivent être compatibles et convertibles en un seul type. La sortie résultante est du type de base Snowflake, à moins qu’elle ne soit explicitement convertie en un type défini par l’utilisateur. Pour plus d’informations, voir Notes générales sur l’utilisation des types définis par l’utilisateur.

Les exemples de cette section utilisent des opérateurs d’ensemble et des expressions conditionnelles avec des types définis par l’utilisateur. Tout d’abord, créez plusieurs types définis par l’utilisateur avec différents types de base :

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

La requête suivante appelle la fonction IFF. L’appel évalue une valeur du type défini par l’utilisateur us_zipcode et une valeur d’un type Snowflake compatible. La requête utilise la fonction SYSTEM$TYPEOF pour montrer que le résultat est le type de base SnowflakeVARCHAR :

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

La requête suivante est identique à la requête précédente, mais elle convertit le résultat en type défini par l’utilisateur 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] |
+--------+-------------------------------+

La requête suivante contient une expression CASE qui évalue les types définis par l’utilisateur différents mais compatibles et renvoie une valeur d’un type de base Snowflake :

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

La requête suivante est identique à la requête précédente, mais elle convertit le résultat en type défini par l’utilisateur 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]             |
+----------+--------------------------------------------+

La requête suivante contient une expression COALESCE qui évalue les types définis par l’utilisateur différents mais compatibles et renvoie une valeur d’un type de base Snowflake :

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

La requête suivante est identique à la requête précédente, mais elle convertit le résultat en type défini par l’utilisateur 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] |
+--------+------------------------------------+