Data Science

25 min. lesen

January 26, 2023

Willkommen zurück zu unserer Serie über Data Engineering auf MS Azure. In den vorangegangenen Blogartikeln haben wir mit DataEngineeringauf Azure - Das Setup gezeigt, wie Sie die Infrastruktur einrichten und mit Basic ETL Processing with Azure Data Factory einige Daten mit Data Factory vorverarbeiten können. Dieser Artikel behandelt die Erstellung und Konfiguration des Synapse DWH sowie die Datenarchitektur und die Einrichtung eines geeigneten Sternschemas.

Synapse SQL-Pool

Für unser Data Warehousing verwenden wir Azure Synapse Analytics (früher SQL DW).

1. Konzepte

Synapse ist die Data Warehousing-Plattform von Azure. Es gibt zwei Versionen, die Standalone-Version, die nur aus dem SQL-Pool besteht (im Azure-Portal "Azure Synapse Analytics (ehemals SQL DW)" genannt) und die "Synapse Data Analytics (Workspaces Preview)", die das DWH mit verschiedenen anderen Azure-Diensten wie PowerBI oder Azure Machine Learning integriert und Spark, SQL, Orchestrierung und Dateneingabe zusammenführt. Wir verwenden für dieses Tutorial die einfachere Standalone-Version.

Synapse SQL Pool ist die Data Warehousing-Lösung von Azure. Sie ist verteilt, skalierbar und beständig. Die Verarbeitungsleistung, der Speicher und der Preis des SQL Pools hängen von den Data Warehousing Units (DWU) ab, die zwischen 100 und 30000 liegen.

2. Erstellung von Ressourcen

Gehen Sie im Azure-Portal zu "Azure Synapse Analytics (früher SQL DW)" und klicken Sie auf "Hinzufügen". Wählen Sie das Abonnement und die Ressourcengruppe, die Sie in den vorherigen Abschnitten erstellt haben, und wählen Sie einen Namen. Wir haben zuvor einen SQL-Server für unsere Datenbank erstellt, den Sie unter "Server" auswählen können.

Um die Kosten zu senken, haben wir die kleinste Instanz "DW100c" gewählt, die für diese Aufgabe ausreicht, aber auch alles andere funktioniert. Unsere Konfiguration sieht wie folgt aus:

3. Datenbank-Einrichtung

Suchen Sie im Azure-Portal nach dem von Ihnen gewählten Namen und gehen Sie zu der Ressource.

Gehen Sie auf der linken Seite auf "Abfrage-Editor". Dadurch gelangen Sie zu einer Web IDE für Ihren SQL Pool.

Wir können damit beginnen, ein Schema namens northwind zu erstellen, indem wir CREATE SCHEMA NORTHWIND ausführen;

Das erste Sternschema, das wir erstellen, ist eines, das die Fragen rund um die Mitarbeiter beantwortet. In der Stammdatenbank kann ein Mitarbeiter einen anderen Mitarbeiter als seinen Vorgesetzten haben. Wir möchten hier verschiedene mitarbeiter- bzw. betreuerspezifische Geschäftsfragen beantworten. Wir definieren einen Vorgesetzten als einen Mitarbeiter, der mindestens einen anderen Mitarbeiter beaufsichtigt. Wir haben hier also eine Vererbungsabhängigkeit.

Jeder Vorgesetzte ist ein Mitarbeiter, aber nicht jeder Mitarbeiter ist ein Vorgesetzter. Wir entwerfen ein Sternschema mit nur einer Dimension DimEmployee, die grundlegende Informationen aus der Stammdatenbank enthält; FactEmployee, das einige mitarbeiterspezifische aggregierte Informationen definiert; FactEmployeeMonthly, das FactEmployeeMonthly ähnelt, aber monatliche Statistiken definiert; und schließlich FactSupervisor, das nur die Mitarbeiter enthält, die auch Vorgesetzte sind, und das vorgesetztenbezogene Statistiken enthält.

