Kategorien:

Tabellenfunktionen

INFER_SCHEMA

Erkennt automatisch das Dateimetadatenschema in einem Satz von Staging-Datendateien, die semistrukturierte Daten enthalten, und ruft die Spaltendefinitionen ab.

Die Funktion GENERATE_COLUMN_DESCRIPTION baut auf der Ausgabe der Funktion INFER_SCHEMA auf, um die Erstellung neuer Tabellen, externer Tabellen oder Ansichten (mit dem entsprechenden CREATE <Objekt>-Befehl) auf der Grundlage der Spaltendefinitionen der Stagingdateien zu vereinfachen.

Die Befehle CREATE TABLE und CREATE EXTERNAL TABLE können mit der USING TEMPLATE-Klausel ausgeführt werden, um eine neue Tabelle bzw. externe Tabelle mit den Spaltendefinitionen zu erstellen, die von der INFER_SCHEMA-Funktionsausgabe abgeleitet sind.

Bemerkung

Diese Funktion unterstützt Apache Parquet-, Apache Avro-, ORC-, JSON- und CSV-Dateien.

Syntax

INFER_SCHEMA(
  LOCATION => '{ internalStage | externalStage }'
  , FILE_FORMAT => '<file_format_name>'
  , FILES => '<file_name>' [ , '<file_name>' ] [ , ... ]
  , IGNORE_CASE => TRUE | FALSE
  , MAX_FILE_COUNT => <num>
  , MAX_RECORDS_PER_FILE => <num>
)
Copy

Wobei:

internalStage ::=
    @[<namespace>.]<int_stage_name>[/<path>][/<filename>]
  | @~[/<path>][/<filename>]
Copy
externalStage ::=
  @[<namespace>.]<ext_stage_name>[/<path>][/<filename>]
Copy

Argumente

LOCATION => '...'

Name des internen oder externen Stagingbereichs, in dem die Dateien gespeichert sind. Geben Sie optional einen Pfad zu einer oder mehreren Dateien im Cloudspeicherort an. Andernfalls durchsucht die Funktion INFER_SCHEMA alle Dateien in allen Unterverzeichnissen des Stagingbereichs:

@[namespace.]int_stage_name[/path][/filename]

Die Dateien befinden sich im angegebenen, benannten internen Stagingbereich.

@[namespace.]ext_stage_name[/path][/filename]

Die Dateien befinden sich im angegebenen, benannten externen Stagingbereich.

@~[/path][/filename]

Die Dateien befinden sich im Stagingbereich des aktuellen Benutzers.

Bemerkung

Diese SQL-Funktion unterstützt nur benannte Stagingbereiche (intern oder extern) und Benutzer-Stagingbereiche. Es werden keine Tabellen-Stagingbereiche unterstützt.

FILES => '<file_name>' [ , '<file_name>' ] [ , ... ]

Gibt eine Liste von einer oder mehreren Dateien (durch Kommas getrennt) aus einer Menge von Stagingdateien an, die semistrukturierten Daten enthalten. Die Dateien müssen bereits entweder am internen Staging-Speicherort von Snowflake oder an dem im Befehl angegebenen externen Staging-Speicherort bereitgestellt worden sein. Wenn eine der angegebenen Dateien nicht gefunden wird, wird die Abfrage abgebrochen.

Die maximale Anzahl der Dateinamen, die angegeben werden können, beträgt 1.000.

Bemerkung

Nur für externe Stagingbereiche (Amazon S3, Google Cloud Storage oder Azure) wird der Dateipfad durch Verkettung der URL in der Stagingbereichsdefinition und der Liste der aufgelösten Dateinamen festgelegt.

Snowflake fügt jedoch implizit kein Trennzeichen zwischen Pfad und Dateinamen ein. Sie müssen explizit ein Trennzeichen (/) entweder am Ende der URL in der Stagingbereichsdefinition oder am Anfang jedes in diesem Parameter angegebenen Dateinamens angeben.

FILE_FORMAT => '<file_format_name>'

Name des Dateiformatobjekts, das die in den Stagingdateien enthaltenen Daten beschreibt. Weitere Informationen dazu finden Sie unter CREATE FILE FORMAT.

IGNORE_CASE => TRUE | FALSE

Gibt an, ob bei den in den Stagingdateien erkannten Spaltennamen die Groß-/Kleinschreibung berücksichtigt werden muss. Standardmäßig ist der Wert FALSE, was bedeutet, dass Snowflake beim Abrufen von Spaltennamen die Groß-/Kleinschreibung von Buchstaben beibehält. Wenn Sie den Wert TRUE angeben, werden die Spaltennamen ohne Berücksichtigung der Groß-/Kleinschreibung behandelt und alle Spaltennamen werden in Großbuchstaben abgerufen.

MAX_FILE_COUNT => <num>

