Creare e usare le viste usando il pool SQL serverless in Azure Synapse Analytics

Questa sezione illustra come creare e usare le viste per il wrapping delle query del pool SQL serverless. Le viste consentono di riutilizzare tali query. Sono anche necessarie se si vogliono usare strumenti, come Power BI, in combinazione con il pool SQL serverless.

Prerequisiti

Il primo passaggio consiste nel creare un database in cui verrà creata la vista e inizializzare gli oggetti necessari per l'autenticazione in archiviazione di Azure mediante l’esecuzione di script di installazione su tale database. Tutte le query in questo articolo verranno eseguite nel database di esempio.

Viste su dati esterni

È possibile creare viste nello stesso modo in cui si creano le normali viste di SQL Server. La query seguente crea una vista che legge il file population.csv.

Nota

Cambiare la prima riga della query, ossia [mydbname], in modo da usare il database creato.

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];

La vista usa un'istruzione EXTERNAL DATA SOURCE con un URL radice della risorsa di archiviazione, come DATA_SOURCE e aggiunge un percorso file relativo per i file.

Viste Delta Lake

Se si creano le viste dalla cartella Delta Lake, è necessario specificare il percorso della cartella radice dopo l'opzione BULK anziché specificare il percorso del file.

La funzione OPENROWSET che legge i dati dalla cartella Delta Lake esaminerà la struttura di cartelle e identificherà automaticamente i percorsi dei file.

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

Per altre informazioni, vedere la pagina self-help del pool SQL serverless di Synapse e i problemi noti di Azure Synapse Analytics.

Viste partizionate

Se è presente un set di file partizionati nella struttura di cartelle gerarchica, è possibile descrivere il modello di partizione usando i caratteri jolly nel percorso del file. Usare la funzione FILEPATH per esporre parti del percorso della cartella come colonne di partizionamento.

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

Le viste partizionate possono migliorare le prestazioni delle query eseguendo l'eliminazione delle partizioni quando si eseguono query con filtri sulle colonne di partizionamento. Tuttavia, non tutte le query supportano l'eliminazione delle partizioni, quindi è importante seguire alcune procedure consigliate.

Per garantire l'eliminazione della partizione, evitare di usare sottoquery nei filtri, poiché possono interferire con la possibilità di eliminare le partizioni. Passare invece il risultato della sottoquery come variabile al filtro.

Quando si usano JOIN nelle query SQL, dichiarare il predicato di filtro come NVARCHAR per ridurre la complessità del piano di query e aumentare la probabilità di eliminazione corretta della partizione. Le colonne di partizione vengono in genere dedotte come NVARCHAR(1024), quindi l'uso dello stesso tipo per il predicato evita la necessità di un cast implicito, che può aumentare la complessità del piano di query.

Viste partizionate Delta Lake

Se si creano viste partizionate nell'account di archiviazione Delta Lake, è possibile specificare solo una cartella Delta Lake radice e non è necessario esporre in modo esplicito le colonne di partizionamento usando la funzione FILEPATH:

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

La funzione OPENROWSET esaminerà la struttura della cartella Delta Lake sottostante, identificando ed esponendo automaticamente le colonne di partizionamento. L'eliminazione delle partizioni verrà eseguita automaticamente se si inserisce la colonna di partizionamento nella clausola WHERE di una query.

Il nome della cartella nella funzione OPENROWSET (yellow in questo esempio) che è concatenato usando l'URI LOCATION definito nell'origine dati DeltaLakeStorage deve fare riferimento alla cartella Delta Lake radice che contiene una sottocartella denominata _delta_log.

Per altre informazioni, vedere la pagina self-help del pool SQL serverless di Synapse e i problemi noti di Azure Synapse Analytics.

Viste JSON

Le viste sono la scelta ottimale se è necessario eseguire attività di elaborazione aggiuntive sul set di risultati recuperato dai file. Ad esempio, se occorre eseguire l'analisi dei file JSON in cui è necessario applicare le funzioni JSON per estrarre i valori dai documenti 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')

La funzione OPENJSON analizza ogni riga dal file JSONL contenente un documento JSON per riga in formato testuale.

Viste di Azure Cosmos DB nei contenitori

È possibile creare viste sui contenitori di Azure Cosmos DB se l'archivio analitico di Azure Cosmos DB è abilitato nel contenitore. Il nome dell'account, il nome del database e il nome del contenitore di Azure Cosmos DB devono essere aggiunti come parte della vista e la chiave di accesso di sola lettura deve essere inserita nelle credenziali con ambito database a cui fa riferimento la vista.

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

Per altre informazioni, vedere Eseguire query sui dati di Azure Cosmos DB con un pool SQL serverless in Collegamento ad Azure Synapse.

Usare una vista

È possibile utilizzare le visualizzazioni nelle query nello stesso modo in cui si utilizzano le visualizzazioni nelle query di SQL Server.

La query seguente illustra l'uso della vista population creata nella sezione Creare una vista. La query restituisce i nomi dei paesi/aree geografiche con la popolazione del 2019 in ordine decrescente.

Nota

Cambiare la prima riga della query, ossia [mydbname], in modo da usare il database creato.

USE [mydbname];
GO

SELECT
    country_name, population
FROM populationView
WHERE
    [year] = 2019
ORDER BY
    [population] DESC;

Quando si esegue una query sulla vista, è possibile che si verifichino errori o risultati imprevisti. Ciò significa probabilmente che la vista fa riferimento a colonne o a oggetti modificati o che non esistono più. È necessario modificare manualmente la definizione della vista in modo che sia allineata alle modifiche dello schema sottostante.

Passaggi successivi

Per informazioni su come eseguire una query su tipi di file diversi, vedere gli articoli Eseguire query su un singolo file CSV, Eseguire query su file Per e Eseguire query su file JSON.

• Novità di Azure Synapse Analytics.
• Procedure consigliate per il pool SQL serverless in Azure Synapse Analytics
• Risolvere i problemi relativi al pool SQL serverless in Azure Synapse Analytics
• Risolvere i problemi relativi a una query lenta in un pool SQL dedicato
• Risoluzione dei problemi relativi a Synapse Studio