Transformation des données pendant un chargement

Snowflake prend en charge la transformation des données tout en les chargeant dans une table à l’aide de la commande COPY INTO <table>, simplifiant considérablement votre pipeline ETL pour des transformations de base. Cette fonction vous évite l’utilisation de tables temporaires pour stocker des données pré-transformées lors de la réorganisation des colonnes au cours d’un chargement de données. Cette fonction s’applique à la fois au chargement en lot et à Snowpipe.

La commande COPY prend en charge les opérations suivantes :

  • Réorganisation des colonnes, omission de colonnes et conversions à l’aide d’une instruction SELECT. Il n’est pas nécessaire que vos fichiers de données aient le même nombre et le même ordre de colonnes que votre table cible.

  • L’option ENFORCE_LENGTH | TRUNCATECOLUMNS, qui permet de tronquer les chaînes de texte qui dépassent la longueur de colonne cible.

Pour obtenir des informations générales sur l’interrogation de fichiers de données préparés, voir Interrogation des données dans des fichiers mis en zone de préparation.

Dans ce chapitre :

Notes sur l’utilisation

Cette section fournit des informations d’utilisation pour transformer des fichiers de données de zone de préparation au cours d’un chargement.

Formats de fichier pris en charge

Les types de formats de fichiers suivants sont pris en charge pour les transformations COPY :

  • CSV

  • JSON

  • Avro

  • ORC

  • Parquet

  • XML

Pour analyser un fichier de données de zone de préparation, il est nécessaire de décrire son format de fichier :

CSV

Le format par défaut est le texte UTF-8 délimité par des caractères. Le délimiteur de champ par défaut est un caractère de virgule (,). Le délimiteur d’enregistrement par défaut est le caractère de nouvelle ligne. Si les données sources sont dans un autre format, spécifiez le type de format de fichier et les options.

Lors de l’interrogation de fichiers de données en zone de préparation, l’option ERROR_ON_COLUMN_COUNT_MISMATCH est ignorée. Il n’est pas nécessaire que vos fichiers de données aient le même nombre et le même ordre de colonnes que votre table cible.

JSON

Pour transformer des données JSON pendant une opération de chargement, vous devez structurer les fichiers de données au format standard NDJSON (« Newline Delimited JSON ») ; sinon, vous risquez de rencontrer l’erreur suivante :

Error parsing JSON: more than one document in the input

Tous les autres types de formats de fichiers

Spécifiez le type de format et les options qui correspondent à vos fichiers de données.

Pour spécifier explicitement les options de format de fichier, définissez-les via l’une des méthodes suivantes :

Interrogation de fichiers de données de zone de préparation à l’aide d’une instruction SELECT :

  • En tant qu’options de format de fichier spécifiées pour un format de fichier ou un objet de zone de préparation nommé. Le format de fichier/d’objet de zone de préparation nommé peut alors être référencé dans l’instruction SELECT.

Chargement de colonnes à partir de fichiers de données de zone de préparation à l’aide de l’instruction COPY INTO <table> ** :**

  • En tant qu’options de format de fichier spécifiées directement dans l’instruction COPY INTO <table>.

  • En tant qu’options de format de fichier spécifiées pour un format de fichier ou un objet de zone de préparation nommé. Le format de fichier/d’objet de zone de préparation nommé peut alors être référencé dans l’instruction COPY INTO <table>.

Fonctions prises en charge

Snowflake prend actuellement en charge le sous-ensemble de fonctions suivant pour des transformations COPY :

Notez en particulier que la fonction VALIDATE ignore la liste SELECT dans une instruction COPY INTO <table>. La fonction analyse les fichiers référencés dans l’instruction et renvoie toute erreur d’analyse. Ce comportement peut être surprenant si vous vous attendez à ce que la fonction évalue les fichiers dans le contexte des expressions COPY INTO <table>.

Notez que les transformations COPY ne prennent pas en charge la fonction FLATTEN, ni la syntaxe JOIN ou GROUP BY (agrégation) :

La liste des fonctions prises en charge peut s’étendre avec le temps.

Les catégories de fonctions suivantes sont également prises en charge :

Note

Pour les UDFs SQL, Snowflake a un support limité pour la gestion des erreurs de transformation, et vous pouvez rencontrer un comportement incohérent ou inattendu de l’option de copie ON_ERROR.