Alle Tabellen in unserem DWH werden Spalten enthalten, die als Schlüssel bezeichnet werden, um die Abhängigkeiten zwischen den Dimensions- und Faktentabellen zu definieren. Wir vermeiden bewusst die Verwendung der ID-Spalten in der Master-Datenbank, da eine Tabelle in Zukunft aufgrund von Aktualisierungen der Daten mehrere IDs für denselben Datensatz haben kann. Außerdem möchten wir die tabellenübergreifende Beziehung zwischen der Master-Datenbank und unserem DWH differenzieren.

Wir definieren DimEmployee wie folgt:

1create table [northwind].[DimEmployee](

1[Schlüssel] bigint UNIQUE NOT ENFORCED,

1[employee_id] int,

1[letzter_name] nvarchar (20) NOT NULL,

1[vorname] nvarchar (10) NOT NULL,

1[Geburtsdatum] datetime NULL,

1[hire_date] datetime NULL,

1[Adresse] nvarchar (60) NULL ,

1[Stadt] nvarchar (15) NULL ,

1[Region] nvarchar (15) NULL ,

1[Postleitzahl] nvarchar (10) NULL ,

1[Land] nvarchar (15) NULL,

1[created_ts] datetime

1)MIT(

1DISTRIBUTION = HASH ([Schlüssel]),

1CLUSTERED COLUMNSTORE INDEX ORDER ([Schlüssel])

1)

1;

1CREATE INDEX name_index ON [northwind].[DimEmployee] ([last_name]);

Die Spalten, die wir hier hinzufügen, sind created_ts - die Zeit, zu der der Datensatz erstellt wurde und key, die eindeutige Identifikation der Zeile. Im zweiten Teil sehen wir die Optionen DISTRIBUTION und CLUSTERED COLUMNSTORE INDEX ORDER ([key]). Dies sind die wichtigen Bestandteile einer verteilten Datenbank wie Synapse.

3.1. SQL Pool Datenverteilung

Synapse SQL Pool ist eine verteilte Plattform, die ihre Daten in einzelnen Knotenpunkten speichert. Es gibt mehrere Strategien für die Verteilung der Daten auf die Distributionen. Unabhängig von der gewählten Leistungsstufe gibt es insgesamt 60 Verteilungen. Die Leistungsstufe DW1500c mit 3 Compute-Knoten verfügt beispielsweise über 60/3=20 Datenverteilungen für jeden seiner Compute-Knoten. Die in Tabellen eingefügten Daten werden auf folgende Weise aufbewahrt.

3.1.1. Hash-Verteilung

Dies ist die oben verwendete Verteilung, bei der die Daten entsprechend dem Hashwert einer Spalte verteilt werden. Das Modulo 60 dieses Hashwerts gibt die Verteilung an, auf die die Daten verteilt werden sollen. Diese Verteilung hat den Vorteil, dass sie beim Filtern oder Zusammenführen der Daten sehr effizient ist - wenn sie richtig eingesetzt wird. Wenn z.B. zwei Tabellen eine Hash-Verteilung auf dieselbe Join-Spalte haben, wird diese Operation auf dieselbe Verteilung gelegt.

Die Hash-Spalte muss sorgfältig ausgewählt werden, um eine Schieflage der Daten zu vermeiden. Wenn der Hash der Verteilungsspalte nicht gleichmäßig über die Knoten verteilt wird, sind die Daten schief. Einige Knoten erhalten zu viele Daten, um sie zu verarbeiten, während andere nicht voll ausgelastet sind.

3.1.2. Round-Robin Verteilung

Jeder Datenpunkt wird nach dem Round-Robin-Prinzip an die Knoten verteilt. Jeder neue Datenpunkt wird in der zirkulären Reihenfolge in die Verteilungen eingefügt. Dadurch wird sichergestellt, dass die Daten gleichmäßig verteilt sind. Der Nachteil ist das Fehlen einer logischen, datenabhängigen Verteilung, die bei Joins zu viel Shuffling verursachen kann.

3.1.3. Replikation

Die Replikation ist eine Methode, die sich für kleine Tabellen eignet. Grundsätzlich wird jeder Datenpunkt in allen Verteilungen gespeichert. Wenn es sich bei der Tabelle um eine kleine Dimensionstabelle handelt, ist dies bei der Verknüpfung mit der Faktentabelle sehr effizient. Wenn die Tabelle jedoch groß ist, kann dies zu Speicherproblemen führen.

