Virtualisieren der CSV-Datei mit PolyBase

  • Artikel

Gilt für: SQL Server 2022 (16.x) und höhere Versionen

SQL Server 2022 (16.x) kann Daten direkt aus CSV-Dateien abfragen. Dieses Konzept, das gemeinhin als Datenvirtualisierung bezeichnet wird, ermöglicht es, dass die Daten am ursprünglichen Speicherort verbleiben und dennoch wie jede andere Tabelle von einer SQL Server-Instanz mit T-SQL-Befehlen abgefragt werden können. Dieses Feature verwendet PolyBase- Connectors und minimiert die Notwendigkeit, Daten über ETL-Prozesse zu kopieren.

Im folgenden Beispiel wird die CSV-Datei in Azure Blob Storage gespeichert und über OPENROWSET oder eine externe Tabelle aufgerufen.

Weitere Informationen zur Datenvirtualisierung finden Sie unter Einführung in die Datenvirtualisierung mit PolyBase.

Vorkonfiguration

1. Aktivieren von PolyBase in sp_configure

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

RECONFIGURE;

2. Erstellen Sie eine Benutzerdatenbank

In dieser Übung wird eine Beispieldatenbank mit Standardeinstellungen und -speicherorten erstellt. Sie verwenden diese leere Beispieldatenbank, um mit den Daten zu arbeiten und die bereichsbezogenen Anmeldeinformationen zu speichern. In diesem Beispiel wird eine neue leere Datenbank mit dem Namen CSV_Demo verwendet.

CREATE DATABASE [CSV_Demo];

3. Erstellen eines Hauptschlüssels und einer datenbankabhängigen Berechtigung

Der Hauptschlüssel der Datenbank in der Benutzerdatenbank wird verwendet, um das datenbankbezogene Berechtigungsgeheimnis blob_storage zu verschlüsseln.

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

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

4. Erstellen einer externen Datenquelle

Für die externe Datenquelle werden datenbankbezogene Anmeldeinformationen verwendet. In diesem Beispiel befindet sich die CSV-Datei in Azure Blob Storage. Verwenden Sie daher das Präfix abs und die Identitätsmethode SHARED ACCESS SIGNATURE. Weitere Informationen zu den Connectors und Präfixen, einschließlich neuer Einstellungen für SQL Server 2022 (16.x), finden Sie unter EXTERNE DATENQUELLE ERSTELLEN.

CREATE EXTERNAL DATA SOURCE Blob_CSV
WITH
(
 LOCATION = 'abs://<container>@<storage_account>.blob.core.windows.net'
,CREDENTIAL = blob_storage
);

Wenn Ihr Speicherkonto beispielsweise s3sampledata und der Container import heißt, lautet der Code:

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

Verwenden von OPENROWSET für den Zugriff auf die Daten

In diesem Beispiel trägt die Datei den Namen call_center.csv, und die Daten beginnen in der zweiten Zeile.

Da die externe Datenquelle Blob_CSV einer Containerebene zugeordnet ist. Das call_center.csv befindet sich in einem Unterordner namens 2022 im Stammverzeichnis des Containers. Um eine Datei in einer Ordnerstruktur abzufragen, geben Sie eine Ordnerzuordnung relativ zum Parameter LOCATION der externen Datenquelle an.

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

Abfragen von Daten mit einer externen Tabelle

CREATE EXTERNAL TABLE kann auch verwendet werden, um die CSV-Daten in SQL Server zu virtualisieren. Die Spalten müssen definiert und stark typisiert werden. Externe Tabellen sind zwar aufwändiger zu erstellen, bieten aber auch zusätzliche Vorteile gegenüber der Abfrage einer externen Datenquelle mit OPENROWSET. Sie können Folgendes ausführen:

  • Stärken der Definition der Datentypisierung für eine bestimmte Spalte
  • Definieren der NULL-Zulässigkeit
  • Definieren der SORTIERUNG
  • Erstellen von Statistiken für eine Spalte zur Optimierung der Qualität des Abfrageplans
  • Erstellen Sie ein detaillierteres Modell innerhalb von SQL Server für den Datenzugriff, um Ihr Sicherheitsmodell zu verbessern.

Weitere Informationen finden Sie unter CREATE EXTERNAL TABLE.

Im folgenden Beispiel wird dieselbe Datenquelle verwendet.

1. Erstellen eines externen Dateiformats

Um die Formatierung der Datei zu definieren, ist ein externes Dateiformat erforderlich. Externe Dateiformate werden auch aufgrund der Wiederverwendbarkeit empfohlen.

Im folgenden Beispiel beginnen die Daten in der zweiten Zeile.

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

2. Erstellen einer externen Tabelle

LOCATION ist der Ordner- und Dateipfad der call_center.csv-Datei relativ zum Pfad des Speicherorts in der externen Datenquelle, die durch DATA_SOURCE definiert ist. In diesem Fall liegt die Datei in einem Unterordner mit dem Namen 2022. Verwenden Sie FILE_FORMAT, um den Pfad zum externen csv_ff-Dateiformat im SQL Server anzugeben.

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

Zugehöriger Inhalt