Erstellen von Data Warehouse-Tabellen

  • 10 Minuten

Nachdem Sie die grundlegenden Architekturprinzipien für ein relationales Data Warehouse-Schema verstanden haben, erfahren Sie hier, wie Sie ein Data Warehouse erstellen.

Erstellen eines dedizierten SQL-Pools

Um in Azure Synapse Analytics ein relationales Data Warehouse zu erstellen, müssen Sie einen dedizierten SQL-Pool erstellen. Die einfachste Möglichkeit, dies in einem vorhandenen Azure Synapse Analytics-Arbeitsbereich zu tun, ist die Verwendung der Seite Verwalten in Azure Synapse Studio, wie hier gezeigt:

A screenshot of the SQL pools tab in the Manage page of Synapse Studio.

Wenn Sie einen dedizierten SQL-Pool bereitstellen, können Sie die folgenden Konfigurationseinstellungen angeben:

  • Ein eindeutiger Name für den dedizierten SQL-Pool.
  • Ein Leistungsniveau für den SQL-Pool, das zwischen DW100c und DW30000c liegen kann und die Kosten pro Stunde für den Pool bestimmt, wenn er ausgeführt wird.
  • Informationen dazu, ob mit einem leeren Pool begonnen oder eine vorhandene Datenbank aus einer Sicherung wiederhergestellt werden soll.
  • Die Sortierung des SQL-Pools, die die Sortierreihenfolge und Regeln für den Zeichenfolgenvergleich für die Datenbank bestimmt. (Nach der Erstellung können Sie die Sortierung nicht mehr ändern.)

Nachdem Sie einen dedizierten SQL-Pool erstellt haben, können Sie den Ausführungszustand auf der Synapse Studio-Seite Verwalten steuern. Wenn der Pool nicht benötigt wird, können Sie ihn anhalten, um unnötige Kosten zu vermeiden.

Wenn der Pool ausgeführt wird, können Sie ihn auf der Seite Daten erkunden und SQL-Skripts erstellen, die darin ausgeführt werden sollen.

Überlegungen zum Erstellen von Tabellen

Um im dedizierten SQL-Pool Tabellen zu erstellen, verwenden Sie die Transact-SQL-Anweisung CREATE TABLE (manchmal auch CREATE EXTERNAL TABLE). Welche Optionen genau in der Anweisung verwendet werden, richtet sich nach dem Typ der zu erstellenden Tabelle. Folgende Typen sind möglich:

  • Faktentabellen
  • Dimensionstabellen
  • Stagingtabellen

Hinweis

Wie bereits erläutert, setzt sich ein Data Warehouse aus Tabellen vom Typ Fakt und Dimension zusammen. Stagingtabellen werden häufig im Rahmen des Data Warehouse-Ladeprozesses verwendet, um Daten aus Quellsystemen zu erfassen.

Beim Entwerfen eines Sternschemamodells für kleine oder mittelgroße Datasets können Sie Ihre bevorzugte Datenbank verwenden, z. B. Azure SQL. Bei größeren Datensätzen profitieren Sie möglicherweise von der Implementierung Ihres Data Warehouse in Azure Synapse Analytics anstatt SQL Server. Wenn Sie Tabellen in Synapse Analytics erstellen, müssen Sie die wesentliche Unterschiede kennen.

Constraints in Bezug auf die Datenintegrität

Dedizierte SQL Pools in Synapse Analytics unterstützen keine FOREIGN KEY- und UNIQUE-Constraints, die es in anderen relationalen Datenbanksystemen wie beispielsweise SQL Server gibt. Das bedeutet, dass Aufträge zum Laden von Daten die Eindeutigkeit und Verweisintegrität für Schlüssel beibehalten müssen, ohne sich darauf zu verlassen, dass die Tabellendefinitionen in der Datenbank dies zu tun.

Tipp

Weitere Informationen zu Constraints in dedizierten SQL-Pools in Azure Synapse Analytics finden Sie unter Primärschlüssel, Fremdschlüssel und eindeutige Schlüssel bei Verwendung eines dedizierten SQL-Pools in Azure Synapse Analytics.

Indizes

Während dedizierte SQL-Pools in Synapse Analytics gruppierte Indizes unterstützen – ebenso wie in SQL Server –, ist der Standardindextyp der gruppierte Columnstore-Index. Dieser Indextyp bietet einen erheblichen Leistungsvorteil beim Abfragen großer Datenmengen in einem typischen Data Warehouse-Schema und sollte verwendet werden, wann immer möglich. Manche Tabellen können jedoch Datentypen enthalten, die nicht in einen gruppierten Columnstore-Index einbezogen werden können, z. B. VARBINARY(MAX). In diesem Fall kann stattdessen ein gruppierter Index verwendet werden.

Tipp

Weitere Informationen zur Indexerstellung in dedizierten SQL-Pools in Azure Synapse Analytics finden Sie unter Indizes von Tabellen in dedizierten SQL-Pools in Azure Synapse Analytics.

