Transformieren von Daten während eines Ladevorgangs

Snowflake unterstützt die Transformation von Daten beim Laden in eine Tabelle mit dem Befehl COPY INTO <Tabelle> und vereinfacht damit Ihre ETL-Pipeline für grundlegende Transformationen erheblich. Mit dieser Funktion müssen Sie keine temporären Tabellen zum Speichern vortransformierter Daten verwenden, wenn Sie Spalten während des Datenladens neu anordnen.

Der Befehl COPY unterstützt Folgendes:

  • Spaltenneuordnung, Spaltenauslassung und Ausgaben unter Verwendung einer SELECT-Anweisung. Es ist nicht erforderlich, dass Ihre Datendateien die gleiche Anzahl und Reihenfolge der Spalten aufweisen wie Ihre Zieltabelle.

  • Die Option ENFORCE_LENGTH | TRUNCATECOLUMNS zum Kürzen von Textzeichenfolgen, die die Länge der Zielspalte überschreiten.

    Derzeit unterstützen diese Kopieroptionen nur CSV-Daten.

Allgemeine Informationen zum Abfragen von Staging-Datendateien finden Sie unter Abfragen von Daten in Stagingdateien.

Unter diesem Thema:

Nutzungshinweise

Dieser Abschnitt enthält Nutzungsinformationen für die Transformation von Staging-Datendateien während des Ladevorgangs.

Unterstützte Stagingbereiche

Für COPY-Transformationen werden nur benannte Stagingbereiche (intern oder extern) und Benutzer-Stagingbereiche unterstützt.

Unterstützte Dateiformate

Die folgenden Dateiformattypen werden für COPY-Transformationen unterstützt:

  • CSV

  • JSON

  • Avro

  • ORC

  • Parquet

  • XML

Um eine Staging-Datendatei zu analysieren, ist es notwendig, ihr Dateiformat zu beschreiben:

CSV

Das Standardformat ist durch Trennzeichen getrennter UTF-8-Text. Das standardmäßige Feldtrennzeichen ist ein Kommazeichen (,). Das standardmäßige Datensatz-Trennzeichen ist das Zeilenumbruchzeichen. Wenn die Quelldaten in einem anderen Format vorliegen, geben Sie den Dateiformattyp und die Optionen an.

Bei der Abfrage von Staging-Datendateien wird die Option ERROR_ON_COLUMN_COUNT_MISMATCH ignoriert. Es ist nicht erforderlich, dass die Datendateien die gleiche Anzahl und Reihenfolge der Spalten aufweisen wie die Zieltabelle.

JSON

Um JSON-Daten während einer Ladeoperation zu transformieren, müssen Sie die Datendateien im Standardformat NDJSON („Newline Delimited JSON“) strukturieren. Andernfalls könnte der folgende Fehler auftreten:

Error parsing JSON: more than one document in the input

Um die Fehlerprüfung zu nutzen, stellen Sie CSV als Formattyp (Standardwert) ein. Ähnlich wie bei CSV ist bei ndjson-compliant Daten jede Zeile ein eigener Datensatz. Snowflake analysiert jede Zeile als gültiges JSON-Objekt oder Array.

Geben Sie den folgenden Formattyp und die folgenden Optionen an:

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

Sie können JSON als Formattyp angeben, aber jeder Fehler in der Transformation würde die Operation COPY stoppen, auch wenn Sie die Option ON_ERROR zum Fortsetzen oder Überspringen der Datei einstellen.

Alle anderen Dateiformattypen

Geben Sie den Formattyp und die Optionen an, die zu Ihren Datendateien passen.

Um Dateiformatoptionen explizit anzugeben, legen Sie diese auf eine der folgenden Arten fest:

Abfrage von Staging-Datendateien mit einer SELECT-Anweisung:

  • Als Dateiformatoptionen, die für ein benanntes Dateiformat oder Stagingobjekt angegeben werden. Das benannte Dateiformat/Stagingobjekt kann dann in der SELECT-Anweisung referenziert werden.

Laden von Spalten aus Staging-Datendateien unter Verwendung einer Anweisung COPY INTO <Tabelle> :

  • Als Dateiformatoptionen, die direkt in der COPYINTO <Tabelle>-Anweisung angegeben werden.

  • Als Dateiformatoptionen, die für ein benanntes Dateiformat oder Stagingobjekt angegeben werden. Das benannte Dateiformat/Stagingobjekt kann dann in der COPY INTO <Tabelle>-Anweisung referenziert werden.

Filtern der Ergebnisse

Das Filtern der Ergebnisse einer FROM-Klausel mit einer WHERE-Klausel wird nicht unterstützt.

Das Schlüsselwort DISTINCT in SELECT-Anweisungen wird nicht vollständig unterstützt. Das Angeben des Schlüsselworts kann zu inkonsistentem oder unerwartetem Verhalten der ON_ERROR-Kopieroptionen führen.

VALIDATION_MODE-Parameter

