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 diesem Feature müssen Sie keine temporären Tabellen zum Speichern vortransformierter Daten verwenden, wenn Sie Spalten während des Datenladens neu anordnen. Dieses Feature ist sowohl für das Massenladen als auch für Snowpipe verfügbar.

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, die Textzeichenfolgen kürzen kann, die die Länge der Zielspalte überschreiten.

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

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.

Unterstützte Funktionen

Snowflake unterstützt derzeit die folgenden Funktionen für COPY-Transformationen:

Beachten Sie insbesondere, dass die Funktion VALIDATE die SELECT-Liste in einer COPY INTO <Tabelle>-Anweisung ignoriert. Die Funktion analysiert die Dateien, auf die in der Anweisung verwiesen wird, und gibt alle Parsing-Fehler zurück. Dieses Verhalten kann überraschend sein, wenn erwartet wird, dass die Funktion die Dateien im Kontext der COPY INTO <Tabelle>-Ausdrücke auswertet.

Beachten Sie, dass COPY-Transformationen die Funktion FLATTEN bzw. die JOIN- oder die (aggregierte) GROUP BY-Syntax nicht unterstützen:

Die Liste der unterstützten Funktionen kann sich im Laufe der Zeit erweitern.

Folgende Funktionskategorien werden ebenfalls unterstützt:

Bemerkung

Für Scalar SQL-UDFs bietet Snowflake nur eine begrenzte Unterstützung für die Behandlung von Transformationsfehlern, sodass es zu einem inkonsistenten oder unerwarteten Verhalten der ON_ERROR-Kopieroptionen kommen kann.

Filtern der Ergebnisse

Das Filtern der Ergebnisse einer FROM-Klausel mit einer WHERE-Klausel wird nicht unterstützt. Die Schlüsselwörter ORDER BY, LIMIT, FETCH, TOP werden in SELECT-Anweisungen ebenfalls 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.

Parameter VALIDATION_MODE

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

Standardspaltenwerte CURRENT_TIME, CURRENT_TIMESTAMP

Anstelle der Verwendung der Werte der Standardspalten CURRENT_TIME, CURRENT_TIMESTAMP zum Erfassen der Ladezeit empfehlen wir Ihnen, METADATA$START_SCAN_TIME abzufragen, um einen genauen Zeitwert für das Laden der Datensätze zu erhalten. Weitere Informationen dazu finden Sie unter Abfragen von Metadaten für bereitgestellte Dateien.

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

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

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
Copy

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

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

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

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

Mit den folgenden SQL-Anweisungen wird die Datei sales.json aus dem internen Stagingbereich mystage geladen:

Bemerkung

In diesem Beispiel werden JSON-Daten geladen, aber die SQL-Anweisungen sind ähnlich wie beim Laden semistrukturierter Daten anderer Typen (z. B. Avro, ORC).

Ein weiteres Beispiel für die Verwendung von Parquet-Daten finden Sie unter Parquet-Daten in separate Spalten laden (unter diesem Thema).

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

Parquet-Daten in separate Spalten laden

Dieses Beispiel ist ähnlich dem vorigen Beispiel, jedoch werden semistrukturierte Daten aus einer Datei im Parquet-Format geladen. Dieses Beispiel ist für Benutzer gedacht, die mit Apache Parquet vertraut sind:

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

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

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"    |
| ]        | ]        |
+----------+----------+
Copy
Sprache: Deutsch