Introdução às tabelas externas¶

Uma tabela externa é um recurso do Snowflake que permite consultar dados armazenados em um estágio externo como se os dados estivessem dentro de uma tabela no Snowflake. O estágio externo não faz parte do Snowflake, então o Snowflake não armazena ou gerencia o estágio.

As tabelas externas permitem armazenar (no Snowflake) determinados metadados em nível de arquivo, incluindo nomes de arquivos, identificadores de versão e propriedades relacionadas. Tabelas externas podem acessar dados armazenados em qualquer formato que o comando COPY INTO <tabela> suporte.

As tabelas externas são somente leitura. Você não pode executar operações da linguagem de manipulação de dados (DML) neles. No entanto, você pode usar tabelas externas para operações de consulta e junção. Você também pode criar exibições em tabelas externas.

Consultar dados em uma tabela externa pode ser mais lento do que consultar dados armazenados nativamente em uma tabela no Snowflake. Para melhorar o desempenho da consulta, você pode usar uma exibição materializada com base em uma tabela externa.

Nota

Se o Snowflake encontrar um erro durante a verificação de um arquivo no armazenamento em nuvem durante uma operação de consulta, o arquivo é ignorado e a verificação continua no arquivo seguinte. Uma consulta pode verificar parcialmente um arquivo e retornar as linhas verificadas antes de o erro ter sido encontrado.

Neste tópico:

Planejamento do esquema de uma tabela externa¶

Esta seção descreve as opções disponíveis para projetar tabelas externas.

Esquema em leitura¶

Todas as tabelas externas incluem as seguintes colunas:

VALUE:: Uma coluna do tipo VARIANT que representa uma única linha no arquivo externo.
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.
METADATA$FILE_ROW_NUMBER:: Uma pseudocoluna que mostra o número da linha para cada registro em um arquivo de dados preparado.

Para criar tabelas externas, você só precisa ter algum conhecimento do formato do arquivo e do formato de registro dos arquivos de dados de origem. O conhecimento do esquema dos arquivos de dados não é necessário.

Observe que SELECT * sempre retorna a coluna VALUE, na qual todos os dados regulares ou semiestruturados são convertidos em linhas de variantes.

Colunas virtuais¶

Se você estiver familiarizado com o esquema dos arquivos de dados de origem, pode criar colunas virtuais adicionais como expressões usando a coluna VALUE e/ou as pseudocolunas METADATA$FILENAME ou METADATA$FILE_ROW_NUMBER. Quando os dados externos são verificados, os tipos de dados de quaisquer campos especificados ou elementos de dados semiestruturados no arquivo de dados devem corresponder aos tipos de dados dessas colunas adicionais na tabela externa. Isto permite uma avançada verificação do tipo e validação do esquema para dados externos.

Recomendações gerais de dimensionamento de arquivos¶

Para otimizar o número de operações de verificação paralelas ao consultar tabelas externas, recomendamos os seguintes tamanhos de arquivo ou grupos de linhas por formato:

Formato	Intervalo de tamanho recomendado	Notas
Arquivos de parquet	256 - 512 MB
Grupos de linhas de parquet	16 - 256 MB	Observe que quando os arquivos Parquet incluem vários grupos de linhas, o Snowflake pode operar em cada grupo de linhas em um servidor diferente. Para melhorar o desempenho da consulta, recomendamos dimensionar os arquivos Parquet no intervalo recomendado; ou, se forem necessários arquivos de grandes dimensões, incluir vários grupos de linhas em cada arquivo.
Todos os outros formatos de arquivo com suporte	16 - 256 MB

Para um desempenho ideal ao consultar grandes arquivos de dados, crie e consulte exibições materializadas em tabelas externas.

Tabelas externas particionadas¶

Recomendamos enfaticamente particionar suas tabelas externas, o que requer que seus dados subjacentes sejam organizados usando caminhos lógicos que incluam data, hora, país ou dimensões similares no caminho. A partição divide seus dados da tabela externa em várias partes usando colunas de partição.