3.2. Daten Indizierung

Ein wichtiger Unterschied zwischen Synapse SQL Pool und anderen RDBMS ist das Fehlen von Primary und Foreign Key Constraints. Obwohl sie existieren, werden sie von Synapse nicht durchgesetzt. Das liegt an der verteilten Natur von Synapse: Jeder Knoten im Cluster verwaltet seine eigenen und die Durchsetzung dieser Einschränkungen würde eine Synchronisierung zwischen den Knoten erfordern. Da dies bei einem solchen System nicht möglich ist, muss der developer sicherstellen, dass die Primary Keys eindeutig sind und die Foreign Keys korrekt gesetzt werden.

In einem typischen RDBMS werden die Indizes mit B-Trees implementiert. Diese Datenstrukturen eignen sich zwar für den Zugriff auf einzelne Zeilen, sind aber bei Aggregatoperationen und Joins nicht so leistungsfähig. Synapse bietet mehrere Möglichkeiten, Daten zu indizieren.

3.2.1. Geclusterte Columnstore Indizes

In diesem Modus werden die Daten spaltenorientiert gespeichert. Die Daten werden in Abschnitten gespeichert und behalten den Bereich ihrer Minimal- und Maximalwerte für jede Spalte bei. Auf diese Weise können Abfragen effizienter sein und Segmente, die außerhalb des Bereichs liegen, herausfiltern.

Da es jedoch standardmäßig keine Ordnung gibt, kommt es zu Überschneidungen zwischen den Segmentbereichen. Sie können die Daten nach einer oder mehreren Spalten ordnen und so die Überschneidungen zwischen den Segmenten reduzieren. Die Ordnung verbessert auch die Suche über die Daten erheblich, da die binäre Suche eine Komplexität von O(log n) hat. Da die Daten spaltenförmig und geordnet sind, sparen Komprimierungsalgorithmen eine Menge Platz. Wenn die Datenmenge gering ist, können andere Indizes vorzuziehen sein, ansonsten kann man mit Sicherheit sagen, dass Clustered Columnstore-Indizes die beste Wahl sind.

Hinweis: Leider wird die Sortierung nach String-Spalten nicht unterstützt. Wir empfehlen stattdessen die Verwendung von Hash-Werten der String-Spalten.

3.2.2. Geclusterte vs. nicht geclusterte Indizes

Dies sind Indizes für zeilenorientierte Daten. Der geclusterte Index beschreibt die Art und Weise, wie die Daten gespeichert werden, während der nicht geclusterte Index ein externer Rowstore-Index ist, der auf die Zeilen in der Originaltabelle verweist.

3.2.3. Heap Index

Heap-Tabellen werden meist empfohlen, wenn Sie kleine und temporäre Tabellen schreiben. Da keine Reihenfolge erforderlich ist, ist das Laden von Daten in den Heap viel schneller. Das bedeutet jedoch, dass die Abfrage der Tabelle sehr viel Zeit in Anspruch nimmt, wenn die Anzahl der Zeilen zu hoch ist. Ein häufiger Anwendungsfall ist das schnelle Einfügen von Daten in einen Heap und das spätere Ändern des Index in einen Cluster-Index. Zum Beispiel kann eine Tabelle, die für Staging verwendet wird, verwendet werden; : alles einmal schreiben, alles einmal lesen, keine anderen Operationen wie Joins.

3.3. Zurück zum Schema

Wir haben FactEmployee, FactSupervisor und FactEmployeeMonthly wie folgt erstellt:

1create table [northwind].[FactEmployee](

1[Schlüssel] bigint,

1[employee_key] bigint, -- bezieht sich auf die Datensätze in der Tabelle dim_employee

1[employee_id] bigint,

1[total_distinct_territories] int,

1[total_distinct_regions] int,

1[num_orders_affiliated] int,

1[num_products_affiliated] int,

1[created_ts] datetime

1)MIT(

1DISTRIBUTION = HASH ([employee_key]),

1CLUSTERED COLUMNSTORE INDEX ORDER ([mitarbeiter_schlüssel])

1);

1-- Personen, die mindestens eine Person beaufsichtigen.