Gibt die maximale Anzahl von Dateien an, die vom Stagingbereich aus gescannt werden. Diese Option wird für eine große Anzahl von Dateien empfohlen, deren Schema in allen Dateien identisch ist. Beachten Sie, dass diese Option nicht bestimmt, welche Dateien gescannt werden. Wenn Sie bestimmte Dateien scannen möchten, verwenden Sie stattdessen die Option FILES.

MAX_RECORDS_PER_FILE => <num>

Gibt die maximale Anzahl der gescannten Datensätze pro Datei an. Diese Option gilt nur für CSV- und JSON-Dateien. Es wird empfohlen, diese Option für große Dateien zu verwenden. Beachten Sie jedoch, dass diese Option die Genauigkeit der Schemaerkennung beeinträchtigen kann.

Ausgabe

Die Funktion gibt die folgenden Spalten zurück:

Spaltenname

Datentyp

Beschreibung

COLUMN_NAME

TEXT

Name einer Spalte in den Stagingdateien.

TYPE

TEXT

Datentyp der Spalte.

NULLABLE

BOOLEAN

Gibt an, ob Zeilen in der Spalte NULL anstelle eines Wertes speichern können. Derzeit kann die abgeleitete NULL-Zulässigkeit einer Spalte für eine Datendatei gelten, aber nicht für andere im gescannten Satz.

EXPRESSION

TEXT

Ausdruck der Spalte im Format $1:COLUMN_NAME::TYPE (hauptsächlich für externe Tabellen). Wenn für IGNORE_CASE der Wert TRUE angegeben ist, hat der Ausdruck der Spalte das Format GET_IGNORE_CASE ($1, COLUMN_NAME)::TYPE.

FILENAMES

TEXT

Namen der Dateien, die die Spalte enthalten.

ORDER_ID

NUMBER

Reihenfolge der Spalten in den Stagingdateien.

Nutzungshinweise

  • Für CSV-Dateien können die Spaltennamen mit der Dateiformatoption PARSE_HEADER = [ TRUE | FALSE ] definiert werden.

    • Wenn die Option auf TRUE gesetzt ist, werden die Überschriften in der ersten Zeile zur Bestimmung der Spaltennamen verwendet.

    • Der Standardwert FALSE gibt die Spaltennamen als „c*“ zurück, wobei „*“ die Position der Spalte ist. Beachten Sie, dass die Option SKIP_HEADER bei PARSE_HEADER = TRUE nicht unterstützt wird.

  • Zum Laden von CSV-Dateien ist die Kopieroption MATCH_BY_COLUMN_NAME in der Vorschau verfügbar. Diese erfordert die Verwendung der oben erwähnten CSV-Dateiformatoption PARSE_HEADER = TRUE.

  • Für CSV- und JSON-Dateien werden die folgenden Dateiformatoptionen derzeit nicht unterstützt: DATE_FORMAT, TIME_FORMAT und TIMESTAMP_FORMAT.

  • Die JSON-Dateiformatoption TRIM_SPACE wird nicht unterstützt.

  • Die wissenschaftlichen Schreibweisen (z. B. 1E2) in JSON-Dateien werden als Datentyp REAL abgerufen.

  • Alle Varianten von Zeitstempel-Datentypen werden als TIMESTAMP_NTZ ohne Zeitzoneninformationen abgerufen.

  • Für die CSV- und JSON-Dateien werden alle Spalten als NULLABLE erkannt.

Beispiele

Abrufen der Spaltendefinitionen für Parquet-Dateien im Stagingbereich mystage:

-- Create a file format that sets the file type as Parquet.
CREATE FILE FORMAT my_parquet_format
  TYPE = parquet;

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage'
      , FILE_FORMAT=>'my_parquet_format'
      )
    );

+-------------+---------+----------+---------------------+--------------------------+----------+
| COLUMN_NAME | TYPE    | NULLABLE | EXPRESSION          | FILENAMES                | ORDER_ID |
|-------------+---------+----------+---------------------+--------------------------|----------+
| continent   | TEXT    | True     | $1:continent::TEXT  | geography/cities.parquet | 0        |
| country     | VARIANT | True     | $1:country::VARIANT | geography/cities.parquet | 1        |
| COUNTRY     | VARIANT | True     | $1:COUNTRY::VARIANT | geography/cities.parquet | 2        |
+-------------+---------+----------+---------------------+--------------------------+----------+
Copy

Ähnlich wie im vorherigen Beispiel, aber es wird eine einzelne Parquet-Datei im Stagingbereich mystage angegeben:

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage/geography/cities.parquet'
      , FILE_FORMAT=>'my_parquet_format'
      )
    );