Uma definição de tabela externa pode incluir várias colunas de partição, que impõem uma estrutura multidimensional sobre os dados externos. As partições são armazenadas nos metadados da tabela externa.

Os benefícios do particionamento incluem a melhoria do desempenho das consultas. Como os dados externos são particionados em fatias/partes separadas, o tempo de resposta da consulta é mais rápido ao processar uma pequena parte dos dados em vez de verificar todo o conjunto de dados.

Com base em seus casos de uso individuais, você pode escolher uma das alternativas:

Adicionar novas partições automaticamente atualizando uma tabela externa que define uma expressão para cada coluna de partição.
Adicionar novas partições manualmente.

As colunas de partição são definidas quando uma tabela externa é criada, usando a sintaxe CREATE EXTERNAL TABLE … PARTITION BY. Depois que uma tabela externa é criada, o método pelo qual as partições são adicionadas não pode ser modificado.

As seções seguintes explicam as diferentes opções para adicionar partições com mais detalhes. Para exemplos, consulte CREATE EXTERNAL TABLE.

Partições adicionadas automaticamente¶

Um criador de tabela externa define colunas de partição em uma nova tabela externa como expressões que analisam as informações de caminho e/ou nome de arquivo armazenadas na pseudocoluna METADATA$FILENAME. 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.

A sintaxe CREATE EXTERNAL TABLE para adicionar partições automaticamente com base em expressões é a seguinte:

CREATE EXTERNAL TABLE
  <table_name>
     ( <part_col_name> <col_type> AS <part_expr> )
     [ , ... ]
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  ..

Copy

O Snowflake calcula e adiciona partições com base nas expressões das colunas de partição definidas quando um metadados de tabela externa é atualizado. Por padrão, os metadados são atualizados automaticamente quando o objeto é criado. Além disso, o proprietário do objeto pode configurar os metadados para serem atualizados automaticamente quando arquivos de dados novos ou atualizados estiverem disponíveis no estágio externo. O proprietário pode, alternativamente, atualizar os metadados manualmente executando o comando ALTER EXTERNAL TABLE … REFRESH.

Partições adicionadas manualmente¶

Um criador de tabela externa determina o tipo de partição de uma nova tabela externa como definida pelo usuário e especifica apenas os tipos de dados das colunas de partição. Use esta opção quando preferir adicionar e remover partições seletivamente em vez de adicionar automaticamente partições para todos os novos arquivos em um local de armazenamento externo que corresponda a uma expressão.

Esta opção geralmente é escolhida para sincronizar tabelas externas com outros metastores (por exemplo, AWS Glue ou Apache Hive).

A sintaxe CREATE EXTERNAL TABLE para partições adicionadas manualmente é a seguinte:

CREATE EXTERNAL TABLE
  <table_name>
     ( <part_col_name> <col_type> AS <part_expr> )
     [ , ... ]
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  PARTITION_TYPE = USER_SPECIFIED
  ..

Copy

Inclua o parâmetro PARTITION_TYPE = USER_SPECIFIED obrigatório.

As definições da coluna de partição são expressões que analisam os metadados de coluna na coluna interna (oculta) METADATA$EXTERNAL_TABLE_PARTITION.

O proprietário do objeto adiciona manualmente as partições aos metadados da tabela externa executando o comando ALTER EXTERNAL TABLE … ADD PARTITION:

ALTER EXTERNAL TABLE <name> ADD PARTITION ( <part_col_name> = '<string>' [ , <part_col_name> = '<string>' ] ) LOCATION '<path>'

Copy

A atualização automática de uma tabela externa com partições definidas pelo usuário não tem suporte. A tentativa de atualizar manualmente este tipo de tabela externa produz um erro do usuário.

Suporte para Delta Lake¶

Delta Lake é um formato de tabela em seu data lake que suporta transações ACID (atomicidade, consistência, isolamento, durabilidade), entre outros recursos. Todos os dados em Delta Lake são armazenados no formato Apache Parquet. Crie tabelas externas que referenciem seus locais de armazenamento em nuvem aprimorados com Delta Lake.

