CREATE EXTERNAL TABLE¶

CREATE EXTERNAL TABLE … USING TEMPLATE¶

Cria uma nova tabela externa com as definições das colunas derivadas de um conjunto de arquivos preparados contendo dados semiestruturados. Este recurso oferece suporte aos arquivos Apache Parquet, Apache Avro, ORC, JSON e CSV. O suporte para arquivos CSV e JSON está atualmente em versão preliminar.

CREATE [ OR REPLACE ] EXTERNAL TABLE <table_name>
  [ COPY GRANTS ]
  USING TEMPLATE <query>
  [ ... ]

Copy

Para obter mais detalhes sobre COPY GRANTS, consulte COPY GRANTS neste documento.

Parâmetros de particionamento¶

Use estes parâmetros para dividir sua tabela externa.

part_col_name col_type AS part_expr

Define uma ou mais colunas de partição na tabela externa.

O formato da definição de uma coluna de partição difere dependendo se as partições são computadas e adicionadas automaticamente a partir de uma expressão em cada coluna de partição ou se as partições são adicionadas manualmente.

Adição a partir de uma expressão:

Uma coluna de partição deve avaliar como uma expressão que analisa o caminho e/ou informações de nome de arquivo na pseudocoluna METADATA$FILENAME. As colunas de partição otimizam o desempenho da consulta, retirando os arquivos de dados que não precisam ser digitalizados (ou seja, particionando a tabela externa). Uma partição consiste em todos os arquivos de dados que correspondem ao caminho e/ou nome do arquivo na expressão para a coluna da partição.

part_col_name	Cadeia de caracteres que especifica o identificador da coluna de partição (ou seja, nome). Todos os requisitos de identificadores de tabela também se aplicam aos identificadores de coluna.
col_type	Cadeia de caracteres (constante) que especifica o tipo de dados da coluna. O tipo de dados deve corresponder ao resultado de part_expr da coluna.
part_expr	Cadeia de caracteres que especifica a expressão da coluna. A expressão deve incluir a pseudocoluna METADATA$FILENAME.

As tabelas externas oferecem suporte atualmente ao seguinte subconjunto de funções em expressões de partição:

Lista de funções suportadas:

• =, <>, >, >=, <, <=

• ||

• +, -

• - (negar)

• *

• AND, OR

• ARRAY_CONSTRUCT

• CASE

• CAST , ::

• CONCAT , ||

• ENDSWITH

• IS [ NOT ] NULL

• IFF

• IFNULL

• [ NOT ] IN

• LOWER

• NOT

• NULLIF

• NVL2

• SPLIT_PART

• STARTSWITH

• SUBSTR , SUBSTRING

• UPPER

• ZEROIFNULL

Adicionado manualmente:

Obrigatório: definir também o valor do parâmetro PARTITION_TYPE como USER_SPECIFIED.

Uma definição de coluna de partição é uma expressão que analisa os metadados da coluna na coluna interna (oculta) METADATA$EXTERNAL_TABLE_PARTITION. Essencialmente, a definição define apenas o tipo de dados para a coluna. O formato da definição da coluna de partição é o seguinte:

part_col_name col_type AS ( PARSE_JSON (METADATA$EXTERNALTABLE_PARTITION):part_col_name::data_type )

Por exemplo, suponha que as colunas col1, col2 e col3 contenham dados varchar, número e carimbo de data/hora (fuso horário), respectivamente:

col1 varchar as (parse_json(metadata$external_table_partition):col1::varchar),
col2 number as (parse_json(metadata$external_table_partition):col2::number),
col3 timestamp_tz as (parse_json(metadata$external_table_partition):col3::timestamp_tz)

Copy

Após definir qualquer coluna de partição para a tabela, identifique essas colunas usando a cláusula PARTITION BY.

Nota

O comprimento máximo dos nomes das colunas de partição especificadas pelo usuário é de 32 caracteres.

PARTITION_TYPE = USER_SPECIFIED

Define o tipo de partição para a tabela externa como definida pelo usuário. O proprietário da tabela externa (ou seja, a função que tem o privilégio OWNERSHIP na tabela externa) deve adicionar partições aos metadados externos manualmente, executando as instruções ALTER EXTERNAL TABLE … ADD PARTITION.

Não defina este parâmetro se as partições forem adicionadas automaticamente aos metadados da tabela externa após a avaliação das expressões nas colunas da partição.

[ PARTITION BY ( part_col_name [, part_col_name ... ] ) ]

