Esercitazione: Creare data warehouse logici con il pool SQL serverless

  • Articolo

Questa esercitazione descrive come creare un data warehouse logico basato su Archiviazione di Azure e Azure Cosmos DB.

Il data warehouse logico è un livello relazionale basato su origini dati di Azure, ad esempio Azure Data Lake Storage (ADLS), archiviazione analitica di Azure Cosmos DB o Archiviazione BLOB di Azure.

Creare un database del data warehouse logico

È necessario creare un database personalizzato in cui verranno archiviate le viste e le tabelle esterne che fanno riferimento a origini dati esterne.

CREATE DATABASE Ldw
      COLLATE Latin1_General_100_BIN2_UTF8;

Queste regole di confronto offrono prestazioni ottimali durante la lettura di file Parquet e Azure Cosmos DB. Se non si vogliono specificare regole di confronto del database, assicurarsi di specificare tali regole nella definizione di colonna.

Configurare origini dati e formati

Come primo passaggio, è necessario configurare l'origine dati e specificare il formato di file dei dati archiviati in remoto.

Creare un'origine dati

Le origini dati rappresentano le informazioni sulla stringa di connessione che descrivono dove vengono inseriti i dati e come eseguire l'autenticazione nell'origine dati.

Un esempio di definizione dell'origine dati che fa riferimento al set di dati aperti di Azure ECDC COVID 19 pubblico è illustrato nell'esempio seguente:

CREATE EXTERNAL DATA SOURCE ecdc_cases WITH (
    LOCATION = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/'
);

Un chiamante può accedere all'origine dati senza credenziali se un proprietario dell'origine dati ha consentito l'accesso anonimo o concede l'accesso esplicito all'identità Microsoft Entra del chiamante.

È possibile definire in modo esplicito una credenziale personalizzata che verrà usata durante l'accesso ai dati nell'origine dati esterna.

Come prerequisito, sarà necessario creare una chiave master nel database:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Setup you password - you need to create master key only once';

Nell'origine dati esterna seguente il pool Synapse SQL deve usare un'identità gestita dell'area di lavoro per accedere ai dati nell'archiviazione.

CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity
WITH IDENTITY = 'Managed Identity';
GO
CREATE EXTERNAL DATA SOURCE ecdc_cases WITH (
    LOCATION = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/',
    CREDENTIAL = WorkspaceIdentity
);

Per accedere all'archiviazione analitica di Azure Cosmos DB, è necessario definire credenziali contenenti una chiave dell'account Azure Cosmos DB di sola lettura.

CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';

Qualsiasi utente con il ruolo Amministratore di Synapse può usare queste credenziali per accedere ad Azure Data Lake Storage o all'archiviazione analitica di Azure Cosmos DB. Se sono presenti utenti con privilegi limitati che non hanno il ruolo Amministratore di Synapse, è necessario concedere loro un'autorizzazione esplicita per fare riferimento a queste credenziali con ambito database:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::WorkspaceIdentity TO <user>
GO
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::MyCosmosDbAccountCredential TO <user>
GO

Per maggiori dettagli, vedere la pagina GRANT - Autorizzazioni per credenziali con ambito database.

Definire i formati di file esterni

I formati di file esterni definiscono la struttura dei file archiviati nell'origine dati esterna. È possibile definire formati di file esterni Parquet e CSV:

CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
GO
CREATE EXTERNAL FILE FORMAT CsvFormat WITH (  FORMAT_TYPE = DELIMITEDTEXT );

Per altre informazioni, vedere Usare tabelle esterne con Synapse SQL e CREATE EXTERNAL FILE FORMAT per descrivere il formato di file CSV o Parquet.

Esplorare i dati

Dopo aver configurato le origini dati, è possibile usare la funzione OPENROWSET per esplorare i dati. La funzione OPENROWSET legge il contenuto di un'origine dati remota, ad esempio un file, e lo restituisce come set di righe.

select top 10  *
from openrowset(bulk 'latest/ecdc_cases.parquet',
                data_source = 'ecdc_cases',
                format='parquet') as a

La funzione OPENROWSET offre informazioni sulle colonne nei file o contenitori esterni e consente di definire uno schema delle tabelle e delle viste esterne.

Creare tabelle esterne in Archiviazione di Azure

Dopo aver individuato lo schema, è possibile creare tabelle e viste esterne basate sulle origini dati esterne. La procedura consigliata consiste nell'organizzare le tabelle e le viste negli schemi dei database. Nella query seguente è possibile creare uno schema in cui inserire tutti gli oggetti che accedono al set di dati ECDC COVID in Azure Data Lake Storage:

create schema ecdc_adls;

Gli schemi dei database sono utili per raggruppare gli oggetti e definire le autorizzazioni per ogni schema.

Dopo aver definito gli schemi, è possibile creare tabelle esterne che fanno riferimento ai file. La tabella esterna seguente fa riferimento al file Parquet di ECDC COVID inserito in Archiviazione di Azure:

