Benutzerdefinierte Typen

Sie können benutzerdefinierte Typen definieren, d. h. neue Datentypen, die auf vorhandenen Snowflake-Datentypen basieren. Angenommen, Sie möchten eine Spalte für das Alter einer Person definieren und möchten die Werte darauf beschränken, Zahlen mit höchstens drei Ziffern und keinen Nachkommastellen einzuschließen. Sie können einen Datentyp namens age definieren, der NUMBER(3,0) entspricht.

Ein benutzerdefinierter Typ ist ein Objekt auf Schemaebene, das an allen Stellen verwendet werden kann, an denen Typen verwendet werden können, einschließlich Spaltendefinitionen, Funktions- und Prozedurdefinitionen sowie Umwandlungsausdrücke.

Benutzerdefinierte Typen können die Schemaverwaltung vereinfachen und die Datenqualität verbessern. Sie können einen benutzerdefinierten Typ einmal definieren und ihn dann in mehreren Objekten verwenden.

Sie können auch benutzerdefinierte Typen verwenden, um zusammengehörige Datenfelder in einer einzigen logischen Spalte zu gruppieren, anstatt mehrere Spalten oder Tabellen für die Felder zu verwenden. Sie können zum Beispiel einen Datentyp für Adressen definieren, der ein strukturierter OBJECT-Typ mit Feldern für Adresse, Ort, Bundesland und ZIP Postleitzahl ist.

Erforderliche Berechtigungen für benutzerdefinierte Typen

Um einen benutzerdefinierten Typ in einem Schema zu erstellen, müssen Sie eine Rolle verwenden, der die Berechtigung CREATE TYPE für dieses Schema zugewiesen wurde.

Weitere Informationen dazu finden Sie unter Anforderungen an die Zugriffssteuerung für benutzerdefinierte Typen.

Allgemeine Nutzungshinweise für benutzerdefinierte Typen

  • Um die Definition eines benutzerdefinierten Typs zu ändern, müssen Sie ihn löschen und neu erstellen.

    Wenn Sie die Definition eines benutzerdefinierten Typs ändern:

    • SQL-Anweisungen, die direkt in Tabellenspalten ausgeführt werden, die den Typ verwenden, können Fehler zurückgeben, einschließlich SELECT-Anweisungen und DML-Anweisungen. SQL-Anweisungen, die nicht direkt in Tabellenspalten ausgeführt werden, die den Typ verwenden, werden normal ausgeführt. Wenn zum Beispiel eine Tabelle eine Spalte vom benutzerdefinierten Typ mit dem Namen typed_column enthält, und eine SELECT-Anweisung andere Spalten in ihrer SELECT-Liste angibt, wird die SELECT-Anweisung normal ausgeführt. Um das Problem zu beheben, können Sie die SQL-Anweisungen zur Verwendung der zugrunde liegenden Snowflake-Typen überarbeiten.

    • Aufrufe von Funktionen und gespeicherte Prozeduren, die den Typ verwenden, geben Fehler zurück. Um das Problem zu beheben, müssen Sie die Funktionen und gespeicherten Prozeduren löschen und neu erstellen.

  • Mit dem Befehl ALTER TABLE … ALTER COLUMN kann der Datentyp einer Spalte von einem benutzerdefinierten Typ in einen kompatiblen Snowflake-Datentyp oder von einem Snowflake-Datentyp in einen benutzerdefinierten Typ geändert werden.

  • Wenn Sie ein Objekt konstruieren, das in eine Spalte eines benutzerdefinierten Typs eingefügt werden soll, indem Sie die OBJECT_CONSTRUCT-Funktion oder eine OBJECT-Konstante verwenden, wandeln Sie das Ergebnis in den benutzerdefinierten Typ um.

    Weitere Beispiele finden Sie unter Verwenden eines benutzerdefinierten Typs für eine Tabellenspalte.

  • Wenn Mengenoperatoren (z. B. UNION, INTERSECT, EXCEPT) oder Funktionen für bedingte Ausdrücke (z. B. CASE, IFF, COALESCE, NVL usw.) einen Ausdruck auswerten, der in einen Wert eines benutzerdefinierten Typs aufgelöst wird, bestimmt Snowflake einen gemeinsamen Typ unter Verwendung der zugrunde liegenden Basistypen der Operanden. Standardmäßig ist der Datentyp des Ergebnisses dieser Basistyp. Wenn das Ergebnis ein Wert eines benutzerdefinierten Typs sein soll, wandeln Sie den endgültigen Ausdruck explizit in den benutzerdefinierten Typ um.

    Die folgenden Regeln gelten, wenn benutzerdefinierte Typen in Mengenoperationen oder Funktionen für bedingte Ausdrücke verwendet werden:

    • Benutzerdefinierte Typen unterscheiden sich von ihren Basistypen, aber bei der Auflösung von Ausdruckstypen werden sie implizit in ihre Basistypen umgewandelt, um einen gemeinsamen Typ zu finden.

    • Wenn die Zweige oder Operanden zu einem einzigen Snowflake-Typ aufgelöst werden (z. B. VARCHAR oder NUMBER), ist dies der Ergebnistyp.

    • Um einen benutzerdefinierten Typ beizubehalten oder ein Ergebnis zu erzeugen, das ein Wert eines benutzerdefinierten Typs ist, wandeln Sie den Gesamtausdruck mithilfe von CAST(expr AS user-defined type) oder expr::user-defined type um.

    • Inkompatible Basistypen (z. B. VARCHAR und NUMBER) folgen den normalen Koersionsregeln. Wenn kein gemeinsamer Basistyp vorhanden ist, wird ein Fehler zurückgegeben.

    Weitere Beispiele finden Sie unter Verwenden von Mengenoperatoren und Funktionen für bedingte Ausdrücke mit benutzerdefinierten Typen.

  • Das Verwenden von benutzerdefinierten Typen und kompatiblen Snowflake-Datentypen für das Überladen von Funktionen ist zulässig. Das heißt, Sie können einen benutzerdefinierten Typ für einen Funktionsargumenttyp angeben, und Sie können einen kompatiblen Snowflake-Datentyp für einen Argumenttyp einer Funktion mit demselben Namen angeben.

  • Wenn ein benutzerdefinierter Typ als RETURN-Typ einer benutzerdefinierten Funktion (UDF) für SQL oder einer gespeicherten Snowflake Scripting-Prozedur angegeben ist, muss der Rückgabewert explizit in den benutzerdefinierten Typ im Textteil der UDF oder der gespeicherten Prozedur umgewandelt werden.

  • Wenn ein benutzerdefinierter Typ als Argument oder Rückgabewert für eine UDF oder Prozedur verwendet wird, die in einer anderen Sprache als SQL (wie beispielsweise Python oder Java) geschrieben ist, wird der benutzerdefinierte Typ genauso behandelt wie sein Basistyp.

  • Die Schemaentwicklung wird für benutzerdefinierte Typen nicht unterstützt.

