Virtualizzare il file CSV 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 dai file CSV. 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, il file CSV viene archiviato 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 CSV_Demo.

CREATE DATABASE [CSV_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, blob_storage.

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

CREATE DATABASE SCOPED CREDENTIAL blob_storage
WITH IDENTITY = '<user_name>', Secret = '<password>';

4. Creare un'origine dati esterna

La credenziale con ambito database viene usata per l'origine dati esterna. In questo esempio, il file CSV si trova in Archiviazione BLOB di Azure, quindi usare il prefisso abs 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 Blob_CSV
WITH
(
 LOCATION = 'abs://<container>@<storage_account>.blob.core.windows.net'
,CREDENTIAL = blob_storage
);

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

CREATE EXTERNAL DATA SOURCE Blob_CSV
WITH
(
 LOCATION = 'abs://import@s3sampledata.blob.core.windows.net'
,CREDENTIAL = blob_storage
)

Usare OPENROWSET per accedere ai dati

In questo esempio, il file è denominato call_center.csv e i dati iniziano nella seconda riga.

Poiché l'origine dati esterna Blob_CSV è mappata a un livello di contenitore. call_center.csv si trova in una sottocartella denominata 2022 nella root del contenitore. 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 '/2022/call_center.csv',
    FORMAT = 'CSV',
    DATA_SOURCE = 'Blob_CSV',
    FIRSTROW = 2
)
WITH (
    cc_call_center_sk INT,
    cc_call_center_id CHAR(16),
    cc_rec_start_date DATE,
    cc_rec_end_date DATE,
    cc_closed_date_sk INT,
    cc_open_date_sk INT,
    cc_name VARCHAR(50),
    cc_class VARCHAR(50),
    cc_employees INT,
    cc_sq_ft INT,
    cc_hours CHAR(20),
    cc_manager VARCHAR(40),
    cc_mkt_id INT,
    cc_mkt_class CHAR(50),
    cc_mkt_desc VARCHAR(100),
    cc_market_manager VARCHAR(40),
    cc_division INT,
    cc_division_name VARCHAR(50),
    cc_company INT,
    cc_company_name CHAR(50),
    cc_street_number CHAR(10),
    cc_street_name VARCHAR(60),
    cc_street_type CHAR(15),
    cc_suite_number CHAR(10),
    cc_city VARCHAR(60),
    cc_county VARCHAR(30),
    cc_state CHAR(2),
    cc_zip CHAR(10),
    cc_country VARCHAR(20),
    cc_gmt_offset DECIMAL(5, 2),
    cc_tax_percentage DECIMAL(5, 2)
) AS [cc];

Eseguire query sui dati con una tabella esterna

È anche possibile usare CREATE EXTERNAL TABLE per virtualizzare i dati CSV 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à.

Nell'esempio seguente, i dati iniziano nella seconda riga.

CREATE EXTERNAL FILE FORMAT csv_ff
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS(
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2)
);

2. Creare una tabella esterna

LOCATION è la cartella e il percorso del file call_center.csv rispetto al percorso del posizione nell'origine dati esterna, definita da DATA_SOURCE. In questo caso, il file si trova in una sottocartella denominata 2022. Usare FILE_FORMAT per specificare il percorso del formato di file esterno csv_ff in SQL Server.

CREATE EXTERNAL TABLE extCall_Center_csv (
    cc_call_center_sk INT NOT NULL,
    cc_call_center_id CHAR(16) NOT NULL,
    cc_rec_start_date DATE,
    cc_rec_end_date DATE,
    cc_closed_date_sk INT,
    cc_open_date_sk INT,
    cc_name VARCHAR(50),
    cc_class VARCHAR(50),
    cc_employees INT,
    cc_sq_ft INT,
    cc_hours CHAR(20),
    cc_manager VARCHAR(40),
    cc_mkt_id INT,
    cc_mkt_class CHAR(50),
    cc_mkt_desc VARCHAR(100),
    cc_market_manager VARCHAR(MAX),
    cc_division VARCHAR(50),
    cc_division_name VARCHAR(50),
    cc_company VARCHAR(60),
    cc_company_name CHAR(50),
    cc_street_number CHAR(10),
    cc_street_name VARCHAR(60),
    cc_street_type CHAR(15),
    cc_suite_number CHAR(10),
    cc_city VARCHAR(60),
    cc_county VARCHAR(30),
    cc_state CHAR(20),
    cc_zip CHAR(20),
    cc_country VARCHAR(MAX),
    cc_gmt_offset DECIMAL(5, 2),
    cc_tax_percentage DECIMAL(5, 2)
    )
WITH (
    LOCATION = '/2022/call_center.csv',
    DATA_SOURCE = Blob_CSV,
    FILE_FORMAT = csv_ff
);
GO

Contenuto correlato