Virtualizzare la tabella delta con PolyBase

  • Articolo

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

SQL Server 2022 (16.x) può eseguire query sui dati direttamente da una cartella della tabella delta. Questo processo, comunemente denominata virtualizzazione dei dati, 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à usa connettori PolyBase e riduce al minimo la necessità di copiare i dati tramite i processi ETL.

Nell'esempio seguente, la cartella della tabella delta viene archiviata in Archiviazione BLOB di Azure e vi si accede tramite OPENROWSET o una tabella esterna.

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

Preconfigurazione

1. Abilitare PolyBase in sp_configure

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;

RECONFIGURE;

2. Creare un database utente

Questo esercizio crea un database di esempio con impostazioni e posizione predefinite. Questo database di esempio vuoto viene usato per lavorare con i dati e archiviare le credenziali con ambito. In questo esempio, viene usato un nuovo database vuoto denominato Delta_demo.

CREATE DATABASE [Delta_demo];

3 Creare la chiave master e le credenziali con ambito database

La chiave master del database nel database utente è obbligatoria per crittografare il segreto delle credenziali con ambito database, delta_storage_dsc. Per questo esempio, la tabella delta si trova in Azure Data Lake Archiviazione Gen2.

USE [Delta_demo];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

4. Creare un'origine dati esterna

La credenziale con ambito database viene usata per l'origine dati esterna. In questo esempio la tabella delta si trova in Azure Data Lake Storage Gen2, quindi usare il prefisso adls e il metodo di identità SHARED ACCESS SIGNATURE. Per altre informazioni sui connettori e sui prefissi, incluse le nuove impostazioni per SQL Server 2022 (16.x), vedere CREATE EXTERNAL DATA SOURCE.

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

Se ad esempio il nome dell'account di archiviazione è delta_lake_sample e quello del contenitore è sink, il codice sarà:

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://sink@delta_lake_sample.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

Usare OPENROWSET per accedere ai dati

In questo esempio la cartella Tabella dati è denominata Contoso.

Poiché l'origine dati esterna Delta_ED è mappata a un livello di contenitore. La cartella della tabella delta Contoso si trova in una root. Per eseguire query su un file in una struttura di cartelle, specificare un mapping di cartelle relativo al parametro LOCATION dell'origine dati esterna.

SELECT * FROM OPENROWSET
(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS [result];

Eseguire query sui dati con una tabella esterna

È anche possibile usare CREATE EXTERNAL TABLE per virtualizzare i dati della tabella delta in SQL Server. Le colonne devono essere definite e fortemente tipizzate. Anche se le tabelle esterne richiedono più impegno per la creazione, offrono anche vantaggi aggiuntivi rispetto all'esecuzione di query su un'origine dati esterna con OPENROWSET. È possibile:

  • Rafforzare la definizione della digitazione dei dati per una determinata colonna
  • Definire il supporto dei valori Null
  • Definire il CONFRONTO
  • Creare statistiche per una colonna per ottimizzare la qualità del piano di query
  • Creare un modello più granulare in SQL Server per l'accesso ai dati per migliorare il modello di sicurezza

Per altre informazioni, vedere CREATE EXTERNAL TABLE.

Per l'esempio seguente, viene usata la stessa origine dati.

1. Creare un formato di file esterno

Per definire la formattazione del file, è necessario un formato di file esterno. I formati di file esterni sono consigliati anche a causa della riutilizzabilità. Per altre informazioni, vedere CREATE EXTERNAL FILE FORMAT.

CREATE EXTERNAL FILE FORMAT DeltaTableFormat WITH(FORMAT_TYPE = DELTA);

2. Creare una tabella esterna

I file di tabella delta si trovano in /delta/Delta_yob/ e l'origine dati esterna per questo esempio è l'archiviazione oggetti compatibile con S3, configurata in precedenza nell'origine dati s3_eds. PolyBase può usare come LOCATION la cartella della tabella delta o il file assoluto stesso, che si trova in delta/Delta_yob/_delta_log/00000000000000000000.json.

-- Create External Table using delta
CREATE EXTERNAL TABLE extCall_Center_delta (
    id INT,
    name VARCHAR(200),
    dob DATE
)
WITH (
        LOCATION = '/delta/Delta_yob/',
        FILE_FORMAT = DeltaTableFormat,
        DATA_SOURCE = s3_eds
);
GO

Limiti

Se si crea una tabella esterna che punta a una tabella delta partizionata, la colonna utilizzata per il partizionamento restituisce NULL quando si esegue una query sulla tabella esterna. Tuttavia, se si usa una query OPENROWSET, viene restituito correttamente il valore della colonna. Per aggirare questa limitazione, creare una vista sulla query OPENROWSET e quindi eseguire una query sulla vista affinché i valori delle colonne partizionate vengano restituiti correttamente.

quando si esegue una query su una tabella Delta esterna, è possibile che si verifichino gli errori seguenti:

Msg 2571, Level 14, State 3, Line 1
User '<user>' does not have permission to run DBCC TRACEON.
Msg 16513, Level 16, State 0, Line 1
Error reading external metadata.

questo problema può verificarsi perché è presente un hint per la query QUERYTRACEON che si può aggiungere alla query sui metadati del file Delta che richiede il ruolo del server sysadmin per l'esecuzione. In tal caso, è possibile risolvere il problema abilitando globalmente il flag di traccia 14073 e impedire così l'aggiunta dell'hint per la query.

Contenuto correlato