Preparação de seus arquivos de dados¶
Este tópico fornece as práticas recomendadas, diretrizes gerais e considerações importantes para preparar seus arquivos de dados para carregamento.
Neste tópico:
Práticas recomendadas e limitações para o dimensionamento de arquivos¶
Para um melhor desempenho do carregamento e para evitar limitações de tamanho, considere as seguintes diretrizes para o dimensionamento de arquivos de dados. Observe que essas recomendações se aplicam a carregamentos de dados em massa, bem como a carregamentos contínuos usando o Snowpipe.
Recomendações gerais de dimensionamento de arquivos¶
O número de operações de carga executadas em paralelo não pode exceder o número de arquivos de dados a serem carregados. Para otimizar o número de operações paralelas de um carregamento, recomendamos que o objetivo seja produzir arquivos de dados de aproximadamente 100-250 MB (ou maiores) comprimidos.
Nota
O carregamento de arquivos muito grandes (por exemplo, 100 GB ou maiores) não é recomendado.
Se você precisar carregar um arquivo grande, considere cuidadosamente o valor da opção de cópia ON_ERROR. Abortar ou ignorar um arquivo devido a um pequeno número de erros pode resultar em atrasos e desperdício de créditos. Além disso, se uma operação de carregamento de dados continuar além da duração máxima permitida de 24 horas, ela poderá ser cancelada sem que nenhuma parte do arquivo seja comprometida.
Agregue arquivos menores para minimizar sobretaxas de processamento para cada arquivo. Divida arquivos maiores em um maior número de arquivos menores para distribuir a carga entre os recursos computacionais em um warehouse ativo. O número de arquivos de dados que são processados em paralelo é determinado pela quantidade de recursos computacionais em um warehouse. Recomendamos dividir arquivos grandes por linha para evitar registros que se estendam por partes.
Se seu banco de dados de origem não permitir a exportação de arquivos de dados em partes menores, você pode usar um utilitário de terceiros para dividir arquivos CSV grandes.
Linux ou macOS¶
O utilitário split permite dividir um arquivo CSV em vários arquivos menores.
Sintaxe:
split [-a suffix_length] [-b byte_count[k|m]] [-l line_count] [-p pattern] [file [name]]
Para obter mais informações, digite man split em uma janela de terminal.
Exemplo:
split -l 100000 pagecounts-20151201.csv pages
Este exemplo divide um arquivo chamado pagecounts-20151201.csv pelo comprimento da linha. Suponha que o arquivo único e grande tenha 8 GB e contenha 10 milhões de linhas. Dividido por 100.000, cada um dos 100 arquivos menores tem 80 MB (10 milhões/100.000 = 100). Os arquivos divididos são nomeados pagessuffix.
Windows¶
O Windows não inclui um utilitário de divisão de arquivos nativo; entretanto, o Windows oferece suporte a muitas ferramentas e scripts de terceiros que podem dividir arquivos de dados grandes.
Reduzir o tamanho de objetos maiores que 16 MB antes de carregar¶
Nota
Para usar esse recurso, você deve habilitar o pacote de mudanças de comportamento 2024_08 na sua conta.
Para ativar este pacote em sua conta, execute a seguinte instrução:
SELECT SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2024_08');
Ocorrerá um erro se você tentar carregar um objeto maior que 16 MB de um arquivo de um estágio em um dos seguintes tipos de colunas:
VARCHAR (incluindo os tipos que são sinônimos de VARCHAR)
BINARY (incluindo os tipos que são sinônimos de BINARY)
VARIANT
OBJECT
ARRAY
GEOGRAPHY
GEOMETRY
O seguinte erro ocorre porque o tamanho máximo de um objeto armazenado em uma coluna é de 16 MB:
Max LOB size (16777216) exceeded
No passado, esse erro também ocorria se você tentasse consultar um arquivo em um estágio e o arquivo contivesse objetos maiores que 16 MB.
Embora ainda não seja possível armazenar objetos maiores que 16 MB em uma coluna, agora você pode consultar objetos de até 128 MB em arquivos de um estágio. Você pode então reduzir o tamanho dos objetos antes de armazená-los em colunas. Não ocorre mais um erro quando você consulta um arquivo contendo objetos maiores que 16 MB, mas menores que 128 MB.
Por exemplo, é possível dividir objetos grandes em várias colunas ou linhas, transformar JSON aninhado em um formato tabular ou simplificar geometrias complexas.
Exemplo: carregando um arquivo JSON grande em linhas separadas¶
Em geral, conjuntos de dados JSON são uma simples concatenação de vários documentos. A saída JSON de um software é composta de uma única grande matriz contendo vários registros. Não há necessidade de separar os documentos com quebras de linha ou vírgulas, embora ambas as formas sejam aceitas.
Se os dados excederem 16 MB, ative a opção de formato de arquivo STRIP_OUTER_ARRAY do comando COPY INTO <tabela> para remover a estrutura da matriz externa e carregar os registros em linhas de tabela separadas:
COPY INTO <table>
FROM @~/<file>.json
FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = true);
Exemplo: carregando e dividindo objetos JSON de um arquivo Parquet¶
Suponha que você esteja carregando um arquivo Parquet de um estágio, e que o arquivo Parquet contenha um objeto JSON maior que 16 MB:
{
"ID": 1,
"CustomerDetails": {
"RegistrationDate": 158415500,
"FirstName": "John",
"LastName": "Doe",
"Events": [
{
"Type": "LOGIN",
"Time": 1584158401,
"EventID": "NZ0000000001"
},
/* ... */
/* this array contains thousands of elements */
/* with total size exceeding 16 MB */
/* ... */
{
"Type": "LOGOUT",
"Time": 1584158402,
"EventID": "NZ0000000002"
}
]
}
}
O exemplo a seguir cria uma tabela para armazenar os dados do arquivo e carrega os dados na tabela. Como o tamanho da matriz de eventos pode exceder 16 MB, o exemplo expande a matriz de eventos em linhas separadas (uma para cada elemento da matriz).
CREATE OR REPLACE TABLE mytable AS
SELECT
t1.$1:ID AS id,
t1.$1:CustomerDetails:RegistrationDate::VARCHAR AS RegistrationDate,
t1.$1:CustomerDetails:FirstName::VARCHAR AS First_Name,
t1.$1:CustomerDetails:LastName::VARCHAR AS as Last_Name,
t2.value AS Event
FROM @json t1,
TABLE(FLATTEN(INPUT => $1:CustomerDetails:Events)) t2;
A seguir está um exemplo do conteúdo da tabela resultante.
+----+-------------------+------------+------------+------------------------------+
| ID | REGISTRATION_DATE | FIRST_NAME | LAST_NAME | EVENT |
|----+-------------------+------------+------------+------------------------------|
| 1 | 158415500 | John | Doe | { |
| | | | | "EventID": "NZ0000000001", |
| | | | | "Time": 1584158401, |
| | | | | "Type": "LOGIN" |
| | | | | } |
| ... thousands of rows ... |
| 1 | 158415500 | John | Doe | { |
| | | | | "EventID": "NZ0000000002", |
| | | | | "Time": 1584158402, |
| | | | | "Type": "LOGOUT" |
| | | | | } |
+----+-------------------+------------+------------+------------------------------+
Inserir resultados FLATTEN em uma tabela existente¶
Para inserir os resultados da função FLATTEN em uma tabela existente, use uma instrução INSERT. Por exemplo:
CREATE OR REPLACE TABLE mytable (
id VARCHAR,
registration_date VARCHAR(16777216),
first_name VARCHAR(16777216),
last_name VARCHAR(16777216),
event VARCHAR(16777216));
INSERT INTO mytable
SELECT
t1.$1:ID,
t1.$1:CustomerDetails:RegistrationDate::VARCHAR,
t1.$1:CustomerDetails:FirstName::VARCHAR,
t1.$1:CustomerDetails:LastName::VARCHAR,
t2.value
FROM @json t1,
TABLE(FLATTEN(INPUT => $1:CustomerDetails:Events)) t2;
Exemplo: carregamento e divisão de XML¶
Suponha que você esteja carregando um arquivo XML de um estágio e que ele contenha um objeto XML maior que 16 MB:
<?xml version='1.0' encoding='UTF-8'?>
<osm version="0.6" generator="osmium/1.14.0">
<node id="197798" version="17" timestamp="2021-09-06T17:01:27Z" />
<node id="197824" version="7" timestamp="2021-08-04T23:17:18Z" >
<tag k="highway" v="traffic_signals"/>
</node>
<!-- thousands of node elements with total size exceeding 16 MB -->
<node id="197826" version="4" timestamp="2021-08-04T16:43:28Z" />
</osm>
O exemplo a seguir cria uma tabela para armazenar os dados do arquivo e carrega os dados na tabela. Como o tamanho do XML excede 16 MB, o exemplo expande cada node em linhas separadas.
CREATE OR REPLACE TABLE mytable AS
SELECT
value:"@id" AS id,
value:"@version" AS version,
value:"@timestamp"::datetime AS TIMESTAMP,
value:"$" AS tags
FROM @mystage,
LATERAL FLATTEN(INPUT => $1:"$")
WHERE value:"@" = 'node';
A seguir está um exemplo do conteúdo da tabela resultante.
+--------+---------+-------------------------+---------------------------------------------+
| ID | VERSION | TIMESTAMP | TAGS |
|--------+---------+-------------------------+---------------------------------------------|
| 197798 | 17 | 2021-09-06 17:01:27.000 | "" |
| 197824 | 7 | 2021-08-04 23:17:18.000 | <tag k="highway" v="traffic_signals"></tag> |
| ... thousands of rows ... |
| 197826 | 4 | 2021-08-04 16:43:28.000 | "" |
+--------+---------+-------------------------+---------------------------------------------+
Exemplo: carregar e simplificar grandes objetos geoespaciais antes de armazená-los¶
Suponha que você esteja carregando um arquivo Parquet de um estágio e o arquivo Parquet contenha um objeto geoespacial maior que 16 MB. Você pode carregar o arquivo do estágio e simplificar o objeto geoespacial (usando ST_SIMPLIFY) antes de armazenar o objeto:
CREATE OR REPLACE TABLE mytable AS
SELECT
ST_SIMPLIFY($1:geo, 10) AS geo
FROM @mystage;
Exemplo: usar COPY INTO <table>¶
Se você precisar usar COPY INTO <tabela> para carregar dados de um arquivo em um estágio, não poderá usar FLATTEN para dividir objetos grandes. Em vez disso, use SELECT. Por exemplo:
CREATE OR REPLACE TABLE mytable (
id VARCHAR,
registration_date VARCHAR,
first_name VARCHAR,
last_name VARCHAR);
COPY INTO mytable (
id,
registration_date,
first_name,
last_name
) FROM (
SELECT
$1:ID,
$1:CustomerDetails::OBJECT:RegistrationDate::VARCHAR,
$1:CustomerDetails::OBJECT:FirstName::VARCHAR,
$1:CustomerDetails::OBJECT:LastName::VARCHAR
FROM @mystage
);
Carregamentos contínuos de dados (ou seja, Snowpipe) e dimensionamento de arquivos¶
O Snowpipe é projetado para carregar novos dados normalmente dentro de um minuto após o envio de uma notificação de arquivo; no entanto, o carregamento pode levar muito mais tempo para arquivos realmente grandes ou em casos onde uma quantidade incomum de recursos computacionais é necessária para descompactar, decodificar e transformar os novos dados.
Além do consumo de recursos, uma sobretaxa para gerenciar arquivos na fila de carga interna está incluída nos custos de utilização cobrados pelo Snowpipe. Essa sobretaxa aumenta em relação ao número de arquivos enfileirados para carregamento. Esta cobrança de sobretaxa aparece como cobranças do Snowpipe em sua fatura porque o Snowpipe é usado para notificações de eventos para a atualização automática da tabela externa.
Para uma experiência de carregamento mais eficiente e econômica com o Snowpipe, recomendamos seguir as recomendações de dimensionamento de arquivos em Práticas recomendadas e limitações para o dimensionamento de arquivos (neste tópico). Carregar arquivos de dados de aproximadamente 100 a 250 MB ou maiores reduz o custo adicional em relação à quantidade total de dados carregados a ponto de o custo adicional ser irrelevante.
Se demorar mais de um minuto para acumular MBs de dados em seus aplicativo de origem, considere a criação de um novo arquivo de dados (potencialmente menor) uma vez por minuto. Esta abordagem normalmente leva a um bom equilíbrio entre o custo (ou seja, recursos gastos no gerenciamento da fila do Snowpipe e o carregamento real) e o desempenho (ou seja, a latência do carregamento).
A criação e preparação de arquivos de dados menores no armazenamento em nuvem com mais frequência do que uma vez por minuto tem as seguintes desvantagens:
Não é possível garantir uma redução na latência entre a preparação e o carregamento dos dados.
Uma sobretaxa para gerenciar arquivos na fila de carga interna está incluída nos custos de utilização cobrados pelo Snowpipe. Essa sobretaxa aumenta em relação ao número de arquivos enfileirados para carregamento.
Várias ferramentas podem agregar e agrupar arquivos de dados em lote. Uma opção conveniente é o Amazon Data Firehose. O Firehose permite definir tanto o tamanho do arquivo desejado, chamado de tamanho de buffer, quanto o intervalo de espera após o qual um novo arquivo é enviado (para armazenamento em nuvem neste caso), chamado de intervalo de buffer. Para obter mais informações, consulte a documentação do Amazon Data Firehose. Se seu aplicativo de origem normalmente acumula dados suficientes em um minuto para preencher arquivos maiores do que o máximo recomendado para um processamento paralelo ideal, você pode diminuir o tamanho do buffer para acionar a entrega de arquivos menores. Manter o ajuste do intervalo de buffer em 60 segundos (o valor mínimo) ajuda a evitar a criação de muitos arquivos ou o aumento da latência.
Preparação de arquivos de texto delimitados¶
Considere as seguintes diretrizes ao preparar seus arquivos de texto delimitados (CSV) para carregamento:
UTF-8 é o conjunto de caracteres padrão, porém codificações adicionais são suportadas. Use a opção de formato de arquivo ENCODING para especificar o conjunto de caracteres para os arquivos de dados. Para obter mais informações, consulte CREATE FILE FORMAT.
Os campos que contêm caracteres delimitadores devem ser delimitados entre aspas (simples ou duplas). Se os dados contiverem aspas simples ou duplas, então essas aspas devem ser evitadas.
Os retornos de carro são comumente introduzidos em sistemas Windows em conjunto com um caractere de alimentação de linha para marcar o final de uma linha (
\r \n). Os campos que contêm retornos de carro também devem ser colocados entre aspas (simples ou duplas).O número de colunas em cada linha deve ser consistente.
Arquivos de dados semiestruturados e colunarização¶
Quando dados semiestruturados são inseridos em uma coluna VARIANT, o Snowflake usa determinadas regras para extrair o máximo possível dos dados para uma forma de coluna. O restante dos dados é armazenado como uma única coluna em uma estrutura semiestruturada analisada.
Por padrão, o Snowflake extrai no máximo 200 elementos por partição, por tabela. Para aumentar este limite, entre em contato com o suporte Snowflake.
Elementos que não são extraídos¶
Elementos com as seguintes características não são extraídos em uma coluna:
Os elementos que contêm até mesmo um único valor “nulo” não são extraídos em uma coluna. Isso se aplica a elementos com valores “nulos” e não a elementos com valores ausentes, que são representados em forma de coluna.
Esta regra garante que nenhuma informação seja perdida (ou seja, que a diferença entre valores de VARIANT “nulos” e valores SQL NULL não seja perdida).
Elementos que contêm múltiplos tipos de dados. Por exemplo:
O elemento
fooem uma linha contém um número:{"foo":1}
O mesmo elemento em outra linha contém uma cadeia de caracteres:
{"foo":"1"}
Como a extração afeta as consultas¶
Quando você consulta um elemento semiestruturado, o mecanismo de execução do Snowflake se comporta de maneira diferente se um elemento tiver sido extraído.
Se o elemento tiver sido extraído em uma coluna, o mecanismo verificará apenas a coluna extraída.
Se o elemento não tiver sido extraído em uma coluna, o mecanismo deve examinar toda a estrutura JSON e então, para cada linha, percorrer a estrutura para emitir valores. Isso afeta o desempenho.
Para evitar o impacto no desempenho de elementos que não foram extraídos, faça o seguinte:
Extraia elementos de dados semiestruturados contendo valores “nulos” em colunas relacionais antes de carregá-los.
Como alternativa, se os valores “nulos” em seus arquivos indicarem valores ausentes e não tiverem nenhum outro significado especial, recomendamos definir a opção de formato do arquivo STRIP_NULL_VALUES como TRUE ao carregar os arquivos de dados semiestruturados. Essa opção remove elementos OBJECT ou ARRAY contendo valores “nulos”.
Certifique-se de que cada elemento único armazene valores de um único tipo de dados nativo no formato em questão (por exemplo, cadeia de caracteres ou número em JSON).
Diretrizes de dados numéricos¶
Evite caracteres embutidos, como vírgulas (por exemplo,
123,456).Se um número inclui um componente fracionário, ele deve ser separado da parte do número inteiro por um ponto decimal (por exemplo,
123456.789).Somente Oracle. Os tipos Oracle NUMBER ou NUMERIC permitem escala arbitrária, ou seja, aceitam valores com componentes decimais, mesmo que o tipo de dados não tenha sido definido com precisão ou escala. Enquanto no Snowflake, as colunas projetadas para valores com componentes decimais devem ser definidas com uma escala para preservar a porção decimal.
Diretrizes de dados de data e de carimbo de data/hora¶
Para obter mais informações sobre os formatos compatíveis para dados de data, hora e carimbo de data/hora, consulte Formatos de entrada e saída de data e hora.
Somente Oracle. O tipo de dados Oracle DATE pode conter informações de data ou de carimbo de data/hora. Se seu banco de dados Oracle incluir colunas DATE que também armazenam informações relacionadas ao tempo, mapeie essas colunas para um tipo de dados TIMESTAMP em vez de DATE no Snowflake.
Nota
O Snowflake verifica os valores dos dados temporais no momento do carregamento. Valores inválidos de data, hora e carimbo de data/hora (por exemplo, 0000-00-00) produzem um erro.