Virtualizzazione dei dati con Istanza gestita di SQL di Azure

Si applica a: Istanza gestita di SQL di Azure

La funzionalità di virtualizzazione dei dati di Istanza gestita di SQL di Azure consente di eseguire query Transact-SQL (T-SQL) sui file che archiviano dati in formati di dati comuni in Azure Data Lake Storage Gen2 o Archiviazione BLOB di Azure e combinarli con relazionali archiviati in locale dati che usano join. In questo modo è possibile accedere in modo trasparente ai dati esterni mantenendoli nel formato e nella posizione originali, noti anche come virtualizzazione dei dati.

Panoramica

La virtualizzazione dei dati offre due modi per eseguire query sui file destinati a diversi set di scenari:

  • Sintassi OPENROWSET: ottimizzata per l'esecuzione di query ad hoc dei file. In genere usato per esplorare rapidamente il contenuto e la struttura di un nuovo set di file.
  • Tabelle esterne: ottimizzate per l'esecuzione ripetitiva di query sui file usando una sintassi identica a quella dei dati archiviati in locale nel database. Le tabelle esterne richiedono diversi passaggi di preparazione rispetto alla sintassi OPENROWSET, ma consentono un maggiore controllo sull'accesso ai dati. Le tabelle esterne vengono in genere usate per carichi di lavoro analitici e report.

Formati di file

I formati di file parquet e di testo delimitato (CSV) sono supportati direttamente. Il formato di file JSON è supportato indirettamente specificando il formato di file CSV in cui le query restituiscono ogni documento come riga separata. È possibile analizzare ulteriormente le righe usando JSON_VALUE e OPENJSON.

Tipi di archiviazione

I file possono essere archiviati in Azure Data Lake Storage Gen2 o Archiviazione BLOB di Azure. Per eseguire query sui file, è necessario specificare il percorso in un formato specifico e usare il prefisso del tipo di percorso corrispondente al tipo di origine esterna e endpoint/protocollo, ad esempio gli esempi seguenti:

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

--Data Lake endpoint
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name>.parquet

Importante

Il prefisso del tipo di posizione specificato viene usato per scegliere il protocollo ottimale per la comunicazione e sfruttare le funzionalità avanzate offerte dal tipo di archiviazione specifico. L'uso del prefisso generico https:// è disabilitato. Usare sempre prefissi specifici dell'endpoint.

Introduzione

Se non si ha familiarità con la virtualizzazione dei dati e si vuole testare rapidamente le funzionalità, iniziare eseguendo query sui set di dati pubblici disponibili in Set di dati aperti di Azure, ad esempio il set di dati BING COVID-19 che consente l'accesso anonimo.

Usare gli endpoint seguenti per eseguire query sui set di dati BING COVID-19:

  • Parquet: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
  • CSV: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

Per una guida introduttiva, eseguire questa semplice query T-SQL per ottenere prima informazioni dettagliate sul set di dati:

--Quick query on a file stored in a publicly available storage account:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet',
 FORMAT = 'parquet'
) AS filerows

È possibile continuare l'esplorazione dei set di dati aggiungendo WHERE, GROUP BY e altre clausole in base al set di risultati della prima query.

Se la prima query ha esito negativo nell'istanza gestita, è probabile che l'istanza abbia accesso agli account di archiviazione di Azure con restrizioni ed è necessario rivolgersi all'esperto di rete per abilitare l'accesso prima di poter procedere con l'esecuzione di query.

Dopo aver acquisito familiarità con l'esecuzione di query sui set di dati pubblici, è consigliabile passare a set di dati non pubblici che richiedono credenziali, concedere diritti di accesso e configurare le regole del firewall. In molti scenari reali si opera principalmente con set di dati privati.

Accesso agli account di archiviazione non pubblici

Un utente connesso a un'istanza gestita deve essere autorizzato ad accedere ai file di query archiviati in un account di archiviazione non pubblico. I passaggi di autorizzazione dipendono dal modo in cui l'istanza gestita esegue l'autenticazione nell'archiviazione. Il tipo di autenticazione e i parametri correlati non vengono forniti direttamente con ogni query. Vengono incapsulati nell'oggetto credenziali con ambito database archiviato nel database utente. Le credenziali vengono usate dal database per accedere all'account di archiviazione ogni volta che viene eseguita la query. Istanza gestita di SQL di Azure supporta i tipi di autenticazione seguenti:

L'identità gestita, nota anche come IDENTITÀ del servizio gestito, è una funzionalità di Azure Active Directory (Azure AD) che fornisce istanze dei servizi di Azure, ad esempio Istanza gestita di SQL di Azure, con un'identità gestita automaticamente in Azure AD. Questa identità può essere usata per autorizzare le richieste di accesso ai dati in account di archiviazione non pubblici.

