Catégories :

Fonctions de table

INFER_SCHEMA

Détecte automatiquement le schéma de métadonnées de fichier dans un ensemble de fichiers de données mis en zone de préparation qui contiennent des données semi-structurées et récupère les définitions de colonnes.

La fonction GENERATE_COLUMN_DESCRIPTION s’appuie sur la sortie de la fonction INFER_SCHEMA pour simplifier la création de nouvelles tables, de tables externes ou de vues (en utilisant la commande CREATE <objet> appropriée) sur la base des définitions de colonnes des fichiers en zone de préparation.

La commande CREATE TABLE ou CREATE EXTERNAL TABLE avec la clause USING TEMPLATE peut être exécutée pour créer une nouvelle table ou table externe avec les définitions de colonnes dérivées de la sortie de la fonction INFER_SCHEMA.

Note

Cette fonction prend en charge les fichiers Apache Parquet, Apache Avro, ORC, JSON et CSV.

Syntaxe

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

Où :

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

Arguments

LOCATION => '...'

Nom de la zone de préparation interne ou externe où les fichiers sont stockés. Vous pouvez inclure un chemin d’accès à un ou plusieurs fichiers dans l’emplacement de stockage dans le Cloud ; sinon, la fonction INFER_SCHEMA analyse les fichiers dans tous les sous-répertoires de la zone de préparation :

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

Les fichiers se trouvent dans la zone de préparation interne nommée spécifiée.

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

Les fichiers se trouvent dans la zone de préparation externe nommée spécifiée.

@~[/path][/filename]

Les fichiers sont mis en zone de préparation pour l’utilisateur actuel.

Note

Cette fonction SQL ne prend en charge que les zones de préparation nommées (internes ou externes) et les zones de préparation utilisateur. Elle ne prend pas en charge les zones de préparation de table.

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

Spécifie une liste d’un ou plusieurs fichiers (séparés par des virgules) dans un ensemble de fichiers en zone de préparation qui contiennent des données semi-structurées. Les fichiers doivent déjà avoir été mis en zone de préparation dans l’emplacement interne de Snowflake ou dans l’emplacement externe spécifié dans la commande. Si l’un des fichiers spécifiés ne peut être trouvé, la requête sera abandonnée.

Le nombre maximum de noms de fichiers pouvant être spécifiés est de 1 000.

Note

Pour les zones de préparation externes seulement (c.-à-d. Amazon S3, Google Cloud Storage ou Microsoft Azure), le chemin du fichier est défini en concaténant l’URL dans la définition de la zone de préparation et la liste des noms de fichiers résolus.

Cependant, Snowflake n’insère pas implicitement un séparateur entre le chemin et le nom du fichier. Vous devez explicitement inclure un séparateur (/) soit à la fin de l’URL dans la définition de la zone de préparation, soit au début de chaque nom de fichier spécifié dans ce paramètre.

FILE_FORMAT => '<file_format_name>'

Nom de l’objet de format de fichier qui décrit les données contenues dans les fichiers en zone de préparation. Pour plus d’informations, voir CREATE FILE FORMAT.

IGNORE_CASE => TRUE | FALSE

Spécifie si les noms de colonnes détectés à partir de fichiers en zone de préparation sont traités en respectant la casse. Par défaut, la valeur est FALSE, ce qui signifie que Snowflake préserve la casse des caractères alphabétiques lors de la récupération des noms de colonnes. Si vous spécifiez la valeur TRUE, les noms de colonnes sont traités sans tenir compte de la casse et tous les noms de colonnes sont récupérés en lettres majuscules.

MAX_FILE_COUNT => <num>

Spécifie le nombre maximum de fichiers analysés depuis la zone de préparation. Cette option est recommandée pour un grand nombre de fichiers dont le schéma est identique d’un fichier à l’autre. Notez que cette option ne permet pas de déterminer les fichiers à analyser. Si vous souhaitez analyser des fichiers spécifiques, utilisez plutôt l’option FILES.

MAX_RECORDS_PER_FILE => <num>

