Utilizar tabelas externas com o Synapse SQL

Uma tabela externa aponta para dados localizados no Hadoop, no blob de Armazenamento do Azure ou no Armazenamento do Azure Data Lake. Você pode usar tabelas externas para ler dados de arquivos ou gravar dados em arquivos no Armazenamento do Azure.

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

Dependendo do tipo de 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 de dados, como CSV, Parquet e ORC. As tabelas externas do Hadoop estão disponíveis em pools SQL dedicados, mas não estão disponíveis em pools 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 estão disponíveis em pools SQL sem servidor e estão em visualização pública em pools SQL dedicados. A gravação/exportação de dados usando o CETAS e as tabelas externas nativas está disponível apenas no pool SQL sem servidor, mas não nos pools SQL dedicados.

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

Tipo de tabela externa	Hadoop	Nativa
Conjunto de SQL dedicado	Disponíveis	Apenas mesas de Parquet estão disponíveis em pré-visualização pública.
Conjunto de SQL sem servidor	Não disponível	Disponíveis
Formatos suportados	Delimitado/CSV, Parquet, ORC, Hive RC e RC	Pool SQL sem servidor: Delimitado/CSV, Parquet e Delta Lake Pool SQL dedicado: Parquet (visualização)
Eliminação de partições de pastas	Não	A eliminação de partições está disponível apenas nas tabelas particionadas criadas nos formatos Parquet ou CSV sincronizadas a partir de pools do Apache Spark. Você pode criar tabelas externas em pastas particionadas do Parquet, mas as colunas de particionamento são inacessíveis e ignoradas, enquanto a eliminação de partições não será aplicada. Não crie tabelas externas em pastas Delta Lake porque elas não são suportadas. Use exibições particionadas Delta se precisar consultar dados particionados do Delta Lake.
Eliminação de ficheiros (pushdown de predicados)	Não	Sim no pool SQL sem servidor. Para o pushdown de cadeia de caracteres, você precisa usar Latin1_General_100_BIN2_UTF8 o agrupamento nas colunas para habilitar o VARCHAR pushdown. Para obter mais informações sobre agrupamentos, consulte Tipos de agrupamento suportados para Synapse SQL.
Formato personalizado para localização	Não	Sim, usando curingas como /year=*/month=*/day=* para os formatos Parquet ou CSV. Caminhos de pasta personalizados não estão disponíveis no Delta Lake. No pool SQL sem servidor, você também pode usar curingas /logs/** recursivos para fazer referência a arquivos Parquet ou CSV em qualquer subpasta abaixo da pasta referenciada.
Varredura de pasta recursiva	Sim	Sim. No Serverless, os pools SQL devem ser especificados /** 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 Microsoft Entra.
Mapeamento de colunas	Ordinal - as colunas na definição da 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. Piscina dedicada: correspondência ordinal. As colunas na definição da 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 funciona apenas no pool SQL sem servidor. Não é possível usar os pools SQL dedicados para exportar dados usando tabelas nativas.

Nota

As mesas 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 do Parquet assim que eles estiverem no GA. Use as tabelas Hadoop somente se precisar acessar alguns tipos que não são suportados em tabelas externas nativas (por exemplo, ORC, RC) ou se a versão nativa não estiver disponível.

Tabelas externas em pool SQL dedicado e pool SQL sem servidor

Você pode usar tabelas externas para:

• Consulte o Armazenamento de Blobs do Azure e o Azure Data Lake Gen2 com instruções Transact-SQL.
• Armazene os resultados da consulta em arquivos no Armazenamento de Blobs do Azure ou no Armazenamento do Azure Data Lake usando o CETAS.
• Importe dados do Armazenamento de Blobs do Azure e do Armazenamento do Azure Data Lake e armazene-os em um pool SQL dedicado (somente tabelas Hadoop no pool dedicado).

Nota

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

Se o desempenho das tabelas externas do Hadoop nos pools dedicados não satisfizer suas metas de desempenho, considere carregar dados externos nas tabelas do Datawarehouse usando a instrução COPY.

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

Você pode criar tabelas externas em pools Synapse SQL através das seguintes etapas:

1. CREATE EXTERNAL DATA SOURCE para fazer referência a um armazenamento externo do Azure e especificar a credencial que deve ser usada para acessar o armazenamento.
2. CREATE EXTERNAL FILE FORMAT para descrever o formato de ficheiros CSV ou Parquet.
3. CREATE EXTERNAL TABLE em cima dos arquivos colocados na fonte de dados com o mesmo formato de arquivo.

Eliminação de partições de pastas

As tabelas externas nativas em pools Synapse são capazes de ignorar os arquivos colocados nas pastas que não são relevantes para as consultas. Se os seus ficheiros estiverem armazenados numa hierarquia de pastas (por exemplo - /year=2020/month=03/day=16) e os valores para year, e day forem expostos como as colunas, monthas consultas que contêm filtros como year=2020 lerão os ficheiros apenas a partir das subpastas colocadas dentro da year=2020 pasta. Os ficheiros e pastas colocados noutras pastas (year=2021 ou year=2022) serão ignorados nesta consulta. Esta eliminação é conhecida como eliminação de partição.

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

Eliminação de ficheiros

Alguns formatos de dados, como Parquet e Delta, contêm estatísticas de arquivo para cada coluna (por exemplo, valores min/max para cada coluna). As consultas que filtram dados não lerão os arquivos onde os valores de coluna necessários não existem. A consulta primeiro explorará valores min/max 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. A pressão de filtro está disponível nos pools SQL sem servidor nos formatos Parquet e Delta. Para aproveitar a pressão de filtro para os tipos de cadeia de caracteres, use o tipo VARCHAR com o Latin1_General_100_BIN2_UTF8 agrupamento. Para obter mais informações sobre agrupamentos, consulte Tipos de agrupamento suportados para Synapse SQL.

Segurança

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

• A fonte de dados sem credencial permite que tabelas externas acessem arquivos disponíveis publicamente no armazenamento do Azure.
• A fonte de dados pode ter uma credencial que permite que tabelas externas acessem apenas os arquivos no armazenamento do Azure usando token SAS ou espaço de trabalho Identidade gerenciada - Para obter exemplos, consulte o artigo Desenvolver controle de acesso de armazenamento de arquivos de armazenamento.

Exemplo para CREATE EXTERNAL DATA SOURCE

Hadoop

O exemplo a seguir cria uma fonte de dados externa do Hadoop no pool 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 exemplo a seguir 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)

Nativo

O exemplo a seguir cria uma fonte de dados externa no pool SQL dedicado ou sem servidor para o Azure Data Lake Gen2 que pode ser acessada usando a credencial SAS:

CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
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 SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);

Nota

Os usuários do SQL precisam ter permissões adequadas nas credenciais do escopo do banco de dados para acessar a fonte de dados no Pool SQL sem Servidor do Azure Synapse Analytics. Acesse o armazenamento externo usando o pool SQL sem servidor no Azure Synapse Analytics. O exemplo a seguir 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/')

Exemplo para CREATE EXTERNAL FILE FORMAT

O exemplo a seguir 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 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 a partir de um arquivo no Azure Data Lake

Usando os recursos de exploração do Data Lake do Synapse Studio, agora você pode criar e consultar uma tabela externa usando o pool Synapse SQL com um simples clique com o botão direito do mouse no arquivo. O gesto de um clique para criar tabelas externas a partir da conta de armazenamento ADLS Gen2 só é suportado para ficheiros Parquet.

Pré-requisitos

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

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

No painel Dados, selecione o arquivo a partir do qual deseja criar a tabela externa:

Abre-se uma janela de diálogo. Selecione pool SQL dedicado ou pool SQL sem servidor, dê um nome à tabela e selecione script aberto:

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

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

A tabela externa agora é criada, para exploração futura do conteúdo dessa tabela externa, o usuário pode consultá-la diretamente do painel Dados:

Próximos passos

Consulte o artigo CETAS para saber como salvar os resultados da consulta em uma tabela externa no Armazenamento do Azure. Ou você pode começar a consultar tabelas externas do Apache Spark for Azure Synapse.