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:

  • Como opções de formato de arquivo especificadas para um formato de arquivo nomeado ou objeto de preparação. O formato do arquivo/objeto de preparação nomeado pode então ser referenciado na instrução SELECT.

Carregar colunas de arquivos de dados preparados usando uma instrução COPY INTO <table> :

  • Como uma opção de formato de arquivo especificada diretamente na instrução COPY INTO <tabela>.

  • Como opções de formato de arquivo especificadas para um formato de arquivo nomeado ou objeto de preparação. O formato do arquivo/objeto de preparação nomeado pode então ser referenciado na instrução COPY INTO <tabela>.

Funções suportadas

O Snowflake atualmente oferece suporte ao seguinte subconjunto de funções para transformações COPY:

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:

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.

Resultados da filtragem

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

Transformar dados CSV

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

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

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

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

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

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

Transformação de dados semiestruturados

Os exemplos nesta seção se aplicam a qualquer tipo de dados semiestruturados, exceto onde indicado.

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

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

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

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

Dividir elementos semiestruturados e carregar 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"    |
| ]        | ]        |
+----------+----------+
Copy
Linguagem: Português