Vorherige

Nächste

Erstellen externer Datenbankobjekte

Abgeschlossen

Sie können die OPENROWSET-Funktion in SQL-Abfragen verwenden, die in der Standardmasterdatenbank des integrierten serverlosen SQL-Pools ausgeführt werden, um Daten im Data Lake zu untersuchen. Manchmal möchten Sie jedoch vielleicht eine benutzerdefinierte Datenbank erstellen, die einige Objekte enthält, die das Arbeiten mit externen Daten im Data Lake erleichtern, die Sie häufig abfragen müssen.

Erstellen einer Datenbank

Sie können eine Datenbank in einem serverlosen SQL-Pool auf die gleiche Weise wie in einer SQL Server-Instanz erstellen. Sie können die grafische Benutzeroberfläche in Synapse Studio oder eine CREATE DATABASE-Anweisung verwenden. Eine Überlegung besteht darin, die Sortierung Ihrer Datenbank so festzulegen, dass sie die Konvertierung von Textdaten in Dateien in geeignete Transact-SQL-Datentypen unterstützt.

Der folgende Beispielcode erstellt eine Datenbank namens salesDB mit einer Sortierung, die es einfacher macht, UTF-8-codierte Textdaten in VARCHAR-Spalten zu importieren.

CREATE DATABASE SalesDB
    COLLATE Latin1_General_100_BIN2_UTF8

Erstellen einer externen Datenquelle

Sie können die OPENROWSET-Funktion mit einem BULK-Pfad verwenden, um Dateidaten aus Ihrer eigenen Datenbank abzufragen, genau wie in der Masterdatenbank. Wenn Sie jedoch planen, Daten häufig an demselben Speicherort abzufragen, ist es effizienter, eine externe Datenquelle zu definieren, die auf diesen Speicherort verweist. Der folgende Code erstellt beispielsweise eine Datenquelle namens files für den hypothetischen Ordner https://mydatalake.blob.core.windows.net/data/files/:

CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/'
)

Ein Vorteil einer externen Datenquelle besteht darin, dass Sie eine OPENROWSET-Abfrage vereinfachen können, um die Kombination aus Datenquelle und relativem Pfad zu den Ordnern oder Dateien zu verwenden, die Sie abfragen möchten:

SELECT *
FROM
    OPENROWSET(
        BULK 'orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'csv',
        PARSER_VERSION = '2.0'
    ) AS orders

In diesem Beispiel wird der Parameter BULK verwendet, um den relativen Pfad für alle CSV-Dateien im Ordner orders anzugeben, bei dem es sich um einen Unterordner des Ordners files handelt, auf den von der Datenquelle verwiesen wird.

Ein weiterer Vorteil der Verwendung einer Datenquelle besteht darin, dass Sie Anmeldeinformationen für die Datenquelle zuweisen können, die beim Zugriff auf den zugrunde liegenden Speicher verwendet werden sollen, sodass Sie über SQL Zugriff auf Daten gewähren können, ohne Benutzern den direkten Zugriff auf die Daten im Speicherkonto zu ermöglichen. Der folgende Code erstellt z. B. Anmeldeinformationen, die ein SAS-Token (Shared Access Signature) verwenden, um sich bei dem zugrunde liegenden Azure-Speicherkonto zu authentifizieren, das den Data Lake hostet.

CREATE DATABASE SCOPED CREDENTIAL sqlcred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';
GO

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = sqlcred
);
GO

Tipp

Zusätzlich zur SAS-Authentifizierung können Sie Anmeldeinformationen definieren, die die verwaltete Identität (die Microsoft Entra-Identität, die von Ihrem Azure Synapse-Arbeitsbereich verwendet wird), einen bestimmten Microsoft Entra-Prinzipal oder die Passthrough-Authentifizierung basierend auf der Identität des Benutzers verwenden, der die Abfrage ausführt (dies ist der standardmäßige Authentifizierungstyp). Weitere Informationen zum Verwenden von Anmeldeinformationen in einem serverlosen SQL-Pool finden Sie im Artikel Speicherkontozugriff für serverlose SQL-Pools in Azure Synapse Analytics in der Dokumentation zu Azure Synapse Analytics.

Erstellen eines externen Dateiformats

Eine externe Datenquelle vereinfacht zwar den Code, der für den Zugriff auf Dateien mit der OPENROWSET-Funktion erforderlich ist, aber Sie müssen immer noch Formatangaben für die Datei vornehmen, auf die zugegriffen werden soll. Dazu können mehrere Einstellungen für durch Trennzeichen getrennte Textdateien gehören. Sie können diese Einstellungen wie folgt in einem externen Dateiformat kapseln:

CREATE EXTERNAL FILE FORMAT CsvFormat
    WITH (
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS(
            FIELD_TERMINATOR = ',',
            STRING_DELIMITER = '"'
        )
    );
GO

Nachdem Sie Dateiformate für die spezifischen Datendateien erstellt haben, mit denen Sie arbeiten müssen, können Sie das Dateiformat verwenden, um externe Tabellen zu erstellen, wie im Folgenden erläutert wird.

Erstellen einer externen Tabelle

Wenn Sie viele Analysen oder Berichte aus Dateien im Data Lake durchführen müssen, kann die Verwendung der OPENROWSET-Funktion zu komplexem Code führen, der Datenquellen und Dateipfade enthält. Um den Zugriff auf die Daten zu vereinfachen, können Sie die Dateien in einer externen Tabelle kapseln, die Benutzer und Berichtsanwendungen mit einer standardmäßigen SQL SELECT-Anweisung wie jede andere Datenbanktabelle abfragen können. Verwenden Sie zum Erstellen einer externen Tabelle die CREATE EXTERNAL TABLE-Anweisung, wobei Sie das Spaltenschema wie bei einer Standardtabelle angeben und eine WITH-Klausel einfügen, die die externe Datenquelle, den relativen Pfad und das externe Dateiformat für Ihre Daten angibt.

CREATE EXTERNAL TABLE dbo.products
(
    product_id INT,
    product_name VARCHAR(20),
    list_price DECIMAL(5,2)
)
WITH
(
    DATA_SOURCE = files,
    LOCATION = 'products/*.csv',
    FILE_FORMAT = CsvFormat
);
GO

-- query the table
SELECT * FROM dbo.products;

Durch das Erstellen einer Datenbank, die die in dieser Lerneinheit behandelten externen Objekte enthält, können Sie eine relationale Datenbankebene über Dateien in einem Data Lake bereitstellen, die vielen Datenanalysten und Berichtstools den Zugriff auf die Daten mithilfe von SQL-Standardabfragesemantik erleichtert.

Weiter