Para criar uma tabela externa que faça referência a um Data Lake, defina o parâmetro TABLE_FORMAT = DELTA na instrução CREATE EXTERNAL TABLE.

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.

Observe que 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 de metadados não está disponível para tabelas externas que fazem referência aos arquivos do Delta Lake. Em vez disso, execute periodicamente uma instrução ALTER EXTERNAL TABLE … REFRESH para registrar qualquer arquivo adicionado ou removido.

Para obter mais informações, incluindo exemplos, consulte CREATE EXTERNAL TABLE.

Adição ou descarte de colunas¶

Altere uma tabela externa existente para adicionar ou remover colunas usando a seguinte sintaxe ALTER TABLE:

Adicionar colunas: ALTER TABLE … ADD COLUMN.
Remover colunas: ALTER TABLE … DROP COLUMN.

Nota

A coluna padrão VALUE e as pseudocolunas METADATA$FILENAME e METADATA$FILE_ROW_NUMBER não podem ser descartadas.

Consulte o exemplo em ALTER TABLE.

Proteção de tabelas externas¶

Você pode proteger uma tabela externa usando uma política de mascaramento e uma política de acesso a linhas. Para obter mais detalhes, consulte:

Exibições materializadas para tabelas externas¶

Em muitos casos, exibições materializadas para tabelas externas podem proporcionar um desempenho que é mais rápido do que as consultas equivalentes para a tabela externa subjacente. Esta diferença de desempenho pode ser significativa quando uma consulta é feita com frequência ou é suficientemente complexa.

Atualize os metadados em nível de arquivo em qualquer tabela externa consultada para que suas exibições materializadas reflitam o conjunto de arquivos atual no local de armazenamento em nuvem referenciado.

Você pode atualizar os metadados de uma tabela externa automaticamente usando o serviço de notificação de eventos para seu serviço de armazenamento em nuvem ou manualmente usando instruções ALTER EXTERNAL TABLE … REFRESH.

Atualização automática dos metadados de tabela externa¶

Os metadados de uma tabela externa podem ser atualizados automaticamente usando o serviço de notificação de eventos para seu serviço de armazenamento em nuvem.

A operação de atualização sincroniza os metadados com o último conjunto de arquivos associados no caminho e estágio externo, ou seja:

Novos arquivos no caminho são adicionados aos metadados da tabela.

As mudanças nos arquivos do caminho são atualizadas nos metadados da tabela.

Os arquivos que não estão mais no caminho são removidos dos metadados da tabela.

Para obter mais informações, consulte Atualização automática de tabelas externas.

Faturamento para tabelas externas¶

Uma sobretaxa para gerenciar notificações de eventos para a atualização automática de metadados de tabelas externas está incluída em suas taxas. Esta sobretaxa aumenta em relação ao número de arquivos adicionados no armazenamento em nuvem para os estágios externos e caminhos especificados para suas tabelas externas. 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. Você pode estimar essa cobrança consultando a função PIPE_USAGE_HISTORY ou examinando o Account Usage Exibição PIPE_USAGE_HISTORY.

Além disso, uma pequena sobretaxa de manutenção é cobrada para atualizar manualmente os metadados de tabela externa (usando ALTER EXTERNAL TABLE … REFRESH). Essa sobretaxa é cobrada de acordo com o modelo de faturamento de serviços de nuvem padrão, como todas as atividades similares no Snowflake. Atualizações manuais de tabelas externas padrão são apenas operações de serviços em nuvem; entretanto, atualizações manuais de tabelas externas aprimoradas com Delta Lake dependem de recursos computacionais gerenciados pelo usuário (ou seja, um warehouse virtual).

Os usuários com a função ACCOUNTADMIN, ou uma função com o privilégio global MONITOR USAGE, podem consultar a função de tabela AUTO_REFRESH_REGISTRATION_HISTORY para recuperar o histórico dos arquivos de dados registrados nos metadados dos objetos especificados e os créditos faturados por essas operações.