create external table ecdc_adls.cases (
    date_rep                   date,
    day                        smallint,
    month                      smallint,
    year                       smallint,
    cases                      smallint,
    deaths                     smallint,
    countries_and_territories  varchar(256),
    geo_id                     varchar(60),
    country_territory_code     varchar(16),
    pop_data_2018              int,
    continent_exp              varchar(32),
    load_date                  datetime2(7),
    iso_country                varchar(16)
) with (
    data_source= ecdc_cases,
    location = 'latest/ecdc_cases.parquet',
    file_format = ParquetFormat
);

Per ottimizzare le prestazioni delle query, assicurarsi di usare i tipi più piccoli possibili per le colonne stringa e numero.

Creare viste in Azure Cosmos DB

In alternativa alle tabelle esterne, è possibile creare viste basate sui dati esterni.

Analogamente alle tabelle illustrate nell'esempio precedente, è necessario inserire le viste in schemi separati:

create schema ecdc_cosmosdb;

È ora possibile creare una vista nello schema che fa riferimento a un contenitore di Azure Cosmos DB:

CREATE OR ALTER VIEW ecdc_cosmosdb.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 ottimizzare le prestazioni, è consigliabile usare i tipi più piccoli possibili nella definizione dello schema WITH.

Nota

È necessario inserire la chiave dell'account Azure Cosmos DB in una credenziale separata e fare riferimento a questa credenziale dalla funzione OPENROWSET. Non mantenere la chiave dell'account nella definizione della vista.

Accesso e autorizzazioni

Come passaggio finale, è necessario creare utenti di database che devono poter accedere al data warehouse logico e concedere loro le autorizzazioni per selezionare i dati dalle tabelle e dalle viste esterne. Nello script seguente è possibile vedere come aggiungere un nuovo utente che verrà autenticato usando l'identità di Microsoft Entra:

CREATE USER [jovan@contoso.com] FROM EXTERNAL PROVIDER;
GO

Invece delle entità di sicurezza di Microsoft Entra, è possibile creare entità SQL che eseguono l'autenticazione con il nome e la password dell'account di accesso.

CREATE LOGIN [jovan] WITH PASSWORD = 'My Very strong Password ! 1234';
CREATE USER [jovan] FROM LOGIN [jovan];

In entrambi i casi, è possibile assegnare autorizzazioni agli utenti.

DENY ADMINISTER DATABASE BULK OPERATIONS TO [jovan@contoso.com]
GO
GRANT SELECT ON SCHEMA::ecdc_adls TO [jovan@contoso.com]
GO
GRANT SELECT ON OBJECT::ecdc_cosmosDB.cases TO [jovan@contoso.com]
GO
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::MyCosmosDbAccountCredential TO [jovan@contoso.com]
GO

Le regole di sicurezza dipendono dai criteri di sicurezza. Alcune linee guida generiche sono:

  • È consigliabile negare l'autorizzazione ADMINISTER DATABASE BULK OPERATIONS per i nuovi utenti perché devono essere in grado di leggere i dati solo usando le tabelle esterne e le viste preparate.
  • È consigliabile fornire l'autorizzazione SELECT solo alle tabelle che alcuni utenti devono essere in grado di usare.
  • Se si fornisce l'accesso ai dati usando le viste, è necessario concedere l'autorizzazione REFERENCES alle credenziali che verranno usate per accedere all'origine dati esterna.

Questo utente ha le autorizzazioni minime necessarie per eseguire query su dati esterni. Se si vuole creare un utente esperto che può configurare autorizzazioni, tabelle e viste esterne, è possibile concedere all'utente l'autorizzazione CONTROL:

GRANT CONTROL TO [jovan@contoso.com]

Sicurezza basata su ruoli

Invece di assegnare autorizzazioni ai singoli utenti, è consigliabile organizzarli in ruoli e gestire le autorizzazioni a livello di ruolo. L'esempio di codice seguente consente di creare un nuovo ruolo che rappresenta le persone autorizzate ad analizzare i casi di COVID-19 e aggiunge tre utenti a questo ruolo:

CREATE ROLE CovidAnalyst;

ALTER ROLE CovidAnalyst ADD MEMBER [jovan@contoso.com];
ALTER ROLE CovidAnalyst ADD MEMBER [milan@contoso.com];
ALTER ROLE CovidAnalyst ADD MEMBER [petar@contoso.com];

È possibile assegnare le autorizzazioni a tutti gli utenti che appartengono al gruppo:

GRANT SELECT ON SCHEMA::ecdc_cosmosdb TO [CovidAnalyst];
GO
DENY SELECT ON SCHEMA::ecdc_adls TO [CovidAnalyst];
GO
DENY ADMINISTER DATABASE BULK OPERATIONS TO [CovidAnalyst];

Questo controllo degli accessi di sicurezza basata sui ruoli potrebbe semplificare la gestione delle regole di sicurezza.

Passaggi successivi