Prima di accedere ai dati, l'amministratore di Archiviazione di Azure deve concedere le autorizzazioni all'identità gestita per accedere ai dati. La concessione delle autorizzazioni all'identità gestita dell'istanza gestita viene eseguita allo stesso modo della concessione dell'autorizzazione a qualsiasi altro utente di Azure AD.

La creazione di credenziali con ambito database per l'autenticazione dell'identità gestita è molto semplice:

-- Optional: Create MASTER KEY if it doesn't exist in the database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>'
GO
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'Managed Identity'

Origine dati esterna

Un'origine dati esterna è un'astrazione che consente di fare facilmente riferimento a un percorso di file tra più query. Per eseguire query sui percorsi pubblici, è sufficiente specificare durante la creazione di un'origine dati esterna il percorso del file:

CREATE EXTERNAL DATA SOURCE MyExternalDataSource
WITH (
	LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
)

Quando si accede agli account di archiviazione non pubblici, insieme al percorso, è anche necessario fare riferimento a una credenziale con ambito database con parametri di autenticazione incapsulati:

--Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE DemoPrivateExternalDataSource
WITH (
	LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
    	CREDENTIAL = [MyCredential] 
)

Eseguire query sulle origini dati con OPENROWSET

La sintassi OPENROWSET consente l'esecuzione immediata di query ad hoc, creando solo il numero minimo di oggetti di database necessari. OPENROWSET richiede solo la creazione dell'origine dati esterna (ed eventualmente la credenziale) anziché l'approccio alla tabella esterna che richiede un formato di file esterno e la tabella esterna stessa.

Il valore del DATA_SOURCE parametro viene automaticamente anteporto al parametro BULK per formare il percorso completo del file.

Quando si usa OPENROWSET specifica il formato del file, ad esempio l'esempio seguente, che esegue una query su un singolo file:

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'bing_covid-19_data.parquet',
 DATA_SOURCE = 'DemoPublicExternalDataSource',
 FORMAT = 'parquet'
) AS filerows

Esecuzione di query su più file e cartelle

Il OPENROWSET comando consente anche di eseguire query su più file o cartelle usando caratteri jolly nel percorso BULK.

L'esempio seguente usa il set di dati aperto dei record delle corse in taxi giallo di New York:

--Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',--You need to create the data source first
 FORMAT = 'parquet'
) AS filerows

Quando si eseguono query su più file o cartelle, tutti i file a cui si accede con il singolo OPENROWSET devono avere la stessa struttura, ad esempio lo stesso numero di colonne e tipi di dati. Le cartelle non possono essere attraversate in modo ricorsivo.

Inferenza dello schema

L'inferenza automatica dello schema consente di scrivere rapidamente query ed esplorare i dati quando non si conoscono schemi di file. L'inferenza dello schema funziona solo con i file di formato Parquet.

Anche se conveniente, il costo è che i tipi di dati dedotti possono essere maggiori dei tipi di dati effettivi. Ciò può comportare prestazioni di query scarse perché potrebbero non essere presenti informazioni sufficienti nei file di origine per garantire che venga usato il tipo di dati appropriato. Ad esempio, i file parquet non contengono metadati relativi alla lunghezza massima della colonna di caratteri, quindi l'istanza lo deduce come varchar(8000).

Usare la stored procedure sp_describe_first_results_set per controllare i tipi di dati risultanti della query, come l'esempio seguente:

EXEC sp_describe_first_result_set N'
 SELECT
 vendor_id, pickup_datetime, passenger_count
 FROM 
 OPENROWSET(
  BULK ''taxi/*/*/*'',
  DATA_SOURCE = ''NYCTaxiDemoDataSource'',
  FORMAT=''parquet''
 ) AS nyc';

Dopo aver appreso i tipi di dati, è possibile specificarli usando la WITH clausola per migliorare le prestazioni:

SELECT TOP 100
 vendor_id, pickup_datetime, passenger_count
FROM
OPENROWSET(
 BULK 'taxi/*/*/*',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT='PARQUET'
 )
WITH (
vendor_id varchar(4), -- we're using length of 4 instead of the inferred 8000
pickup_datetime datetime2,
passenger_count int
) AS nyc;

Poiché lo schema dei file CSV non può essere determinato automaticamente, specificare in modo esplicito le colonne usando la WITH clausola :

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'population/population.csv',
 DATA_SOURCE = 'PopulationDemoDataSourceCSV',
 FORMAT = 'CSV')