Fluxo de trabalho¶

Amazon S3¶

Esta seção fornece uma visão geral de alto nível do fluxo de trabalho de configuração e carga das tabelas externas que fazem referência aos estágios do Amazon S3. Para instruções completas, consulte Atualização automática de tabelas externas para Amazon S3.

Crie um objeto de preparação nomeado (usando CREATE STAGE) que faça referência ao local externo (ou seja, bucket S3) onde seus arquivos de dados são preparados.
Crie uma tabela externa (usando CREATE EXTERNAL TABLE) que faça referência ao estágio nomeado.
Atualize manualmente os metadados de tabela externa usando ALTER EXTERNAL TABLE … REFRESH para sincronizar os metadados com a lista atual de arquivos no caminho do estágio. Esta etapa também verifica as configurações em sua definição da tabela externa.
Configure uma notificação de evento para o bucket S3. O Snowflake depende de notificações de eventos para atualizar continuamente os metadados de tabela externa para manter a consistência com os arquivos preparados.
Atualize manualmente os metadados de tabela externa mais uma vez usando ALTER EXTERNAL TABLE … REFRESH para sincronizar os metadados com quaisquer alterações ocorridas desde a Etapa 3. Depois disso, as notificações de eventos S3 acionam automaticamente a atualização dos metadados.
Configure privilégios de controle de acesso ao Snowflake para quaisquer funções adicionais a fim de conceder-lhes acesso à tabela externa.

Google Cloud Storage¶

Esta seção fornece uma visão geral de alto nível do fluxo de trabalho de configuração e carga das tabelas externas que fazem referência aos estágios do Google Cloud Storage (GCS).

Configure uma assinatura do Google Pub/Sub para eventos GCS.
Crie uma integração de notificação no Snowflake. 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, como Pub/Sub.
Crie um objeto de preparação nomeado (usando CREATE STAGE) que faça referência ao local externo (ou seja, bucket GCS) onde seus arquivos de dados são preparados.
Crie uma tabela externa (usando CREATE EXTERNAL TABLE) que faça referência ao estágio nomeado e integração.
Atualize manualmente os metadados de tabela externa uma vez usando ALTER EXTERNAL TABLE … REFRESH para sincronizar os metadados com quaisquer alterações ocorridas desde a Etapa 4. Depois disso, as notificações de eventos Pub/Sub acionam automaticamente a atualização dos metadados.
Configure privilégios de controle de acesso ao Snowflake para quaisquer funções adicionais a fim de conceder-lhes acesso à tabela externa.

Microsoft Azure¶

Esta seção fornece uma visão geral de alto nível do fluxo de trabalho de configuração e carga das tabelas externas que fazem referência aos estágios do Azure. Para instruções completas, consulte Atualização automática de tabelas externas para o armazenamento de blobs do Azure.

Configure uma assinatura da Event Grid para eventos de armazenamento Azure.
Crie uma integração de notificação no Snowflake. 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, como a Event Grid da Microsoft.
Crie um objeto de preparação nomeado (usando CREATE STAGE) que faça referência ao local externo (ou seja, contêiner Azure) onde seus arquivos de dados são preparados.
Crie uma tabela externa (usando CREATE EXTERNAL TABLE) que faça referência ao estágio nomeado e integração.
Atualize manualmente os metadados de tabela externa uma vez usando ALTER EXTERNAL TABLE … REFRESH para sincronizar os metadados com quaisquer alterações ocorridas desde a Etapa 4. Depois disso, as notificações da Event Grid acionam automaticamente a atualização dos metadados.
Configure privilégios de controle de acesso ao Snowflake para quaisquer funções adicionais a fim de conceder-lhes acesso à tabela externa.

Consulta de tabelas externas¶

Consulte tabelas externas como você faria com as tabelas padrão.

Filtragem de registros em arquivos Parquet¶

Para tirar proveito das estatísticas de grupos de linhas para remover dados de arquivos Parquet, uma cláusula WHERE pode incluir colunas de partição ou colunas regulares, ou ambas. Aplicam-se as seguintes limitações:

A cláusula não pode incluir nenhuma coluna VARIANT.
A cláusula só pode incluir um ou mais dos seguintes operadores de comparação:
- =
- >
- <
A cláusula só pode incluir um ou mais operadores lógicos/booleanos, assim como a função STARTSWITH SQL.

Além disso, consultas no formato "value:<caminho>::<tipo de dados>" (ou o equivalente à função GET/ GET_PATH , :) aproveitam o verificador vetorizado. Consultas no formato "value" ou simplesmente "value:<caminho>" são processadas usando o verificador não vetorizado. Converta todos os dados de fuso horário em um fuso horário padrão usando a função CONVERT_TIMEZONE para consultas que usam o verificador vetorizado.

Quando os arquivos são classificados por uma chave incluída em um filtro de consulta, e se houver vários grupos de linhas nos arquivos, melhores resultados de remoção são possíveis.

A tabela a seguir mostra estruturas de consulta similares que ilustram os comportamentos nesta seção, onde et é uma tabela externa e c1, c2, e c3 são colunas virtuais:

Otimizado	Não otimizado
`SELECT c1, c2, c3 FROM et;`	`SELECT value:c1, c2, c3 FROM et;`
`SELECT c1, c2, c3 FROM et WHERE c1 = 'foo';` `SELECT c1, c2, c3 FROM et WHERE value:c1::string = 'foo';`	`SELECT c1, c2, c3 FROM et WHERE value:c1 = 'foo';`

Otimizado

Não otimizado

SELECT c1, c2, c3 FROM et;

SELECT value:c1, c2, c3 FROM et;

SELECT c1, c2, c3 FROM et WHERE c1 = 'foo';

SELECT c1, c2, c3 FROM et WHERE value:c1::string = 'foo';

SELECT c1, c2, c3 FROM et WHERE value:c1 = 'foo';

Resultados da consulta persistente¶

Similar às tabelas, os resultados da consulta para tabelas externas persistem durante 24 horas. Dentro deste período de 24 horas, as seguintes operações invalidam e limpam o cache de resultados da consulta para tabelas externas:

Qualquer operação DDL que modifique a definição da tabela externa. Isto inclui modificar explicitamente a definição da tabela externa (usando ALTER EXTERNAL TABLE) ou recriar a tabela externa (usando CREATE OR REPLACE EXTERNAL TABLE).
Alterações no conjunto de arquivos no armazenamento em nuvem que são registrados nos metadados da tabela externa. As operações de atualização automática usando o serviço de notificação de eventos para o local de armazenamento ou as operações de atualização manual (usando ALTER EXTERNAL TABLE … REFRESH) invalidam o cache de resultados.

Observe que alterações nos arquivos referenciados no armazenamento em nuvem não invalidam o cache de resultados da consulta nas seguintes circunstâncias, levando a resultados de consulta desatualizados:

A operação de atualização automatizada está desativada (ou seja, AUTO_REFRESH = FALSE) ou não está configurada corretamente.
Os metadados de tabela externa não são atualizados manualmente.

Remoção de arquivos preparados mais antigos dos metadados de tabelas externas¶

Um procedimento armazenado pode remover arquivos preparados mais antigos dos metadados de uma tabela externa usando uma instrução ALTER EXTERNAL TABLE … REMOVE FILES. O procedimento armazenado removeria os arquivos dos metadados com base na data de última modificação no estágio.

Por exemplo:

Crie o procedimento armazenado usando uma instrução CREATE PROCEDURE:

