CREATE EXTERNAL TABLE

Cria uma nova tabela externa no esquema atual/especificado ou substitui uma tabela externa existente. Quando consultada, uma tabela externa lê os dados de um conjunto de um ou mais arquivos em um estágio externo especificado e os produz em uma única coluna VARIANT.

Colunas adicionais podem ser definidas, sendo que cada definição de coluna consiste em um nome, tipo de dados e, opcionalmente, se a coluna exige um valor (NOT NULL) ou tem alguma restrição de integridade referencial (chave primária, chave estrangeira etc.). Consulte as notas de uso para obter mais informações.

Consulte também:

ALTER EXTERNAL TABLE , DROP EXTERNAL TABLE , SHOW EXTERNAL TABLES , DESCRIBE EXTERNAL TABLE

Sintaxe

-- Partitions computed from expressions
CREATE [ OR REPLACE ] EXTERNAL TABLE [IF NOT EXISTS]
  <table_name>
    ( [ <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ]
      [ inlineConstraint ]
      [ , <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ... ]
      [ , ... ] )
  cloudProviderParams
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  [ WITH ] LOCATION = externalStage
  [ REFRESH_ON_CREATE =  { TRUE | FALSE } ]
  [ AUTO_REFRESH = { TRUE | FALSE } ]
  [ PATTERN = '<regex_pattern>' ]
  FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET } [ formatTypeOptions ] } )
  [ AWS_SNS_TOPIC = '<string>' ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON (VALUE) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

-- Partitions added and removed manually
CREATE [ OR REPLACE ] EXTERNAL TABLE [IF NOT EXISTS]
  <table_name>
    ( [ <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ]
      [ inlineConstraint ]
      [ , <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ... ]
      [ , ... ] )
  cloudProviderParams
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  [ WITH ] LOCATION = externalStage
  PARTITION_TYPE = USER_SPECIFIED
  FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET } [ formatTypeOptions ] } )
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON (VALUE) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

-- Delta Lake
CREATE [ OR REPLACE ] EXTERNAL TABLE [IF NOT EXISTS]
  <table_name>
    ( [ <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ]
      [ inlineConstraint ]
      [ , <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ... ]
      [ , ... ] )
  cloudProviderParams
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  [ WITH ] LOCATION = externalStage
  PARTITION_TYPE = USER_SPECIFIED
  FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET } [ formatTypeOptions ] } )
  [ TABLE_FORMAT = DELTA ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON (VALUE) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
Copy

Onde:

inlineConstraint ::=
  [ NOT NULL ]
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY | [ FOREIGN KEY ] REFERENCES <ref_table_name> [ ( <ref_col_name> [ , <ref_col_name> ] ) ] }
  [ <constraint_properties> ]
Copy

Para detalhes adicionais de restrição em linha, consulte CREATE | ALTER TABLE … CONSTRAINT.

cloudProviderParams (for Google Cloud Storage) ::=
  [ INTEGRATION = '<integration_name>' ]

cloudProviderParams (for Microsoft Azure) ::=
  [ INTEGRATION = '<integration_name>' ]
Copy
externalStage ::=
  @[<namespace>.]<ext_stage_name>[/<path>]
Copy
formatTypeOptions ::=
-- If FILE_FORMAT = ( TYPE = CSV ... )
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
     RECORD_DELIMITER = '<character>' | NONE
     FIELD_DELIMITER = '<character>' | NONE
     SKIP_HEADER = <integer>
     SKIP_BLANK_LINES = TRUE | FALSE
     ESCAPE_UNENCLOSED_FIELD = '<character>' | NONE
     TRIM_SPACE = TRUE | FALSE
     FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | NONE
     NULL_IF = ( '<string1>' [ , '<string2>' , ... ] )
     EMPTY_FIELD_AS_NULL = TRUE | FALSE
     ENCODING = '<string>'
-- If FILE_FORMAT = ( TYPE = JSON ... )
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
     ALLOW_DUPLICATE = TRUE | FALSE
     STRIP_OUTER_ARRAY = TRUE | FALSE
     STRIP_NULL_VALUES = TRUE | FALSE
     REPLACE_INVALID_CHARACTERS = TRUE | FALSE
-- If FILE_FORMAT = ( TYPE = AVRO ... )
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
     REPLACE_INVALID_CHARACTERS = TRUE | FALSE
