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.

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.

    Actuellement, ces options de copie prennent uniquement en charge les données CSV.

Pour obtenir des informations générales sur l’interrogation de fichiers de données en zone de préparation, 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.

Zones de préparation prises en charge

Seules les zones de préparation nommées (internes ou externes) et les zones de préparation utilisateur sont prises en charge pour les transformations COPY.

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

Pour profiter de la vérification des erreurs, définissez CSV comme type de format (valeur par défaut). Comme le format CSV, avec des données conformes à ndjson, chaque ligne est un enregistrement séparé. Snowflake analyse chaque ligne comme un objet ou un tableau JSON valide.

Spécifiez le type de format et les options suivants :

type = 'csv' field_delimiter = none record_delimiter = '\\n'

Vous pouvez spécifier JSON comme type de format, mais toute erreur dans la transformation arrêterait l’opération COPY, même si vous définissez l’option ON_ERROR pour continuer ou ignorer le fichier.

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

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.

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

Lors du chargement de données dans une table qui capture le temps de chargement dans une colonne avec une valeur par défaut de CURRENT_TIME() ou CURRENT_TIMESTAMP(), toutes les lignes chargées en utilisant une instruction spécifique COPY ont la même valeur d’horodatage. La valeur enregistre l’heure à laquelle l’instruction COPY a commencé.

Par exemple :

create or replace table mytable(
  c1 timestamp DEFAULT current_timestamp(),
  c2 number
);

copy into mytable(c2)
  from (select t.$1 from @mystage/myfile.csv.gz t);

+-------------------------------+----------+
| C1                            | C2       |
|-------------------------------+----------+
| 2018-09-05 08:58:28.718 -0700 | 1        |
..
| 2018-09-05 08:58:28.718 -0700 | 500      |
+-------------------------------+----------+

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

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

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

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

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

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

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é préparé dans des colonnes de table séparées avec différents types de données.

Dans cet exemple, nous chargeons les données JSON 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"}

L’exemple charge le fichier sales.json à partir de la zone de préparation interne mystage :

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

-- 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": "4-25-16", |
|   "type": "Residential"   |
| }                         |
| {                         |
|   "dimensions": {         |
|     "sq_ft": "1103"       |
|   },                      |
|   "location": {           |
|     "city": "Belmont",    |
|     "zip": "02478"        |
|   },                      |
|   "price": "92567",       |
|   "sale_date": "6-18-16", |
|   "type": "Residential"   |
| }                         |
| {                         |
|   "dimensions": {         |
|     "sq_ft": "1122"       |
|   },                      |
|   "location": {           |
|     "city": "Winchester", |
|     "zip": "01890"        |
|   },                      |
|   "price": "89921",       |
|   "sale_date": "1-31-16", |
|   "type": "Condo"         |
| }                         |
+---------------------------+

-- Create a target table for the JSON data
create or replace table home_sales (
  location variant,
  sq_ft number,
  type string default 'Residential',
  sale_date string,
  price string
);

-- Copy elements from the staged JSON file into the target table
-- Note that all JSON data is stored in a single column ($1)
copy into home_sales(location, sq_ft, sale_date, price)
   from (select $1:location, $1:dimensions.sq_ft, $1:sale_date, $1:price
   from @mystage/sales.json.gz t);

-- Query the target table
SELECT * from home_sales;

+-------------------------+-------+-------------+-----------+-------+
| LOCATION                | SQ_FT | TYPE        | SALE_DATE | PRICE |
|-------------------------+-------+-------------+-----------+-------|
| {                       |  1000 | Residential | 4-25-16   | 75836 |
|   "city": "Lexington",  |       |             |           |       |
|   "zip": "40503"        |       |             |           |       |
| }                       |       |             |           |       |
| {                       |  1103 | Residential | 6-18-16   | 92567 |
|   "city": "Belmont",    |       |             |           |       |
|   "zip": "02478"        |       |             |           |       |
| }                       |       |             |           |       |
| {                       |  1122 | Residential | 1-31-16   | 89921 |
|   "city": "Winchester", |       |             |           |       |
|   "zip": "01890"        |       |             |           |       |
| }                       |       |             |           |       |
+-------------------------+-------+-------------+-----------+-------+

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

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

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

L’exemple suivant charge les éléments d’un fichier Parquet dans des colonnes de table séparées dans une table relationnelle avec différents types de données :

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