1create table [northwind].[FactSupervisor](

1[Schlüssel] bigint,

1[employee_id] bigint,

1[employee_key] bigint, -- bezieht sich auf die Datensätze in der Tabelle dim_employee

1[num_employees_directly_supervised] int,

1[created_ts] datetime

1)MIT(

1DISTRIBUTION = HASH ([employee_key]),

1CLUSTERED COLUMNSTORE INDEX ORDER ([mitarbeiter_schlüssel])

1)

1;

1create table [northwind].[FactEmployeeMonthly](

1[Schlüssel] bigint, -- hash(mitarbeiter_id, jahr, monat)

1[employee_key] bigint, -- bezieht sich auf die Datensätze in der Tabelle dim_employee

1[employee_id] bigint,

1[Jahr] int,

1[Monat] int,

1[total_products] int,

1[total_distinct_products] int,

1[total_orders] int,

1[created_ts] datetime

1)MIT(

1DISTRIBUTION = HASH ([employee_key]),

1CLUSTERED COLUMNSTORE INDEX ORDER ([employee_key])

1)

1;

Dies kann man sich als Polymorphismus in SQL vorstellen. Zum Beispiel beziehen sich die Tabellen FactEmployee, FactSupervisor und FactEmployeeMonthly auf eine einzige Zeile in DimEmployee, die alle Eigenschaften eines einfachen Mitarbeiters erbt. Alle Faktentabellen, die wir definiert haben, haben die gleiche Verteilung und die gleichen Indizes. Nehmen wir FactSupervisor als Beispiel. Wir definieren die Verteilung als den Hash von employee_key, der für die Verknüpfung mit DimEmployee(key) verwendet wird. Da DimEmployee ebenfalls auf dieselbe Spalte verteilt ist, bedeutet dies, dass die Verknüpfung zwischen den beiden auf demselben Knoten stattfindet. Um die Effizienz der Verknüpfung weiter zu verbessern, haben wir CLUSTERED COLUMNSTORE INDEX ORDER ([Schlüssel]) auf DimEmployee hinzugefügt. Das bedeutet, dass beim Left Join zwischen der Fakten- und der Dimensionstabelle die Datensätze viel schneller durchsucht werden.

Außerdem haben wir die Einschränkung UNIQUE NOT ENFORCED für die Schlüsselspalte von DimEmployee hinzugefügt. Die Eindeutigkeitseigenschaft der Spalte wird nicht erzwungen, der developer muss sicherstellen, dass die Werte eindeutig sind. Die Synapse-Engine kann diese Einschränkung jedoch nutzen, um die Abfragen bei der Suche nach den Daten zu optimieren.

Zu DimEmployee haben wir auch einen nicht geclusterten Index mit CREATE INDEX name_index ON [northwind].[DimEmployee] ([last_name]) hinzugefügt. In den meisten Geschäftsfällen können die Mitarbeiterstatistiken durch Filtern nach dem Namen durchsucht werden. Dadurch wird die Suche nach der Namensspalte der Dimension beschleunigt. Die Faktentabellen, die auf die Dimensionstabelle verweisen, können dann leicht gefiltert werden, da sie einen geclusterten Columnstore-Index auf den employee_key haben.

3.3.1. FactCustomer

Wir haben eine weitere Tabelle namens FactCustomer ohne Dimensionstabellen.

1CREATE TABLE [northwind].[FactCustomer](

1[Schlüssel] bigint,

1[customer_id] nvarchar(100),

1[Firmenname] nvarchar(100),

1[contact_name] nvarchar(100),

1[contact_title] nvarchar(100),

1[Adresse] nvarchar(100),

1[Stadt] nvarchar(100),

1[Region] nvarchar(100),

1[Land] nvarchar(100),

1[Telefon] nvarchar(100),

1[fax] nvarchar(100),

1[total_orders] int,

1[total_raw_price] float,

1[total_discount] float,

1[average_discount] float,

1[created_ts] datetime

1)MIT(

1VERTEILUNG = ROUND_ROBIN,

1GECLUSTERTER COLUMNSTORE-INDEX

1)

1;

1CREATE INDEX company_name_index ON [northwind].[FactCustomer] ([company_name]);

