Inhalt

Star-Schema vs. OBT: Eine Analyse der besten Eignung für Ihr Data Warehouse

Die Ergebnisse: Denormalisierte Tabellen führen zu schnelleren Abfrageantworten

Analysedetails

Weitere Erwägungen

Die Data-Warehouse-Modellierung ist ein entscheidender, aber oft vergessener Teil der Entwicklung eines Data Warehouse. Bei der Data-Warehouse-Modellierung werden Schemata für die zusammengefassten und detaillierten Informationen in einem Data Warehouse erstellt.

Ihr Data Warehouse ist im Grunde ein Repository für Daten, die aus mehreren Quellen erfasst wurden, die verwandte oder ähnliche Daten in verschiedenen Formaten enthalten. Die Data-Warehouse-Modellierung ordnet diese verschiedenen Strukturen und Formate zu, um zu ermitteln, wie sichergestellt werden kann, dass eingehende Daten dem Design Ihres Data Warehouse entsprechen. Dies sorgt dafür, dass Ihre Daten produktiv für Data Mining und Analysen genutzt werden können.

Sie können sich Ihr Data-Warehouse-Modell als eine Vermittlersprache vorstellen, über die sich Ihre Systeme in Bezug auf Annahme und Verständnis der im Modell erwähnten Daten koordinieren können. Die Wahl Ihres Schemas kann die Leistung des Data Warehouse beeinflussen. Es kommen häufig Fragen oder gar Behauptungen auf, ob das Star-Schema oder One Big Table (OBT) die beste Leistung in einem Data Warehouse erzielt. Dieser Artikel beschreibt einige durchgeführte Tests und soll Ihnen helfen, die richtige Wahl zu treffen. Zunächst beleuchten wir jedoch die Data-Warehouse-Schemata, die zum Einsatz kommen werden.

[CTA_MODULE]

Was ist ein Star-Schema?

Ein Star-Schema verwendet eine zentrale Tabelle, die sogenannte Faktentabelle, für die Speicherung gemessener oder transaktionaler Daten. Eine Faktentabelle nutzt aggregierte Fakten für die Speicherung von Geschäftsinformationen wie Finanzdaten oder protokollierten Leistungsdaten. Faktentabellen sind von mehreren dimensionalen Tabellen umgeben, die Attribute enthalten, die mit den Daten in der Faktentabelle in Zusammenhang stehen.

Ein Star-Schema gilt allgemein als ideal für die Berichterstattung, da es den Datenabruf erleichtert. Die Verwendung von Zusammenführungen in Star-Schemata kann zudem die Abfrageleistung verbessern.

Was ist OBT?

OBT bedeutet „One Big Table“. Dem Namen entsprechend wird eine einzige Tabelle verwendet und alle Daten sind in einer einzigen Tabelle vorhanden. Dieser Ansatz sorgt dafür, dass das Warehouse nicht nebenher Zusammenführungen durchführen muss. OBT eignet sich aufgrund seiner Einfachheit für kleine Teams und Projekte, die sich auf die Verfolgung eines bestimmten Elements konzentrieren. Dieses Element ist üblicherweise jenes, das mit mehreren Attributen verknüpft ist.

Bei der Verwendung Ihres Data Warehouse für Kundenanalysen wäre Ihr OBT beispielsweise auf „Kunden“ mit Attributen fokussiert, etwa Kunden-ID, Name, Alter usw.

Star-Schema vs. OBT: Eine Analyse der besten Eignung für Ihr Data Warehouse

Das Ziel dieser Analyse besteht darin, ein Verständnis der Leistungsauswirkungen dieser verschiedenen Warehouse-Modellierungsmuster bei BI-Workloads für ein bestimmtes Warehouse zu verstehen. Es geht also nicht um Benchmarks zum Vergleich verschiedener Warehouses oder ihre relativen Leistungs- und Kostenkompromisse. Es soll ein Verständnis davon entstehen, welche Leistung verschiedene Data-Warehouse-Modellierungsmuster bringen, nachdem das zu verwendende Warehouse ausgewählt wurde.

