Criar e usar tabelas externas nativas usando o pool de SQL no Azure Synapse Analytics

Artigo
12/11/2024

Nesta seção, você aprenderá a criar e usar tabelas externas nativas no pool de SQL do Synapse. As tabelas externas nativas têm melhor desempenho quando comparadas a tabelas externas com TYPE=HADOOP em sua definição de fonte de dados externa. Isso porque as tabelas externas nativas usam código nativo para acessar dados externos.

Tabelas externas são úteis quando você quer controlar o acesso a dados externos no pool de SQL do Synapse. Elas também são úteis se você quiser usar ferramentas, como o Power BI, em conjunto com o pool de SQL do Synapse. As tabelas externas podem acessar dois tipos de armazenamento:

Armazenamento público, onde os usuários acessam os arquivos de armazenamento público.
Armazenamento protegido em que os usuários acessam arquivos de armazenamento usando a credencial SAS, a identidade do Microsoft Entra ou a Identidade Gerenciada do workspace do Synapse.

Observação

Em pools de SQL dedicados, você só pode usar tabelas externas nativas com um tipo de arquivo Parquet, e esse recurso está em versão prévia pública. Se você quiser usar a funcionalidade de leitor do Parquet (que está em disponibilidade geral) em pools de SQL dedicados ou precisar acessar arquivos CSV ou ORC, use tabelas externas do Hadoop. As tabelas externas nativas geralmente estão disponíveis em pools de SQL sem servidor. Saiba mais sobre as diferenças entre tabelas externas nativas e em Hadoop em Usar tabelas externas com o SQL do Synapse.

A tabela a seguir lista os formatos de dados com suporte:

Formato de dados (tabelas externas nativas)	Pool de SQL sem servidor	Pool de SQL dedicado
Parquet	Sim (GA)	Sim (visualização pública)
CSV	Sim	Não (Alternativamente, use as tabelas externas do Hadoop)
delta	Sim	Não
Spark	Sim	Não
Dataverse	Sim	Não
Formatos de dados do Azure Cosmos DB (JSON, BSON, etc.)	Não (Alternativamente, criar exibições)	Não

Pré-requisitos

Sua primeira etapa é criar um banco de dados no qual as tabelas serão criadas. Antes de criar uma credencial no escopo do banco de dados, o banco de dados deve ter uma chave mestra para proteger a credencial. Para obter mais informações sobre isso, confira CREATE MASTER KEY (Transact-SQL). Então crie os seguintes objetos que são usados neste exemplo:

A CREDENCIAL NO ESCOPO DO BANCO DE DADOS sqlondemand que permite o acesso a uma conta de armazenamento do Azure https://sqlondemandstorage.blob.core.windows.net protegida por 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'

A FONTE DE DADOS EXTERNA sqlondemanddemo que faz referência à conta de armazenamento de demonstração protegida com a chave SAS, e a FONTE DE DADOS EXTERNA nyctlc que faz referência à conta de armazenamento do Azure disponível publicamente no local https://azureopendatastorage.blob.core.windows.net/nyctlc/.

CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);
GO
CREATE EXTERNAL DATA SOURCE nyctlc
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/')
GO
CREATE EXTERNAL DATA SOURCE DeltaLakeStorage
WITH ( location = 'https://sqlondemandstorage.blob.core.windows.net/delta-lake/' );

Os formatos de arquivo QuotedCSVWithHeaderFormat e ParquetFormat que descrevem os tipos de arquivo CSV e parquet.

CREATE EXTERNAL FILE FORMAT QuotedCsvWithHeaderFormat
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2   )
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
GO
CREATE EXTERNAL FILE FORMAT DeltaLakeFormat WITH (  FORMAT_TYPE = DELTA );
GO

As consultas neste artigo serão executadas no banco de dados de exemplo e usam esses objetos.

Tabela externa em um arquivo

Você pode criar tabelas externas que acessam dados em uma conta de armazenamento do Azure que permite acesso as usuários com alguma identidade do Microsoft Entra ou chave SAS. Você pode criar tabelas externas da mesma maneira que cria tabelas externas convencionais do SQL Server.

A consulta a seguir cria uma tabela externa que lê o arquivo population.csv da conta de armazenamento do Azure da demonstração do SynapseSQL que é referenciada usando a fonte de dados sqlondemanddemo e protegida com a credencial no escopo do banco de dados chamada sqlondemand.

Observação

Altere a primeira linha da consulta, ou seja, [mydbname], de modo que você esteja usando o banco de dados criado.

USE [mydbname];
GO
CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat
);

As tabelas CSV nativas estão disponíveis atualmente apenas nos pools de SQL sem servidor.

Tabela externa em um conjunto de arquivos

Você pode criar tabelas externas que leem dados de um conjunto de arquivos colocados no Armazenamento do Azure:

CREATE EXTERNAL TABLE Taxi (
     vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
     pickup_datetime DATETIME2, 
     dropoff_datetime DATETIME2,
     passenger_count INT,
     trip_distance FLOAT,
     fare_amount FLOAT,
     tip_amount FLOAT,
     tolls_amount FLOAT,
     total_amount FLOAT
) WITH (
         LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
         DATA_SOURCE = nyctlc,
         FILE_FORMAT = ParquetFormat
);

Você pode especificar o padrão que os arquivos devem satisfazer para que tabela externa faça referência a eles. O padrão é necessário apenas para tabelas Parquet e CSV. Se você estiver usando o formato Delta Lake, precisará especificar apenas uma pasta raiz e a tabela externa encontrará automaticamente o padrão.