Umwandeln von benutzerdefinierten Typen

Benutzerdefinierte Typen unterstützen die Konvertierung des Datentyps, einschließlich expliziter Umwandlung und impliziter Umwandlung (Koersion):

Explizite Umwandlung in und von benutzerdefinierten Typen

Ein benutzerdefinierter Typwert kann in dieselben Datentypen umgewandelt werden wie Werte seines Basistyps. Erstellen Sie zum Beispiel einen benutzerdefinierten Typ namens age, der auf dem NUMBER-Typ basiert:

CREATE TYPE age AS NUMBER(3,0);

Ein Wert kann in einen benutzerdefinierten Typ umgewandelt werden, wenn der Wert in den Basistyp des benutzerdefinierten Typs umgewandelt werden kann. Zum Beispiel kann der Wert 10 in den NUMBER-Typ umgewandelt werden, sodass Sie den Wert in den age-Typ umwandeln können:

SELECT 10::age;

Ein Wert eines benutzerdefinierten Typs kann in einen anderen Datentyp umgewandelt werden, wenn der Basistyp des benutzerdefinierten Typs in diesen Datentyp umgewandelt werden kann. Beispiel: Ein NUMBER-Wert kann in den VARCHAR-Typ umgewandelt werden, sodass der Wert 10 des benutzerdefinierten Typs age in den VARCHAR-Typ umgewandelt werden kann:

SELECT 10::age::VARCHAR;

Koersion von benutzerdefinierten Typen

Ein benutzerdefinierter Typwert wird implizit in seinen Basistyp umgewandelt. Daher verhält er sich bei allen Operationen genauso wie sein Basistyp. Erstellen Sie zum Beispiel einen benutzerdefinierten Typ namens age, der auf dem NUMBER-Typ basiert, und eine Tabelle mit zwei Spalten des Typs age:

CREATE TYPE age AS NUMBER(3,0);

CREATE TABLE test_age_udf(a age, b age);

Fügen Sie Werte in die Tabelle ein:

INSERT INTO test_age_udf VALUES (10, 20);