CREATE or replace PROCEDURE remove_old_files(external_table_name varchar, num_days float)
  RETURNS varchar
  LANGUAGE javascript
  EXECUTE AS CALLER
  AS
  $$
  // 1. Get the relative path of the external table
  // 2. Find all files registered before the specified time period
  // 3. Remove the files


  var resultSet1 = snowflake.execute({ sqlText:
    `call exttable_bucket_relative_path('` + EXTERNAL_TABLE_NAME + `');`
  });
  resultSet1.next();
  var relPath = resultSet1.getColumnValue(1);


  var resultSet2 = snowflake.execute({ sqlText:
    `select file_name
     from table(information_schema.EXTERNAL_TABLE_FILES (
         TABLE_NAME => '` + EXTERNAL_TABLE_NAME +`'))
     where last_modified < dateadd(day, -` + NUM_DAYS + `, current_timestamp());`
  });

  var fileNames = [];
  while (resultSet2.next())
  {
    fileNames.push(resultSet2.getColumnValue(1).substring(relPath.length));
  }

  if (fileNames.length == 0)
  {
    return 'nothing to do';
  }


  var alterCommand = `ALTER EXTERNAL TABLE ` + EXTERNAL_TABLE_NAME + ` REMOVE FILES ('` + fileNames.join(`', '`) + `');`;

  var resultSet3 = snowflake.execute({ sqlText: alterCommand });

  var results = [];
  while (resultSet3.next())
  {
    results.push(resultSet3.getColumnValue(1) + ' -> ' + resultSet3.getColumnValue(2));
  }

  return results.length + ' files: \n' + results.join('\n');

  $$;

  CREATE or replace PROCEDURE exttable_bucket_relative_path(external_table_name varchar)
  RETURNS varchar
  LANGUAGE javascript
  EXECUTE AS CALLER
  AS
  $$
  var resultSet = snowflake.execute({ sqlText:
    `show external tables like '` + EXTERNAL_TABLE_NAME + `';`
  });

  resultSet.next();
  var location = resultSet.getColumnValue(10);

  var relPath = location.split('/').slice(3).join('/');
  return relPath.endsWith("/") ? relPath : relPath + "/";

  $$;

Copy

Chame o procedimento armazenado:

-- Remove all files from the exttable external table metadata:
call remove_old_files('exttable', 0);

-- Remove files staged longer than 90 days ago from the exttable external table metadata:
call remove_old_files('exttable', 90);

Copy

Alternativamente, crie uma tarefa usando CREATE TASK que chama o procedimento armazenado periodicamente para remover arquivos mais antigos dos metadados de tabela externa.

Integração do metastore do Apache Hive¶

O Snowflake suporta a integração de metastores do Apache Hive com o Snowflake utilizando tabelas externas. O conector do Hive detecta eventos de metastore e os transmite ao Snowflake para manter as tabelas externas sincronizadas com o metastore do Hive. Isto permite aos usuários gerenciar seus dados no Hive enquanto os consultam com o Snowflake.

Para obter instruções, consulte Integração de metastores do Apache Hive com o Snowflake.

DDL de tabela externa¶

Para apoiar a criação e o gerenciamento de tabelas externas, o Snowflake fornece o seguinte conjunto de comandos DDL especiais:

Privilégios de acesso obrigatórios¶

A criação e o gerenciamento de tabelas externas requer uma função com um mínimo das seguintes permissões de função:

Objeto	Privilégio
Banco de dados	USAGE
Esquema	USAGE CREATE STAGE (se estiver criando um novo estágio), CREATE EXTERNAL TABLE
Estágio (se utilizando um estágio existente)	USAGE

Information Schema¶

O Snowflake Snowflake Information Schema inclui exibições e funções de tabela que você pode consultar para recuperar informações sobre suas tabelas externas e seus arquivos de dados preparados.

Exibição¶

Exibição EXTERNAL_TABLES: Exibe informações de tabelas externas no banco de dados especificado (ou atual).

Funções de tabela¶

AUTO_REFRESH_REGISTRATION_HISTORY: Recuperar o histórico dos arquivos de dados registrados nos metadados dos objetos especificados e os créditos faturados por essas operações.
EXTERNAL_TABLE_FILES: Recupere informações sobre os arquivos de dados preparados incluídos nos metadados de uma tabela externa específica.
EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY: Recupere informações sobre o histórico dos metadados para uma tabela externa, incluindo quaisquer erros encontrados ao atualizar os metadados.