Der VALIDATION_MODE-Parameter unterstützt keine COPY-Anweisungen, bei denen Daten während eines Ladevorgangs transformiert werden.

Standardspaltenwerte CURRENT_TIME, CURRENT_TIMESTAMP

Beim Laden von Daten in eine Tabelle, die die Ladezeit in einer Spalte mit einem Standardwert von CURRENT_TIME() oder CURRENT_TIMESTAMP() erfasst, haben alle Zeilen, die mit einer bestimmten COPY-Anweisung geladen werden, den gleichen Zeitstempelwert. Der Wert zeichnet die Zeit auf, zu der die COPY-Anweisung gestartet wurde.

Beispiel:

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

Transformieren von CSV-Daten

Eine Teilmenge von Tabellendaten laden

Laden Sie eine Teilmenge von Daten in eine Tabelle. Für fehlende Spalten fügt Snowflake die Standardwerte ein. Im folgenden Beispiel werden Daten aus den Spalten 1, 2, 6 und 7 einer bereitgestellten CSV-Datei geladen:

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

CSV-Spalten während eines Ladevorgangs neu ordnen

Das folgende Beispiel ordnet die Spaltendaten aus einer bereitgestellten CSV-Datei neu, bevor sie in eine Tabelle geladen werden. Zusätzlich verwendet die COPY-Anweisung die Funktion SUBSTR , SUBSTRING, um die ersten paar Zeichen einer Zeichenfolge zu entfernen, bevor sie sie einfügt:

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

Datentypen während eines Ladevorgangs konvertieren

Konvertieren Sie im Stagingbereich bereitgestellte Daten während des Datenladens in andere Datentypen. Alle Konvertierungsfunktionen werden unterstützt.

Konvertieren Sie Zeichenfolgen beispielsweise mit den Funktionen TO_BINARY, TO_DECIMAL , TO_NUMBER , TO_NUMERIC und TO_TIMESTAMP / TO_TIMESTAMP_* in Binärwerte, Dezimalzahlen oder Zeitstempel.

Beispiel CSV-Datei:

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

SQL-Anweisungen:

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

Sequenzspalten in geladene Daten einfügen

Erstellen Sie ein Sequenzobjekt mit CREATE SEQUENCE. Wenn Sie Daten mit dem Befehl COPY in eine Tabelle laden, greifen Sie mit einem NEXTVAL-Ausdruck auf das Objekt zu, um die Daten in einer Zielnummernspalte zu sequenzieren. Weitere Informationen zur Verwendung von Sequenzen in Abfragen finden Sie unter Verwenden von Sequenzen.

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

AUTOINCREMENT-/IDENTITY-Spalten in geladene Daten einfügen

Legen Sie den Standardwert AUTOINCREMENT oder IDENTITY für eine Zahlenspalte fest. Wenn Sie Daten mit dem Befehl COPY in eine Tabelle laden, lassen Sie die Spalte in der SELECT-Anweisung weg. Die Anweisung füllt die Spalte automatisch aus.

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

Semistrukturierte Daten transformieren

Die Beispiele in diesem Abschnitt gelten für jeden semistrukturierten Datentyp, sofern nicht anders angegeben.

Semistrukturierte Daten in separate Spalten laden

Im folgenden Beispiel werden sich wiederholende Elemente aus einer semistrukturierten Stagingdatei in separate Tabellenspalten mit unterschiedlichen Datentypen geladen.

In diesem Beispiel werden die folgenden JSON-Daten in separate Spalten einer relationalen Tabelle geladen, wobei die location-Objektwerte in eine VARIANT-Spalte und die restlichen Werte in relationale Spalten geladen werden:

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

In dem Beispiel wird die Datei sales.json aus dem internen Stagingbereich mystage geladen:

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

Semistrukturierte Daten vereinfachen

FLATTEN ist eine Tabellenfunktion, mit der die laterale Ansicht einer VARIANT-, OBJECT- oder ARRAY-Spalte erstellt werden kann. Erstellen Sie unter Verwendung der Beispieldaten aus Semistrukturierte Daten in separate Spalten laden eine Tabelle mit einer separaten Zeile für jedes Element in den Objekten.

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

Semistrukturierte Elemente aufteilen und als VARIANT-Werte in separate Spalten laden

Gemäß den Anweisungen in Semistrukturierte Daten in separate Spalten laden können Sie einzelne Elemente aus semistrukturierten Daten in verschiedene Spalten Ihrer Zieltabelle laden. Darüber hinaus können Sie mit der Funktion SPLIT Elementwerte, die ein Trennzeichen enthalten, aufteilen und als Array laden.

Teilen Sie beispielsweise IP-Adressen im Punkttrenner in sich wiederholende Elemente auf. Laden Sie die IP-Adressen als Arrays in separate Spalten:

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

Parquet-Daten in separate Spalten laden

Im folgenden Beispiel werden Elemente in einer Parquet-Datei in separate Tabellenspalten einer relationalen Tabelle mit unterschiedlichen Datentypen geladen:

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