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 allgemein als Datenvirtualisierung bezeichnet wird, ermöglicht es den Daten, an seinem ursprünglichen Speicherort zu bleiben, kann aber von einer SQL Server-Instanz mit T-SQL-Befehlen wie jeder anderen Tabelle abgefragt werden. Dieses Feature verwendet PolyBase-Connectors und minimiert die Notwendigkeit des Kopierens von Daten über ETL-Prozesse.

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

Weitere Informationen zur Datenvirtualisierung, 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 einer 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 datenbankbezogener Anmeldeinformationen

Der Datenbankmasterschlüssel in der Benutzerdatenbank ist erforderlich, um den geheimen Anmeldeinformationsbereich der Datenbank zu verschlüsseln. 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. Erstellen einer externen Datenquelle

Für die externe Datenquelle wird eine Datenbank mit Anmeldeinformationen verwendet. In diesem Beispiel befindet sich die CSV-Datei in Azure Blob Storage. Verwenden Sie daher Präfix abs und 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 CREATE EXTERNAL DATA SOURCE.

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 wird die Datei benannt call_center.csv, und die Daten beginnen in der zweiten Zeile.

Da die externe Datenquelle Blob_CSV einer Containerebene zugeordnet ist. Die call_center.csv Datei befindet sich in einem Unterordner, der im Stammverzeichnis des Containers aufgerufen 2022 wird. Um eine Datei in einer Ordnerstruktur abzufragen, stellen Sie eine Ordnerzuordnung relativ zum LOCATION-Parameter der externen Datenquelle bereit.

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 zum Virtualisieren der CSV-Daten in SQL Server verwendet werden. Die Spalten müssen definiert und stark typiert werden. Während externe Tabellen mehr Aufwand zum Erstellen haben, bieten sie auch zusätzliche Vorteile beim Abfragen einer externen Datenquelle mit OPENROWSET. Ihre Möglichkeiten:

  • Stärken der Definition der Datentypisierung für eine bestimmte Spalte
  • Definieren der Nullierbarkeit
  • DEFINIEREN DER SORTIERUNG
  • Erstellen von Statistiken für eine Spalte zur Optimierung der Qualität des Abfrageplans
  • Erstellen sie ein differenzierteres Modell in SQL Server, um den Datenzugriff zu verbessern, 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, definiert durch DATA_SOURCE. In diesem Fall liegt die Datei in einem Unterordner mit dem Namen 2022. Verwenden Sie FILE_FORMAT, um den Pfad zum csv_ff externen Dateiformat in 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