Especifica qualquer coluna de partição a ser avaliada para a tabela externa.

Uso:

Ao consultar uma tabela externa, incluir uma ou mais colunas de partição em uma cláusula WHERE, por exemplo:

... WHERE part_col_name = 'filter_value'

O Snowflake filtra as colunas de partição para restringir o conjunto de arquivos de dados a serem escaneados. Note que todas as linhas destes arquivos são digitalizadas. Se uma cláusula WHERE incluir colunas não particionadas, esses filtros são avaliados depois que os arquivos de dados forem filtrados.

Uma prática comum é particionar os arquivos de dados com base em incrementos de tempo; ou, se os arquivos de dados forem preparados a partir de múltiplas fontes, particionar por um identificador da fonte de dados e carimbo de data/hora.

TYPE = CSV¶

COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE

String (constante) que especifica o algoritmo de compressão atual para os arquivos de dados a serem consultados. O Snowflake usa esta opção para detectar como os arquivos de dados já comprimidos foram comprimidos para que os dados comprimidos nos arquivos possam ser extraídos para consulta.

Valores suportados	Notas
AUTO	Algoritmo de compressão detectado automaticamente, exceto para arquivos comprimidos com Brotli, que atualmente não podem ser detectados automaticamente. Se consultar arquivos comprimidos com Brotli, use explicitamente BROTLI em vez de AUTO.
GZIP
BZ2
BROTLI	Deve ser especificado ao consultar arquivos comprimidos com Brotli.
ZSTD	Zstandard v0.8 (e superior) suportado.
DEFLATE	Arquivos compactados Deflate (com cabeçalho zlib, RFC1950).
RAW_DEFLATE	Arquivos compactados Raw Deflate (sem cabeçalho, RFC1951).
NONE	Os arquivos de dados não foram comprimidos.

RECORD_DELIMITER = 'character' | NONE

Um ou mais caracteres que separam registros em um arquivo de entrada. Aceita sequências de escape comuns ou os seguintes caracteres de byte único ou multibyte:

Caracteres de byte único:

Valores octais (com prefixo \\) ou valores hexadecimais (com prefixo 0x ou \x). Por exemplo, para registros delimitados por acento circunflexo (^), especifique o valor octal (\\136) ou hexadecimal (0x5e).

Caracteres multibyte:

Valores hexadecimais (com prefixo \x). Por exemplo, para registros delimitados pelo caractere de centavo (¢), especifique o valor hexadecimal (\xC2\xA2).

O delimitador para RECORD_DELIMITER ou FIELD_DELIMITER não pode ser um substrato do delimitador para a outra opção de formato do arquivo (por exemplo, FIELD_DELIMITER = 'aa' RECORD_DELIMITER = 'aabb').

O delimitador especificado deve ser um caractere válido UTF-8 e não uma sequência aleatória de bytes. Observe também que o delimitador é limitado a um máximo de 20 caracteres.

Também aceita um valor de NONE.

Padrão: caractere de nova linha Note que «nova linha» é lógica de tal forma que \r\n é entendido como uma nova linha para arquivos em uma plataforma Windows.

FIELD_DELIMITER = 'character' | NONE

Um ou mais caracteres de byte único ou multibyte que separam os campos em um arquivo de entrada. Aceita sequências de escape comuns ou os seguintes caracteres de byte único ou multibyte:

Caracteres de byte único:

Valores octais (com prefixo \\) ou valores hexadecimais (com prefixo 0x ou \x). Por exemplo, para registros delimitados por acento circunflexo (^), especifique o valor octal (\\136) ou hexadecimal (0x5e).

Caracteres multibyte:

Valores hexadecimais (com prefixo \x). Por exemplo, para registros delimitados pelo caractere de centavo (¢), especifique o valor hexadecimal (\xC2\xA2).

O delimitador para RECORD_DELIMITER ou FIELD_DELIMITER não pode ser um substrato do delimitador para a outra opção de formato do arquivo (por exemplo, FIELD_DELIMITER = 'aa' RECORD_DELIMITER = 'aabb').

Nota

Para caracteres não ASCII, você deve usar o valor da sequência de bytes hexadecimais para obter um comportamento determinístico.

O delimitador especificado deve ser um caractere válido UTF-8 e não uma sequência aleatória de bytes. Observe também que o delimitador é limitado a um máximo de 20 caracteres.

Também aceita um valor de NONE.

Padrão: vírgula (,)

SKIP_HEADER = integer