Spécifie le nombre maximum d’enregistrements numérisés par fichier. Cette option ne s’applique qu’aux fichiers CSV et JSON. Il est recommandé d’utiliser cette option pour les fichiers volumineux. Notez toutefois que cette option peut affecter la précision de la détection des schémas.

Sortie

La fonction renvoie les colonnes suivantes :

Nom de la colonne

Type de données

Description

COLUMN_NAME

TEXT

Nom d’une colonne dans les fichiers en zone de préparation.

TYPE

TEXT

Type de données de la colonne.

NULLABLE

BOOLEAN

Spécifie si les lignes de la colonne peuvent stocker NULL au lieu d’une valeur. Actuellement, la nullité déduite d’une colonne peut s’appliquer à un fichier de données, mais pas aux autres dans le jeu analysé.

EXPRESSION

TEXT

Expression de la colonne au format $1:COLUMN_NAME::TYPE (principalement pour les tables externes). Si IGNORE_CASE est défini sur TRUE, l’expression de la colonne sera au format GET_IGNORE_CASE ($1, COLUMN_NAME)::TYPE.

FILENAMES

TEXT

Noms des fichiers qui contiennent la colonne.

ORDER_ID

NUMBER

Ordre des colonnes dans les fichiers en zone de préparation.

Notes sur l’utilisation

  • Pour les fichiers CSV, les noms des colonnes peuvent être définis en utilisant l’option de format de fichier PARSE_HEADER = [ TRUE | FALSE ].

    • Si l’option est définie sur TRUE, les en-têtes de la première ligne seront utilisés pour déterminer les noms des colonnes.

    • La valeur par défaut FALSE renvoie les noms de colonnes sous la forme c , où est la position de la colonne. Notez que l’option SKIP_HEADER n’est pas prise en charge avec PARSE_HEADER = TRUE.

  • Pour le chargement des fichiers CSV, l’option de copie MATCH_BY_COLUMN_NAME est disponible en avant-première. Elle nécessite l’utilisation de l’option de format de fichier CSV mentionnée ci-dessus PARSE_HEADER = TRUE.

  • Pour les fichiers CSV et JSON, les options de format de fichier suivantes ne sont actuellement pas prises en charge : DATE_FORMAT, TIME_FORMAT et TIMESTAMP_FORMAT.

  • L’option de format de fichier TRIM_SPACE JSON n’est pas prise en charge.

  • Les annotations scientifiques (par exemple 1E2) dans les fichiers JSON sont récupérées en tant que type de données REAL.

  • Toutes les variantes des types de données d’horodatage sont récupérées sous la forme de TIMESTAMP_NTZ sans aucune information sur le fuseau horaire.

  • Pour les fichiers CSV et JSON, toutes les colonnes sont identifiées comme NULLABLE.

Exemples

Récupérez les définitions de colonnes pour les fichiers Parquet dans la zone de préparation 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

Similaire à l’exemple précédent, mais spécifiez un seul fichier Parquet dans la zone de préparation mystage :

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

Récupérez les définitions de colonnes pour les fichiers Parquet dans la zone de préparation mystage avec IGNORE_CASE défini sur TRUE. Dans la sortie renvoyée, tous les noms de colonnes sont récupérés en lettres majuscules.

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

Récupérez les définitions de colonnes pour les fichiers JSON dans la zone de préparation 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

Crée une table en utilisant le schéma détecté à partir de fichiers JSON en zone de préparation.

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

Note

L’utilisation de * pour ARRAY_AGG(OBJECT_CONSTRUCT()) peut entraîner une erreur si le résultat renvoyé est supérieur à 16MB. Il est recommandé d’éviter d’utiliser * pour les jeux de résultats plus importants et de n’utiliser que les colonnes nécessaires, COLUMN NAME, TYPE, et NULLABLE, pour la requête. La colonne facultative ORDER_ID peut être incluse lorsque vous utilisez WITHIN GROUP (ORDER BY order_id).

Récupérez les définitions des colonnes des fichiers CSV dans la zone de préparation mystage et chargez les fichiers CSV à l’aide de 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