Observação

A tabela é criada na estrutura de pastas particionada, mas você não pode aproveitar a eliminação de uma partição. Se quiser ter um melhor desempenho ignorando os arquivos que não atendem a algum critério (como ano ou mês específico nesse caso), use exibições em dados externos.

Tabela externa em arquivos acrescentáveis

Os arquivos referenciados por uma tabela externa não devem ser alterados enquanto a consulta estiver em execução. Na consulta de execução prolongada, o pool de SQL pode repetir leituras, ler partes dos arquivos ou até mesmo ler o arquivo várias vezes. Alterações no conteúdo dos arquivos causariam resultados errados. Portanto, se o pool de SQL detecta que o tempo de modificação de qualquer arquivo é alterado durante a execução da consulta, ela falha. Em alguns cenários, talvez você queira criar uma tabela nos arquivos que são acrescentados constantemente. Para evitar falhas de consulta devido a arquivos constantemente acrescentados, você pode especificar que a tabela externa deve ignorar leituras potencialmente inconsistentes usando a configuração TABLE_OPTIONS.

CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat,
    TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'
);

A opção de leitura ALLOW_INCONSISTENT_READS desabilitará a verificação de tempo de modificação do arquivo durante o ciclo de vida da consulta e lerá tudo o que estiver disponível nos arquivos referenciados pela tabela externa. Em arquivos acrescentáveis, o conteúdo existente não é atualizado e apenas novas linhas são adicionadas. Portanto, a probabilidade de resultados errados é minimizada em comparação com os arquivos atualizáveis. Essa opção pode permitir que você leia os arquivos acrescentados com frequência sem precisar lidar com os erros.

Essa opção está disponível apenas nas tabelas externas criadas no formato de arquivo CSV.

Observação

Como o nome da opção implica, o criador da tabela aceita um risco de que os resultados podem não ser consistentes. Nos arquivos acrescentáveis, você poderá obter resultados incorretos se forçar várias leituras dos arquivos subjacentes ingressando na tabela por conta própria. Na maioria das consultas "clássicas", a tabela externa ignorará apenas algumas linhas que forem acrescentadas enquanto a consulta estiver em execução.

Tabela externa do Delta Lake

Tabelas externas podem ser criadas sobre uma pasta do Delta Lake. A única diferença entre as tabelas externas criadas em um único arquivo ou um conjunto de arquivos e as tabelas externas criadas em um formato Delta Lake é que, na tabela externa do Delta Lake, você precisa fazer referência a uma pasta que contém a estrutura do Delta Lake.

Pasta Delta Lake ECDC COVID-19

Um exemplo de uma definição de tabela criada em uma pasta do Delta Lake é:

CREATE EXTERNAL TABLE Covid (
     date_rep date,
     cases int,
     geo_id varchar(6)
) WITH (
        LOCATION = 'covid', --> the root folder containing the Delta Lake files
        data_source = DeltaLakeStorage,
        FILE_FORMAT = DeltaLakeFormat
);

Tabelas externas não podem ser criadas em uma pasta particionada. Analise outros problemas conhecidos na página de autoajuda do pool de SQL sem servidor do Synapse.

Tabelas delta em pastas particionadas

Tabelas externas em pools de SQL sem servidor não dão suporte ao particionamento no formato Delta Lake. Use as exibições particionadas Delta em vez de tabelas se você tiver conjuntos de dados do Delta Lake particionados.

Importante

Não crie tabelas externas em pastas do Delta Lake particionadas, mesmo que você veja que elas podem funcionar em alguns casos. O uso de recursos sem suporte, como tabelas externas em pastas delta particionadas, pode causar problemas ou instabilidade do pool sem servidor. O Suporte do Azure não poderá resolver nenhum problema se estiver usando tabelas em pastas particionadas. Você seria solicitado a fazer a transição para exibições particionadas Delta e reescrever seu código para usar apenas o recurso com suporte antes de prosseguir com a resolução de problemas.

Usar uma tabela externa

Você pode usar tabelas externas nas suas consultas da mesma maneira que as usa em consultas do SQL Server.

A consulta a seguir demonstra isso usando a tabela externa population que criamos na seção anterior. Ela retorna nomes de países/regiões com o respectivo número de habitantes em 2019 em ordem decrescente.

Observação

Altere a primeira linha da consulta, ou seja, [mydbname], de modo que você esteja usando o banco de dados criado.

USE [mydbname];
GO

SELECT
    country_name, population
FROM populationExternalTable
WHERE
    [year] = 2019
ORDER BY
    [population] DESC;

O desempenho dessa consulta pode variar dependendo da região. Seu espaço de trabalho pode não ser colocado na mesma região que as contas de armazenamento do Azure usadas nesses exemplos. Para cargas de trabalho de produção, coloque seu espaço de trabalho Synapse e o Armazenamento do Azure na mesma região.

Próxima etapa

Armazenar os resultados da consulta no armazenamento

Compartilhar via

Criar e usar tabelas externas nativas usando o pool de SQL no Azure Synapse Analytics

Pré-requisitos

Tabela externa em um arquivo

Tabela externa em um conjunto de arquivos

Tabela externa em arquivos acrescentáveis

Tabela externa do Delta Lake

Tabelas delta em pastas particionadas

Usar uma tabela externa

Próxima etapa

Comentários

Recursos adicionais