Transformation des données lors d’un chargement¶

Formats de fichiers 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 :

Fonctions prises en charge¶

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

• ARRAY_CONSTRUCT

• ARRAY_SIZE

• ASCII

• CASE

• CAST , ::

• CEIL

• CHECK_JSON

• CHECK_XML

• CHR , CHAR

• CONCAT , ||

• CONVERT_TIMEZONE

• ENDSWITH

• EQUAL_NULL

• FLOOR

• GET

• GET_PATH , :

• HEX_DECODE_STRING

• HEX_ENCODE

• IFF

• IFNULL

• [ NOT ] ILIKE

• [ NOT ] IN

• IS_ARRAY

• IS_BOOLEAN

• IS_DECIMAL

• IS_INTEGER

• IS_NULL_VALUE

• IS_OBJECT

• IS_TIME

• IS_TIMESTAMP_*

• LENGTH, LEN

• [ NOT ] LIKE

• LPAD

• LTRIM

• MD5 , MD5_HEX

• NULLIF

• NVL

• NVL2

• OBJECT_CONSTRUCT

• PARSE_IP

• PARSE_JSON

• PARSE_URL

• PARSE_XML

• RANDOM

• REGEXP_REPLACE

• REGEXP_SUBSTR

• REPLACE

• REVERSE

• RPAD

• RTRIM

• SPLIT

• SPLIT_PART

• STARTSWITH

• SUBSTR , SUBSTRING

• TO_ARRAY

• TO_BINARY

• TO_BOOLEAN

• TO_CHAR , TO_VARCHAR

• TO_DATE , DATE

  Notez que lorsque cette fonction est utilisée pour convertir explicitement une valeur, ni l’option de format de fichier DATE_FORMAT ni le paramètre DATE_INPUT_FORMAT n’est appliqué.

• TO_DECIMAL , TO_NUMBER , TO_NUMERIC

• TO_DOUBLE

• TO_OBJECT

• TO_TIME , TIME

  Notez que lorsque cette fonction est utilisée pour convertir explicitement une valeur, ni l’option de format de fichier TIME_FORMAT ni le paramètre TIME_INPUT_FORMAT n’est appliqué.

• TO_TIMESTAMP / TO_TIMESTAMP_*

  Notez que lorsque cette fonction est utilisée pour convertir explicitement une valeur, ni l’option de format de fichier TIMESTAMP_FORMAT ni le paramètre TIMESTAMP_INPUT_FORMAT n’est appliqué.

• TO_VARIANT

• TRIM

• TRY_CAST

• TRY_HEX_DECODE_STRING

• TRY_TO_BINARY

• TRY_TO_BOOLEAN

• TRY_TO_DATE

  Notez que la commande COPY INTO <table> ne prend pas en charge l’argument facultatif format pour cette fonction.

• TRY_TO_DECIMAL, TRY_TO_NUMBER, TRY_TO_NUMERIC

• TRY_TO_DOUBLE

• TRY_TO_TIME

  Notez que la commande COPY INTO <table> ne prend pas en charge l’argument facultatif format pour cette fonction.

• TRY_TO_TIMESTAMP / TRY_TO_TIMESTAMP_*

  Notez que la commande COPY INTO <table> ne prend pas en charge l’argument facultatif format pour cette fonction.

• UNICODE

• UUID_STRING

• XMLGET

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 :

• SQL UDFs scalaires.

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.

Chargement d’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éorganisation des colonnes CSV lors d’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

Conversion des types de données lors d’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

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

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

Chargement de données semi-structurées dans des colonnes distinctes¶

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

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

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

Chargement de données Parquet dans des colonnes distinctes¶

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

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

Fractionnement d’éléments semi-structurés et leur chargement sous forme de valeurs VARIANT dans des colonnes distinctes¶

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