Condividi tramite

Virtualizzazione dei dati con il database SQL di Azure (anteprima)

Si applica a:Database SQL di Azure

La funzionalità di virtualizzazione dei dati del database SQL di Azure consente di eseguire query Transact-SQL (T-SQL) su file che archiviano dati in formati di dati comuni come CSV (senza bisogno di usare testo delimitato), Parquet e Delta (1.0). È possibile eseguire query su questi dati in Azure Data Lake Storage Gen2 o archiviazione BLOB di Azure e combinarli con i dati relazionali archiviati in locale usando join. In questo modo è possibile accedere in modo trasparente ai dati esterni (in modalità di sola lettura) mantenendolo nel formato e nella posizione originali, noto anche come virtualizzazione dei dati.

Informazioni generali

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

  • Sintassi OPENROWSET: ottimizzata per le query di file. Usato in genere per esplorare velocemente il contenuto e la struttura di un nuovo set di file.
  • Sintassi CREATE EXTERNAL TABLE: 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 una preparazione maggiore rispetto alla sintassi OPENROWSET, ma consentono un maggiore controllo sull'accesso ai dati. Le tabelle esterne vengono in genere usate per i carichi di lavoro analitici e i report.

In entrambi i casi, è necessario creare un'origine dati esterna usando la sintassi T-SQL CREATE EXTERNAL DATA SOURCE, come illustrato in questo articolo.

Formati di file

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

Tipi di archiviazione

È possibile archiviare i file in Archiviazione BLOB di Azure, File di Azure o Azure Data Lake Storage Gen2. Per eseguire query sui file, è necessario specificare il percorso in un formato specifico e usare il prefisso del tipo di posizione 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
--or
abs://<storage_account_name>.blob.core.windows.net/<container_name>/

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

Importante

Usare sempre prefissi specifici dell'endpoint. Il prefisso del tipo di posizione specificato viene usato per scegliere il protocollo ottimale per la comunicazione e sfruttare tutte le funzionalità avanzate offerte dal tipo di archiviazione specifico.

Il prefisso generico https:// è supportato solo per BULK INSERT, ma non per altri casi d'uso, incluso OPENROWSET o EXTERNAL TABLE.

Inizia subito

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 i seguenti endpoint 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 un avvio rapido, eseguire questa semplice query T-SQL per ottenere informazioni dettagliate sul set di dati. Questa query usa OPENROWSET per eseguire query su un file archiviato in un account di archiviazione disponibile pubblicamente:

--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 del set di dati aggiungendo WHEREGROUP BY e altre clausole in base al set di risultati della prima 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 l'inserimento di 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 database SQL di Azure deve essere autorizzato ad accedere ai file di query archiviati in account di archiviazione non pubblici. I passaggi di autorizzazione dipendono dal modo in cui il database SQL di Azure autentica l'archiviazione. I tipi di autenticazione e gli eventuali parametri correlati non vengono forniti direttamente con ogni query. Vengono incapsulati nell'oggetto credenziale 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.

Il database SQL di Azure supporta i tipi di autenticazione seguenti:

  • Firma di accesso condiviso (SAS)
  • Identità gestita
  • Autenticazione pass-through di Microsoft Entra tramite identità utente

Una firma di accesso condiviso fornisce l'accesso delegato ai file in un account di archiviazione. SAS offre un controllo granulare sul tipo di accesso che concedi, inclusi l'intervallo di validità, le autorizzazioni concesse e l'intervallo di indirizzi IP accettabili. Dopo aver creato il token di firma di accesso condiviso, non può essere revocato o eliminato e consente l'accesso fino alla scadenza del periodo di validità.

  1. È possibile ottenere un token SAS in diversi modi:

  2. Concedere le autorizzazioni Lettura ed elenco tramite la firma di accesso condiviso per accedere ai dati esterni. Attualmente, la virtualizzazione dei dati con il database SQL di Azure è di sola lettura.

  3. Per creare credenziali con ambito database nel database SQL di Azure, è prima necessario creare la chiave master del database, se non ne esiste già una. Una chiave master del database è necessaria quando la credenziale richiede SECRET.

    -- Create MASTER KEY if it doesn't exist in the database:
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>';

  4. Quando viene generato un token SAS, include un punto interrogativo (?) all'inizio del token. Per usare il token, è necessario rimuovere il punto interrogativo (?) durante la creazione di una credenziale. Per esempio:

    CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=secret string here';

