Virtualizzare un file Parquet in una risorsa di archiviazione di oggetti compatibile con S3 con PolyBase

  • Articolo

Si applica a: SQL Server 2022 (16.x)

SQL Server 2022 (16.x) può virtualizzare i dati dai file Parquet. Questo processo consente di mantenere i dati nel percorso originale, ma di poter eseguire query da un'istanza di SQL Server con comandi T-SQL come per qualsiasi altra tabella. Questa funzionalità utilizza connettori PolyBase e riduce al minimo la necessità di processi di estrazione, trasformazione e caricamento (ETL).

Nell'esempio seguente, verrà virtualizzato un file Parquet archiviato nelle risorse di archiviazione di oggetti compatibile con S3.

Per altre informazioni sulla virtualizzazione dei dati, vedere Introduzione alla virtualizzazione dei dati con PolyBase.

Prerequisiti

Per usare le funzionalità di integrazione delle risorse di archiviazione di oggetti compatibili con S3, sono necessari gli strumenti e le risorse seguenti:

  • Installare la funzionalità PolyBase per SQL Server.
  • Installare SQL Server Management Studio (SSMS) o Azure Data Studio.
  • Archiviazione compatibile con S3.
  • Bucket S3 creato. Non è possibile creare o configurare bucket da SQL Server.
  • Un utente (Access Key ID) e il segreto (Secret Key ID) e quell'utente sono noti. È necessario eseguire l'autenticazione a fronte dell'endpoint di archiviazione oggetti S3.
  • Autorizzazione ListBucket per l'utente S3.
  • Autorizzazione ReadOnly per l'utente S3.
  • TLS deve essere stato configurato. Si presuppone che tutte le connessioni vengano trasmesse in modo sicuro tramite HTTPS non HTTP. L'endpoint verrà convalidato da un certificato installato nell'host del sistema operativo di SQL Server.

Autorizzazione

Per consentire all'utente proxy di leggere il contenuto di un bucket S3, l'utente deve poter eseguire le azioni seguenti a fonte dell'endpoint S3:

  • ListBucket;
  • ReadOnly;

Preconfigurazione

  1. Abilitare PolyBase in sp_configure:
exec sp_configure @configname = 'polybase enabled', @configvalue = 1
;
RECONFIGURE
;
exec sp_configure @configname = 'polybase enabled'
;
  1. Prima di creare credenziali con ambito database, il database utente deve avere una chiave master per proteggerle. Per altre informazioni, vedere CREATE MASTER KEY.

Creare una credenziale con ambito di database

Lo script di esempio seguente crea una credenziale s3-dc con ambito database nel database utente di origine in SQL Server. Per altre informazioni sulle credenziali, vedere CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).

IF NOT EXISTS(SELECT * FROM sys.credentials WHERE name = 's3_dc')
BEGIN
 CREATE DATABASE SCOPED CREDENTIAL s3_dc
 WITH IDENTITY = 'S3 Access Key',
 SECRET = '<AccessKeyID>:<SecretKeyID>' ;
END

Verificare le nuove credenziali con ambito database con sys.database_scoped_credentials (Transact-SQL):

SELECT * FROM sys.database_scoped_credentials;

Creare un'origine dati esterna

Lo script di esempio seguente crea un'origine dati esterna s3_ds nel database utente di origine in SQL Server. L'origine dati esterna fa riferimento alle credenziali con ambito database di s3_dc. Per altre informazioni, vedere CREATE EXTERNAL DATA SOURCE.

CREATE EXTERNAL DATA SOURCE s3_ds
WITH
(   LOCATION = 's3://<ip_address>:<port>/'
,   CREDENTIAL = s3_dc
);
GO

Verificare la nuova origine dati esterna con sys.external_data_sources.

SELECT * FROM sys.external_data_sources;

URL ospitati virtuali

Alcuni sistemi di archiviazione compatibili con S3, ad esempio Amazon Web Services, usano gli URL di stile virtual_hosted per implementare la struttura di cartelle nel bucket S3. Aggiungere CONNECTION_OPTIONS seguente per consentire la creazione di tabelle esterne che puntano ai percorsi delle cartelle nel bucket S3, ad esempio CONNECTION_OPTIONS = '{"s3":{"url_style":"virtual_hosted"}}'.

Senza questa impostazione CONNECTION_OPTIONS, quando si eseguono query su tabelle esterne che puntano a una cartella, è possibile osservare l'errore seguente:

Msg 13807, Level 16, State 1, Line 23  
Content of directory on path '/<folder_name>/' cannot be listed.

Scegliere SELECT da un file parquet con OPENROWSET

L'esempio seguente mostra quindi l'uso di T-SQL per eseguire query su un file Parquet archiviato in una risorsa di archiviazione di oggetti compatibile con S3 tramite query OPENROWSET. Per altre informazioni, vedere OPENROWSET (Transact-SQL).

Poiché si tratta di un file parquet, vengono eseguite automaticamente due cose importanti:

  1. SQL Server legge lo schema dal file stesso, pertanto non è necessario definire la tabella, le colonne o i tipi di dati.
  2. Non è necessario dichiarare il tipo di compressione per il file da leggere.
SELECT  * 
FROM    OPENROWSET
        (   BULK '/<bucket>/<parquet_folder>'
        ,   FORMAT       = 'PARQUET'
        ,   DATA_SOURCE  = 's3_ds'
        ) AS [cc];

Eseguire query sull'archiviazione di oggetti compatibile con S3 tramite una tabella esterna

Nell'esempio seguente viene illustrato l'uso di T-SQL per eseguire query su un file Parquet archiviato nell'archivio di oggetti compatibile con S3 tramite query su tabella esterna. L'esempio usa un percorso relativo all'interno dell'origine dati esterna.

CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE Region (
r_regionkey BIGINT,
r_name CHAR(25),
r_comment VARCHAR(152) )
WITH (LOCATION = '/region/', DATA_SOURCE = 's3_ds', 
FILE_FORMAT = ParquetFileFormat);
GO

SELECT * FROM [Region];

Per altre informazioni, vedi:

Limiti

  1. Le query di SQL Server in una tabella esterna supportata dall'archiviazione compatibile con S3 sono limitate a 1.000 oggetti per prefisso. Ciò avviene perché l'elenco di oggetti compatibili con S3 è limitato a 1.000 chiavi oggetto per prefisso.
  2. Per l'archiviazione di oggetti compatibile con S3, ai clienti non è consentito creare l'ID chiave di accesso contenente un carattere :.
  3. La lunghezza totale dell'URL è limitata a 259 caratteri. Ciò significa che s3://<hostname>/<objectkey> non può superare i 259 caratteri. s3:// conta a fronte di questo limite, quindi la lunghezza del percorso non può superare 259-5 = 254 caratteri.
  4. Il nome delle credenziali SQL è limitato a 128 caratteri in formato UTF-16.
  5. Il nome delle credenziali creato deve contenere il nome del bucket, a meno che questa credenziale non sia per una nuova origine dati esterna.
  6. L'ID chiave di accesso e l'ID chiave privata possono contenere solo valori alfanumerici.

Passaggi successivi