Tutorial: Erstellen eines logischen Data Warehouse mit serverlosem SQL-Pool

  • Artikel

In diesem Tutorial erfahren Sie, wie Sie ein logisches Data Warehouse (LDW) erstellen, das auf Azure Storage und Azure Cosmos DB basiert.

LDW ist eine relationale Ebene, der Azure-Datenquellen wie Azure Data Lake Storage (ADLS), Azure Cosmos DB-Analysespeicher oder Azure Blob Storage zugrunde liegen.

Erstellen einer LDW-Datenbank

Sie müssen eine benutzerdefinierte Datenbank erstellen, in der Sie Ihre externen Tabellen und Sichten speichern können, von denen auf externe Datenquellen verwiesen wird.

CREATE DATABASE Ldw
      COLLATE Latin1_General_100_BIN2_UTF8;

Diese Sortierung bietet die optimale Leistung beim Lesen von Parquet- und Azure Cosmos DB-Daten. Wenn Sie die Datenbanksortierung nicht angeben möchten, muss die Sortierung in der Spaltendefinition angegeben werden.

Konfigurieren von Datenquellen und -formaten

Als Erstes müssen Sie die Datenquelle konfigurieren und das Dateiformat remote gespeicherter Daten angeben.

Erstellen der Datenquelle

Datenquellen stellen Verbindungszeichenfolgeninformationen dar, die beschreiben, wo Ihre Daten platziert werden und wie Sie sich bei Ihrer Datenquelle authentifizieren.

Das folgende Beispiel zeigt eine Datenquellendefinition mit Verweis auf das öffentliche ECDC-COVID 19-Dataset aus Azure Open Datasets:

CREATE EXTERNAL DATA SOURCE ecdc_cases WITH (
    LOCATION = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/'
);

Die aufrufende Funktion kann ohne Anmeldeinformationen auf die Datenquelle zugreifen, wenn ein*e Besitzer*in der Datenquelle anonymen Zugriff zugelassen hat oder der Microsoft Entra-Identität der aufrufenden Funktion explizit Zugriff gewährt.

Sie können explizit benutzerdefinierte Anmeldeinformationen definieren, die beim Zugriff auf Daten in einer externen Datenquelle verwendet werden.

Zunächst muss in der Datenbank ein Hauptschlüssel erstellt werden:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Setup you password - you need to create master key only once';

In der folgenden externen Datenquelle soll vom Synapse SQL-Pool eine verwaltete Identität des Arbeitsbereichs verwendet werden, um auf Daten im Speicher zuzugreifen.

CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity
WITH IDENTITY = 'Managed Identity';
GO
CREATE EXTERNAL DATA SOURCE ecdc_cases WITH (
    LOCATION = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/',
    CREDENTIAL = WorkspaceIdentity
);

Für den Zugriff auf den Azure Cosmos DB-Analysespeicher müssen Anmeldeinformationen definiert werden, die einen schreibgeschützten Azure Cosmos DB-Schlüssel enthalten.

CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';

Jeder Benutzer mit der Rolle „Synapse-Administrator“ kann diese Anmeldeinformationen verwenden, um auf Azure Data Lake Storage oder analytischen Azure Cosmos DB-Speicher zuzugreifen. Wenn Sie Benutzer mit niedrigen Berechtigungen haben, die nicht über die Rolle „Synapse-Administrator“ verfügen, müssten Sie ihnen eine explizite Berechtigung zum Verweisen auf diese datenbankweit gültigen Anmeldeinformationen erteilen:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::WorkspaceIdentity TO <user>
GO
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::MyCosmosDbAccountCredential TO <user>
GO

Weitere Details finden Sie auf der Seite Gewähren von Berechtigungen für DATENBANKWEIT GÜLTIGE ANMELDEINFORMATIONEN.

Definieren externer Dateiformate

Externe Dateiformate dienen zum Definieren der Struktur der Dateien, die in einer externen Datenquelle gespeichert sind. Sie können externe Dateiformate für Parquet und CSV definieren:

CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
GO
CREATE EXTERNAL FILE FORMAT CsvFormat WITH (  FORMAT_TYPE = DELIMITEDTEXT );

Weitere Informationen zur Beschreibung des Formats von CSV- oder Parquet-Dateien finden Sie unter Verwenden externer Tabellen mit Synapse SQL und CREATE EXTERNAL FILE FORMAT.

Untersuchen Ihrer Daten

Nachdem Sie Ihre Datenquellen eingerichtet haben, können Sie Ihre Daten mithilfe der Funktion OPENROWSET erkunden. Die Funktion OPENROWSET liest den Inhalt einer Remotedatenquelle (z. B. eine Datei) und gibt ihn als eine Reihe von Zeilen zurück.

select top 10  *
from openrowset(bulk 'latest/ecdc_cases.parquet',
                data_source = 'ecdc_cases',
                format='parquet') as a

Mit der Funktion OPENROWSET erhalten Sie Informationen zu den Spalten in den externen Dateien oder Containern und können ein Schema Ihrer externen Tabellen und Sichten definieren.

Erstellen externer Tabellen für Azure-Speicher

Nach der Schemaerkennung können Sie externe Tabellen und Sichten auf der Grundlage Ihrer externen Datenquellen erstellen. Es empfiehlt sich, Ihre Tabellen und Sichten in Datenbankschemas zu strukturieren. In der folgenden Abfrage können Sie ein Schema erstellen, bei dem alle Objekte, von denen auf das ECDC-COVID-Dataset zugegriffen wird, in Azure Data Lake Storage platziert werden:

create schema ecdc_adls;

Die Datenbankschemas ermöglichen das Gruppieren der Objekte sowie das Definieren schemaspezifischer Berechtigungen.

Nachdem Sie die Schemas definiert haben, können Sie externe Tabellen erstellen, von denen auf die Dateien verwiesen wird. In der folgenden externen Tabelle wird auf die ECDC-COVID-Parquet-Datei im Azure-Speicher verwiesen:

create external table ecdc_adls.cases (
    date_rep                   date,
    day                        smallint,
    month                      smallint,
    year                       smallint,
    cases                      smallint,
    deaths                     smallint,
    countries_and_territories  varchar(256),
    geo_id                     varchar(60),
    country_territory_code     varchar(16),
    pop_data_2018              int,
    continent_exp              varchar(32),
    load_date                  datetime2(7),
    iso_country                varchar(16)
) with (
    data_source= ecdc_cases,
    location = 'latest/ecdc_cases.parquet',
    file_format = ParquetFormat
);

Verwenden Sie für Zeichenfolgen- und Zahlenspalten jeweils den kleinstmöglichen Typ, um die Leistung Ihrer Abfragen zu optimieren.

Erstellen von Sichten für Azure Cosmos DB

Als Alternative zu externen Tabellen können Sie Sichten auf der Grundlage Ihrer externen Daten erstellen.

Die Sichten sollten ähnlich wie bei den Tabellen im vorherigen Beispiel in separaten Schemas platziert werden:

create schema ecdc_cosmosdb;

Nun können Sie eine Sicht im Schema mit Verweis auf einen Azure Cosmos DB-Container erstellen:

CREATE OR ALTER VIEW ecdc_cosmosdb.Ecdc
AS SELECT *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;Database=covid',
      OBJECT = 'Ecdc',
      CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) WITH
     ( date_rep varchar(20), 
       cases bigint,
       geo_id varchar(6) 
     ) as rows

Zur Optimierung der Leistung empfiehlt es sich, in der Schemadefinition WITH die kleinstmöglichen Typen zu verwenden.

Hinweis

Platzieren Sie Ihren Azure Cosmos DB-Kontoschlüssel in separaten Anmeldeinformationen, und verwenden Sie die Funktion OPENROWSET, um auf diese Anmeldeinformationen zu verweisen. Lassen Sie Ihren Kontoschlüssel nicht in der Sichtdefinition.