1CREATE INDEX contact_name_index ON [northwind].[FactCustomer] ([contact_name]);

1CREATE INDEX contact_title_index ON [northwind].[FactCustomer] ([contact_title]);

Es ist unwahrscheinlich, dass diese Tabelle mit anderen Tabellen verknüpft wird, so dass wir keine Columnstore-Indexreihenfolge für eine bestimmte Spalte hinzufügen müssen. Wir brauchen auch keinen Schlüssel für die Verteilung, also verwenden wir die ROUND_ROBIN-Verteilung, die für Gleichheit zwischen den Knoten sorgt. Da es notwendig sein könnte, Daten auf der Grundlage von company_name, contact_name oder contact_title zu durchsuchen, haben wir zusätzliche Indizes erstellt, um die Suche über diese spezifischen Felder zu beschleunigen. Diese Indizes sind nicht geclustert, liegen außerhalb der Daten und ändern weder die Reihenfolge noch die Verteilung der Daten. Jedes Mal, wenn Daten in diese Tabelle eingefügt werden, werden diese Indizes jedoch automatisch aktualisiert, wodurch sich die Einfügezeit erhöht.

3.4. Vorbereiten von Geschäftsberichtsansichten

Um die Vorteile der Sternschemata nutzen zu können, müssen die Fakten- und Dimensionstabellen miteinander verbunden werden, um Daten anzuzeigen. Der Benutzer möchte jedoch nicht jedes Mal, wenn er die Daten abfragen möchte, die Tabellen verbinden. Wir können Ansichten verwenden, um dies zu automatisieren.