Diese Analyse legt den Schwerpunkt insbesondere auf Architekturmuster für die Unterstützung von BI-Workloads, nicht so sehr auf die Abfrageleistung bei diversen Ad-hoc-Abfragen willkürlicher Komplexität. Bei heutzutage gängigen Bauweisen von Warehouses (mit einem ELT-Paradigma und einem Tool wie dbt) wird das Star-Schema am Ende einer ELT-Ausführung konstruiert und explizit für die Unterstützung von BI-Abfragen in Tools wie Looker oder Periscope entwickelt. Vor diesem Hintergrund sind die zum Testen dieser verschiedenen Verteilungsstile verwendeten Abfragen nicht unbedingt komplex, da sie gezielt dafür entwickelt wurden, gängige Abfragen widerzuspiegeln, die von BI-Tools ausgeführt werden – die Aggregation von Messungen über verschiedene Dimensionen hinweg, mit gelegentlichen CTE- oder Fensterfunktionen.

Die für den Test verwendeten Abfragen sind hier einsehbar.

Die Ergebnisse: Denormalisierte Tabellen führen zu schnelleren Abfrageantworten

Bei allen drei getesteten Warehouses – Redshift, Snowflake und BigQuery – führte die Verwendung einer einzelnen denormalisierten Tabelle statt eines Star-Schemas zu einer erheblichen Verbesserung der Abfragezeiten. Die Geschwindigkeitssteigerung durch die Nutzung einer einzelnen denormalisierten Tabelle stellte je nach verwendetem Warehouse eine Verbesserung um 25 % bis 50 % dar. Dies entspricht einem Unterschied von etwa 10 Sekunden für einen Single-Node-Cluster in Redshift. Mit Ausnahme der Abfragekompilierungszeit in Redshift ergeben sich folgende Verbesserungen:

• Redshift: 25 %–30 % (je nach Größe des Warehouse und Anzahl der Cluster)
• Snowflake: ~25 %
• BigQuery: ~50 %

Redshift

Für Redshift werden Daten von Ausführungen mit einem großen Multi-Node-Cluster sowie mit einem kleinen Single-Node-Cluster dargestellt. Die Ergebnisse wurden ebenfalls in die erstmalige Ausführung einer Abfrage (welche die Zeit einschließt, die Redshift für die Kompilierung der Abfrage benötigt) und die weiteren Ausführungen aufgeteilt, da diese nur die Compute-Zeit umfassen.

Single-Node

Erste Ausführung

Weitere Ausführungen

Multi-Node

Erste Ausführung

Weitere Ausführungen

Hier zeigt das (denormalisierte) OBT-Modell bei neun von zehn getesteten Abfragen eine höhere Leistung als das Star-Schema. Mit Ausnahme des Query-4-Enigma übertrifft die denormalisierte Tabelle die Leistung des Star-Schemas um 10 % bis 45 %, je nach Abfrage.

Snowflake

Bei Snowflake sind die Ergebnisse weniger eindeutig. Das (denormalisierte) OBT-Modell ist zwar bei den langsamsten Abfragen (Abfrage 8, 9 und 10) unzweifelhaft schneller als das Star-Schema, dieses übertrifft jedoch das OBT-Modell bei einigen der einfacheren Abfragen (3, 4 und 7). Beachten Sie, dass diese Abfragen die Zeit für die Kompilierung der Abfrage einschließen.

Uns fehlt ein ausreichendes Verständnis der inneren Abläufe von Snowflake, sodass wir nicht erläutern können, warum dies auftritt. Sollten unter den Lesern Snowflake-Experten sein, würden wir Ihre Hypothesen gerne erfahren.

BigQuery

Bei BigQuery sind die Ergebnisse noch deutlicher als bei Redshift: Die durchschnittliche Verbesserung der Abfragereaktionszeit lag bei 49 % und die denormalisierte Tabelle übertraf das Star-Schema in allen Bereichen. Beachten Sie, dass diese Abfragen die Zeit für die Kompilierung der Abfrage einschließen.

Ein beachtenswerter Aspekt sind die erheblichen Unterschiede der Varianzen bei der Abfragereaktionszeit zwischen den beiden Verteilungstypen: Das Star-Schema weist eine erheblich höhere Varianz bei der Abfragereaktionszeit auf, was möglicherweise damit zu tun hat, dass BigQuery die Ausführung intern plant. Wir sind allerdings keine BQ-Experten und würden es begrüßen, wenn jemand, der über weitergehendes Wissen verfügt, ausführen würde, warum das so ist.

Analysedetails

Dieser Vergleich wurde mithilfe einer Teilmenge der Daten des TPC-DS-Benchmark erstellt, das freundlicherweise von Fivetran zur Verfügung gestellt wurde. Bei allen Analysen wurden die TPC-DS-Daten vom Typ „100“ verwendet.

• Redshift:
• dc2.large mit 1 Knoten
• dc2.8xlarge-Cluster mit drei Knoten
• Snowflake:
• X-Large-Warehouse (16 Server)
• Bigquery:
• Es wurde die Standardkonfiguration für ein neu erstelltes Warehouse verwendet