Accesso all'archiviazione pubblica tramite account anonimi

Se il set di dati desiderato consente l'accesso pubblico (noto anche come accesso anonimo), non è necessaria alcuna credenziale purché Archiviazione di Azure sia configurata correttamente, vedere Configurare l'accesso in lettura anonimo per contenitori e BLOB.

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:

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 ad account di archiviazione non pubblici, insieme al percorso, è anche necessario fare riferimento a una credenziale con ambito database con parametri di autenticazione incapsulati. Lo script seguente crea un'origine dati esterna che punta al percorso del file e fa riferimento alle credenziali con ambito database.

--Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource
WITH (
    LOCATION = 'abs://<privatecontainer>@privatestorageaccount.blob.core.windows.net/dataset/' 
       CREDENTIAL = [MyCredential]
);

Eseguire query sulle origini dati con OPENROWSET

La sintassi OPENROWSET consente l'esecuzione immediata di query ad hoc, creando al tempo stesso solo il numero minimo di oggetti di database necessari.

OPENROWSET richiede solo la creazione dell'origine dati esterna (e possibilmente le credenziali) anziché l'approccio alla tabella esterna, che richiede un formato di file esterno e la tabella esterna stessa.

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

Quando si usa OPENROWSET, specificare 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 = 'MyExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Esecuzione di query su più file o cartelle

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

Nell'esempio seguente viene usato il set di dati aperto per i record delle corse dei taxi di New York.

Innanzitutto creare l'origine dati esterna:

--Create the data source first:
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

È ora possibile eseguire query su tutti i file con estensione .parquet nelle cartelle. Ad esempio, in questo caso si eseguirà una query solo sui file corrispondenti a un criterio di nome:

--Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 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 gli schemi di file. L'inferenza dello schema funziona solo con i file Parquet.

Sebbene siano pratici, i tipi di dati da inferenza potrebbero essere più grandi dei tipi di dati effettivi perché potrebbero essere presenti informazioni sufficienti nei file di origine per garantire l'uso appropriato del tipo di dati. Ciò può comportare prestazioni di query scarse. Ad esempio, i file Parquet non contengono metadati sulla lunghezza massima delle colonne di caratteri, quindi l’istanza deduce che si tratta di 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
 vendorID, tpepPickupDateTime, passengerCount
 FROM
 OPENROWSET(
  BULK ''yellow/*/*/*.parquet'',
  DATA_SOURCE = ''NYCTaxiExternalDataSource'',
  FORMAT=''parquet''
 ) AS nyc';

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

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount
FROM
OPENROWSET(
 BULK 'yellow/*/*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT='PARQUET'
 )
WITH (
vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000
tpepPickupDateTime datetime2,
passengerCount int
) AS nyc;

Poiché lo schema dei file CSV non può essere determinato automaticamente, le colonne devono sempre essere specificate usando la clausola WITH:

SELECT TOP 10 id, updated, confirmed, confirmed_change
FROM OPENROWSET(
 BULK 'bing_covid-19_data.csv',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'CSV',
 FIRSTROW = 2
)
WITH (
 id int,
 updated date,
 confirmed int,
 confirmed_change int
) AS filerows;

Funzioni per i metadati dei file

Quando si eseguono query su più file o cartelle, è possibile usare le funzioni filepath() 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 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;