-- If FILE_FORMAT = ( TYPE = ORC ... )
     TRIM_SPACE = TRUE | FALSE
     REPLACE_INVALID_CHARACTERS = TRUE | FALSE
     NULL_IF = ( '<string>' [ , '<string>' ... ]
-- If FILE_FORMAT = ( TYPE = PARQUET ... )
     COMPRESSION = AUTO | SNAPPY | NONE
     BINARY_AS_TEXT = TRUE | FALSE
     REPLACE_INVALID_CHARACTERS = TRUE | FALSE
Copy

Sintaxe da variante

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

Nota

Se a instrução estiver substituindo uma tabela existente com o mesmo nome, então as concessões são copiadas da tabela que está sendo substituída. Se não existir uma tabela com esse nome, então as concessões são copiadas da tabela de origem que está sendo clonada.

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

Parâmetros obrigatórios

table_name

Cadeia de caracteres que especifica o identificador (ou seja, nome) da tabela; deve ser único para o esquema no qual a tabela é criada.

Além disso, o identificador deve começar com um caractere alfabético e não pode conter espaços ou caracteres especiais, a menos que toda a cadeia de caracteres do identificador esteja entre aspas duplas (por exemplo, "My object"). Os identificadores delimitados por aspas duplas também diferenciam letras maiúsculas de minúsculas.

Para obter mais detalhes, consulte Requisitos para identificadores.

[ WITH ] LOCATION =

Especifica o estágio externo e o caminho opcional onde os arquivos contendo os dados a serem lidos são preparados:

@[namespace.]ext_stage_name[/path]

Os arquivos estão no estágio externo nomeado especificado.

Não há suporte para literais de cadeia de caracteres nem variáveis SQL.

Onde:

  • namespace é o banco de dados e/ou esquema no qual reside o estágio externo, na forma de database_name.schema_name ou schema_name. É opcional se um banco de dados e um esquema estiverem em uso atualmente dentro da sessão do usuário; caso contrário, ele é obrigatório.

  • path é um caminho opcional que distingue letras maiúsculas de minúsculas para arquivos no local de armazenamento em nuvem (ou seja, os arquivos têm nomes que começam com uma cadeia de caracteres comum) que limita o carregamento do conjunto de arquivos. Os caminhos são chamados alternativamente de prefixos ou pastas por diferentes serviços de armazenamento em nuvem.

    A tabela externa anexa este caminho a qualquer caminho especificado na definição do estágio. Para ver a definição do estágio, executar DESC STAGE stage_name e verificar o valor da propriedade url. Por exemplo, se o URL do estágio inclui o caminho a e o local da tabela externa inclui o caminho b, então a tabela externa lê os arquivos preparados em stage/a/b.

    Observe que o valor [ WITH ] LOCATION não pode fazer referência a nomes de arquivos específicos. Para apontar uma tabela externa para arquivos individuais preparados, use o parâmetro PATTERN.

FILE_FORMAT = ( FORMAT_NAME = 'file_format_name' ) ou . FILE_FORMAT = ( TYPE = CSV | JSON | AVRO | ORC | PARQUET [ ... ] )

Cadeia de caracteres (constante) que especifica o formato do arquivo:

FORMAT_NAME = file_format_name

Especifica um formato de arquivo nomeado existente que descreve os arquivos de dados preparados a serem escaneados. O formato de arquivo nomeado determina o tipo de formato (CSV, JSON etc.), bem como quaisquer outras opções de formato, para arquivos de dados.

TYPE = CSV | JSON | AVRO | ORC | PARQUET [ ... ]

Especifica o tipo de formato dos arquivos de dados preparados a serem escaneados ao consultar a tabela externa.

Se um tipo de formato de arquivo for especificado, opções adicionais específicas de formato podem ser determinadas. Para obter mais detalhes, consulte Opções do tipo de formato (neste tópico).

As opções de formato de arquivo podem ser configuradas tanto no nível da tabela externa quanto no nível do estágio. Qualquer configuração especificada no nível da tabela externa tem precedência. Qualquer configuração não especificada em nenhum dos níveis assume os valores padrão.

Padrão: TYPE = CSV.

Importante

A tabela externa não herda o formato do arquivo, se houver, na definição do estágio. Você deve especificar explicitamente quaisquer opções de formato de arquivo para a tabela externa usando o parâmetro FILE_FORMAT.

Nota

FORMAT_NAME e TYPE são mutuamente exclusivos; para evitar comportamentos involuntários, deve-se especificar apenas um ou outro ao criar uma tabela externa.

Parâmetros opcionais

col_name

Cadeia de caracteres que especifica o identificador da coluna (ou seja, o nome). Todos os requisitos de identificadores de tabela também se aplicam aos identificadores de coluna.

Para obter mais detalhes, consulte Requisitos para identificadores.

col_type

Cadeia de caracteres (constante) que especifica o tipo de dados da coluna. O tipo de dados deve corresponder ao resultado de expr da coluna.

Para obter mais detalhes sobre os tipos de dados que podem ser especificados para colunas de tabela, consulte Tipos de dados.

expr

Cadeia de caracteres que especifica a expressão da coluna. Quando consultada, a coluna retorna resultados derivados dessa expressão.

As colunas da tabela externa são colunas virtuais, que são definidas usando uma expressão explícita. Adicione colunas virtuais como expressões usando a coluna VALUE e/ou a pseudocoluna METADATA$FILENAME:

VALUE

Uma coluna do tipo VARIANT que representa uma única linha no arquivo externo.

CSV

A coluna VALUE estrutura cada linha como um objeto com elementos identificados pela posição da coluna (ou seja, {c1: <valor_da_coluna_1>, c2: <valor_da_coluna_2>, c3: <valor_da_coluna_1> ...}).

Por exemplo, adicione uma coluna VARCHAR chamada mycol que faça referência à primeira coluna nos arquivos CSV preparados:

mycol varchar as (value:c1::varchar)
Copy
Dados semiestruturados

Inclua os nomes e valores dos elementos entre aspas duplas. Percorra o caminho na coluna VALUE usando a notação de pontos.

Por exemplo, suponha que o seguinte represente uma única linha de dados semiestruturados em um arquivo preparado:

{ "a":"1", "b": { "c":"2", "d":"3" } }
Copy

Adicione uma coluna VARCHAR chamada mycol que faça referência ao elemento c repetido aninhado no arquivo preparado:

mycol varchar as (value:"b"."c"::varchar)
Copy
METADATA$FILENAME

Uma pseudocoluna que identifica o nome de cada arquivo de dados preparado incluído na tabela externa, incluindo seu caminho no estágio. Para um exemplo, consulte Partições adicionadas automaticamente a partir de expressões de coluna de partição (neste tópico).

CONSTRAINT ...

Cadeia de caracteres que define uma restrição em linha ou fora de linha para a(s) coluna(s) especificada(s) na tabela.

Para detalhes de sintaxe, consulte CREATE | ALTER TABLE … CONSTRAINT. Para obter mais informações sobre restrições, consulte Restrições.

REFRESH_ON_CREATE = TRUE | FALSE

Especifica se deve atualizar automaticamente os metadados da tabela externa uma vez, imediatamente após a criação da tabela externa. A atualização dos metadados da tabela externa sincroniza os metadados com a lista atual de arquivos de dados no caminho de estágio especificado. Esta ação é necessária para que os metadados registrem quaisquer arquivos de dados existentes no estágio nomeado especificado na configuração de [ WITH ] LOCATION =.

TRUE

O Snowflake atualiza automaticamente os metadados da tabela externa uma vez após a criação.

Nota

Se o local especificado contiver quase 1 milheão de arquivos ou mais, recomendamos que você defina REFRESH_ON_CREATE = FALSE. Depois de criar a tabela externa, atualize os metadados de forma incremental executando as instruções ALTER EXTERNAL TABLE … REFRESH que especificam subcaminhos no local (ou seja, subconjuntos de arquivos a serem incluídos na atualização) até que os metadados incluam todos os arquivos no local.

FALSE

O Snowflake não atualiza automaticamente os metadados da tabela externa. Para registrar quaisquer arquivos de dados existentes no estágio, você deve atualizar manualmente os metadados da tabela externa uma vez usando ALTER EXTERNAL TABLE … REFRESH.

Padrão: TRUE

AUTO_REFRESH = TRUE | FALSE

Especifica se o Snowflake deve permitir acionar atualizações automáticas dos metadados da tabela externa quando os arquivos de dados novos ou atualizados estiverem disponíveis no estágio externo nomeado especificado na configuração de [ WITH ] LOCATION =.

Nota

TRUE

O Snowflake permite acionar atualizações automáticas dos metadados da tabela externa.

FALSE

O Snowflake não permite acionar atualizações automáticas dos metadados da tabela externa. Você deve atualizar manualmente os metadados da tabela externa periodicamente usando ALTER EXTERNAL TABLE … REFRESH para sincronizar os metadados com a lista atual de arquivos no caminho do estágio.

Padrão: TRUE

PATTERN = 'regex_pattern'

Uma cadeia de caracteres de padrão de expressão regular, entre aspas simples, especificando os nomes de arquivos e/ou caminhos no estágio externo a serem combinados.

Dica

Para o melhor desempenho, tente evitar a aplicação de padrões que filtram em um grande número de arquivos.

AWS_SNS_TOPIC = 'string'

Necessário somente ao configurar AUTO_REFRESH para estágios Amazon S3 usando o Amazon Simple Notification Service (SNS). Especifica o nome do recurso Amazon (ARN) para o tópico SNS de seu bucket S3. A instrução CREATE EXTERNAL TABLE inscreve a fila do Amazon Simple Queue Service (SQS) no tópico SNS especificado. Notificações de eventos por meio das atualizações de metadados do acionador do tópico SNS. Para obter mais informações, consulte Atualização automática de tabelas externas para Amazon S3.

TABLE_FORMAT = DELTA

Identifica a tabela externa como referenciando um Delta Lake no local de armazenamento em nuvem. Um Delta Lake no armazenamento em nuvem Amazon S3, Google Cloud Storage ou Microsoft Azure é suportado.

Nota

Esse recurso em versão preliminar está disponível para todas as contas.

Quando este parâmetro é definido, a tabela externa procura por arquivos de log de transações Delta Lake no local [ WITH ] LOCATION. Os arquivos de log Delta têm nomes como _delta_log/00000000000000000000.json, _delta_log/00000000000000000010.checkpoint.parquet etc.

Quando os metadados de uma tabela externa são atualizados, o Snowflake analisa os logs de transações do Delta Lake e determina quais arquivos Parquet são atuais. Em segundo plano, a atualização realiza operações de adição e remoção de arquivos para manter os metadados de tabela externa em sincronia.

Nota

  • O estágio externo e o caminho opcional especificados em [ WITH ] LOCATION = devem conter os arquivos de dados e metadados somente para uma única tabela Delta Lake. Ou seja, o local de armazenamento especificado só pode conter um diretório __delta_log.

  • O pedido de notificações de eventos acionados pelas operações DDL no armazenamento em nuvem não é garantido. Portanto, a capacidade de atualização automática não está disponível para tabelas externas que fazem referência aos arquivos do Delta Lake. Ambos REFRESH_ON_CREATE e AUTO_REFRESH devem ser definidos como FALSE.

    Execute periodicamente uma instrução ALTER EXTERNAL TABLE … REFRESH para registrar qualquer arquivo adicionado ou removido.

  • O valor FILE_FORMAT deve especificar Parquet como o tipo de arquivo.

  • Para um ótimo desempenho, recomendamos a definição de colunas de partição para a tabela externa.

  • Os seguintes parâmetros não são suportados quando se faz referência a um Delta Lake:

    • AWS_SNS_TOPIC = 'string'

    • PATTERN = 'regex_pattern'

COPY GRANTS

Especifica a retenção de permissões de acesso da tabela original quando uma tabela externa é recriada usando a variante CREATE OR REPLACE TABLE. O parâmetro copia todas as permissões, exceto OWNERSHIP, da tabela existente para a nova tabela. Por padrão, a função que executa o comando CREATE EXTERNAL TABLE é proprietária da nova tabela externa.

Nota

A operação de cópia de concessões ocorre atomicamente no comando CREATE EXTERNAL TABLE (isto é, dentro da mesma transação).

COMMENT = 'string_literal'

Cadeia de caracteres (literal) que especifica um comentário para a tabela externa.

Padrão: sem valor

ROW ACCESS POLICY <policy_name> ON (VALUE)

Especifica a política de acesso a linhas a ser definida na tabela.

Especifique a coluna VALUE ao aplicar uma política de acesso a linhas a uma tabela externa.

TAG ( tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ] )

