Criar e usar tabelas externas nativas usando pools SQL no Azure Synapse Analytics

Artigo
12/11/2024

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

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

Armazenamento público onde os usuários acessam arquivos de armazenamento público.
Armazenamento protegido onde os usuários acessam arquivos de armazenamento usando a credencial SAS, a identidade Microsoft Entra ou a identidade gerenciada do espaço de trabalho Synapse.

Nota

Em pools SQL dedicados, você só pode usar tabelas externas nativas com um tipo de arquivo Parquet, e esse recurso está em visualização pública. Se você quiser usar a funcionalidade de leitor de Parquet disponível em pools 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 SQL sem servidor. Saiba mais sobre as diferenças entre tabelas nativas e externas do Hadoop em Usar tabelas externas com Synapse SQL.

A tabela a seguir lista os formatos de dados suportados:

Formato de dados (tabelas externas nativas)	Conjunto de SQL sem servidor	Conjunto de SQL dedicado
Parquet	Sim (GA)	Sim (pré-visualização pública)
CSV	Sim	Não (alternativamente, use tabelas externas do Hadoop)
delta	Sim	No
Spark	Sim	No
Dataverse	Sim	No
Formatos de dados do Azure Cosmos DB (JSON, BSON etc.)	Não (alternativamente, criar vistas)	Não

Pré-requisitos

Seu primeiro passo é criar um banco de dados onde as tabelas serão criadas. Antes de criar uma credencial com escopo de banco de dados, o banco de dados deve ter uma chave mestra para proteger a credencial. Para obter mais informações sobre isso, consulte CREATE MASTER KEY (Transact-SQL). Em seguida, crie os seguintes objetos que são usados neste exemplo:

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

FONTE sqlondemanddemo DE DADOS EXTERNA que faz referência à conta de armazenamento de demonstração protegida com chave SAS e FONTE nyctlc DE DADOS EXTERNA 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/' );

Formatos QuotedCSVWithHeaderFormat de arquivo 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 em seu banco de dados de exemplo e usarão 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 a usuários com alguma identidade do Microsoft Entra ou chave SAS. Você pode criar tabelas externas da mesma forma que cria tabelas externas regulares do SQL Server.

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

Nota

Altere a primeira linha da consulta, ou seja, [mydbname], para 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
);

Atualmente, as tabelas CSV nativas estão disponíveis apenas nos pools 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 serem referenciados pela tabela externa. 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.

Nota

A tabela é criada na estrutura de pastas particionadas, mas você não pode aproveitar alguma eliminação de partição. Se você quiser obter um melhor desempenho ignorando os arquivos que não satisfazem algum critério (como ano ou mês específico, neste caso), use visualizações em dados externos.

Tabela externa em arquivos anexáveis

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

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 ALLOW_INCONSISTENT_READS opção de leitura desativará a verificação do tempo de modificação do arquivo durante o ciclo de vida da consulta e lerá o que estiver disponível nos arquivos referenciados pela tabela externa. Em arquivos anexá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 anexados com freqüência sem manipular os erros.

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

Nota

Como o nome da opção indica, o criador da tabela aceita o risco de os resultados não serem consistentes. Nos arquivos apensáveis, você pode obter resultados incorretos se forçar a leitura múltipla dos arquivos subjacentes unindo automaticamente a tabela. Na maioria das consultas "clássicas", a tabela externa simplesmente ignorará algumas linhas que são acrescentadas enquanto a consulta estava em execução.

Mesa externa Delta Lake

Tabelas externas podem ser criadas sobre uma pasta 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 Delta Lake você precisa fazer referência a uma pasta que contém a estrutura Delta Lake.

ECDC COVID-19 Delta Lake pasta

Um exemplo de uma definição de tabela criada em uma pasta 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
);

Não é possível criar tabelas externas numa pasta particionada. Analise os outros problemas conhecidos na página de autoajuda do pool SQL sem servidor Synapse.

Tabelas delta em pastas particionadas

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

Importante

Não crie tabelas externas em pastas Delta Lake particionadas, mesmo que você veja que elas podem funcionar em alguns casos. O uso de recursos não suportados, 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 suportado antes de prosseguir com a resolução do problema.

Usar uma tabela externa

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

A consulta a seguir demonstra isso usando a tabela externa de população que criamos na seção anterior. Devolve os nomes dos países/regiões com a sua população em 2019 por ordem decrescente.

Nota

Altere a primeira linha da consulta, ou seja, [mydbname], para 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 o espaço de trabalho Synapse e o armazenamento do Azure na mesma região.

Próximo passo

Armazenar os resultados da consulta no armazenamento

Partilhar via

Criar e usar tabelas externas nativas usando pools SQL no Azure Synapse Analytics

Pré-requisitos

Tabela externa em um arquivo

Tabela externa em um conjunto de arquivos

Tabela externa em arquivos anexáveis

Mesa externa Delta Lake

Tabelas delta em pastas particionadas

Usar uma tabela externa

Próximo passo

Comentários

Recursos adicionais