Usar tabelas externas com Synapse SQL

  • Artigo

Uma tabela externa aponta para dados localizados no Hadoop, no Azure Storage Blob ou no Azure Data Lake Storage. Use as tabelas externas para ler dados de arquivos ou gravar dados em arquivos no Armazenamento do Azure.

Com o SQL do Synapse, você pode usar tabelas externas para ler dados externos usando pool de SQL dedicado ou pool de SQL sem servidor.

Dependendo do tipo da fonte de dados externa, você pode usar dois tipos de tabelas externas:

  • Tabelas externas do Hadoop que você pode usar para ler e exportar dados em vários formatos, como CSV, Parquet e ORC. As tabelas externas do Hadoop são disponibilizadas em pools dedicados do SQL, mas não estão disponíveis em pools de SQL sem servidor.
  • Tabelas externas nativas que você pode usar para ler e exportar dados em vários formatos de dados, como CSV e Parquet. As tabelas externas nativas são disponibilizadas em pools de SQL sem servidor e estão em versão prévia pública em pools de SQL dedicados. A gravação/a exportação de dados usando o CETAS e as tabelas externas nativas só estão disponíveis no pool de SQL sem servidor, mas não nos pools de SQL dedicados.

As principais diferenças entre o Hadoop e as tabelas externas nativas:

Tipo da tabela externa O Hadoop Nativo
Pool de SQL dedicado Disponível Apenas as tabelas do Parquet estão disponíveis em versão prévia pública.
Pool de SQL sem servidor Não disponível Disponível
Formatos com suporte Delimitado/CSV, Parquet, ORC, Hive RC e RC Pool de SQL sem servidor: Delimitado/CSV, Parquet e Delta Lake
Pool de SQL dedicado: Parquet (versão prévia)
Eliminação de partição de pasta No A eliminação de partição está disponível apenas nas tabelas particionadas criadas em formatos Parquet ou CSV sincronizados de pools do Apache Spark. É possível criar tabelas externas em pastas particionadas por Parquet, mas as colunas de particionamento são inacessíveis e ignoradas, enquanto a eliminação de partição não será aplicada. Não crie tabelas externas em pastas Delta Lake porque elas não têm suporte. Use exibições particionadas Delta se precisar consultar dados particionados do Delta Lake.
Eliminação de arquivos (pushdown de predicado) Não Sim, no pool de SQL sem servidor. Para o pushdown de cadeia de caracteres, você precisa usar o agrupamento Latin1_General_100_BIN2_UTF8 nas colunas VARCHAR para habilitar o pushdown. Para obter mais informações sobre os agrupamentos, consulte Tipos de agrupamentos compatíveis com o SQL do Synapse.
Formato personalizado para localização No Sim, usando curingas como /year=*/month=*/day=* para formatos Parquet ou CSV. Caminhos de pasta personalizados não estão disponíveis no Delta Lake. No pool de SQL sem servidor, também é possível usar curingas recursivos /logs/** para fazer referência a arquivos Parquet ou CSV em qualquer subpasta abaixo da pasta referenciada.
Verificação de pasta recursiva Sim Sim. Em pools de SQL sem servidor, /** precisa ser especificado no final do caminho do local. No pool dedicado, as pastas são sempre verificadas recursivamente.
Autenticação de armazenamento Chave de Acesso de Armazenamento (SAK), passagem do Microsoft Entra, Identidade gerenciada, identidade personalizada do aplicativo Microsoft Entra Assinatura de Acesso Compartilhado (SAS), passagem do Microsoft Entra, Identidade gerenciada, identidade do Aplicativo personalizado do Microsoft Entra.
Mapeamento de coluna Ordinal – as colunas na definição de tabela externa são mapeadas para as colunas nos arquivos Parquet subjacentes por posição. Pool sem servidor: por nome. As colunas na definição de tabela externa são mapeadas para as colunas nos arquivos Parquet subjacentes por correspondência de nome de coluna.
Pool dedicado: correspondência ordinal. As colunas na definição de tabela externa são mapeadas para as colunas nos arquivos Parquet subjacentes por posição.
CETAS (exportação/transformação) Sim O CETAS com as tabelas nativas como destino só funciona no pool de SQL sem servidor. Não é possível usar os pools de SQL dedicados para exportar dados usando tabelas nativas.

Observação

As tabelas externas nativas são a solução recomendada nos pools onde estão geralmente disponíveis. Se você precisar acessar dados externos, sempre use as tabelas nativas em pools sem servidor. Em pools dedicados, você deve alternar para as tabelas nativas para ler arquivos Parquet quando eles estiverem em GA. Use as tabelas do Hadoop somente se precisar acessar alguns tipos que não têm suporte em tabelas externas nativas (por exemplo, ORC, RC) ou se a versão nativa não estiver disponível.

Tabelas externas no pool de SQL dedicado e no pool de SQL sem servidor

Você pode usar tabelas externas para:

  • Consultar o Armazenamento de Blobs do Azure e o Azure Data Lake Gen2 com instruções Transact-SQL.
  • Armazenar resultados de consulta em arquivos no Armazenamento de Blobs do Azure ou no Azure Data Lake Storage usando CETAS.
  • Importar dados do Armazenamento de Blobs do Azure e Azure Data Lake Storage e armazená-los em um pool de SQL dedicado (somente tabelas Hadoop no pool dedicado).

Observação

Quando usada em conjunto com a instrução CREATE TABLE AS SELECT, a seleção em uma tabela externa importa dados para uma tabela no pool de SQL dedicado.

Se o desempenho das tabelas externas do Hadoop nos pools dedicados não atender às suas metas de desempenho, considere carregar dados externos nas tabelas DataWarehouse usando a instrução Copy.

Para ver um tutorial de carregamento, confira Usar o PolyBase para carregar dados do Armazenamento de Blobs do Azure.

Você pode criar tabelas externas nos pools de SQL do Synapse por meio das seguintes etapas:

  1. CRIE UMA FONTE DE DADOS EXTERNA para fazer referência a um armazenamento externo do Azure, e especifique a credencial que deve ser usada para acessar o armazenamento.
  2. CRIE UM FORMATO DE ARQUIVO EXTERNO para descrever o formato dos arquivos CSV ou Parquet.
  3. CRIE UMA TABELA EXTERNA na parte superior dos arquivos colocados na fonte de dados com o mesmo formato de arquivo.

Eliminação de partição de pasta

As tabelas externas nativas nos pools do Synapse podem ignorar os arquivos colocados nas pastas que não são relevantes para as consultas. Se os arquivos estiverem armazenados em uma hierarquia de pastas (por exemplo,- /year=2020/month=03/day=16) e os valores de year, monthe day forem expostos como colunas, as consultas que contêm filtros como year=2020 lerá os arquivos somente das subpastas colocadas na pasta year=2020. Os arquivos e pastas colocados em outras pastas (year=2021 ou year=2022) serão ignorados nessa consulta. Essa eliminação é conhecida como eliminação de partição.

A eliminação de partição de pasta está disponível nas tabelas externas nativas que são sincronizadas dos Pools do Spark Synapse. Se você tiver um conjunto de dados particionado e quiser aproveitar a eliminação de partição com as tabelas externas criadas, use as exibições particionadas em vez das tabelas externas.

Eliminação de arquivos

Alguns formatos de dados, como Parquet e Delta, contêm estatísticas de arquivo para cada coluna (por exemplo, valores mínimos/máximos para cada coluna). As consultas que filtram dados não leem os arquivos em que os valores de coluna necessários não existem. A consulta primeiro irá explorar os valores mínimos/máximos para as colunas usadas no predicado de consulta para localizar os arquivos que não contêm os dados necessários. Esses arquivos serão ignorados e eliminados do plano de consulta. Essa técnica também é conhecida como pushdown de predicado de filtro e pode melhorar o desempenho de suas consultas. O pushdown de filtro está disponível nos pools de SQL sem servidor nos formatos de Parquet e Delta. Para aproveitar o pushdown de filtro para os tipos de cadeia de caracteres, use o tipo VARCHAR com o agrupamento Latin1_General_100_BIN2_UTF8. Para obter mais informações sobre os agrupamentos, consulte Tipos de agrupamentos compatíveis com o SQL do Synapse.

Segurança

O usuário deve ter a permissão SELECT em uma tabela externa para ler os dados. Acesso às tabelas externas subjacente ao Armazenamento do Azure usando a credencial com escopo do banco de dados definida na fonte de dados usando as seguintes regras:

  • Uma fonte de dados sem credencial habilita as tabelas externas a acessarem arquivos publicamente disponíveis no Armazenamento do Azure.
  • A fonte de dados pode ter uma credencial que habilita as tabelas externas a acessarem somente os arquivos no Armazenamento do Azure usando o token SAS ou a Identidade Gerenciada do workspace – para ver exemplos, confira o artigo Desenvolver o controle de acesso ao armazenamento de arquivos.

Exemplo de CREATE EXTERNAL DATA SOURCE

O seguinte exemplo cria uma fonte de dados externa do Hadoop em um pool de SQL dedicado para o Azure Data Lake Gen2 apontando para o conjunto de dados de Nova York:

CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
GO
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net' ,
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  ) ;

O seguinte exemplo cria uma fonte de dados externa para o Azure Data Lake Gen2 apontando para o conjunto de dados de Nova York disponível publicamente:

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',
       TYPE = HADOOP)

Exemplo de CREATE EXTERNAL FILE FORMAT

O seguinte exemplo cria um formato de arquivo externo para arquivos de censo:

CREATE EXTERNAL FILE FORMAT census_file_format
WITH
(  
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)

Exemplo de CREATE EXTERNAL TABLE

O exemplo a seguir cria uma tabela externa. Ele retorna a primeira linha:

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)
GO

SELECT TOP 1 * FROM census_external_table

Criar e consultar tabelas externas de um arquivo no Azure Data Lake

Usando os recursos de exploração de Data Lake do Synapse Studio, agora você pode criar e consultar uma tabela externa usando o pool de SQL do Synapse com um simples clique com o botão direito do mouse no arquivo. Só há suporte para o gesto de único clique para criar tabelas externas da conta de armazenamento do ADLS Gen2 para arquivos Parquet.

Pré-requisitos

  • Você precisa ter acesso ao espaço de trabalho com pelo menos a função de acesso Storage Blob Data Contributor à conta ADLS Gen2 ou às ACLs (Listas de Controle de Acesso) que permitem consultar os arquivos.

  • Você deve ter pelo menos permissões para criar uma tabela externa e consultar tabelas externas no pool de SQL do Synapse (dedicado ou sem servidor).

No painel Dados, selecione o arquivo do qual você gostaria de criar a tabela externa:

externaltable1

Uma janela de diálogo será aberta. Selecione o pool de SQL dedicado ou o pool de SQL sem servidor, dê um nome à tabela e selecione abrir script:

externaltable2

O Script do SQL é gerado automaticamente inferindo o esquema do arquivo:

externaltable3

Execute o script. O script executará automaticamente Select Top 100 *:

externaltable4

Agora a tabela externa está criada; para exploração futura do conteúdo dessa tabela externa, o usuário poderá consultá-la diretamente no painel Dados:

externaltable5

Próximas etapas

Consulte o artigo CETAS para saber como salvar os resultados da consulta em uma tabela externa no Armazenamento do Azure. Ou você pode tentar consultar as Tabelas externas do Apache Spark para o Azure Synapse.