Distribution

Dedizierte SQL-Pools in Azure Synapse Analytics verwenden eine Architektur der massiv-parallelen Verarbeitung (MPP) im Gegensatz zur symmetrischen Multiprocessing-Architektur (SMP), die in den meisten OLTP-Datenbanksystemen verwendet wird. In einem MPP-System werden die Daten in einer Tabelle für die Verarbeitung auf einen Pool aus mehreren Knoten verteilt. Synapse Analytics unterstützt die folgenden Arten der Verteilung:

  • Hash: Für die angegebene Spalte wird ein deterministischer Hashwert berechnet und zum Zuweisen der Zeile zu einem Computeknoten verwendet.
  • Roundrobin: Die Zeilen werden gleichmäßig auf alle Computeknoten verteilt.
  • Repliziert: Auf jedem Computeknoten wird eine Kopie der Tabelle gespeichert.

Der Tabellentyp bestimmt oftmals, welche Option für das Verteilen der Tabelle ausgewählt wird.

Tabellentyp Empfohlene Verteilungsoption
Dimension Verwenden Sie die replizierte Verteilung für kleinere Tabellen, um ein Mischen der Daten zu verhindern, wenn die verteilten Faktentabellen verknüpft werden. Wenn die Tabellen zu groß sind, um sie auf den einzelnen Computeknoten zu speichern, verwenden Sie die Hashverteilung.
Fakt Verwenden Sie die Hashverteilung mit dem gruppierten Columnstore-Index, um Faktentabellen auf Computeknoten zu verteilen.
Staging Verwenden Sie die Roundrobinverteilung für Stagingtabellen, um Daten gleichmäßig auf Computeknoten zu verteilen.

Tipp

Weitere Informationen zu Verteilungsstrategien für Tabellen in Azure Synapse Analytics finden Sie unter Leitfaden zum Entwerfen von verteilten Tabellen mithilfe eines dedizierten SQL-Pools in Azure Synapse Analytics.

Erstellen von Dimensionstabellen

Wenn Sie eine Dimensionstabelle erstellen, stellen Sie sicher, dass die Tabellendefinition Ersatz- und Alternativschlüssel sowie Spalten für die Attribute der Dimension enthält, die Sie zum Gruppieren von Aggregationen verwenden möchten. Häufig ist es am einfachsten, eine IDENTITY-Spalte zum automatischen Generieren eines inkrementellen Ersatzschlüssels zu verwenden (andernfalls müssen Sie jedes Mal, wenn Sie Daten laden, eindeutige Schlüssel generieren). Das folgende Beispiel zeigt eine CREATE TABLE-Anweisung für eine hypothetische DimCustomer-Dimensionstabelle.

