Compartilhar via

Virtualização de dados com o Banco de Dados SQL do Azure (versão prévia)

Aplica-se a:Banco de Dados SQL do Azure

O recurso de virtualização de dados do Banco de Dados SQL do Azure permite executar consultas Transact-SQL (T-SQL) em arquivos que armazenam dados em formatos de dados comuns, como CSV (sem necessidade de usar Texto Delimitado), Parquet e Delta (1.0). Você pode consultar esses dados no Azure Data Lake Storage Gen2 ou no Armazenamento de Blobs do Azure e combiná-los com dados relacionais armazenados localmente usando junções. Dessa forma, você pode acessar transparentemente os dados externos (em modo somente leitura) enquanto os mantém em seu formato original e local, também conhecido como virtualização de dados.

Visão geral

A virtualização de dados oferece duas maneiras de consultar arquivos destinados a diferentes conjuntos de cenários:

  • Sintaxe da OPENROWSET – otimizada para consulta ad hoc de arquivos. Normalmente usado para explorar rapidamente o conteúdo e a estrutura de um novo conjunto de arquivos.
  • Sintaxe da CREATE EXTERNAL TABLE – otimizada para consulta repetitiva de arquivos usando sintaxe idêntica como se os dados fossem armazenados localmente no banco de dados. As tabelas externas exigem várias etapas de preparação em comparação com a sintaxe OPENROWSET, mas permitem mais controle sobre o acesso a dados. As tabelas externas são normalmente usadas para cargas de trabalho e relatórios analíticos.

Em qualquer dos casos, uma fonte de dados externa deve ser criada usando a sintaxe CREATE EXTERNAL DATA SOURCE do T-SQL, conforme demonstrado neste artigo.

Formatos de arquivo

Os formatos de arquivo parquet e de texto delimitado (CSV) têm suporte direto. O formato de arquivo JSON tem suporte indiretamente especificando o formato de arquivo CSV em que as consultas retornam todos os documentos como uma linha separada. Você pode analisar linhas posteriormente usando JSON_VALUE e OPENJSON.

Tipos de armazenamento

Os arquivos podem ser armazenados no Azure Data Lake Storage Gen2 ou no Armazenamento de Blobs do Azure. Para consultar os arquivos, você precisa fornecer o local em um formato específico e usar o prefixo de tipo de local correspondente ao tipo da origem externa e o ponto de extremidade/protocolo, como os seguintes exemplos:

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet
--or
abs://<storage_account_name>.blob.core.windows.net/<container_name>/

--Data Lake endpoint
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name>.parquet
--or
adls://<storage_account_name>.dfs.core.windows.net/<container_name>/

Importante

Sempre use prefixos específicos do ponto de extremidade. O prefixo de tipo de Local fornecido é usado para escolher o protocolo ideal para comunicação e aproveitar todos os recursos avançados oferecidos pelo tipo de armazenamento específico.

O prefixo genérico https:// só tem suporte para BULK INSERT, mas não para outros casos de uso, incluindo OPENROWSET ou EXTERNAL TABLE.

Introdução

Se você estiver se familiarizando com a virtualização de dados e quiser testar rapidamente a funcionalidade, comece consultando conjuntos de dados disponíveis publicamente no Azure Open Datasets, como o Conjunto de dados de COVID-19 do Bing, permitindo acesso anônimo.

Use os seguintes pontos de extremidade para consultar os conjuntos de dados de COVID-19 do Bing:

  • Parquete: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
  • CSV: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

Para um início rápido, execute esta consulta T-SQL simples para obter os primeiros insights sobre o conjunto de dados. Essa consulta usa OPENROWSET para consultar um arquivo armazenado em uma conta de armazenamento disponível publicamente:

--Quick query on a file stored in a publicly available storage account:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet',
 FORMAT = 'parquet'
) AS filerows;

Você pode continuar a exploração do conjunto de dados acrescentando WHEREe GROUP BY outras cláusulas com base no conjunto de resultados da primeira consulta.

Depois de se familiarizar com a consulta de conjuntos de dados públicos, considere mudar para conjuntos de dados não públicos que exigem o fornecimento de credenciais, a concessão de direitos de acesso e a configuração de regras de firewall. Em muitos cenários do mundo real, você operará principalmente com conjuntos de dados privados.

Acesso a contas de armazenamento não públicas