Im folgenden Beispiel wird eine Additionsoperation für die Tabellenwerte ausgeführt, und Snowflake erzwingt die age-Werte bis zu Werten vom Typ NUMBER, um die Operation abzuschließen. In dem Beispiel wird die SYSTEM$TYPEOF-Funktion verwendet, um den Datentyp des Ergebnisses anzuzeigen:

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

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

Beispiele für benutzerdefinierte Datentypen

Die folgenden Beispiele zeigen Ihnen, wie Sie benutzerdefinierte Typen verwenden können:

Verwenden eines benutzerdefinierten Typs für eine Tabellenspalte

Im folgenden Beispiel erstellen Sie einen benutzerdefinierten Typ namens address und verwenden dann den Typ in einer Tabelle:

  1. Erstellen Sie einen benutzerdefinierten Typ, der auf einem strukturierten OBJECT-Typ basiert, um Adressinformationen zu speichern:

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

  2. Erstellen Sie eine Tabelle, in der Kundeninformationen gespeichert werden, einschließlich der Adresse:

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

  3. Fügen Sie eine Zeile in die Tabelle ein, und geben Sie den Wert für die cust_address-Spalte durch Umwandlung einer OBJECT-Konstante in den address-Typ an:

    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. Fügen Sie eine Zeile in die Tabelle ein, und geben Sie den Wert für die cust_address-Spalte durch Aufrufen der Funktion OBJECT_CONSTRUCT und Umwandeln des Rückgabewerts in den address-Typ an:

    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. Fügen Sie eine Zeile in die Tabelle ein, und geben Sie den Wert für die cust_address-Spalte durch Umwandlung einer OBJECT-Konstante in den OBJECT-Typ an, der der Basistyp des address-Typs ist. Es ist normalerweise einfacher, eine OBJECT-Konstante in den benutzerdefinierten Typ umzuwandeln, aber dieses Beispiel zeigt, dass die OBJECT-Konstante implizit in den benutzerdefinierten Typ umgewandelt wird:

    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. Um die eingefügten Zeilen anzuzeigen, fragen Sie die Tabelle ab:

    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. Fragen Sie die Tabelle ab, und verwenden Sie den Doppelpunkt-Operator, um nur die zip_code-Werte in den address-Daten anzuzeigen:

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

Verwenden von Mengenoperatoren und Funktionen für bedingte Ausdrücke mit benutzerdefinierten Typen

Wenn Mengenoperatoren oder Funktionen für bedingte Ausdrücke Werte von Snowflake-Typen und benutzerdefinierten Typen auswerten, müssen die Typen kompatibel und in einen einzigen Typ implizit umwandelbar sein. Die resultierende Ausgabe entspricht dem Snowflake-Basistyp, sofern sie nicht explizit in einen benutzerdefinierten Typ umgewandelt wird. Weitere Informationen dazu finden Sie unter Allgemeine Nutzungshinweise für benutzerdefinierte Typen.

Die Beispiele in diesem Abschnitt verwenden Mengenoperatoren und bedingte Ausdrücke mit benutzerdefinierten Typen. Erstellen Sie zunächst mehrere benutzerdefinierte Typen mit verschiedenen Basistypen:

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

Die folgende Abfrage ruft die IFF-Funktion auf. Der Aufruf wertet einen Wert des benutzerdefinierten us_zipcode-Typs und einen Wert eines kompatiblen Snowflake-Typs aus. Die Abfrage verwendet die SYSTEM$TYPEOF-Funktion, um zu zeigen, dass das Ergebnis vom Snowflake-Basistyp VARCHAR ist:

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

Die folgende Abfrage ist dieselbe wie die vorherige Abfrage, aber sie wandelt das Ergebnis in den benutzerdefinierten us_zipcode-Typ um:

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

Die folgende Abfrage enthält einen CASE-Ausdruck, der verschiedene, aber kompatible benutzerdefinierte Typen auswertet und einen Wert eines Snowflake-Basistyps zurückgibt:

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

Die folgende Abfrage ist dieselbe wie die vorherige Abfrage, aber sie wandelt das Ergebnis in den benutzerdefinierten uk_postcode-Typ um:

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

Die folgende Abfrage enthält einen COALESCE-Ausdruck, der verschiedene, aber kompatible benutzerdefinierte Typen auswertet und einen Wert eines Snowflake-Basistyps zurückgibt:

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

Die folgende Abfrage ist dieselbe wie die vorherige Abfrage, aber sie wandelt das Ergebnis in den benutzerdefinierten positive_number-Typ um:

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