Criar e usar exibições usando o pool de SQL sem servidor no Azure Synapse Analytics

  • Artigo

Nesta seção, você aprenderá a criar e usar exibições para encapsular consultas do pool de SQL sem servidor. As exibições permitirão que você reutilize essas consultas. As exibições também serão necessárias se você quiser usar ferramentas, como o Power BI, em conjunto com o pool de SQL sem servidor.

Pré-requisitos

A primeira etapa é criar um banco de dados em que a exibição será criada e inicializar os objetos necessários para autenticar no armazenamento do Azure executando o script de instalação nesse banco de dados. Todas as consultas neste artigo serão executadas no banco de dados de exemplo.

Exibições sobre dados externos

Você pode criar exibições da mesma maneira que cria exibições convencionais do SQL Server. A consulta a seguir cria uma exibição que lê o arquivo population.csv.

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

DROP VIEW IF EXISTS populationView;
GO

CREATE VIEW populationView AS
SELECT * 
FROM OPENROWSET(
        BULK 'csv/population/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', 
        FIELDTERMINATOR =',', 
        ROWTERMINATOR = '\n'
    )
WITH (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
) AS [r];

A exibição usa um EXTERNAL DATA SOURCE com uma URL raiz de seu armazenamento como um DATA_SOURCE e adiciona um caminho de arquivo relativo aos arquivos.

Exibições do Delta Lake

Se você estiver criando exibições na pasta Delta Lake, será necessário especificar o local para a pasta raiz após a opção BULK em vez de especificar o caminho do arquivo.

Pasta Delta Lake ECDC COVID-19

A função OPENROWSET que lê dados da pasta Delta Lake examinará a estrutura de pastas e identificará automaticamente os locais de arquivos.

create or alter view CovidDeltaLake
as
select *
from openrowset(
           bulk 'covid',
           data_source = 'DeltaLakeStorage',
           format = 'delta'
    ) with (
           date_rep date,
           cases int,
           geo_id varchar(6)
           ) as rows

Para obter mais informações, examine a Página de autoajuda do pool de SQL sem servidor do Synapse e os Problemas conhecidos do Azure Synapse Analytics.

Exibições particionadas

Se você tiver um conjunto de arquivos particionado na estrutura de pastas hierárquica, poderá descrever o padrão de partição usando os curingas no caminho do arquivo. Use a função FILEPATH para expor partes do caminho da pasta como colunas de particionamento.

CREATE VIEW TaxiView
AS SELECT *, nyc.filepath(1) AS [year], nyc.filepath(2) AS [month]
FROM
    OPENROWSET(
        BULK 'parquet/taxi/year=*/month=*/*.parquet',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT='PARQUET'
    ) AS nyc

Exibições particionadas podem melhorar o desempenho de suas consultas executando a eliminação de partição ao consultá-las com filtros nas colunas de particionamento. No entanto, nem todas as consultas dão suporte à eliminação de partição, portanto, é importante seguir algumas práticas recomendadas.

Para garantir a eliminação da partição, evite usar subconsultas em filtros, pois elas podem interferir na capacidade de eliminar partições. Em vez disso, passe o resultado da subconsulta como uma variável para o filtro.

Ao usar JOINs em consultas SQL, declare o predicado de filtro como NVARCHAR para reduzir a complexidade do plano de consulta e aumentar a probabilidade de eliminação de partição correta. As colunas de partição normalmente são inferidas como NVARCHAR(1024), portanto, usar o mesmo tipo para o predicado evita a necessidade de uma conversão implícita, o que pode aumentar a complexidade do plano de consulta.

Exibições particionadas Delta Lake

Se você estiver criando as exibições particionadas no armazenamento Delta Lake, poderá especificar apenas uma pasta Delta Lake raiz e não precisará expor explicitamente as colunas de particionamento usando a função FILEPATH:

CREATE OR ALTER VIEW YellowTaxiView
AS SELECT *
FROM  
    OPENROWSET(
        BULK 'yellow',
        DATA_SOURCE = 'DeltaLakeStorage',
        FORMAT='DELTA'
    ) nyc

A função OPENROWSET examinará a estrutura da pasta Delta Lake subjacente e identificará e exporá automaticamente as colunas de particionamento. A eliminação de partição será feita automaticamente se você colocar a coluna de particionamento na cláusula WHERE de uma consulta.

O nome da pasta na função OPENROWSET (yellow neste exemplo) concatenado com o URI LOCATION definido na fonte de dados DeltaLakeStorage deve referenciar a pasta raiz do Delta Lake que contém uma subpasta denominada _delta_log.

Pasta Yellow Taxi Delta Lake

Para obter mais informações, examine a Página de autoajuda do pool de SQL sem servidor do Synapse e os Problemas conhecidos do Azure Synapse Analytics.

Exibições JSON

As exibições são a melhor opção se você precisa realizar processamento adicional do conjunto de resultados que é buscado dos arquivos. Um exemplo pode ser a análise de arquivos JSON, em que precisamos aplicar as funções JSON para extrair os valores dos documentos desse tipo:

CREATE OR ALTER VIEW CovidCases
AS 
select
    *
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
    cross apply openjson (doc)
        with (  date_rep datetime2,
                cases int,
                fatal int '$.deaths',
                country varchar(100) '$.countries_and_territories')

A função OPENJSON analisará cada linha do arquivo JSONL que contenha um documento JSON por linha no formato textual.

Exibições do Azure Cosmos DB em contêineres

As exibições poderão ser criadas com base nos contêineres do Azure Cosmos DB se o armazenamento analítico do Azure Cosmos DB estiver habilitado no contêiner. O nome da conta do Azure Cosmos DB, o nome do banco de dados e o nome do contêiner devem ser adicionados como parte da exibição, e a chave de acesso somente leitura deve ser colocada na credencial contida no escopo do banco de dados e referenciada pela exibição.

CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
GO
CREATE OR ALTER VIEW Ecdc
AS SELECT *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;Database=covid',
      OBJECT = 'Ecdc',
      CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

Para obter mais informações, confira Consultar dados do Azure Cosmos DB com um pool de SQL sem servidor no Link do Azure Synapse.

Usar uma exibição

Você pode usar exibições em suas consultas da mesma maneira que usa exibições em consultas do SQL Server.

A consulta a seguir demonstra o uso da exibição population_csv que criamos em Criar uma exibição. 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 populationView
WHERE
    [year] = 2019
ORDER BY
    [population] DESC;

Ao consultar a exibição, você pode encontrar erros ou resultados inesperados. Isso provavelmente significa que a exibição faz referência a colunas ou objetos que foram modificados ou não existem mais. Você precisa ajustar manualmente a definição de exibição para se alinhar com as alterações de esquema subjacentes.

Próximas etapas

Para obter informações sobre como consultar diferentes tipos de arquivo, veja os artigos Consultar um arquivo CSV individual, Consultar arquivos Parquet e Consultar arquivos JSON.