Número de linhas no início do arquivo a ser pulado.

Observe que SKIP_HEADER não usa os valores RECORD_DELIMITER ou FIELD_DELIMITER para determinar o que é uma linha de cabeçalho; em vez disso, simplesmente ignora o número especificado de linhas delimitadas de CRLF (Carriage Return, Line Feed) no arquivo. RECORD_DELIMITER e FIELD_DELIMITER são então usados para determinar as linhas de dados a serem carregadas.

Padrão: 0

SKIP_BLANK_LINES = TRUE | FALSE

Uso:

Somente consulta de dados

Definição:

Booleano que especifica ignorar quaisquer linhas em branco encontradas nos arquivos de dados; caso contrário, linhas em branco produzem um erro de fim de registro (comportamento padrão).

Padrão: FALSE

ESCAPE_UNENCLOSED_FIELD = 'character' | NONE

Uma cadeia de caracteres de caractere de byte único usada como caractere de escape apenas para valores de campo não delimitados. Um caractere de escape invoca uma interpretação alternativa em caracteres subsequentes em uma sequência de caracteres. Você pode usar o caractere ESCAPE para interpretar instâncias dos caracteres FIELD_DELIMITER ou RECORD_DELIMITER nos dados como literais. O caractere de escape também pode ser usado para escapar de instâncias de si mesmo nos dados.

Aceita sequências de escape comuns, valores octais ou valores hexadecimais.

Especifica o caractere de escape somente para campos não delimitados.

Nota

• O valor padrão é \\. Se uma linha em um arquivo de dados terminar no caractere de barra invertida (\), este caractere escapa do caractere de linha nova ou de retorno de carro especificado para a opção de formato do arquivo RECORD_DELIMITER. Como resultado, esta linha e a próxima linha são tratadas como uma única linha de dados. Para evitar este problema, defina o valor como NONE.

• Esta opção de formato de arquivo oferece suporte somente a caracteres de byte único. Observe que a codificação de caractere UTF-8 representa caracteres ASCII de ordem superior como caracteres multibyte. Se seu arquivo de dados for codificado com o conjunto de caracteres UTF-8, você não poderá especificar um caractere ASCII de ordem superior como o valor da opção.

  Além disso, se você especificar um caractere ASCII de ordem superior, recomendamos que você defina a opção de formato de arquivo ENCODING = 'string' como a codificação de caracteres para seus arquivos de dados para garantir que o caractere seja interpretado corretamente.

Padrão: barra invertida (\\)

TRIM_SPACE = TRUE | FALSE

Booleano que especifica se é necessário remover o espaço em branco dos campos.

Por exemplo, se seu software de banco de dados externo delimita os campos com aspas, mas insere um espaço à esquerda, o Snowflake lê o espaço à esquerda em vez do caractere de abertura de aspas como o início do campo (ou seja, as aspas são interpretadas como parte da cadeia de caracteres de dados do campo). Defina esta opção como TRUE para remover espaços indesejáveis ao consultar dados.

Como outro exemplo, se os espaços no início e no final das aspas que delimitam as cadeias de caracteres, você pode remover esses espaços usando esta opção e o caractere de aspas usando a opção FIELD_OPTIONALLY_ENCLOSED_BY. Note que quaisquer espaços entre as aspas são preservados. Por exemplo, considerando FIELD_DELIMITER = '|' e FIELD_OPTIONALLY_ENCLOSED_BY = '"':

|"Hello world"|    /* returned as */  >Hello world<
|" Hello world "|  /* returned as */  > Hello world <
| "Hello world" |  /* returned as */  >Hello world<

Copy

Observe que os parênteses neste exemplo não são retornados; eles são usados para demarcar o início e o fim das cadeias de caracteres retornadas.

Padrão: FALSE

FIELD_OPTIONALLY_ENCLOSED_BY = 'character' | NONE