CREATE TABLE dbo.DimCustomer
(
    CustomerKey INT IDENTITY NOT NULL,
    CustomerAlternateKey NVARCHAR(15) NULL,
    CustomerName NVARCHAR(80) NOT NULL,
    EmailAddress NVARCHAR(50) NULL,
    Phone NVARCHAR(25) NULL,
    StreetAddress NVARCHAR(100),
    City NVARCHAR(20),
    PostalCode NVARCHAR(10),
    CountryRegion NVARCHAR(20)
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

Hinweis

Bei Bedarf können Sie ein spezifisches Schema als Namespace für Ihre Tabellen erstellen. In diesem Beispiel wird das standardmäßige dbo-Schema verwendet.

Wenn Sie ein Schneeflockenschema verwenden möchten, in dem Dimensionstabellen zueinander in Beziehung stehen, sollten Sie den Schlüssel für die übergeordnete Dimension in die Definition der untergeordneten Dimensionstabelle einschließen. Beispielsweise könnten Sie den folgenden SQL Code verwenden, um die geografischen Adressinformationen aus der Tabelle DimCustomer in eine separate DimGeography-Dimensionstabelle zu verschieben:

CREATE TABLE dbo.DimGeography
(
    GeographyKey INT IDENTITY NOT NULL,
    GeographyAlternateKey NVARCHAR(10) NULL,
    StreetAddress NVARCHAR(100),
    City NVARCHAR(20),
    PostalCode NVARCHAR(10),
    CountryRegion NVARCHAR(20)
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

CREATE TABLE dbo.DimCustomer
(
    CustomerKey INT IDENTITY NOT NULL,
    CustomerAlternateKey NVARCHAR(15) NULL,
    GeographyKey INT NULL,
    CustomerName NVARCHAR(80) NOT NULL,
    EmailAddress NVARCHAR(50) NULL,
    Phone NVARCHAR(25) NULL
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

Zeitdimensionstabelle

Die meisten Data Warehouses enthalten eine Dimensionstabelle vom Typ Zeit, mit der Sie Daten über mehrere hierarchische Zeitintervallebenen hinweg aggregieren können. Das folgende Beispiel erstellt eine DimDate-Tabelle mit Attributen, die sich auf bestimmte Datumsangaben beziehen.

CREATE TABLE dbo.DimDate
( 
    DateKey INT NOT NULL,
    DateAltKey DATETIME NOT NULL,
    DayOfMonth INT NOT NULL,
    DayOfWeek INT NOT NULL,
    DayName NVARCHAR(15) NOT NULL,
    MonthOfYear INT NOT NULL,
    MonthName NVARCHAR(15) NOT NULL,
    CalendarQuarter INT  NOT NULL,
    CalendarYear INT NOT NULL,
    FiscalQuarter INT NOT NULL,
    FiscalYear INT NOT NULL
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

Tipp

Ein gängiges Muster beim Erstellen einer Dimensionstabelle für Datumsangaben besteht darin, das numerische Datum im Format DDMMYYYY oder YYYYMMDD als ganzzahliger Ersatzschlüssel und das Datum mit dem Datentyp DATE oder DATETIME als Alternativschlüssel zu verwenden.

Erstellen von Faktentabellen

Faktentabellen enthalten die Schlüssel für jede Dimension, mit der sie verknüpft sind, sowie die Attribute und numerischen Messwerte für bestimmte Ereignisse oder Beobachtungen, die Sie analysieren möchten.

Das folgende Codebeispiel erstellt eine hypothetische Faktentabelle namens FactSales, die durch Schlüsselspalten (Datum, Kunde, Produkt und Store) mit mehreren Dimensionen verknüpft ist.

CREATE TABLE dbo.FactSales
(
    OrderDateKey INT NOT NULL,
    CustomerKey INT NOT NULL,
    ProductKey INT NOT NULL,
    StoreKey INT NOT NULL,
    OrderNumber NVARCHAR(10) NOT NULL,
    OrderLineItem INT NOT NULL,
    OrderQuantity SMALLINT NOT NULL,
    UnitPrice DECIMAL NOT NULL,
    Discount DECIMAL NOT NULL,
    Tax DECIMAL NOT NULL,
    SalesAmount DECIMAL NOT NULL
)
WITH
(
    DISTRIBUTION = HASH(OrderNumber),
    CLUSTERED COLUMNSTORE INDEX
);

Erstellen von Stagingtabellen

Stagingtabellen werden als temporärer Speicher für Daten verwendet, während diese in das Data Warehouse geladen werden. Ein typisches Muster besteht darin, die Tabelle so effizient wie möglich zu strukturieren, um die Daten aus ihrer externen Quelle (häufig Dateien in einem Data Lake) in die relationale Datenbank einzugeben. Dann werden SQL-Anweisungen verwendet, um die Daten aus den Stagingtabellen in die Dimensions- und Faktentabellen zu laden.

Das folgende Codebeispiel erstellt eine Stagingtabelle für Produktdaten, die letztendlich in eine Dimensionstabelle geladen werden:

CREATE TABLE dbo.StageProduct
(
    ProductID NVARCHAR(10) NOT NULL,
    ProductName NVARCHAR(200) NOT NULL,
    ProductCategory NVARCHAR(200) NOT NULL,
    Color NVARCHAR(10),
    Size NVARCHAR(10),
    ListPrice DECIMAL NOT NULL,
    Discontinued BIT NOT NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

Verwenden von externen Tabellen

Wenn sich die zu ladenden Daten in Dateien mit einer geeigneten Struktur befinden, kann es in einigen Fällen effektiver sein, externe Tabellen zu erstellen, die auf den Dateispeicherort verweisen. Auf diese Weise lassen sich die Daten direkt aus den Quelldateien lesen und müssen nicht in den relationalen Speicher geladen werden. Das folgende Beispiel zeigt, wie Sie eine externe Tabelle erstellen, die auf Dateien in dem Data Lake verweist, der dem Synapse-Arbeitsbereich zugeordnet ist:


-- External data source links to data lake location
CREATE EXTERNAL DATA SOURCE StagedFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/stagedfiles/'
);
GO

-- External format specifies file format
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

-- External table references files in external data source
CREATE EXTERNAL TABLE dbo.ExternalStageProduct
(
    ProductID NVARCHAR(10) NOT NULL,
    ProductName NVARCHAR(200) NOT NULL,
    ProductCategory NVARCHAR(200) NOT NULL,
    Color NVARCHAR(10),
    Size NVARCHAR(10),
    ListPrice DECIMAL NOT NULL,
    Discontinued BIT NOT NULL
)
WITH
(
    DATA_SOURCE = StagedFiles,
    LOCATION = 'products/*.parquet',
    FILE_FORMAT = ParquetFormat
);
GO

Hinweis

Weitere Informationen zur Verwendung externer Tabellen finden Sie in der Azure Synapse Analytics-Dokumentation unter Verwenden externer Tabellen mit Synapse SQL.