Es wurden die folgenden Tabellen verwendet: store_sales, date_dim, store, household_demographics, customer_address.

Im Fall des Star-Schemas wurden die Tabellen unverändert belassen (Verteilung der Faktentabelle über „ss_item_key“ und Verteilung der Dimensionstabellen auf alle Knoten. In Redshift erfolgte die Verteilung dieser ebenfalls über „ss_item_key“).

Für die Timing-Tests wurden die Zwischenspeicherungsmechanismen für Abfragen anhand der jeweiligen Dokumentationen für Redshift, BigQuery und Snowflake deaktiviert.

Für Snowflake wurde zudem die erste Ausführung für jene Abfragen ausgeschlossen, die dem „Aufwärmen des Zwischenspeichers“ dienen und die Daten aus S3 auf die SSD auslesen, die Snowflake für die Zwischenspeicherung verwendet. Die Ergebnisse der Abfrage werden zwar nicht direkt zwischengespeichert, dennoch sollte Snowflake nicht für Fälle bewertet werden, in denen die Daten aus S3 gelesen werden. Weitere Informationen zur Zwischenspeicherung in Snowflake finden Sie hier.

Bei denormalisierten Tabellen wird alles mithilfe einer einfachen Zusammenführung zusammengefasst.

SELECT 
  * 
FROM 
  public.store_sales 
  LEFT JOIN public.date_dim ON store_sales.ss_sold_date_sk = date_dim.d_date_sk 
  LEFT JOIN public.store ON store_sales.ss_store_sk = store.s_store_sk 
  LEFT JOIN public.household_demographics ON store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk 
  LEFT JOIN public.customer_address ON store_sales.ss_addr_sk = customer_address.ca_address_sk

Der gesamte Code für die Reproduktion der Analyse ist in diesem Repository verfügbar.

Weitere Erwägungen

Es gibt mehrere Gründe, die für die Verwendung des Star-Schemas (oder ähnlicher Ansätze) sprechen:

• Das Star-Schema fördert eine bessere Konzeptualisierung und Organisation von ELT/ETL-Code.
• Das Star-Schema erleichtert Endbenutzern (Analysten und anderen Abfrageautoren) die Navigation.
• Das Star-Schema nimmt weniger Speicher in Anspruch.

Die ersten beiden Aspekte sind zwar wichtig, können jedoch über eine Vorbereitung der ELT-Prozesse bewältigt werden, durch die alle Daten in eine Art Star-Schema transformiert werden, bevor sie für die Abfrage durch Endbenutzer wieder zusammengeführt werden.

Der dritte Punkt erfordert mehr Aufmerksamkeit, insbesondere bei einem Data Warehouse wie Redshift, denn die Umsetzung der Denormalisierung nimmt eine erhebliche Menge an Speicher des Clusters in Anspruch. Die Umsetzung der Tabelle erhöhte die Speicherauslastung von etwas mehr als 30 Gigabyte auf über 90.

Dies ist auch nur eine Teilmenge der Daten, die zu „store_sales“ hätten zusammengefasst werden können. Zu Beginn dieser Analyse war geplant, alle möglichen Dimensionen in „store_sales“ zusammenzuführen, was jedoch nicht möglich war, da Redshift nicht genügend Speicher (auf einem dc2.large-Cluster mit einem Knoten) aufwies.

Je nach Umfang Ihrer Daten wären die Kosten für die Duplikation aller Dimensionen im Speicher möglicherweise zu hoch.

Kontaktieren Sie uns

Wenn Sie Fragen oder Gedanken zu dieser Analyse haben, nehmen Sie Kontakt auf. Ich bin per E-Mail unter kaminsky.michael@gmail.com oder über meinen Blog locallyoptimistic.com erreichbar.

Hinweise

Zu bestimmen, warum das Star-Schema die denormalisierte Tabelle bei Abfrage 4 im Single-Node-Cluster übertrifft (nicht aber im Multi-Node-Cluster), ist an dieser Stelle dem Leser überlassen. Hauptsächlich, weil ich keine Idee dazu habe.

Da dbt keine Möglichkeit bietet, die Spaltenkompression oder den Kodierungsstil in Redshift anzugeben, stellt dies vermutlich den schlimmsten anzunehmenden Fall in Bezug auf Speicher dar. Mit der richtigen Spaltenkodierung ließe sich dieses Problem womöglich deutlich lindern.

[CTA_MODULE]