WITH (
 [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
 [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
 [year] smallint,
 [population] bigint
) AS filerows

Funzioni per i metadati dei file

Quando si eseguono query su più file o cartelle, è possibile usare Filepath funzioni e Filename per leggere i metadati dei file e ottenere parte del percorso o del percorso completo e del nome del file da cui ha origine la riga nel set di risultati:

--Query all files and project file path and file name information for each row:
SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder],
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], *
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT = 'parquet') AS filerows
--List all paths:
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder]
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT = 'parquet') AS filerows

Quando viene chiamato senza un parametro, la Filepath funzione restituisce il percorso del file da cui ha origine la riga. Quando DATA_SOURCE viene usato in OPENROWSET, restituisce il percorso relativo a DATA_SOURCE, in caso contrario restituisce il percorso completo del file.

Se viene chiamata con un parametro, viene restituita una parte del percorso che corrisponde al carattere jolly nella posizione specificata nel parametro. Ad esempio, il valore del parametro 1 restituisce la parte del percorso che corrisponde al primo carattere jolly.

La Filepath funzione può essere usata anche per filtrare e aggregare righe:

SELECT
 r.filepath() AS filepath
 ,r.filepath(1) AS [year]
 ,r.filepath(2) AS [month]
 ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
DATA_SOURCE = 'NYCTaxiDemoDataSource',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2017')
 AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
 r.filepath()
 ,r.filepath(1)
 ,r.filepath(2)
ORDER BY
 filepath;

Creazione di una visualizzazione sopra OPENROWSET

È possibile creare e usare viste per eseguire il wrapping delle query OPENROWSET in modo da poter riutilizzare facilmente la query sottostante:

CREATE VIEW TaxiRides AS
SELECT *
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT = 'parquet'
) AS filerows

È anche utile aggiungere colonne con i dati del percorso del file a una visualizzazione usando la Filepath funzione per filtrare in modo più semplice e efficiente. L'uso delle viste può ridurre il numero di file e la quantità di dati che la query deve leggere ed elaborare quando viene filtrata in base a una di queste colonne:

CREATE VIEW TaxiRides AS
SELECT *
 ,filerows.filepath(1) AS [year]
 ,filerows.filepath(2) AS [month]
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT = 'parquet'
) AS filerows

Le visualizzazioni consentono anche agli strumenti di creazione di report e analitici come Power BI di utilizzare i risultati di OPENROWSET.

Tabelle esterne

Le tabelle esterne incapsulano l'accesso ai file rendendo l'esperienza di query quasi identica all'esecuzione di query sui dati relazionali locali archiviati nelle tabelle utente. La creazione di una tabella esterna richiede che gli oggetti origine dati esterna e formato di file esterno esistano:

--Create external file format
CREATE EXTERNAL FILE FORMAT DemoFileFormat
WITH (
 FORMAT_TYPE=PARQUET
)
GO

--Create external table:
CREATE EXTERNAL TABLE tbl_TaxiRides(
 vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2,
 pickup_datetime DATETIME2,
 dropoff_datetime DATETIME2,
 passenger_count INT,
 trip_distance FLOAT,
 fare_amount FLOAT,
 extra FLOAT,
 mta_tax FLOAT,
 tip_amount FLOAT,
 tolls_amount FLOAT,
 improvement_surcharge FLOAT,
 total_amount FLOAT
)
WITH (
 LOCATION = 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = DemoDataSource,
 FILE_FORMAT = DemoFileFormat
);
GO

Dopo aver creato la tabella esterna, è possibile eseguire una query come qualsiasi altra tabella:

SELECT TOP 10 *
FROM tbl_TaxiRides

Analogamente OPENROWSETa , le tabelle esterne consentono di eseguire query su più file e cartelle usando caratteri jolly. Le funzioni di inferenza dello schema e filepath/filename non sono supportate con tabelle esterne.

Considerazioni sulle prestazioni

Non esiste alcun limite rigido al numero di file o alla quantità di dati su cui è possibile eseguire query, ma le prestazioni delle query dipendono dalla quantità di dati, dal formato dei dati, dalla modalità di organizzazione dei dati e dalla complessità delle query e dei join.

Esecuzione di query sui dati partizionati

I dati sono spesso organizzati in sottocartelle denominate anche partizioni. È possibile indicare all'istanza gestita di eseguire query solo su cartelle e file specifici. In questo modo si riduce il numero di file e la quantità di dati che la query deve leggere ed elaborare, ottenendo prestazioni migliori. Questo tipo di ottimizzazione delle query è noto come eliminazione della partizione o eliminazione della partizione. È possibile eliminare le partizioni dall'esecuzione di query usando il filepath() della funzione di metadati nella clausola WHERE della query.

