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

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

Pré-requisitos

Sua primeira etapa é criar um banco de dados onde 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 em seu banco de dados de exemplo.

Visualizações sobre dados externos

Você pode criar modos de exibição da mesma forma que cria modos de exibição regulares do SQL Server. A consulta a seguir cria o modo de exibição que lê population.csv arquivo.

Nota

Altere a primeira linha da consulta, ou seja, [mydbname], para 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 do seu armazenamento, como um DATA_SOURCE e adiciona um caminho de arquivo relativo aos arquivos.

Vistas para o Lago Delta

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

A OPENROWSET função que lê os dados da pasta Delta Lake examinará a estrutura da pasta e identificará automaticamente os locais dos 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, consulte a página de autoajuda do pool SQL sem servidor Synapse e os problemas conhecidos do Azure Synapse Analytics.

Vistas particionadas

Se você tiver um conjunto de arquivos particionado na estrutura hierárquica de pastas, poderá descrever o padrão de partição usando os curingas no caminho do arquivo. Use a FILEPATH função 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

As exibições particionadas podem melhorar o desempenho de suas consultas executando a eliminação de partições quando você as consulta com filtros nas colunas de particionamento. No entanto, nem todas as consultas suportam a eliminação de partições, por isso é importante seguir algumas práticas recomendadas.

Para garantir a eliminação de partições, evite o uso de 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 do filtro como NVARCHAR para reduzir a complexidade do plano de consulta e aumentar a probabilidade de eliminação correta da partição. As colunas de partição são normalmente 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.

Vistas particionadas do Lago Delta

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

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

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

O nome da OPENROWSET pasta na função (yellow neste exemplo) que é concatenada com o LOCATION URI definido na DeltaLakeStorage fonte de dados deve fazer referência à pasta Delta Lake raiz que contém uma subpasta chamada _delta_log.

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

Visualizações JSON

As visualizações são a boa escolha se você precisar fazer algum processamento extra em cima do conjunto de resultados que é buscado dos arquivos. Um exemplo pode ser a análise de arquivos JSON onde precisamos aplicar as funções JSON para extrair os valores dos documentos JSON:

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 OPENJSON função analisa cada linha do arquivo JSONL contendo um documento JSON por linha em formato textual.

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

As exibições podem ser criadas sobre os 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 do modo de exibição, e a chave de acesso somente leitura deve ser colocada na credencial de escopo do banco de dados à qual o modo de exibição faz referência.

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, consulte Consultar dados do Azure Cosmos DB com um pool SQL sem servidor no Azure Synapse Link.

Utilizar uma vista

Pode utilizar vistas nas suas consultas do mesmo modo que utiliza vistas em consultas do SQL Server.

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

Ao consultar o modo de exibição, você pode encontrar erros ou resultados inesperados. Isso provavelmente significa que o modo de 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 alinhar com as alterações de esquema subjacentes.

Conteúdos relacionados

Para obter informações sobre como consultar diferentes tipos de arquivo, consulte os artigos Consultar arquivo CSV único, Consultar arquivos Parquet e Consultar arquivos JSON.