Se viene chiamata senza un parametro, la funzione filepath() restituisce il percorso completo del file da cui ha origine la riga. Quando DATA_SOURCE viene usato in OPENROWSET, restituisce il percorso relativo all'oggetto 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 funzione filepath() 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 'yellow/puYear=*/puMonth=*/*.parquet',
DATA_SOURCE = 'NYCTaxiExternalDataSource',
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;

Creare una visualizzazione all'inizio di OPENROWSET

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

CREATE VIEW TaxiRides AS
SELECT *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

È anche utile aggiungere colonne con i dati del percorso del file a una visualizzazione usando la funzione filepath() per un filtro più semplice ed efficiente. L'uso delle visualizzazioni consente di ridurre il numero di file e la quantità di dati di cui la query deve essere usata per la lettura e l'elaborazione 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 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Le visualizzazioni consentono anche di creare report e strumenti analitici come Power BI per usare i risultati di OPENROWSET.

Tabelle esterne

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

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

--Create external table:
CREATE EXTERNAL TABLE tbl_TaxiRides(
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
 tpepPickupDateTime DATETIME2,
 tpepDropoffDateTime DATETIME2,
 passengerCount INT,
 tripDistance FLOAT,
 puLocationId VARCHAR(8000),
 doLocationId VARCHAR(8000),
 startLon FLOAT,
 startLat FLOAT,
 endLon FLOAT,
 endLat FLOAT,
 rateCodeId SMALLINT,
 storeAndFwdFlag VARCHAR(8000),
 paymentType VARCHAR(8000),
 fareAmount FLOAT,
 extra FLOAT,
 mtaTax FLOAT,
 improvementSurcharge VARCHAR(8000),
 tipAmount FLOAT,
 tollsAmount FLOAT,
 totalAmount FLOAT
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);

Dopo aver creato la tabella esterna, è possibile eseguirvi le query come su qualsiasi altra tabella:

SELECT TOP 10 *
FROM tbl_TaxiRides;

Analogamente a OPENROWSET, le tabelle esterne consentono di eseguire query su più file e cartelle usando caratteri jolly. L'inferenza dello schema non è supportata con le 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.

Eseguire query su dati partizionati

I dati sono spesso organizzati in sottocartelle denominate anche partizioni. È possibile indicare alla query di leggere solo cartelle e file specifici. Questa operazione ridurrà il numero di file e la quantità di dati che la query deve leggere ed elaborare, garantendo prestazioni migliori. Questo tipo di ottimizzazione delle query è noto come eliminazione di partizioni o eliminazione di partizioni. È possibile eliminare le partizioni dall'esecuzione di query usando la funzione filepath() di metadati nella WHERE clausola della query.

L'esempio seguente legge i file di dati dei taxi di New York 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 'yellow/puYear=*/puMonth=*/*.parquet',
        DATA_SOURCE = 'NYCTaxiExternalDataSource',
        FORMAT = 'parquet'
    )
WITH (
    vendorID 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 di partizionarli al fine di migliorare le prestazioni.

Se si usano tabelle esterne, le funzioni filepath() e filename() sono supportate, ma non nella clausola WHERE.

Risoluzione dei Problemi

I problemi relativi all'esecuzione delle query sono in genere causati dal database SQL di Azure che non è in grado di accedere al percorso del file. I messaggi di errore correlati potrebbero indicare diritti di accesso insufficienti, percorso o percorso di file inesistente, file usato da un altro processo o che la directory non può essere elencata. Nella maggior parte dei casi indica che l'accesso ai file è bloccato dai criteri di controllo del traffico di rete o che non sono presenti diritti di accesso. Ecco cosa deve essere controllato:

  • Percorso errato o non corretto.
  • Validità della chiave di firma di accesso condiviso: potrebbe essere scaduta, contenente un errore di digitazione, a partire da un punto interrogativo.
  • Autorizzazioni della chiave SAS consentite: Lettura come minimo, ed Elenco se vengono usati caratteri jolly.
  • Traffico in ingresso bloccato nell'account di archiviazione. Consulta Gestione delle regole di rete virtuale per Azure Storage.
  • Diritti di accesso all'identità gestita: assicurarsi che all'identità gestita del database SQL di Azure vengano concessi diritti di accesso all'account di archiviazione.
  • Il livello di compatibilità del database deve essere 130 o superiore per il funzionamento delle query di virtualizzazione dei dati.

Limitazioni

  • Attualmente, le statistiche sulle tabelle esterne non sono supportate nel database SQL di Azure.
  • Attualmente, CREATE EXTERNAL TABLE AS SELECT non è disponibile nel database SQL di Azure.
  • La sicurezza a livello di riga è supportata con tabelle esterne.
  • Non è possibile definire una regola di maschera dati dinamica per una colonna in una tabella esterna.
  • L'identità gestita non supporta scenari tra tenant, se l'account di archiviazione di Azure si trova in un tenant diverso, la firma di accesso condiviso è il metodo supportato.

Problemi noti

  • Quando la parametrizzazione per Always Encrypted è abilitata in SQL Server Management Studio (SSMS), le query di virtualizzazione dei dati hanno esito negativo con messaggio di errore Incorrect syntax near 'PUSHDOWN'.

Contenuti correlati

  • Last updated on