Especifica o nome da tag e o valor da cadeia de caracteres dela.

O valor de tag é sempre uma cadeia de caracteres, e o número máximo de caracteres do valor da tag é 256.

Para obter mais detalhes sobre como especificar tags em uma instrução, consulte Cotas de tags para objetos e colunas.

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:

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.

Parâmetros do provedor de nuvem (cloudProviderParams)

Google Cloud Storage

INTEGRATION = integration_name

Especifica o nome da integração de notificação usada para atualizar automaticamente os metadados de tabela externa usando notificações de evento Google Pub/Sub. Uma integração de notificação é um objeto Snowflake que fornece uma interface entre o Snowflake e serviços de enfileiramento de mensagens de terceiros.

Este parâmetro é necessário para permitir operações de atualização automática para a tabela externa. Para instruções sobre a configuração do recurso de atualização automática, consulte Atualização automática de tabelas externas para Google Cloud Storage.

Microsoft Azure

INTEGRATION = integration_name

Especifica o nome da integração da notificação usada para atualizar automaticamente os metadados de tabela externa usando as notificações do Azure Event Grid. Uma integração de notificação é um objeto Snowflake que fornece uma interface entre o Snowflake e serviços de enfileiramento de mensagens de terceiros.

Este parâmetro é necessário para permitir operações de atualização automática para a tabela externa. Para instruções sobre a configuração do recurso de atualização automática, consulte Atualização automática de tabelas externas para o armazenamento de blobs do Azure.

