Erstellen externer Datenbankobjekte

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 Datensee zu untersuchen. Manchmal möchten Sie jedoch möglicherweise eine benutzerdefinierte Datenbank erstellen, die einige Objekte enthält, die das Arbeiten mit externen Daten im Datensee vereinfachen, die Sie häufig abfragen müssen.

Erstellen einer Datenbank

Sie können eine Datenbank genauso wie in einer SQL Server-Instanz in einem serverlosen SQL-Pool erstellen. Sie können die grafische Benutzeroberfläche in Synapse Studio oder eine CREATE DATABASE-Anweisung verwenden. Eine Berücksichtigung 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 das Importieren von UTF-8-codierten Textdaten in VARCHAR-Spalten erleichtert.

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 wie in der Masterdatenbank abzufragen. Wenn Sie daten jedoch häufig an demselben Speicherort abfragen möchten, ist es effizienter, eine externe Datenquelle zu definieren, die auf diesen Speicherort verweist. Der folgende Code erstellt z. B. eine Datenquelle namens Dateien für den hypothetischen https://mydatalake.blob.core.windows.net/data/files/ Ordner:

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 der Datenquelle und den relativen 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 BULK-Parameter verwendet, um den relativen Pfad für alle .csv Dateien im Ordner "Orders " anzugeben, bei dem es sich um einen Unterordner des Ordners " Dateien " handelt, auf den von der Datenquelle verwiesen wird.

Ein weiterer Vorteil der Verwendung einer Datenquelle besteht darin, dass Sie eine Anmeldeinformation für die Datenquelle zuweisen können, die beim Zugriff auf den zugrunde liegenden Speicher verwendet werden soll, sodass Sie Zugriff auf Daten über SQL ermöglichen können, ohne benutzern den Zugriff auf die Daten direkt im Speicherkonto zu ermöglichen. Der folgende Code erstellt z. B. eine Anmeldeinformation, die eine freigegebene Zugriffssignatur (SAS) verwendet, um sich beim zugrunde liegenden Azure-Speicherkonto zu authentifizieren, das den Data Lake hosten wird.

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

Erstellen eines externen Dateiformats

Während eine externe Datenquelle den Code vereinfacht, der für den Zugriff auf Dateien mit der OPENROWSET-Funktion erforderlich ist, müssen Sie dennoch Formatdetails für die Datei bereitstellen, auf die zugegriffen wird. die mehrere Einstellungen für durch Trennzeichen getrennte Textdateien enthalten können. 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 der Sie arbeiten müssen, können Sie das Dateiformat verwenden, um externe Tabellen zu erstellen, wie im nächsten Schritt erläutert.

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. welche Benutzer und Berichterstellungsanwendungen eine SQL SELECT-Standard-Anweisung wie jede andere Datenbanktabelle abfragen können. Verwenden Sie zum Erstellen einer externen Tabelle die CREATE EXTERNAL TABLE-Anweisung, geben Sie das Spaltenschema für eine Standardtabelle an, und fügen Sie eine WITH-Klausel hinzu, 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;

Indem Sie eine Datenbank erstellen, die die in dieser Einheit erläuterten externen Objekte enthält, können Sie eine relationale Datenbankschicht über Dateien in einem Data Lake bereitstellen, wodurch viele Datenanalysten und Berichterstellungstools einfacher auf die Daten zugreifen können, indem Sie die standardmäßige SQL-Abfragesemantik verwenden.