Introduzione con PolyBase in SQL Server 2022

  • Articolo

Si applica a: SQL Server 2016 (13.x) - Windows e versioni successive SQL Server 2017 (14.x) - Linux e versioni successive

Questo articolo guida in un’esercitazione che illustra come lavorare con più cartelle e file con PolyBase in SQL Server 2022 (16.x). Questo insieme di query di esercitazione illustra varie funzionalità di PolyBase.

La virtualizzazione dei dati con PolyBase in SQL Server consente di sfruttare le funzioni dei file di metadati per eseguire query su più cartelle, file o eseguire l'eliminazione di cartelle. La combinazione di individuazione dello schema con l'eliminazione di cartelle e file è una potente funzionalità che consente a SQL di recuperare solo i dati necessari di qualsiasi soluzione di account di archiviazione Azure o di risorse di archiviazione di oggetti compatibili con S3.

Prerequisiti

Prima di usare PolyBase in questa esercitazione, è necessario:

  1. Installare PolyBase su Windows o installare PolyBase su Linux.
  2. Abilitare PolyBase in sp_configure, se necessario.
  3. Consentire l'accesso alla rete esterna per accedere all'archiviazione BLOB di Azure disponibile pubblicamente all'indirizzo pandemicdatalake.blob.core.windows.net e azureopendatastorage.blob.core.windows.net.

Insieme di dati campione

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, come 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:

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 WHERE, GROUP BY e altre clausole T-SQL basate sul set di risultati della prima query.

Se la prima query non riesce nell'istanza di SQL Server, è probabile che venga impedito l'accesso di rete all'account di archiviazione di Azure pubblico. 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, valutare di passare a set di dati non pubblici che richiedono l'inserimento di credenziali, di concedere diritti di accesso e di configurare le regole del firewall. In molti scenari reali si opera principalmente con set di dati privati.

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 la posizione dei file 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' 
);

Nota

Se viene visualizzato un messaggio di errore 46530, External data sources are not supported with type GENERIC, controllare l'opzione di configurazione PolyBase Enabled nell'istanza di SQL Server. Il valore dovrebbe essere 1.

Eseguire le istruzioni seguenti per abilitare PolyBase nell'istanza di SQL Server:

EXEC sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;

Quando si accede ad account di archiviazione non pubblici, insieme al percorso, è anche necessario fare riferimento a credenziali 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://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 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.

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

Per prima cosa, 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:

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 tratti di varchar (8000).

Usare la stored procedure sys.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 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. Nell'esempio seguente, eseguire una query su tutti i file e il percorso del file di progetto e le informazioni sul nome file per ogni riga:

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

--List all paths: 
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder] 
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, la funzione filepath() restituisce una parte del percorso che corrisponde al carattere jolly nella posizione specificata nel parametro. Ad esempio, il valore del primo parametro restituisce la parte del percorso che corrisponde al primo carattere jolly.

La funzione filepath() può anche essere usata 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 sopra a OPENROWSET

È possibile creare visualizzazioni per eseguire il wrapping delle query OPENROWSET in modo da poter riutilizzare facilmente la query sottostante. Le visualizzazioni consentono anche di creare report e strumenti analitici come Power BI per usare i risultati di OPENROWSET.

Considerare ad esempio la visualizzazione seguente in base a un comando OPENROWSET:

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 sopra alla visualizzazione deve eseguire 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;

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 
) 
GO 
 
--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 
);

Una volta creata la tabella esterna, è possibile eseguirvi le query come su qualsiasi altra tabella:

SELECT TOP 10 * 
FROM tbl_TaxiRides;

Proprio come 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.

Origini dati esterne

Per altre esercitazioni sulla creazione di origini dati esterne e tabelle esterne in un'ampia gamma di origini dati, vedere le Informazioni di riferimento su Transact-SQL per PolyBase.

Per altre esercitazioni su varie origini dati esterne, vedere:

Contenuto correlato