Opções de tipo de formato (formatTypeOptions)

As opções de tipo de formato são usadas para carregar dados e descarregar dados das tabelas.

Dependendo do tipo de formato de arquivo especificado (FILE_FORMAT = ( TYPE = ... )), você pode incluir uma ou mais das seguintes opções de formato específicas (separadas por espaços em branco, vírgulas ou novas linhas):

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.

Nota

Recomendamos que você use a opção padrão AUTO porque ela determinará a compactação do arquivo e do codec. A especificação de uma opção de compactação refere-se à compactação de arquivos, não à compactação de blocos (codecs).

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

Requisitos de controle de acesso

Uma função usada para executar este comando SQL deve ter os seguintes privilégios no mínimo:

Privilégio

Objeto

Notas

CREATE EXTERNAL TABLE

Esquema

CREATE STAGE

Esquema

Necessário se for criado um novo estágio.

USAGE

Estágio

Necessário se for feita referência a um estágio existente.

USAGE

Formato do arquivo

Observe que operar em qualquer objeto de um esquema também requer o privilégio USAGE no banco de dados e esquema principais.

Para instruções sobre como criar uma função personalizada com um conjunto específico de privilégios, consulte Criação de funções personalizadas.

Para informações gerais sobre concessões de funções e privilégios para executar ações de SQL em objetos protegíveis, consulte Visão geral do controle de acesso.