Filtrage des résultats

Le filtrage des résultats d’une clause FROM à l’aide d’une clause WHERE n’est pas pris en charge. Les mots-clés ORDER BY, LIMIT,FETCH,TOP dans les instructions SELECT ne sont pas non plus pris en charge.

Le mot clé DISTINCT dans les instructions SELECT n’est pas totalement pris en charge. La spécification du mot clé peut entraîner un comportement incohérent ou inattendu de l’option de copie ON_ERROR.

Paramètre VALIDATION_MODE

Le paramètre VALIDATION_MODE ne prend pas en charge les instructions COPY qui transforment les données pendant un chargement.

Valeurs de colonne par défaut CURRENT_TIME, CURRENT_TIMESTAMP

Au lieu d’utiliser les valeurs par défaut des colonnes CURRENT_TIME, CURRENT_TIMESTAMP pour saisir le temps de chargement, nous vous recommandons d’interroger METADATA$START_SCAN_TIME pour obtenir une valeur temporelle précise du chargement de l’enregistrement. Pour plus d’informations, reportez-vous à Interrogation de métadonnées de fichiers préparés.

Transformation des données CSV

Charger un sous-ensemble de données de table

Chargez un sous-ensemble de données dans une table. Pour toute colonne manquante, Snowflake insère les valeurs par défaut. L’exemple suivant charge les données des colonnes 1, 2, 6 et 7 d’un fichier CSV de zone de préparation :

copy into home_sales(city, zip, sale_date, price)
   from (select t.$1, t.$2, t.$6, t.$7 from @mystage/sales.csv.gz t)
   FILE_FORMAT = (FORMAT_NAME = mycsvformat);
Copy

Réorganiser les colonnes CSV pendant un chargement

L’exemple suivant réorganise les données d’une colonne à partir d’un fichier CSV de zone de préparation avant de le charger dans une table. De plus, l’instruction COPY utilise la fonction SUBSTR , SUBSTRING pour supprimer les premiers caractères d’une chaîne avant de l’insérer :

copy into home_sales(city, zip, sale_date, price)
   from (select SUBSTR(t.$2,4), t.$1, t.$5, t.$4 from @mystage t)
   FILE_FORMAT = (FORMAT_NAME = mycsvformat);
Copy

Convertir des types de données pendant un chargement

Convertissez les données de la zone de préparation en d’autres types de données au cours d’un chargement de données. Toutes les fonctions de conversion sont prises en charge.

Par exemple, convertissez les chaînes en tant que caractères en valeurs binaires, en décimales ou en horodatages en utilisant les fonctions TO_BINARY, TO_DECIMAL , TO_NUMBER , TO_NUMERIC et TO_TIMESTAMP / TO_TIMESTAMP_*, respectivement.

Exemple de fichier CSV :

snowflake,2.8,2016-10-5
warehouse,-12.3,2017-01-23
Copy

Instructions SQL :

-- Stage a data file in the internal user stage
PUT file:///tmp/datafile.csv @~;

-- Query the staged data file
select t.$1,t.$2,t.$3 from @~/datafile.csv.gz t;

-- Create the target table
create or replace table casttb (
  col1 binary,
  col2 decimal,
  col3 timestamp_ntz
  );

-- Convert the staged CSV column data to the specified data types before loading it into the destination table
copy into casttb(col1, col2, col3)
from (
  select to_binary(t.$1, 'utf-8'),to_decimal(t.$2, '99.9', 9, 5),to_timestamp_ntz(t.$3)
  from @~/datafile.csv.gz t
)
file_format = (type = csv);

-- Query the target table
select * from casttb;

+--------------------+------+-------------------------+
| COL1               | COL2 | COL3                    |
|--------------------+------+-------------------------|
| 736E6F77666C616B65 |    3 | 2016-10-05 00:00:00.000 |
| 77617265686F757365 |  -12 | 2017-01-23 00:00:00.000 |
+--------------------+------+-------------------------+
Copy

Inclure des colonnes de séquence dans les données chargées

Créez un objet de séquence à l’aide de CREATE SEQUENCE. Lors du chargement de données dans une table à l’aide de la commande COPY, accédez à l’objet en utilisant une expression NEXTVAL pour séquencer les données dans une colonne de nombre cible. Pour plus d’informations sur l’utilisation des séquences dans les requêtes, voir Utilisation de séquences.