Um usuário conectado a um Banco de Dados SQL do Azure deve estar autorizado a acessar e consultar arquivos armazenados em contas de armazenamento não públicas. As etapas de autorização dependem de como o Banco de Dados SQL do Azure autentica o armazenamento. Os tipos de autenticações e quaisquer parâmetros relacionados não são fornecidos diretamente com cada consulta. Eles são encapsulados no objeto de credencial no escopo do banco de dados armazenado no banco de dados do usuário. A credencial é usada pelo banco de dados para acessar a conta de armazenamento sempre que a consulta é executada.

O Banco de Dados SQL do Azure dá suporte aos seguintes tipos de autenticação:

  • SAS (assinatura de acesso compartilhado)
  • Identidade gerenciada
  • Autenticação por passagem do Microsoft Entra via Identidade do Usuário

Uma SAS (assinatura de acesso compartilhado) fornece acesso delegado a arquivos em uma conta de armazenamento. A SAS fornece controle granular sobre o tipo de acesso que você concede, incluindo intervalo de validade, permissões concedidas e intervalo de endereços IP aceitável. Depois que o token SAS é criado, ele não pode ser revogado ou excluído e permite o acesso até que o período de validade expire.

  1. Você pode obter um token SAS de várias maneiras:

  2. Conceda permissões Leitura e Listar por meio da SAS para acessar dados externos. Atualmente, a virtualização de dados com o Banco de Dados SQL do Azure é somente leitura.

  3. Para criar uma credencial com escopo de banco de dados no Banco de Dados SQL do Azure, primeiro você deve criar a chave mestra do banco de dados, caso ainda não exista. Uma chave mestra de banco de dados é necessária quando a credencial requer SECRET.

    -- Create MASTER KEY if it doesn't exist in the database:
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>';

  4. Quando um token SAS é gerado, ele inclui um ponto de interrogação (?) no início do token. Para usar o token, você deve remover o ponto de interrogação (?) ao criar uma credencial. Por exemplo:

    CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=secret string here';

Acesso ao armazenamento público por meio de contas anônimas

Se o conjunto de dados desejado permitir acesso público (também conhecido como acesso anônimo), nenhuma credencial será necessária desde que o Armazenamento do Azure esteja configurado corretamente, consulte Configurar o acesso de leitura anônimo para contêineres e blobs.

Fonte de dados externa

Uma fonte de dados externa é uma abstração que permite fazer referência fácil a um local de arquivo em várias consultas. Para consultar locais públicos, tudo o que você precisa especificar ao criar uma fonte de dados externa é o local do arquivo:

CREATE EXTERNAL DATA SOURCE MyExternalDataSource
WITH (
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
);

Ao acessar contas de armazenamento não públicas, além do local, você também precisará fazer referência a uma credencial com escopo do banco de dados com parâmetros de autenticação encapsulados. O script a seguir cria uma fonte de dados externa apontando para o caminho do arquivo e fazendo referência a uma credencial com escopo de banco de dados.

--Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource
WITH (
    LOCATION = 'abs://<privatecontainer>@privatestorageaccount.blob.core.windows.net/dataset/' 
       CREDENTIAL = [MyCredential]
);

Consultar fontes de dados usando OPENROWSET

A sintaxe OPENROWSET permite a consulta ad hoc instantânea, ao mesmo tempo que cria apenas o número mínimo de objetos de banco de dados necessários.

O OPENROWSET requer apenas a criação da fonte de dados externa (e possivelmente a credencial), em oposição à abordagem de tabela externa, que requer um formato de arquivo externo e a própria tabela externa.

O valor do parâmetro DATA_SOURCE é automaticamente anexado ao parâmetro EM MASSA para formar o caminho completo para o arquivo.

Ao usar OPENROWSET, forneça o formato do arquivo, como o exemplo a seguir, que consulta um único arquivo:

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'bing_covid-19_data.parquet',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Consultar vários arquivos e pastas

O comando OPENROWSET também permite consultar vários arquivos ou pastas usando curingas no caminho EM MASSA.

O exemplo a seguir usa o conjunto de dados aberto dos registros de viagem de táxi amarelo NYC.

Em primeiro lugar, crie a fonte de dados externa:

--Create the data source first:
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

Agora, podemos consultar todos os arquivos com extensão .parquet em pastas. Por exemplo, aqui consultaremos apenas os arquivos que correspondem a um padrão de nome:

--Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Ao consultar vários arquivos ou pastas, todos os arquivos acessados com o único OPENROWSET devem ter a mesma estrutura (como o mesmo número de colunas e tipos de dados). As pastas não podem ser percorridas recursivamente.

Inferência de esquema