Zugriff und Berechtigungen

Zum Schluss sollten Sie noch Datenbankbenutzer erstellen, die auf Ihr LDW zugreifen können, und ihnen Berechtigungen zum Auswählen von Daten aus den externen Tabellen und Sichten erteilen. Im folgenden Skript erfahren Sie, wie Sie eine*n neue*n Benutzer*in hinzufügen, der bzw. die mithilfe einer Microsoft Entra-Identität authentifiziert wird:

CREATE USER [jovan@contoso.com] FROM EXTERNAL PROVIDER;
GO

Anstelle von Microsoft Entra-Prinzipalen können Sie SQL-Prinzipale erstellen, die sich mit dem Anmeldenamen und Kennwort authentifizieren.

CREATE LOGIN [jovan] WITH PASSWORD = 'My Very strong Password ! 1234';
CREATE USER [jovan] FROM LOGIN [jovan];

In beiden Fällen können Sie den Benutzern Berechtigungen zuweisen.

DENY ADMINISTER DATABASE BULK OPERATIONS TO [jovan@contoso.com]
GO
GRANT SELECT ON SCHEMA::ecdc_adls TO [jovan@contoso.com]
GO
GRANT SELECT ON OBJECT::ecdc_cosmosDB.cases TO [jovan@contoso.com]
GO
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::MyCosmosDbAccountCredential TO [jovan@contoso.com]
GO

Die Sicherheitsregeln hängen von Ihren Sicherheitsrichtlinien ab. Einige allgemeine Richtlinien lauten:

  • Verweigern Sie den neuen Benutzern die Berechtigung ADMINISTER DATABASE BULK OPERATIONS, da sie nur Daten aus den von Ihnen vorbereiteten externen Tabellen und Sichten lesen können sollen.
  • Gewähren Sie die Berechtigung SELECT nur für die Tabellen, die für einige Benutzer nutzbar sein sollen.
  • Wenn Sie Datenzugriff mithilfe der Sichten gewähren, erteilen Sie den Anmeldeinformationen, die für den Zugriff auf externe Datenquellen verwendet werden, die Berechtigung REFERENCES.

Dieser Benutzer verfügt über die Mindestberechtigungen, die zum Abfragen externer Daten erforderlich sind. Wenn Sie einen Poweruser erstellen möchten, der Berechtigungen, externe Tabellen und Ansichten einrichten kann, können Sie dem Benutzer die Berechtigung CONTROL erteilen:

GRANT CONTROL TO [jovan@contoso.com]

Rollenbasierte Sicherheit

Anstatt den einzelnen Benutzern Berechtigungen zu erteilen, ist es eine bewährte Methode, die Benutzer in Rollen zu organisieren und Berechtigungen auf Rollenebene zu verwalten. Das folgende Codebeispiel erstellt eine neue Rolle, die die Personen darstellt, die COVID-19-Fälle analysieren können, und fügt dieser Rolle drei Benutzer hinzu:

CREATE ROLE CovidAnalyst;

ALTER ROLE CovidAnalyst ADD MEMBER [jovan@contoso.com];
ALTER ROLE CovidAnalyst ADD MEMBER [milan@contoso.com];
ALTER ROLE CovidAnalyst ADD MEMBER [petar@contoso.com];

Sie können die Berechtigungen allen Benutzern zuweisen, die zur Gruppe gehören:

GRANT SELECT ON SCHEMA::ecdc_cosmosdb TO [CovidAnalyst];
GO
DENY SELECT ON SCHEMA::ecdc_adls TO [CovidAnalyst];
GO
DENY ADMINISTER DATABASE BULK OPERATIONS TO [CovidAnalyst];

Diese rollenbasierte Sicherheitszugriffssteuerung kann die Verwaltung Ihrer Sicherheitsregeln vereinfachen.

Nächste Schritte