Criar e utilizar vistas com o conjunto de SQL sem servidor no Azure Synapse Analytics

Artigo
05/08/2023

Nesta secção, irá aprender a criar e utilizar vistas para encapsular consultas do conjunto de SQL sem servidor. As vistas permitir-lhe-ão reutilizar essas consultas. As vistas também são necessárias se quiser utilizar ferramentas, como o Power BI, em conjunto com o conjunto de SQL sem servidor.

Pré-requisitos

O primeiro passo é criar uma base de dados onde a vista será criada e inicializar os objetos necessários para autenticar no armazenamento do Azure ao executar o script de configuração nessa base de dados. Todas as consultas neste artigo serão executadas na base de dados de exemplo.

Vistas sobre dados externos

Pode criar vistas da mesma forma que cria vistas de SQL Server regulares. A consulta seguinte cria uma vista que lê population.csv ficheiro.

Nota

Altere a primeira linha na consulta, ou seja, [mydbname], para que esteja a utilizar a base de dados que criou.

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 vista utiliza um EXTERNAL DATA SOURCE com um URL de raiz do seu armazenamento, como um DATA_SOURCE e adiciona um caminho de ficheiro relativo aos ficheiros.

Vistas do Delta Lake

Se estiver a criar as vistas na parte superior da pasta do Delta Lake, terá de especificar a localização para a pasta raiz após a opção BULK em vez de especificar o caminho do ficheiro.

Pasta ECDC COVID-19 Delta Lake

A OPENROWSET função que lê dados da pasta do Delta Lake examinará a estrutura de pastas e identificará automaticamente as localizações dos ficheiros.

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, veja a página de ajuda autónoma do conjunto de SQL sem servidor do Synapse e os problemas conhecidos do Azure Synapse Analytics.

Vistas particionadas

Se tiver um conjunto de ficheiros particionados na estrutura de pastas hierárquicas, pode descrever o padrão de partição com os carateres universais no caminho do ficheiro. Utilize a FILEPATH função para expor partes do caminho da pasta como colunas de criação de partições.

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 vistas particionadas podem melhorar o desempenho das suas consultas ao efetuar a eliminação de partições quando as consulta com filtros nas colunas de criação de partições. No entanto, nem todas as consultas suportam a eliminação de partições, pelo que é importante seguir algumas das melhores práticas.

Para garantir a eliminação da partição, evite utilizar subconsultas nos filtros, uma vez que podem interferir com a capacidade de eliminar partições. Em vez disso, transmita o resultado da subconsulta como uma variável para o filtro.

Ao utilizar 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ções correta. Normalmente, as colunas de partição são inferidas como NVARCHAR(1024), pelo que utilizar 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 Delta Lake

Se estiver a criar as vistas particionadas sobre o armazenamento do Delta Lake, pode especificar apenas uma pasta raiz do Delta Lake e não precisa de expor explicitamente as colunas de criação de partições com 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 subjacente do Delta Lake e identificará e exporá automaticamente as colunas de criação de partições. A eliminação da partição será feita automaticamente se colocar a coluna de criação de partições na WHERE cláusula de uma consulta.

O nome da pasta na função (yellow neste exemplo) que é concatenado com o URI definido na DeltaLakeStorage origem OPENROWSETLOCATION de dados tem de referenciar a pasta raiz do Delta Lake que contém uma subpasta chamada _delta_log.

Pasta Yellow Taxi Delta Lake

Para obter mais informações, veja a página de ajuda autónoma do conjunto de SQL sem servidor do Synapse e os problemas conhecidos do Azure Synapse Analytics.

Vistas JSON

As vistas são a boa opção se precisar de efetuar algum processamento adicional sobre o conjunto de resultados que é obtido a partir dos ficheiros. Um exemplo pode ser analisar ficheiros JSON em que precisamos de 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 ficheiro JSONL que contém um documento JSON por linha no formato textual.

Vistas do Azure Cosmos DB em contentores

As vistas podem ser criadas sobre os contentores do Azure Cosmos DB se o armazenamento analítico do Azure Cosmos DB estiver ativado no contentor. O nome da conta do Azure Cosmos DB, o nome da base de dados e o nome do contentor devem ser adicionados como parte da vista e a chave de acesso só de leitura deve ser colocada na credencial no âmbito da base de dados que a vista referencia.

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

Utilizar uma vista

Pode utilizar vistas nas suas consultas da mesma forma que utiliza vistas em consultas SQL Server.

A consulta seguinte demonstra como utilizar a vista population_csv que criámos em Criar uma vista. Devolve nomes de país/região com a respetiva população em 2019 por ordem descendente.