A inferência automática de esquemas ajuda a escrever rapidamente consultas e explorar dados quando você não conhece os esquemas de arquivos. A inferência de esquema funciona apenas com arquivos parquet.

Embora convenientes, os tipos de dados inferidos podem ser maiores do que os tipos de dados reais porque pode haver informações suficientes nos arquivos de origem para garantir que o tipo de dados apropriado seja usado. Isso pode levar a um baixo desempenho de consulta. Por exemplo, os arquivos parquet não contêm metadados sobre o comprimento máximo da coluna de caracteres, portanto, a instância o infere como varchar(8000).

Use o procedimento armazenado sp_describe_first_results_set para verificar os tipos de dados resultantes da sua consulta, como o exemplo a seguir:

EXEC sp_describe_first_result_set N'
 SELECT
 vendorID, tpepPickupDateTime, passengerCount
 FROM
 OPENROWSET(
  BULK ''yellow/*/*/*.parquet'',
  DATA_SOURCE = ''NYCTaxiExternalDataSource'',
  FORMAT=''parquet''
 ) AS nyc';

Depois de conhecer os tipos de dados, você pode especificá-los usando a cláusula WITH para melhorar o desempenho:

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount
FROM
OPENROWSET(
 BULK 'yellow/*/*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT='PARQUET'
 )
WITH (
vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000
tpepPickupDateTime datetime2,
passengerCount int
) AS nyc;

Como o esquema de arquivos CSV não pode ser determinado automaticamente, as colunas devem sempre ser especificadas usando a cláusula WITH:

SELECT TOP 10 id, updated, confirmed, confirmed_change
FROM OPENROWSET(
 BULK 'bing_covid-19_data.csv',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'CSV',
 FIRSTROW = 2
)
WITH (
 id int,
 updated date,
 confirmed int,
 confirmed_change int
) AS filerows;

Funções de metadados de arquivo

Ao consultar vários arquivos ou pastas, você pode usar as funções filepath() e filename() para ler metadados de arquivo e obter parte do caminho ou caminho completo e o nome do arquivo do qual a linha no conjunto de resultados se origina:

--Query all files and project file path and file name information for each row:
SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder],
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;

Quando chamada sem um parâmetro, a função filepath() retorna o caminho do arquivo de onde a linha se origina. Quando DATA_SOURCE é usado no OPENROWSET, ele retorna o caminho relativo ao DATA_SOURCE, caso contrário, ele retorna o caminho de arquivo completo.

Quando chamada com um parâmetro, ela retorna parte do caminho correspondente ao caractere curinga na posição especificada no parâmetro. Por exemplo, o valor de parâmetro 1 retornaria a parte do caminho correspondente ao primeiro caractere curinga.

A função filepath() também pode ser usada para filtrar e agregar linhas:

SELECT
 r.filepath() AS filepath
 ,r.filepath(1) AS [year]
 ,r.filepath(2) AS [month]
 ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
DATA_SOURCE = 'NYCTaxiExternalDataSource',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2017')
 AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
 r.filepath()
 ,r.filepath(1)
 ,r.filepath(2)
ORDER BY
 filepath;

Criar exibição com base na OPENROWSET

É possível criar e usar modos de exibição para encapsular consultas OPENROWSET para que você possa facilmente reutilizar a consulta subjacente:

CREATE VIEW TaxiRides AS
SELECT *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Também é conveniente adicionar colunas com os dados do local do arquivo a uma exibição usando a função filepath() para uma filtragem mais fácil e de alto desempenho. O uso de modos de exibição pode reduzir o número de arquivos e a quantidade de dados que a consulta na parte superior da exibição precisa ler e processar quando filtrada por qualquer uma dessas colunas:

CREATE VIEW TaxiRides AS
SELECT *
 , filerows.filepath(1) AS [year]
 , filerows.filepath(2) AS [month]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

As exibições também permitem que ferramentas analíticas e de relatório, como Power BI, consumam os resultados de OPENROWSET.

Tabelas externas

Tabelas externas encapsulam o acesso a arquivos, tornando a experiência de consulta quase idêntica à consulta de dados relacionais locais armazenados em tabelas de usuário. A criação de uma tabela externa exige que a fonte de dados externa e os objetos de formato de arquivo externo existam:

--Create external file format
CREATE EXTERNAL FILE FORMAT DemoFileFormat
WITH (
 FORMAT_TYPE=PARQUET
);