La query di esempio seguente legge i file di dati nyc Yellow Taxi solo per gli ultimi tre mesi del 2017:

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',        
        FIRSTROW = 2
    )
WITH (
    vendor_id INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Se i dati archiviati non sono partizionati, prendere in considerazione il partizionamento per migliorare le prestazioni delle query.

Statistiche

La raccolta di statistiche sui dati esterni è una delle operazioni più importanti che è possibile eseguire per l'ottimizzazione delle query. Più l'istanza conosce i dati, più velocemente può eseguire query. Query Optimizer del motore SQL è un ottimizzatore basato sui costi. Esegue un confronto fra i costi dei vari piani di query e poi sceglie quello che costa meno, che in molti casi è il piano eseguito più velocemente.

Creazione automatica di statistiche

Istanza gestita di SQL di Azure analizza le query utente in ingresso per individuare le statistiche mancanti. Se mancano statistiche, Query Optimizer crea automaticamente statistiche su singole colonne nel predicato di query o condizione di join per migliorare le stime della cardinalità per il piano di query. La creazione automatica di statistiche viene generata in modo sincrono; se nelle colonne non sono presenti tutte le statistiche, quindi, è possibile che si verifichi un leggero peggioramento delle prestazioni delle query. Il tempo necessario per creare le statistiche relative a una colonna dipende dalle dimensioni dei file di destinazione.

Statistiche manuali DI OPENROWSET

È possibile creare statistiche a colonna singola per il OPENROWSET percorso usando la sp_create_openrowset_statistics stored procedure passando la query select con una singola colonna come parametro:

EXEC sys.sp_create_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
'

Per impostazione predefinita, l'istanza usa il 100% dei dati forniti nel set di dati per creare statistiche. Facoltativamente, è possibile specificare le dimensioni del campione come percentuale usando le TABLESAMPLE opzioni. Per creare statistiche a colonna singola per più colonne, eseguire la stored procedure per ognuna delle colonne. Non è possibile creare statistiche su più colonne per il OPENROWSET percorso.

Per aggiornare le statistiche esistenti, eliminarle prima usando la sp_drop_openrowset_statistics stored procedure e quindi ricrearle usando :sp_create_openrowset_statistics

EXEC sys.sp_drop_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
'

Statistiche manuali della tabella esterna

La sintassi per la creazione di statistiche su tabelle esterne è simile a quella usata per le tabelle utente normali. Per creare statistiche su una colonna, specificare un nome per l'oggetto statistiche e il nome della colonna:

CREATE STATISTICS sVendor
ON tbl_TaxiRides (vendor_id)
WITH FULLSCAN, NORECOMPUTE

Le WITH opzioni sono obbligatorie e, per le dimensioni del campione, le opzioni consentite sono FULLSCAN e SAMPLE n percentuale. Per creare statistiche a colonna singola per più colonne, eseguire la stored procedure per ognuna delle colonne. Le statistiche su più colonne non sono supportate.

Risoluzione dei problemi

I problemi relativi all'esecuzione di query sono in genere causati da un'istanza gestita che non è in grado di accedere al percorso del file. I messaggi di errore correlati potrebbero segnalare diritti di accesso insufficienti, percorso o percorso file non esistente, file utilizzato da un altro processo o tale directory non può essere elencata. Nella maggior parte dei casi questo indica che l'accesso ai file è bloccato dai criteri di controllo del traffico di rete o a causa della mancanza di diritti di accesso. Questo è ciò che deve essere controllato:

  • Percorso errato o non corretto.
  • Validità della chiave di firma di accesso condiviso: potrebbe essere scaduta, ad esempio fuori dal periodo di validità, contenente un errore di ortografia, a partire da un punto interrogativo.
  • Autorizzazioni per la chiave di firma di accesso condiviso consentite: lettura minima ed elenco se vengono usati caratteri jolly
  • Traffico in ingresso bloccato nell'account di archiviazione. Per altri dettagli, vedere Gestione delle regole di rete virtuale per Archiviazione di Azure e assicurarsi che l'accesso dalla rete virtuale dell'istanza gestita sia consentito.
  • Il traffico in uscita bloccato nell'istanza gestita usando i criteri dell'endpoint di archiviazione. Consentire il traffico in uscita verso l'account di archiviazione.
  • Diritti di accesso all'identità gestita: assicurarsi che l'entità servizio di Azure AD che rappresenta l'identità gestita dell'istanza disponga dei diritti di accesso concessi per l'account di archiviazione.

Passaggi successivi