-- Create a sequence
create sequence seq1;

-- Create the target table
create or replace table mytable (
  col1 number default seq1.nextval,
  col2 varchar,
  col3 varchar
  );

-- Stage a data file in the internal user stage
PUT file:///tmp/myfile.csv @~;

-- Query the staged data file
select $1, $2 from @~/myfile.csv.gz t;

+-----+-----+
| $1  | $2  |
|-----+-----|
| abc | def |
| ghi | jkl |
| mno | pqr |
| stu | vwx |
+-----+-----+

-- Include the sequence nextval expression in the COPY statement
copy into mytable (col1, col2, col3)
from (
  select seq1.nextval, $1, $2
  from @~/myfile.csv.gz t
)
;

select * from mytable;

+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
|    1 | abc  | def  |
|    2 | ghi  | jkl  |
|    3 | mno  | pqr  |
|    4 | stu  | vwx  |
+------+------+------+
Copy

Inclure des colonnes AUTOINCREMENT / IDENTITY dans les données chargées

Définissez la valeur par défaut AUTOINCREMENT ou IDENTITY pour une colonne de nombre. Lorsque vous chargez des données dans une table à l’aide de la commande COPY, omettez la colonne dans l’instruction SELECT. L’instruction remplit automatiquement la colonne.

-- Create the target table
create or replace table mytable (
  col1 number autoincrement start 1 increment 1,
  col2 varchar,
  col3 varchar
  );

-- Stage a data file in the internal user stage
PUT file:///tmp/myfile.csv @~;

-- Query the staged data file
select $1, $2 from @~/myfile.csv.gz t;

+-----+-----+
| $1  | $2  |
|-----+-----|
| abc | def |
| ghi | jkl |
| mno | pqr |
| stu | vwx |
+-----+-----+

-- Omit the sequence column in the COPY statement
copy into mytable (col2, col3)
from (
  select $1, $2
  from @~/myfile.csv.gz t
)
;

select * from mytable;

+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
|    1 | abc  | def  |
|    2 | ghi  | jkl  |
|    3 | mno  | pqr  |
|    4 | stu  | vwx  |
+------+------+------+
Copy

Transformation de données semi-structurées

Les exemples de cette section s’appliquent à tout type de données semi-structurées, sauf indication contraire.

Chargement de données semi-structurées dans des colonnes séparées

L’exemple suivant charge des éléments répétitifs d’un fichier semi-structuré en zone de préparation dans des colonnes de table séparées avec différents types de données.

Dans cet exemple, nous chargeons les données semi-structurées suivantes dans des colonnes séparées d’une table relationnelle, avec les valeurs d’objets location étant chargées dans une colonne VARIANT et les autres valeurs dans des colonnes relationnelles :

-- Sample data:
{"location": {"city": "Lexington","zip": "40503"},"dimensions": {"sq_ft": "1000"},"type": "Residential","sale_date": "4-25-16","price": "75836"},
{"location": {"city": "Belmont","zip": "02478"},"dimensions": {"sq_ft": "1103"},"type": "Residential","sale_date": "6-18-16","price": "92567"},
{"location": {"city": "Winchester","zip": "01890"},"dimensions": {"sq_ft": "1122"},"type": "Condo","sale_date": "1-31-16","price": "89921"}
Copy

Les instructions SQL suivantes chargent le fichier sales.json à partir de la zone de préparation interne mystage :

Note

Cet exemple charge des données JSON, mais les instructions SQL sont similaires lors du chargement de données semi-structurées d’autres types (par exemple, Avro, ORC, etc.).