Notas de uso

  • As tabelas externas oferecem suporte somente a estágios externos (ou seja, S3, Azure ou GCS); os estágios internos (ou seja, Snowflake) não são suportados.

    Não é possível acessar dados mantidos em classes de armazenamento em nuvem que exigem restauração antes que possam ser recuperados. Estas classes de armazenamento de arquivos incluem, por exemplo, a Amazon S3 Glacier Flexible Retrieval ou Glacier Deep Archive, o ainda o Microsoft Azure Archive Storage.

  • O Snowflake não impõe restrições de integridade em tabelas externas. Em particular, ao contrário das tabelas normais, o Snowflake não impõe restrições NOT NULL.

  • As tabelas externas incluem a seguinte coluna de metadados:

    • METADATA$FILENAME: nome de cada arquivo de dados preparado incluído na tabela externa. Inclui o caminho para o arquivo de dados no estágio.

    • METADATA$FILE_ROW_NUMBER: número de linha para cada registro no arquivo de dados preparado.

  • As tabelas a seguir não são suportadas para tabelas externas:

    • Chaves de clustering

    • Clonagem

    • Dados no formato XML.

  • O Time Travel não é suportado para tabelas externas.

  • Políticas:

    • Você não pode adicionar uma política de mascaramento a uma coluna de tabela externa enquanto cria a tabela externa, porque uma política de mascaramento não pode ser anexada a uma coluna virtual.

    • Você pode adicionar uma política de acesso a linhas a uma tabela externa ao criar a tabela externa.

  • Usar OR REPLACE é o equivalente a usar DROP EXTERNAL TABLE na tabela externa existente e depois criar uma nova tabela externa com o mesmo nome.

    Instruções CREATE OR REPLACE <object> são atômicas. Ou seja, quando um objeto é substituído, o objeto antigo é excluído e o novo objeto é criado em uma única transação.

    Isso significa que qualquer consulta concorrente com a operação CREATE OR REPLACE EXTERNAL TABLE utiliza a versão da tabela externa antiga ou nova.

  • Em relação aos metadados:

    Atenção

    Os clientes devem garantir que nenhum dado pessoal (exceto para um objeto do usuário), dados sensíveis, dados controlados por exportação ou outros dados regulamentados sejam inseridos como metadados ao usar o serviço Snowflake. Para obter mais informações, consulte Campos de metadados no Snowflake.

  • Ao criar uma tabela externa com uma política de acesso a linhas adicionada à tabela externa, use a função POLICY_CONTEXT para simular uma consulta na tabela externa protegida por uma política de acesso a linhas.

  • SELECT * sempre retorna a coluna VALUE, na qual todos os dados regulares ou semiestruturados são convertidos nas linhas de variante.

Exemplos

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

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

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

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

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