- Catégories :
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. La prise en charge des fichiers JSON et CSV est actuellement disponible en avant-première.
Syntaxe¶
INFER_SCHEMA(
LOCATION => '{ internalStage | externalStage }'
, FILE_FORMAT => '<file_format_name>'
, FILES => '<file_name>' [ , '<file_name>' ] [ , ... ]
, IGNORE_CASE => TRUE | FALSE
)
Où :
internalStage ::= @[<namespace>.]<int_stage_name>[/<path>][/<filename>] | @~[/<path>][/<filename>]externalStage ::= @[<namespace>.]<ext_stage_name>[/<path>][/<filename>]
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.
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 |
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 |
+-------------+---------+----------+---------------------+--------------------------+----------+
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 |
+-------------+---------+----------+---------------------+--------------------------+----------+
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 |
+-------------+---------+----------+---------------------+---------------------------------------------+----------+
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 |
+-------------+---------------+----------+---------------------------+--------------------------+----------+
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' ) ));
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;