Caractere usado para delimitar as cadeias de caracteres. O valor pode ser NONE, caractere de aspas simples (') ou caractere de aspas duplas ("). Para usar o caractere de aspas simples, use a representação octal ou hexadecimal (0x27) ou o escape de aspas simples dupla ('').

Quando um campo contém este caractere, aplique o escape usando o mesmo caractere. Por exemplo, se o valor for o caractere de aspas duplas e um campo tiver a cadeia de caracteres A "B" C, aplique o escape das aspas duplas como segue:

A ""B"" C

Padrão: NONE

NULL_IF = ( 'string1' [ , 'string2' , ... ] )

Cadeia de caracteres usada para converter de e para SQL NULL:

Ao consultar os dados, o Snowflake substitui estes valores nos dados retornados por SQL NULL. Para especificar mais de uma cadeia de caracteres, coloque a lista de cadeias de caracteres entre parênteses e use vírgulas para separar cada valor.

Observe que o Snowflake converte todas as instâncias do valor em NULL, independentemente do tipo de dados. Por exemplo, se 2 for especificado como um valor, todas as instâncias de 2 como uma cadeia de caracteres ou número são convertidas.

Por exemplo:

NULL_IF = ('\N', 'NULL', 'NUL', '')

Observe que esta opção pode incluir cadeias de caracteres vazias.

Padrão: \\N (ou seja, NULL, que considera que o valor ESCAPE_UNENCLOSED_FIELD é \\)

EMPTY_FIELD_AS_NULL = TRUE | FALSE

Especifica se deve retornar SQL NULL para campos vazios em um arquivo de entrada, que são representados por dois delimitadores sucessivos (por exemplo, ,,).

Se definido como FALSE, o Snowflake tenta converter um campo vazio no tipo de coluna correspondente. Uma cadeia de caracteres vazia é retornada para colunas do tipo STRING. Para outros tipos de colunas, a consulta retorna um erro.

Padrão: TRUE

ENCODING = 'string'

Cadeia de caracteres (constante) que especifica o conjunto de caracteres dos dados de origem ao consultar dados.

Conjunto de caracteres	Valor ENCODING	Linguagens suportadas	Notas
Big5	BIG5	Chinês Tradicional
EUC-JP	EUCJP	Japonês
EUC-KR	EUCKR	Coreano
GB18030	GB18030	Chinês
IBM420	IBM420	Árabe
IBM424	IBM424	Hebraico
IBM949	IBM949	Coreano
ISO-2022-CN	ISO2022CN	Chinês simplificado
ISO-2022-JP	ISO2022JP	Japonês
ISO-2022-KR	ISO2022KR	Coreano
ISO-8859-1	ISO88591	Alemão, Dinamarquês, Espanhol, Francês, Holandês, Inglês, Italiano, Norueguês, Português, Sueco
ISO-8859-2	ISO88592	Tcheco, Húngaro, Polonês, Romeno
ISO-8859-5	ISO88595	Russo
ISO-8859-6	ISO88596	Árabe
ISO-8859-7	ISO88597	Grego
ISO-8859-8	ISO88598	Hebraico
ISO-8859-9	ISO88599	Turco
ISO-8859-15	ISO885915	Alemão, Dinamarquês, Espanhol, Francês, Holandês, Inglês, Italiano, Norueguês, Português, Sueco	Idêntico a ISO-8859-1 exceto para 8 caracteres, incluindo o símbolo da moeda Euro.
KOI8-R	KOI8R	Russo
Shift_JIS	SHIFTJIS	Japonês
UTF-8	UTF8	Todos os idiomas	Para carregar dados de arquivos delimitados (CSV, TSV etc.), UTF-8 é o padrão. . . Para carregar dados de todos os outros formatos de arquivo suportados (JSON, Avro etc.), bem como descarregar dados, o UTF-8 é o único conjunto de caracteres suportado.
UTF-16	UTF16	Todos os idiomas
UTF-16BE	UTF16BE	Todos os idiomas
UTF-16LE	UTF16LE	Todos os idiomas
UTF-32	UTF32	Todos os idiomas
UTF-32BE	UTF32BE	Todos os idiomas
UTF-32LE	UTF32LE	Todos os idiomas
windows-949	WINDOWS949	Coreano
windows-1250	WINDOWS1250	Tcheco, Húngaro, Polonês, Romeno
windows-1251	WINDOWS1251	Russo
windows-1252	WINDOWS1252	Alemão, Dinamarquês, Espanhol, Francês, Holandês, Inglês, Italiano, Norueguês, Português, Sueco
windows-1253	WINDOWS1253	Grego
windows-1254	WINDOWS1254	Turco
windows-1255	WINDOWS1255	Hebraico
windows-1256	WINDOWS1256	Árabe

Padrão: UTF8

Nota

O Snowflake armazena todos os dados internamente no conjunto de caracteres UTF-8. Os dados são convertidos em UTF-8.

TYPE = JSON¶

COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE

String (constante) que especifica o algoritmo de compressão atual para os arquivos de dados a serem retornados. O Snowflake usa esta opção para detectar como os arquivos de dados já comprimidos foram comprimidos para que os dados comprimidos nos arquivos possam ser extraídos para consulta.

Valores suportados	Notas
AUTO	Algoritmo de compressão detectado automaticamente, exceto para arquivos comprimidos com Brotli, que atualmente não podem ser detectados automaticamente. Se consultar arquivos comprimidos com Brotli, use explicitamente BROTLI em vez de AUTO.
GZIP
BZ2
BROTLI
ZSTD
DEFLATE	Arquivos compactados Deflate (com cabeçalho zlib, RFC1950).
RAW_DEFLATE	Arquivos compactados Raw Deflate (sem cabeçalho, RFC1951).
NONE	Indica que os arquivos não foram comprimidos.

Padrão: AUTO

ALLOW_DUPLICATE = TRUE | FALSE

Booleano que especifica permitir a duplicação de nomes de campos de objetos (somente o último será preservado).

Padrão: FALSE

STRIP_OUTER_ARRAY = TRUE | FALSE

Booleano que instrui o analisador JSON a remover parênteses externos (ou seja, [ ]).

Padrão: FALSE

STRIP_NULL_VALUES = TRUE | FALSE

Booleano que instrui o analisador JSON a remover campos de objetos ou elementos de matriz contendo valores null. Por exemplo, quando definido como TRUE:

Antes	Depois
[null]	[]
[null,null,3]	[,,3]
{"a":null,"b":null,"c":123}	{"c":123}
{"a":[1,null,2],"b":{"x":null,"y":88}}	{"a":[1,,2],"b":{"y":88}}

Padrão: FALSE

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

Booleano que especifica se deve substituir os caracteres UTF-8 inválidos pelo caractere de substituição Unicode (�). Esta opção realiza uma substituição de caracteres um a um.

Se definido como TRUE, o Snowflake substitui os caracteres inválidos UTF-8 pelo caractere de substituição Unicode.

Se definido como FALSE, a operação de carregamento produz um erro quando a codificação de caracteres UTF-8 inválida é detectada.

Padrão: FALSE

TYPE = AVRO¶

COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE

String (constante) que especifica o algoritmo de compressão atual para os arquivos de dados a serem consultados. O Snowflake usa esta opção para detectar como os arquivos de dados já comprimidos foram comprimidos para que os dados comprimidos nos arquivos possam ser extraídos para consulta.

Valores suportados	Notas
AUTO	Algoritmo de compressão detectado automaticamente, exceto para arquivos comprimidos com Brotli, que atualmente não podem ser detectados automaticamente. Se consultar arquivos comprimidos com Brotli, use explicitamente BROTLI em vez de AUTO.
GZIP
BZ2
BROTLI
ZSTD
DEFLATE	Arquivos compactados Deflate (com cabeçalho zlib, RFC1950).
RAW_DEFLATE	Arquivos compactados Raw Deflate (sem cabeçalho, RFC1951).
NONE	Os arquivos de dados a serem consultados não foram comprimidos.

Padrão: AUTO.

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

Booleano que especifica se deve substituir os caracteres UTF-8 inválidos pelo caractere de substituição Unicode (�). Esta opção realiza uma substituição de caracteres um a um.

Se definido como TRUE, o Snowflake substitui os caracteres inválidos UTF-8 pelo caractere de substituição Unicode.

Se definido como FALSE, a operação de carregamento produz um erro quando a codificação de caracteres UTF-8 inválida é detectada.

Padrão: FALSE

TYPE = ORC¶

TRIM_SPACE = TRUE | FALSE

Booleano que especifica se é necessário remover o espaço no início e no final das cadeias de caracteres.

Por exemplo, se seu software de banco de dados externo delimita os campos com aspas, mas insere um espaço à esquerda, o Snowflake lê o espaço à esquerda em vez do caractere de abertura de aspas como o início do campo (ou seja, as aspas são interpretadas como parte da cadeia de caracteres de dados do campo). Defina esta opção como TRUE para remover espaços indesejáveis.

Esta opção de formato de arquivo é aplicada apenas às seguintes ações:

• Consulta de valores de objetos em arquivos de dados preparados ORC.

• Consulta de dados ORC em colunas separadas usando a opção de cópia MATCH_BY_COLUMN_NAME.

• Consulta de dados ORC em colunas separadas especificando uma consulta na instrução COPY (isto é, transformação COPY).

Padrão: FALSE

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

Booleano que especifica se deve substituir os caracteres UTF-8 inválidos pelo caractere de substituição Unicode (�). Esta opção realiza uma substituição de caracteres um a um.

Se definido como TRUE, o Snowflake substitui os caracteres inválidos UTF-8 pelo caractere de substituição Unicode.

Se definido como FALSE, a operação de carregamento produz um erro quando a codificação de caracteres UTF-8 inválida é detectada.

Padrão: FALSE

NULL_IF = ( 'string1' [ , 'string2' , ... ] )

String usada para converter de e para SQL NULL. O Snowflake substitui estas cadeias de caracteres na origem de dados por SQL NULL. Para especificar mais de uma cadeia de caracteres, coloque a lista de cadeias de caracteres entre parênteses e use vírgulas para separar cada valor.

Observe que o Snowflake converte todas as instâncias do valor em NULL, independentemente do tipo de dados. Por exemplo, se 2 for especificado como um valor, todas as instâncias de 2 como uma cadeia de caracteres ou número são convertidas.

Por exemplo:

NULL_IF = ('\N', 'NULL', 'NUL', '')

Observe que esta opção pode incluir cadeias de caracteres vazias.

Esta opção de formato de arquivo é aplicada ao consultar os valores dos objetos em arquivos de dados preparados ORC.

Padrão: \\N (ou seja, NULL, que considera que o valor ESCAPE_UNENCLOSED_FIELD é \\)

TYPE = PARQUET¶

COMPRESSION = AUTO | SNAPPY | NONE

Cadeia de caracteres (constante) que especifica o algoritmo de compressão atual para as colunas nos arquivos Parquet.

Valores suportados	Notas
AUTO	Algoritmo de compressão detectado automaticamente. Oferece suporte aos seguintes algoritmos de compressão: Brotli, gzip, Lempel-Ziv-Oberhumer (LZO), LZ4, Snappy ou Zstandard v0.8 (e superior).
SNAPPY
NONE	Os arquivos de dados não foram comprimidos.

Padrão: AUTO

BINARY_AS_TEXT = TRUE | FALSE

Booleano que especifica se deve interpretar colunas sem tipo de dados lógicos definidos como texto UTF-8. Quando definido como FALSE, o Snowflake interpreta estas colunas como dados binários.

Padrão: TRUE

Nota

Snowflake recomenda que você defina BINARY_AS_TEXT como FALSE para evitar possíveis problemas de conversão.

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

Booleano que especifica se deve substituir os caracteres UTF-8 inválidos pelo caractere de substituição Unicode (�). Esta opção realiza uma substituição de caracteres um a um.

Se definido como TRUE, o Snowflake substitui os caracteres inválidos UTF-8 pelo caractere de substituição Unicode.

Se definido como FALSE, a operação de carregamento produz um erro quando a codificação de caracteres UTF-8 inválida é detectada.

Padrão: FALSE

Partições adicionadas automaticamente a partir de expressões de colunas de partição¶

Criar uma tabela externa com partições computadas a partir de expressões nas definições das colunas de partição.

No exemplo a seguir, os arquivos de dados são organizados em armazenamento em nuvem com a seguinte estrutura: logs/YYYY/MM/DD/HH24. Por exemplo:

• logs/2018/08/05/0524/

• logs/2018/08/27/1408/

1. Criar um estágio externo chamado s1 para o local de armazenamento onde os arquivos de dados são armazenados. Para obter mais informações, consulte CREATE STAGE.

   A definição do estágio inclui o caminho /files/logs/:

   Amazon S3

   CREATE STAGE s1
     URL='s3://mybucket/files/logs/'
     ...
     ;
   

   Copy

   Google Cloud Storage

   CREATE STAGE s1
     URL='gcs://mybucket/files/logs/'
     ...
     ;
   

   Copy

   Microsoft Azure

   CREATE STAGE s1
     URL='azure://mycontainer/files/logs/'
     ...
     ;
   

   Copy

2. Consulte a pseudocoluna METADATA$FILENAME nos dados preparados. Usar os resultados para desenvolver sua(s) coluna(s) de partição:

   SELECT metadata$filename FROM @s1/;
   
   +----------------------------------------+
   | METADATA$FILENAME                      |
   |----------------------------------------|
   | files/logs/2018/08/05/0524/log.parquet |
   | files/logs/2018/08/27/1408/log.parquet |
   +----------------------------------------+
   

   Copy

3. Crie a tabela externa particionada.

   A coluna de partição date_part converte YYYY/MM/DD na pseudocoluna METADATA$FILENAME como uma data usando TO_DATE , DATE. O comando SQL também especifica Parquet como o tipo de formato do arquivo.

   As tabelas externas para armazenamento em nuvem Amazon S3 e Microsoft Azure incluem o parâmetro necessário para atualizar os metadados automaticamente quando acionados por notificações de eventos do respectivo serviço de mensagens em nuvem:

   Amazon S3

   CREATE EXTERNAL TABLE et1(
    date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
      || '/' || SPLIT_PART(metadata$filename, '/', 4)
      || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
    timestamp bigint AS (value:timestamp::bigint),
    col2 varchar AS (value:col2::varchar))
    PARTITION BY (date_part)
    LOCATION=@s1/logs/
    AUTO_REFRESH = true
    FILE_FORMAT = (TYPE = PARQUET)
    AWS_SNS_TOPIC = 'arn:aws:sns:us-west-2:001234567890:s3_mybucket';
   

   Copy

   Google Cloud Storage

   CREATE EXTERNAL TABLE et1(
     date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
       || '/' || SPLIT_PART(metadata$filename, '/', 4)
       || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
     timestamp bigint AS (value:timestamp::bigint),
     col2 varchar AS (value:col2::varchar))
     PARTITION BY (date_part)
     LOCATION=@s1/logs/
     AUTO_REFRESH = true
     FILE_FORMAT = (TYPE = PARQUET);
   

   Copy

   Microsoft Azure

   CREATE EXTERNAL TABLE et1(
     date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
       || '/' || SPLIT_PART(metadata$filename, '/', 4)
       || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
     timestamp bigint AS (value:timestamp::bigint),
     col2 varchar AS (value:col2::varchar))
     PARTITION BY (date_part)
     INTEGRATION = 'MY_INT'
     LOCATION=@s1/logs/
     AUTO_REFRESH = true
     FILE_FORMAT = (TYPE = PARQUET);
   

   Copy

4. Atualizar os metadados da tabela externa:

   ALTER EXTERNAL TABLE et1 REFRESH;
   

   Copy

Ao consultar a tabela externa, filtrar os dados pela coluna de partição usando uma cláusula WHERE. O Snowflake digitaliza apenas os arquivos nas partições especificadas que correspondem às condições do filtro:

SELECT timestamp, col2 FROM et1 WHERE date_part = to_date('08/05/2018');

Partições adicionadas manualmente¶

Crie uma tabela externa com partições definidas pelo usuário (ou seja, as partições são adicionadas manualmente pelo proprietário da tabela externa).

1. Criar um estágio externo chamado s2 para o local de armazenamento onde os arquivos de dados são armazenados:

   A definição do estágio inclui o caminho /files/logs/:

   Amazon S3

   CREATE STAGE s2
     URL='s3://mybucket/files/logs/'
     ...
     ;
   

   Copy

   Google Cloud Storage

   CREATE STAGE s2
     URL='gcs://mybucket/files/logs/'
     ...
     ;
   

   Copy

   Microsoft Azure

   CREATE STAGE s2
     URL='azure://mycontainer/files/logs/'
     ...
     ;
   

   Copy

2. Crie a tabela externa particionada. A tabela externa inclui três colunas de partição com diferentes tipos de dados.

   Observe que os nomes das colunas nas expressões das partições diferenciam letras maiúsculas de minúsculas. As seguintes regras são aplicáveis:

   • Um nome da coluna de partição deve estar em letras maiúsculas, a menos que o nome da coluna esteja entre aspas duplas. Como opção, use GET_IGNORE_CASE em vez do caractere : que diferencia maiúsculas de minúsculas na expressão SQL.

   • Se o nome de uma coluna estiver entre aspas duplas (por exemplo, “Coluna1”), o nome da coluna de partição também deve estar entre aspas duplas e corresponder exatamente ao nome da coluna.

   A sintaxe para cada um dos três serviços de armazenamento em nuvem (Amazon S3, Google Cloud Storage e Microsoft Azure) é idêntica porque os metadados de tabela externa não são atualizados:

   create external table et2(
     col1 date as (parse_json(metadata$external_table_partition):COL1::date),
     col2 varchar as (parse_json(metadata$external_table_partition):COL2::varchar),
     col3 number as (parse_json(metadata$external_table_partition):COL3::number))
     partition by (col1,col2,col3)
     location=@s2/logs/
     partition_type = user_specified
     file_format = (type = parquet);
   

   Copy

3. Adicionar partições para as colunas de partição:

   ALTER EXTERNAL TABLE et2 ADD PARTITION(col1='2022-01-24', col2='a', col3='12') LOCATION '2022/01';
   

   Copy

   O Snowflake adiciona as partições aos metadados de tabela externa. A operação também acrescenta aos metadados quaisquer novos arquivos de dados no local especificado:

   +---------------------------------------+----------------+-------------------------------+
   |                       file            |     status     |          description          |
   +---------------------------------------+----------------+-------------------------------+
   | mycontainer/files/logs/2022/01/24.csv | REGISTERED_NEW | File registered successfully. |
   | mycontainer/files/logs/2022/01/25.csv | REGISTERED_NEW | File registered successfully. |
   +---------------------------------------+----------------+-------------------------------+
   

   Copy

Ao consultar a tabela externa, filtrar os dados pelas colunas da partição usando uma cláusula WHERE. Este exemplo retorna os registros na ordem em que são armazenados nos arquivos de dados preparados:

SELECT col1, col2, col3 FROM et1 WHERE col1 = TO_DATE('2022-01-24') AND col2 = 'a' ORDER BY METADATA$FILE_ROW_NUMBER;

Tabela externa que faz referência a arquivos em um Delta Lake¶

Criar uma tabela externa particionada chamada ext_twitter_feed que faz referência aos arquivos do Delta Lake em formato Parquet no estágio externo mystage e no caminho daily.

A coluna de partição date_part converte YYYY/MM/DD na pseudocoluna METADATA$FILENAME como uma data usando TO_DATE , DATE:

CREATE EXTERNAL TABLE ext_twitter_feed(
 date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
   || '/' || SPLIT_PART(metadata$filename, '/', 4)
   || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
 timestamp bigint AS (value:timestamp::bigint),
 col2 varchar AS (value:col2::varchar))
 PARTITION BY (date_part)
 LOCATION=@mystage/daily/
 REFRESH_ON_CREATE =  FALSE
 AUTO_REFRESH = FALSE
 FILE_FORMAT = (TYPE = PARQUET)
 TABLE_FORMAT = DELTA;

Exibição materializada em uma tabela externa¶

Criar uma exibição materializada baseada em uma subconsulta das colunas da tabela externa criada no exemplo Partições adicionadas automaticamente a partir de expressões de colunas de partição:

CREATE MATERIALIZED VIEW et1_mv
  AS
  SELECT col2 FROM et1;

Para sintaxe geral, notas de uso e outros exemplos para este comando SQL, consulte CREATE MATERIALIZED VIEW.

Tabela externa criada com definições de coluna detectadas¶

Criar uma tabela externa onde as definições das colunas são derivadas de um conjunto de arquivos preparados que contenham dados Avro, Parquet ou ORC.

Observe que o estágio mystage e o formato de arquivo my_parquet_format mencionados na instrução já devem existir. Um conjunto de arquivos já deve ser preparado no local de armazenamento em nuvem referenciado na definição do estágio.

Este exemplo se baseia em um exemplo no tópico INFER_SCHEMA:

CREATE EXTERNAL TABLE mytable
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    FROM TABLE(
      INFER_SCHEMA(
        LOCATION=>'@mystage',
        FILE_FORMAT=>'my_parquet_format'
      )
    )
  )
  LOCATION=@mystage
  FILE_FORMAT=my_parquet_format
  AUTO_REFRESH=false;

Copy

Usar * para ARRAY_AGG(OBJECT_CONSTRUCT()) pode resultar em erro se o resultado retornado for maior que 16 MB. Evite usar * para conjuntos de resultados maiores, e use apenas as colunas necessárias, COLUMN NAME, TYPE e NULLABLE, para a consulta, como o exemplo a seguir demonstra. A coluna opcional ORDER_ID pode ser incluída ao usar WITHIN GROUP (ORDER BY order_id).

CREATE EXTERNAL TABLE mytable
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT('COLUMN_NAME',COLUMN_NAME, 'TYPE',TYPE, 'NULLABLE', NULLABLE, 'EXPRESSION',EXPRESSION))
    FROM TABLE(
      INFER_SCHEMA(
        LOCATION=>'@mystage',
        FILE_FORMAT=>'my_parquet_format'
      )
    )
  )
  LOCATION=@mystage
  FILE_FORMAT=my_parquet_format
  AUTO_REFRESH=false;

Copy