--Create external table:
CREATE EXTERNAL TABLE tbl_TaxiRides(
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
 tpepPickupDateTime DATETIME2,
 tpepDropoffDateTime DATETIME2,
 passengerCount INT,
 tripDistance FLOAT,
 puLocationId VARCHAR(8000),
 doLocationId VARCHAR(8000),
 startLon FLOAT,
 startLat FLOAT,
 endLon FLOAT,
 endLat FLOAT,
 rateCodeId SMALLINT,
 storeAndFwdFlag VARCHAR(8000),
 paymentType VARCHAR(8000),
 fareAmount FLOAT,
 extra FLOAT,
 mtaTax FLOAT,
 improvementSurcharge VARCHAR(8000),
 tipAmount FLOAT,
 tollsAmount FLOAT,
 totalAmount FLOAT
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);

Depois que a tabela externa for criada, você poderá consultá-la assim como qualquer outra tabela:

SELECT TOP 10 *
FROM tbl_TaxiRides;

Assim como OPENROWSET, as tabelas externas permitem consultar vários arquivos e pastas usando curingas. A inferência de esquema não é suportada com tabelas externas.

Considerações sobre desempenho

Não há limite rígido no número de arquivos ou na quantidade de dados que podem ser consultados, mas o desempenho da consulta depende da quantidade de dados, do formato dos dados, da forma como os dados são organizados e da complexidade das consultas e junções.

Consultar dados particionados

Os dados geralmente são organizados em subpastas, também chamadas de partições. Você pode instruir a consulta a ler somente pastas e arquivos específicos. Isso reduz o número de arquivos e a quantidade de dados que a consulta precisa ler e processar, resultando em melhor desempenho. Esse tipo de otimização de consulta é conhecido como poda de partição ou eliminação de partição. Você pode eliminar partições da execução da consulta usando a função filepath() na cláusula WHERE da consulta.

O exemplo a seguir consulta arquivos de dados de Táxis amarelos em NYC relativos somente aos últimos três meses de 2017:

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'yellow/puYear=*/puMonth=*/*.parquet',
        DATA_SOURCE = 'NYCTaxiExternalDataSource',
        FORMAT = 'parquet'
    )
WITH (
    vendorID INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Se os dados armazenados não forem particionados, considere particioná-los para aprimorar o desempenho da consulta.

Se você estiver usando tabelas externas, e houver suporte para as funções filepath() e filename(), mas não na cláusula WHERE.

Solucionar problemas

Problemas com a execução da consulta normalmente são causados por o Banco de Dados SQL do Azure não conseguir acessar o local do arquivo. As mensagens de erro relacionadas talvez relatem direitos de acesso insuficientes, local ou caminho de arquivo não existente, arquivo que está sendo usado por outro processo ou esse diretório não pode ser listado. Na maioria dos casos, isso indica que o acesso aos arquivos é bloqueado pelas políticas de controle de tráfego de rede ou devido à falta de direitos de acesso. Este é o que deve ser verificado:

  • Caminho do local errado ou digitado incorretamente.
  • Validade da chave SAS: ela pode estar expirada, conter um erro de digitação, começar com um ponto de interrogação.
  • Permissões de chave SAS autorizadas: no mínimo Leitura, e Listar se curingas forem usados.
  • Tráfego de entrada bloqueado na conta de armazenamento. Verifique o gerenciamento de regras de rede virtual para o Armazenamento do Azure.
  • Direitos de acesso à Identidade Gerenciada: verifique se a identidade gerenciada do Banco de Dados SQL do Azure recebe direitos de acesso à conta de armazenamento.
  • O nível de compatibilidade do banco de dados deve ser 130 ou superior para que as consultas da virtualização de dados funcionem.

Limitações

  • Atualmente, não há suporte para estatísticas em tabelas externas no Banco de Dados SQL do Azure.
  • Atualmente, CREATE EXTERNAL TABLE AS SELECT não está disponível no Banco de Dados SQL do Azure.
  • Não há suporte para o recurso segurança em nível de linha em tabelas externas.
  • A regra da Máscara Dinâmica de Dados não pode ser definida para uma coluna em uma tabela externa.
  • A Identidade Gerenciada não dá suporte a cenários entre locatários, se a Conta de Armazenamento do Azure estiver em um locatário diferente, a assinatura de acesso compartilhado será o método com suporte.

Problemas conhecidos

  • Quando a parametrização do Always Encrypted está habilitada no SSMS (SQL Server Management Studio), as consultas da virtualização de dados falham com a mensagem de erro Incorrect syntax near 'PUSHDOWN'.

Conteúdo relacionado

  • Last updated on