Pour un autre exemple d’utilisation des données Parquet, voir Chargement des données Parquet dans des colonnes séparées (dans ce chapitre).

 -- Create an internal stage with the file type set as JSON.
 CREATE OR REPLACE STAGE mystage
   FILE_FORMAT = (TYPE = 'json');

 -- Stage a JSON data file in the internal stage.
 PUT file:///tmp/sales.json @mystage;

 -- Query the staged data. The data file comprises three objects in NDJSON format.
 SELECT t.$1 FROM @mystage/sales.json.gz t;

 +------------------------------+
 | $1                           |
 |------------------------------|
 | {                            |
 |   "dimensions": {            |
 |     "sq_ft": "1000"          |
 |   },                         |
 |   "location": {              |
 |     "city": "Lexington",     |
 |     "zip": "40503"           |
 |   },                         |
 |   "price": "75836",          |
 |   "sale_date": "2022-08-25", |
 |   "type": "Residential"      |
 | }                            |
 | {                            |
 |   "dimensions": {            |
 |     "sq_ft": "1103"          |
 |   },                         |
 |   "location": {              |
 |     "city": "Belmont",       |
 |     "zip": "02478"           |
 |   },                         |
 |   "price": "92567",          |
 |   "sale_date": "2022-09-18", |
 |   "type": "Residential"      |
 | }                            |
 | {                            |
 |   "dimensions": {            |
 |     "sq_ft": "1122"          |
 |   },                         |
 |   "location": {              |
 |     "city": "Winchester",    |
 |     "zip": "01890"           |
 |   },                         |
 |   "price": "89921",          |
 |   "sale_date": "2022-09-23", |
 |   "type": "Condo"            |
 | }                            |
 +------------------------------+

 -- Create a target table for the data.
 CREATE OR REPLACE TABLE home_sales (
   CITY VARCHAR,
   POSTAL_CODE VARCHAR,
   SQ_FT NUMBER,
   SALE_DATE DATE,
   PRICE NUMBER
 );

 -- Copy elements from the staged file into the target table.
 COPY INTO home_sales(city, postal_code, sq_ft, sale_date, price)
 FROM (select
 $1:location.city::varchar,
 $1:location.zip::varchar,
 $1:dimensions.sq_ft::number,
 $1:sale_date::date,
 $1:price::number
 FROM @mystage/sales.json.gz t);

 -- Query the target table.
 SELECT * from home_sales;

+------------+-------------+-------+------------+-------+
| CITY       | POSTAL_CODE | SQ_FT | SALE_DATE  | PRICE |
|------------+-------------+-------+------------+-------|
| Lexington  | 40503       |  1000 | 2022-08-25 | 75836 |
| Belmont    | 02478       |  1103 | 2022-09-18 | 92567 |
| Winchester | 01890       |  1122 | 2022-09-23 | 89921 |
+------------+-------------+-------+------------+-------+
Copy

Chargement de données Parquet dans des colonnes séparées

Similaire à l’exemple précédent, mais charge des données semi-structurées à partir d’un fichier au format Parquet. Cet exemple est fourni pour les utilisateurs qui sont familiers avec Apache Parquet :

-- Create a file format object that sets the file format type. Accept the default options.
create or replace file format my_parquet_format
  type = 'parquet';

-- Create an internal stage and specify the new file format
create or replace temporary stage mystage
  file_format = my_parquet_format;

-- Create a target table for the data.
create or replace table parquet_col (
  custKey number default NULL,
  orderDate date default NULL,
  orderStatus varchar(100) default NULL,
  price varchar(255)
);

-- Stage a data file in the internal stage
put file:///tmp/mydata.parquet @mystage;

-- Copy data from elements in the staged Parquet file into separate columns
-- in the target table.
-- Note that all Parquet data is stored in a single column ($1)
-- SELECT list items correspond to element names in the Parquet file
-- Cast element values to the target column data type
copy into parquet_col
  from (select
  $1:o_custkey::number,
  $1:o_orderdate::date,
  $1:o_orderstatus::varchar,
  $1:o_totalprice::varchar
  from @mystage/mydata.parquet);

-- Query the target table
SELECT * from parquet_col;

+---------+------------+-------------+-----------+
| CUSTKEY | ORDERDATE  | ORDERSTATUS | PRICE     |
|---------+------------+-------------+-----------|
|   27676 | 1996-09-04 | O           | 83243.94  |
|  140252 | 1994-01-09 | F           | 198402.97 |
...
+---------+------------+-------------+-----------+
Copy

Aplatir des données semi-structurées

FLATTEN est une fonction de table qui produit une vue latérale d’une colonne VARIANT, OBJECT ou ARRAY. En utilisant les données d’exemple de la section Chargement de données semi-structurées dans des colonnes séparées, créez une table avec une ligne séparée pour chaque élément dans les objets.

-- Create an internal stage with the file delimiter set as none and the record delimiter set as the new line character
create or replace stage mystage
  file_format = (type = 'json');