+-------------+---------+----------+---------------------+--------------------------+----------+
| COLUMN_NAME | TYPE    | NULLABLE | EXPRESSION          | FILENAMES                | ORDER_ID |
|-------------+---------+----------+---------------------+--------------------------|----------+
| continent   | TEXT    | True     | $1:continent::TEXT  | geography/cities.parquet | 0        |
| country     | VARIANT | True     | $1:country::VARIANT | geography/cities.parquet | 1        |
| COUNTRY     | VARIANT | True     | $1:COUNTRY::VARIANT | geography/cities.parquet | 2        |
+-------------+---------+----------+---------------------+--------------------------+----------+
Copy

Abrufen der Spaltendefinitionen für Parquet-Dateien im Stagingbereich mystage, wobei IGNORE_CASE den Wert TRUE hat. In der zurückgegebenen Ausgabe werden alle Spaltennamen in Großbuchstaben abgerufen.

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage'
      , FILE_FORMAT=>'my_parquet_format'
      , IGNORE_CASE=>TRUE
      )
    );

+-------------+---------+----------+----------------------------------------+--------------------------+----------+
| COLUMN_NAME | TYPE    | NULLABLE | EXPRESSION                             | FILENAMES                | ORDER_ID |
|-------------+---------+----------+---------------------+---------------------------------------------|----------+
| CONTINENT   | TEXT    | True     | GET_IGNORE_CASE ($1, CONTINENT)::TEXT  | geography/cities.parquet | 0        |
| COUNTRY     | VARIANT | True     | GET_IGNORE_CASE ($1, COUNTRY)::VARIANT | geography/cities.parquet | 1        |
+-------------+---------+----------+---------------------+---------------------------------------------+----------+
Copy

Abrufen der Spaltendefinitionen für JSON-Dateien im Stagingbereich mystage:

-- Create a file format that sets the file type as JSON.
CREATE FILE FORMAT my_json_format
  TYPE = json;

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage/json/'
      , FILE_FORMAT=>'my_json_format'
      )
    );

+-------------+---------------+----------+---------------------------+--------------------------+----------+
| COLUMN_NAME | TYPE          | NULLABLE | EXPRESSION                | FILENAMES                | ORDER_ID |
|-------------+---------------+----------+---------------------------+--------------------------|----------+
| col_bool    | BOOLEAN       | True     | $1:col_bool::BOOLEAN      | json/schema_A_1.json     | 0        |
| col_date    | DATE          | True     | $1:col_date::DATE         | json/schema_A_1.json     | 1        |
| col_ts      | TIMESTAMP_NTZ | True     | $1:col_ts::TIMESTAMP_NTZ  | json/schema_A_1.json     | 2        |
+-------------+---------------+----------+---------------------------+--------------------------+----------+
Copy

Erstellt eine Tabelle unter Verwendung des erkannten Schemas aus JSON-Stagingdateien.

CREATE TABLE mytable
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@mystage/json/',
          FILE_FORMAT=>'my_json_format'
        )
      ));
Copy

Bemerkung

Die Verwendung von * für ARRAY_AGG(OBJECT_CONSTRUCT()) kann zu einem Fehler führen, wenn das zurückgegebene Ergebnis größer als 16 MB ist. Es wird empfohlen, * bei größeren Resultsets zu vermeiden und nur die erforderlichen Spalten COLUMN NAME, TYPE und NULLABLE für die Abfrage zu verwenden. Die optionale Spalte ORDER_ID kann bei Verwendung von WITHIN GROUP (ORDER BY order_id) hinzugefügt werden.

Abrufen der Spaltendefinitionen für CSV-Dateien im Stagingbereich mystage und Laden der CSV-Dateien mit MATCH_BY_COLUMN_NAME:

-- Create a file format that sets the file type as CSV.
CREATE FILE FORMAT my_csv_format
  TYPE = csv
  PARSE_HEADER = true;

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage/csv/'
      , FILE_FORMAT=>'my_csv_format'
      )
    );

+-------------+---------------+----------+---------------------------+--------------------------+----------+
| COLUMN_NAME | TYPE          | NULLABLE | EXPRESSION                | FILENAMES                | ORDER_ID |
|-------------+---------------+----------+---------------------------+--------------------------|----------+
| col_bool    | BOOLEAN       | True     | $1:col_bool::BOOLEAN      | json/schema_A_1.csv      | 0        |
| col_date    | DATE          | True     | $1:col_date::DATE         | json/schema_A_1.csv      | 1        |
| col_ts      | TIMESTAMP_NTZ | True     | $1:col_ts::TIMESTAMP_NTZ  | json/schema_A_1.csv      | 2        |
+-------------+---------------+----------+---------------------------+--------------------------+----------+

-- Load the CSV file using MATCH_BY_COLUMN_NAME.
COPY into mytable from @mystage/csv/' FILE_FORMAT = (FORMAT_NAME= 'my_csv_format') MATCH_BY_COLUMN_NAME=CASE_INSENSITIVE;
Copy