Transformação de dados durante um carregamento¶
Snowflake oferece suporte à transformação de dados enquanto os carrega em uma tabela usando o comando COPY INTO <tabela>, simplificando drasticamente seu pipeline ETL para transformações básicas. Este recurso ajuda a evitar o uso de tabelas temporárias para armazenar dados pré-transformados ao reordenar colunas durante um carregamento de dados. Este recurso se aplica tanto ao carregamento em massa quanto ao Snowpipe.
O comando COPY oferece suporte a:
Reordenação de colunas, omissão de colunas e conversões usando uma instrução SELECT. Não há exigência de que seus arquivos de dados tenham o mesmo número e ordenação de colunas que sua tabela de destino.
A opção ENFORCE_LENGTH | TRUNCATECOLUMNS, que pode truncar cadeias de caracteres de texto que excedam o comprimento da coluna de destino.
Para obter mais informações gerais sobre como consultar arquivos de dados preparados, consulte Consulta de dados em arquivos preparados.
Neste tópico:
Notas de uso¶
Esta seção fornece informações de uso para a transformação de arquivos de dados preparados durante um carregamento.
Formatos de arquivo suportados¶
Os seguintes tipos de formato de arquivo são suportados para as transformações COPY:
CSV
JSON
Avro
ORC
Parquet
XML
Para analisar um arquivo de dados preparado, é necessário descrever seu formato de arquivo:
- CSV:
O formato padrão é texto UTF-8 delimitado por caracteres. O delimitador de campo padrão é um caractere de vírgula (
,
). O delimitador de registro padrão é o novo caractere de nova linha. Se os dados de origem estiverem em outro formato, especifique o tipo de formato do arquivo e as opções.Ao consultar os arquivos de dados preparados, a opção
ERROR_ON_COLUMN_COUNT_MISMATCH
é ignorada. Não há exigência de que seus arquivos de dados tenham o mesmo número e ordenação de colunas que sua tabela de destino.- JSON:
Para transformar dados JSON durante uma operação de carregamento, você deve estruturar os arquivos de dados no formato padrão NDJSON («Newline Delimited JSON»); caso contrário, você pode encontrar o seguinte erro:
Error parsing JSON: more than one document in the input
- Todos os outros tipos de formato de arquivo:
Especifique o tipo de formato e as opções que correspondem a seus arquivos de dados.
Para especificar explicitamente as opções de formato de arquivo, defina-as de uma das seguintes maneiras:
Consultar arquivos de dados preparados usando uma instrução SELECT: |
|
Carregar colunas de arquivos de dados preparados usando uma instrução COPY INTO <table> : |
|
Funções suportadas¶
O Snowflake atualmente oferece suporte ao seguinte subconjunto de funções para transformações COPY:
-
Observe que quando esta função é usada para converter um valor explicitamente, nem a opção de formato de arquivo DATE_FORMAT nem o parâmetro DATE_INPUT_FORMAT são aplicados.
-
Observe que quando esta função é usada para converter um valor explicitamente, nem a opção de formato de arquivo TIME_FORMAT nem o parâmetro TIME_INPUT_FORMAT são aplicados.
-
Observe que quando esta função é usada para converter um valor explicitamente, nem a opção de formato de arquivo TIMESTAMP_FORMAT nem o parâmetro TIMESTAMP_INPUT_FORMAT são aplicados.
-
Note que o comando COPY INTO <table> não oferece suporte ao argumento opcional
format
para esta função. -
Note que o comando COPY INTO <table> não oferece suporte ao argumento opcional
format
para esta função. TRY_TO_TIMESTAMP / TRY_TO_TIMESTAMP_*
Note que o comando COPY INTO <table> não oferece suporte ao argumento opcional
format
para esta função.
Observe, em particular, que a função VALIDATE ignora a lista SELECT em uma instrução COPY INTO. <table>. A função analisa os arquivos mencionados na instrução e retorna quaisquer erros de análise. Este comportamento pode ser surpreendente se você espera que a função avalie os arquivos no contexto de expressões COPY INTO <tabela>.
Observe que transformações COPY não oferecem suporte à função FLATTEN ou à sintaxe (agregada) JOIN ou GROUP BY.
A lista de funções suportadas pode se aumentar com o tempo.
As seguintes categorias de funções também são suportadas:
Escalar SQL UDFs.
Nota
Para UDFs SQL escalares, o Snowflake tem suporte limitado para manipulação de erros de transformação e você pode encontrar comportamentos de opção de cópia ON_ERROR inconsistentes ou inesperados.
Filtragem dos resultados¶
A filtragem dos resultados de uma cláusula FROM usando uma cláusula WHERE não é suportada. As palavras-chave ORDER BY, LIMIT,FETCH,TOP em instruções SELECT também não são suportadas.
A palavra-chave DISTINCT nas instruções SELECT não é totalmente suportada. A especificação da palavra-chave pode levar a um comportamento de opção de cópia ON_ERROR inconsistente ou inesperado.
Parâmetro VALIDATION_MODE¶
O parâmetro VALIDATION_MODE não suporta COPY instruções que transformam dados durante um carregamento.
Valores padrão de coluna CURRENT_TIME, CURRENT_TIMESTAMP¶
Em vez de usar os valores padrão das colunas CURRENT_TIME, CURRENT_TIMESTAMP para capturar o tempo de carregamento, recomendamos que você consulte METADATA$START_SCAN_TIME para obter um valor preciso do tempo de carregamento do registro. Para obter mais informações, consulte Consulta de metadados para arquivos preparados.
Transformação de dados CSV¶
Carregamento de um subconjunto de dados da tabela¶
Carregar um subconjunto de dados em uma tabela. Para qualquer coluna que esteja faltando, o Snowflake insere os valores padrão. O exemplo a seguir carrega dados das colunas 1, 2, 6 e 7 de um arquivo CSV preparado:
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);
Reordenação de colunas CSV durante um carregamento¶
O exemplo seguinte reordena os dados de coluna de um arquivo CSV preparado antes de carregar em uma tabela. Além disso, a instrução COPY usa a função SUBSTR , SUBSTRING para remover os primeiros caracteres de uma cadeia de caracteres antes de inseri-la:
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);
Conversão de tipos de dados durante um carregamento¶
Converte dados preparados em outros tipos de dados durante um carregamento de dados. Todas as funções de conversão são suportadas.
Por exemplo, converter cadeias de caracteres como valores binários, decimais ou carimbos de data/hora usando as funções TO_BINARY, TO_DECIMAL , TO_NUMBER , TO_NUMERIC e TO_TIMESTAMP / TO_TIMESTAMP_*, respectivamente.
Arquivo CSV de exemplo
snowflake,2.8,2016-10-5 warehouse,-12.3,2017-01-23
Instruções 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 | +--------------------+------+-------------------------+
Inclusão de colunas de sequência nos dados carregados¶
Criar um objeto de sequência usando CREATE SEQUENCE. Ao carregar dados em uma tabela usando o comando COPY, acesse o objeto usando uma expressão NEXTVAL
para sequenciar os dados em uma coluna de números alvo. Para obter mais informações sobre o uso de sequências em consultas, consulte Uso de sequências.
-- 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 | +------+------+------+
Inclusão de colunas AUTOINCREMENT / IDENTITY em dados carregados¶
Defina o valor padrão AUTOINCREMENT ou IDENTITY para uma coluna de números. Ao carregar dados em uma tabela usando o comando COPY, omitir a coluna na instrução SELECT. A instrução preenche a coluna automaticamente.
-- 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 | +------+------+------+
Transformação de dados semiestruturados¶
Os exemplos nesta seção se aplicam a qualquer tipo de dados semiestruturados, exceto onde indicado.
Carregamento de dados semiestruturados em colunas separadas¶
O exemplo a seguir carrega elementos repetidos de um arquivo semiestruturado preparado em colunas de tabela separadas com diferentes tipos de dados.
Este exemplo carrega os dados semiestruturados a seguir em colunas separadas numa tabela relacional com os valores de objeto location
carregados em uma coluna VARIANT e os valores restantes carregados em colunas relacionais:
-- 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"}
As instruções SQL a seguir carregam o arquivo sales.json
a partir do estágio interno mystage
:
Nota
Este exemplo carrega dados JSON, mas as instruções SQL são semelhantes ao carregar dados semiestruturados de outros tipos (por exemplo, Avro, ORC, etc.).
Para um exemplo adicional usando dados Parquet, consulte Carregar dados Parquet em colunas separadas (neste tópico).
-- 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 |
+------------+-------------+-------+------------+-------+
Carregamento de dados Parquet em colunas separadas¶
Similar ao exemplo anterior, mas carrega dados semiestruturados de um arquivo no formato Parquet. Este exemplo é fornecido para usuários familiarizados com o 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 | ... +---------+------------+-------------+-----------+
Nivelamento de dados semiestruturados¶
FLATTEN é uma função de tabela que produz uma exibição lateral de uma coluna VARIANT, OBJECT ou ARRAY. Usando os dados de exemplo de Carregar dados semiestruturados em colunas separadas, crie uma tabela com uma linha separada para cada elemento nos objetos.
-- 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" |
| | | | | | } |
+-----+-----------+-----------+-------+-------------------------+-----------------------------+
Divisão dos elementos semiestruturados e carregamento como valores VARIANT em colunas separadas¶
Seguindo as instruções em Carregar dados semiestruturados em colunas separadas, você pode carregar elementos individuais de dados semiestruturados em diferentes colunas em sua tabela de destino. Além disso, usando a função SPLIT, você pode dividir valores de elemento que contenham um separador e carregá-los como matriz.
Por exemplo, dividir endereços IP no separador de pontos em elementos repetidos. Carregar os endereços IP como matrizes em colunas separadas:
-- 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" | | ] | ] | +----------+----------+