-- Stage a JSON data file in the internal stage with the default values
put file:///tmp/sales.json @mystage;

-- Create a table composed of the output from the FLATTEN function
create or replace table flattened_source
(seq string, key string, path string, index string, value variant, element variant)
as
  select
    seq::string
  , key::string
  , path::string
  , index::string
  , value::variant
  , this::variant
  from @mystage/sales.json.gz
    , table(flatten(input => parse_json($1)));

  select * from flattened_source;

+-----+-----------+-----------+-------+-------------------------+-----------------------------+
| SEQ | KEY       | PATH      | INDEX | VALUE                   | ELEMENT                     |
|-----+-----------+-----------+-------+-------------------------+-----------------------------|
| 1   | location  | location  | NULL  | {                       | {                           |
|     |           |           |       |   "city": "Lexington",  |   "location": {             |
|     |           |           |       |   "zip": "40503"        |     "city": "Lexington",    |
|     |           |           |       | }                       |     "zip": "40503"          |
|     |           |           |       |                         |   },                        |
|     |           |           |       |                         |   "price": "75836",         |
|     |           |           |       |                         |   "sale_date": "2017-3-5",  |
|     |           |           |       |                         |   "sq__ft": "1000",         |
|     |           |           |       |                         |   "type": "Residential"     |
|     |           |           |       |                         | }                           |
...
| 3   | type      | type      | NULL  | "Condo"                 | {                           |
|     |           |           |       |                         |   "location": {             |
|     |           |           |       |                         |     "city": "Winchester",   |
|     |           |           |       |                         |     "zip": "01890"          |
|     |           |           |       |                         |   },                        |
|     |           |           |       |                         |   "price": "89921",         |
|     |           |           |       |                         |   "sale_date": "2017-3-21", |
|     |           |           |       |                         |   "sq__ft": "1122",         |
|     |           |           |       |                         |   "type": "Condo"           |
|     |           |           |       |                         | }                           |
+-----+-----------+-----------+-------+-------------------------+-----------------------------+
Copy

Fractionner les éléments semi-structurés et charger comme valeurs VARIANT dans des colonnes séparées

En suivant les instructions de la section Chargement de données semi-structurées dans des colonnes séparées, vous pouvez charger des éléments individuels depuis des données semi-structurées dans différentes colonnes de votre table cible. De plus, en utilisant la fonction SPLIT, vous pouvez diviser les valeurs des éléments qui contiennent un séparateur et les charger sous forme de tableau.

Par exemple, divisez les adresses IP sur le séparateur de point en éléments répétitifs. Chargez les adresses IP sous forme de tableaux dans des colonnes séparées :

-- Create an internal stage with the file delimiter set as none and the record delimiter set as the new line character
create or replace stage mystage
  file_format = (type = 'json');

-- Stage a semi-structured data file in the internal stage
put file:///tmp/ipaddress.json @mystage auto_compress=true;

-- Query the staged data
select t.$1 from @mystage/ipaddress.json.gz t;

+----------------------------------------------------------------------+
| $1                                                                   |
|----------------------------------------------------------------------|
| {"ip_address": {"router1": "192.168.1.1","router2": "192.168.0.1"}}, |
| {"ip_address": {"router1": "192.168.2.1","router2": "192.168.3.1"}}  |
+----------------------------------------------------------------------+

-- Create a target table for the semi-structured data
create or replace table splitjson (
  col1 array,
  col2 array
  );

-- Split the elements into individual arrays using the SPLIT function and load them into separate columns
-- Note that all JSON data is stored in a single column ($1)
copy into splitjson(col1, col2)
from (
  select split($1:ip_address.router1, '.'),split($1:ip_address.router2, '.')
  from @mystage/ipaddress.json.gz t
);

-- Query the target table
select * from splitjson;

+----------+----------+
| COL1     | COL2     |
|----------+----------|
| [        | [        |
|   "192", |   "192", |
|   "168", |   "168", |
|   "1",   |   "0",   |
|   "1"    |   "1"    |
| ]        | ]        |
| [        | [        |
|   "192", |   "192", |
|   "168", |   "168", |
|   "2",   |   "3",   |
|   "1"    |   "1"    |
| ]        | ]        |
+----------+----------+
Copy