Nehmen wir an, wir möchten einen Bericht über die Supervisoren erhalten; dann können wir das FactSupervisor und seine Beziehung `DimEmployee`` verwenden. Wir erstellen die Ansichten wie folgt;

1CREATE VIEW [northwind].[supervisor_report] AS

1SELECT e.*, f.num_employees_directly_supervised FROM [northwind].[FactSupervisor] f

1JOIN [northwind].DimEmployee e ON e.[key] = f.[employee_key];

Wir können die von uns erstellte Ansicht wie folgt sehen:

Jedes Mal, wenn eine Ansicht abgerufen wird, wird die zugrunde liegende Abfrage erneut ausgeführt und das Ergebnis zurückgegeben. Dieser Mechanismus ermöglicht die Trennung der Komplexität der Joins oder Mappings von externen Mechanismen.

Ein Nachteil dieses Mechanismus ist, dass das Ergebnis der Ansicht nicht zwischengespeichert wird. Jedes Mal, wenn die Ansicht abgerufen wird, muss die Abfrage erneut ausgeführt werden. Für die Zwischenspeicherung können materialisierte Ansichten wie folgt verwendet werden:

1CREATE MATERIALIZED VIEW [northwind].[supervisor_report] AS

1SELECT e.*, f.num_employees_directly_supervised FROM [northwind].[FactSupervisor] f

1JOIN [northwind].DimEmployee e ON e.[key] = f.[employee_key];

Sie funktioniert, indem sie das Ergebnis der Abfrage speichert, wenn sie erstellt oder neu erstellt wird oder die zugrunde liegenden Daten geändert werden. Ähnlich wie bei den Tabellen im SQL Pool können die materialisierten Ansichten durch ihre Verteilung, Partitionierung und Indizierung angepasst werden. Im Gegensatz zu anderen Cloud Data Warehouses wie Amazon Redshift müssen die materialisierten Ansichten bei Synapse nicht manuell mit den neu eingehenden Daten aktualisiert werden, sondern werden automatisch von Synapse aktualisiert, sobald sich die zugrunde liegenden Daten ändern.

Materialisierte Tabellen können jedoch gelegentlich langsamer sein als die direkte Ausführung der Abfragen, da die Columnstore-Indizes nach inkrementellen Änderungen durchsucht werden müssen. Zur erneuten Optimierung muss der Befehl REBUILD verwendet werden, um die materialisierte Ansicht mit den neuen Daten neu zu erstellen.

4. Aufnahme von Daten aus externen Quellen

Es ist typisch für ein DWH, Daten von einem externen Speicherort wie einem Datalake in eine persistente Tabelle zu kopieren. Dies kann mit dem Befehl COPY geschehen. Lassen Sie uns ein kleines Beispiel machen, indem wir eine Parkettdatei, die wir auf unserer Datalake haben, in Synapse kopieren.

Um auf einen Datenspeicher zuzugreifen, benötigen Sie einen Zugangsschlüssel. Wir empfehlen, entweder einen Storage Account Key oder einen Shared Access Key zu verwenden. Der Speicherkonto-Schlüssel ist der Master-Zugangscode für unser Speicherkonto. Sie können den im Setup verwendeten Wert übernehmen.

1CREATE TABLE [northwind].[temp_category] (

1[CustomerID] varchar(200),

1[Firmenname] varchar(200),

1[Kontaktname] varchar(200),

1[ContactTitle] varchar(200),

1[Adresse] varchar(200),

1[Stadt] varchar(200),

1[Region] varchar(200),

1[Postleitzahl] varchar(200),

1[Land] varchar(200),

1[Telefon] varchar(200),

1[Fax] varchar(200)

1)

1COPY INTO [northwind].[temp_category](

1[CustomerID],

1[Firmenname],

1[Kontaktname],

1[ContactTitle],

1[Adresse],

1[Stadt],

1[Region],

1[Postleitzahl],

1[Land],

1[Telefon],

1[Fax]

1)FROM 'https://qdestorageaccount.blob.core.windows.net/northwind/prepared/customers/year=1996/month=7/day=16/dbo.Customers.parquet'

1WITH (FILE_TYPE= 'PARQUET',

1CREDENTIAL=(IDENTITY = 'Storage Account Key',

1SECRET = '<IHR_STORAGE_ACCOUNT_KEY>')

1)

Eine Alternative ist die Verwendung von Shared Access Signature. Das ist ein Berechtigungsmodell, bei dem Sie die Berechtigungen auf verschiedenen Ebenen einschränken können. Außerdem können Sie einschränken, ab wann und bis wann es verwendet werden kann. Lassen Sie uns für unser Speicherkonto eine gemeinsame Zugriffssignatur erstellen, die nur Lesezugriff erlaubt. Gehen Sie zu Ihrem Speicherkonto -> Zugriffsschlüssel -> SAS Token kopieren. Die Konfiguration kann wie folgt eingestellt werden:

Kopieren Sie von 'SAS Token' unten:

Sie müssen das führende '?' aus dem Token entfernen, da es zu Problemen mit der Berechtigung führen kann. Der Kopierbefehl kann mit den Anmeldedaten ausgeführt werden:

1mit (FILE_TYPE= 'PARQUET',

1CREDENTIAL=(IDENTITY = 'GEMEINSAME ZUGRIFFSSIGNATUR',

1SECRET = 'IHR_SAS_TOKEN_OHNE_DIE_LEADING_?')

1)

5. Externe Tabellen erstellen

Synapse hat auch die Möglichkeit, die im Data Lake gespeicherten Daten dynamisch zu lesen. Lassen Sie uns eine externe Tabelle in derselben Parquet-Datei erstellen, die wir zuvor verwendet haben. Diesmal müssen die Anmeldeinformationen von Synapse sicher gespeichert werden. Dazu führen wir CREATE MASTER KEY aus, um einen Schlüssel zu erstellen, der unsere Geheimnisse verschlüsselt speichert. Sie müssen sich als Benutzer admin anmelden, um diese Befehle auszuführen.

5.1. Parquet File Format

Zunächst erstellen wir ein Parquet-Dateiformat. Dies kann auch ein ORC- oder CSV-Parser sein, aber wir bevorzugen der Einfachheit halber Parquet.

1CREATE EXTERNAL FILE FORMAT ParkettFormat

1MIT (

1FORMAT_TYPE = PARQUET);

5.2. Berechtigungsnachweise

Zweitens werden die Anmeldeinformationen erstellt, die wir in Synapse speichern möchten. Diese werden mit dem Master-Schlüssel der Datenbank verschlüsselt. Wir verwenden denselben Schlüssel für das Speicherkonto wie oben.

1CREATE DATABASE SCOPED CREDENTIAL datalake_sak

1WITH IDENTITY = 'Schlüssel des Speicherkontos',

1SECRET = '<IHR_STORAGE_ACCOUNT_KEY>';

5.3. Datenquelle

Dann erstellen wir eine Data-Quelle. Dazu benötigen wir nur die Anmeldeinformationen und das Stammverzeichnis. Wir haben unseren Container 'northwind' unter dem Speicherkonto 'qdestorageaccount' bereitgestellt.

1CREATE EXTERNAL DATA SOURCE datalakesource

1MIT (

1-- abfss wird für datalake gen2 verwendet

1LOCATION = 'abfss://northwind@qdestorageaccount.dfs.core.windows.net' ,

1-- Verwenden Sie den soeben erstellten Berechtigungsnachweis

1CREDENTIAL = [datalake_sak],

1TYPE=HADOOP) ;

5.4. Externe Tabelle

Schließlich erstellen wir die externe Tabelle. Wir geben unsere Datenquelle an, die automatisch die Anmeldeinformationen verwenden wird. Als Dateiformat geben wir das soeben erstellte Parquet-Format an. Der Speicherort wird relativ zum Stamm des Data Lake Containers angegeben.

1CREATE EXTERNAL TABLE [northwind].[ext_category](

1[CustomerID] varchar(200),

1[Firmenname] varchar(200),

1[ContactName] varchar(200),

1[ContactTitle] varchar(200),

1[Adresse] varchar(200),

1[Stadt] varchar(200),

1[Region] varchar(200),

1[Postleitzahl] varchar(200),

1[Land] varchar(200),

1[Telefon] varchar(200),

1[Fax] varchar(200)

1)

1MIT

1(

1-- Der Pfad sollte relativ zum Stammverzeichnis des Containers in der DATA_SOURCE angegeben werden.

1LOCATION = 'prepared/customers/year=1996/month=7/day=16/dbo.Customers.parquet',

1DATA_SOURCE = [datalakesource],

1FILE_FORMAT = [ParkettFormat]

1)

1;

6. Kontrolle der Kosten

Die Verwendung von Synapse mit kleineren Daten (weniger als ca. 1 TB) muss nicht unbedingt die effizienteste sein. Die Daten werden immer gespeichertin 60 Verteilungen was bei kleinen Daten und nur einem Rechenknoten unnötigen Parallelitäts-Overhead mit sich bringen kann.

Außerdem wird Ihnen bei der Erstellung des Clusters automatisch 1 TB Speicherplatz zugewiesen. Das heißt, selbst wenn Sie 100GB von 1TB nutzen, zahlen Sie für 1TB pro Stunde (€ 0,16). Wenn Ihre Kapazität 1TB übersteigt, wird der Speicherplatz automatisch auf 2TB aktualisiert; dann zahlen Sie 2*(€0,16) pro Stunde. Beachten Sie, dass die Datenbank automatisch Snapshots erstellt, die auf demselben Speicherplatz gespeichert werden.

Die Datenverarbeitung ist vom Speicher getrennt, so dass Sie unabhängig vom Speicher skalieren können. Wenn Sie Tabellen zusammenführen, werden die Daten zwischen den Rechenknoten gesendet. Dies verursacht Netzwerkkosten. Sie können die Kosten senken, indem Sie Ihre Daten so verteilen, dass weniger Daten verschoben werden müssen.

Wenn Sie keine bereitgestellten Ressourcen benötigen, können Sie auch das serverlose SQL-on-demand von Synapse Workspace nutzen. Sie werden nach dem TB an Daten berechnet, die Sie verarbeiten (5 €/TB). Ihre Tabellen können jedoch nur extern sein. Leider können die externen Tabellen die Hive-Partitionierung nicht erkennen. Verwenden Sie stattdessen Views mit der Funktion OPENROWSET.

Quellen

• Azure Docs: Synapse Bewährte Praktiken

• Azure Docs: Synapse Indizierung

• Azure Docs: Synapse Verteilen

• Azure Docs: Synapse-Ansichten

• Azure Docs: Synapse Copy-Anweisung

• Azure Docs: Externe Synapse-Tabellen

• MS Azure Synapse Preisgestaltung

Azure Docs: Synapse Architektur

479

Anteile

127

Ansichten