Datenbank fürData-Warehouse

Datenbanken

Untersuchung von 4 Datenbankverwaltungssystemen auf Tauglichkeit zum Datawarehouse. Zwei Vertreter der Open Source Welt und zwei kommerzielle Datenbanken, jeweils die aktuellsten offizielle Versionen:

  • Oracle 11G
  • MS SQL Server 2008
  • PostgreSQL 8.3.3
  • MySQL 5.1

Vergleich

Merkmal Oracle 11G Microsof SQL Server
2008
PostgreSQL 8.3.3 MySQL 5.1
Partitionierung
Subpartitionen ja nein nein ja
List ja nein ja ja
Range ja ja ja ja
Hash ja nein nein ja
Weitere Intervall,
Reference,
System
nein nein Key
Aggregationen
ROLLUP Operator ja ja nein ja
CUBE Operator ja ja nein nein
GROUPING SETS ja ja nein nein
Eigene
Aggregationsfunktionen
ja ja ja nur in C
Materialized Views
Rebuild ja nei ja nein
Refresh ja ja ja nein
Query Optimierung ja ja nein nein
Analytische Funktionen ja ja nein nein
Bitmapindexe ja nein ja nein
MERGE Operator ja ja nein durch INSERT
Virtuelle Felder ja ja nein nein
Komprimierung ja ja ja ja
Explain Tool ja ja ja nein
Job Scheduling ja ja ja nein
Schneller Datenimport ja
Schema-Modellierung ja ja ja ja
ETL-Modellierung ja ja nein nein

Partitionierung

Logische und physikalische Zerteilung von Daten in sehr großen Tabellen, mit dem Ziel – SQL Abfragen zu beschleunigen und Verwaltung von sehr großen Datenmengen zu vereinfachen. Die bekanntesten Partitionierungsmethoden sind: List, Range und Hash Partitionierung. Außerdem können manche Datenbanken mehrere Partitionierungsmethoden kombinieren (z.B. Range für Alter und Subpartition List für Geschlecht). Falls Datenbank Range Methode unterstützt, können List und Hash Methoden simuliert werden, dafür muss der Partitionierungs-Attribut ins „Range“ Wert umgerechnet werden (z.B. durch die Verwendung einer Hash-Funktion), dieser Ansatz führt aber zur Steigerung der Komplexität und erhöhtem CPU und Festplattenplatz Verbrauch (findet man beim Microsoft SQL Server).

Oracle Partitioning lässt kaum Wünsche von dem Developer/DB Administrator offen. Microsoft und MySQL unterstützen nur die logische Partitionierung. MySQL hat zwar mehr Partitionierungs-Methoden als SQL Server und kennt Subpartition, die Partitionierung von MySQL hat aber sehr viel Einschränkungen und lässt sich nicht so leicht und flexibel warten wie beim SQL Server. Partitionierung von Daten im PostgreSQL wird erledigt einerseits durch UNION von mehreren Tabellen zu einem “partitioniertem” VIEW, andererseits durch INSERT RULES (quasi transparente INSERTs, durch Umlenkung von Daten in die entsprechenden Tabellen). Due PostgreSQL Lösung ist absolut nicht transparent (die kann man fast kaum Partitionierung nennen), bietet aber Möglichkeit zur physikalischen Partitionierung und bisschen mehr Flexibilität als die anderen Ansätze.

Performanz-Steigerung durch Partitionierung erzielt man vor allem dank Partition Pruning (Ausschluss von nicht benötigten Partitionen), durch die Verwendung von partitionierten Indexes kann man das noch weiter verbessern. Beispiel: DML Operation ändert nur eine betroffene Partition, dadurch wird Transaktions-Verwaltung (zB. Undo) schneller, Indexe werden nur für diese Partition neu berechnet, Constraints können schneller geprüft werden. Beim Abfragen profitiert man vor allem wegen kleineren Indexes und schnelleren FULL ACCESS Zugriffen auf Tabellen (die in diesem Fall nicht mehr vollständig gelesen werden müssen). Um von der Partitionierung zu profitieren, muss der Attribut, nach dem Tabelle partitioniert wurde, unbedingt in WHERE Klause verwendet werden. Weitere Möglichkeit zur Performanz-Steigerung ist Partition-wise Join, wobei nur die Partitionen im „Join“ benutzt werden, wo eine Verbindung tatsächlich Sinn hat.

Beispiel: Tabelle SALES ist nach SALES_DATE mit „Range“ partitioniert, für Jeden Monat ist eigene Partition definiert. Partitionen der letzten Jahre werden in Tablespaces ausgelagert, die auf langsamen Medien platziert sind, die neuesten Daten landen auf einem Hochgeschwindigkeits-RAID. Abfrage „select * from SALES_DATE = current_month“ führt dazu, dass nur eine einzige Partition vom RAID gelesen wird.

Aggregationen

ROLLUP, CUBE und GROUPING SETS wurden im ANSI SQL99 Standard definiert. Oracle DB unterstützt diesen Standard seit Version 9i, Microsoft SQL Server hat die Unterstützung erst seit Version 2008 (der alte „WITH“ Operator wurde als veraltet definiert). MySQL implementiert nur den alten ROLLUP Operator (WITH ROLLUP), CUBE und GROUPING SETS fehlen. Postgress hat keine Implementierung von Gruppierung Operatoren.

ROLLUP Operator

ROLLUP Operator ermöglicht gleichzeitige Aggregation auf alle Ebenen einer Hierarchie, so resultiert ein ROLLUP von Attributen „country, city, street“ in Aggregation von allen (Straßen, Städten, Ländern), (Städten, Ländern), (Ländern) und allen Werten. ROLLUP auf N Ebenen kann durch UNION von N+1 SELECT Abfragen mit immer höheren GROUP BY Ebene simuliert werden, was aber zum N gleichen physikalischen Zugriffen auf die Tabelle führen kann.

<code class="sql">select city, street, sum(sales) from sales group by rollup city, street
</code>

liefert die gleiche Ergebnisse wie:

<code class="sql">select city, street, sum(sales) from sales group by city, street
union all
select city, null as street, sum(sales) from sales group by city
union all
select null, null as street, sum(sales) from sales
</code>

Weitere Möglichkeit ist Verwendung einer temporären Tabelle mit iterativem Aggregieren auf immer höhere Ebenen, was leider nicht mit einem einzigen SELECT durchgeführt werden kann (eventuell mit stored procedure).

CUBE Operator

CUBE Operator ermöglicht gleichzeitige Aggregation auf alle Kombinationen von Attributen. CUBE von „country, city, street“ produziert Aggregation von allen (Straßen), (Straßen, Städten), (Straßen, Ländern), (Straßen, Städten, Ländern), (Städten), (Städten, Ländern), (Ländern) und (*). Für N Attribute entsteht ein Ergebnis, der aus Zweierpotenz von N Aggregationen besteht, daher ist CUBE Operator sehr rechenintensiv und kann entweder für wenige Attribute oder für wenige Datensätze benutzt werden (ist in manchen Situationen trotzdem sehr praktisch). CUBE Operator kann, mit gleichen Techniken wie der ROLLUP, durch normales GROUP BY simuliert werden.

GROUPING SETS

GROUPING SETS erlauben gleichzeitige Aggregation von mehreren Kombinationen der Attribute. Mit GROUPING SETS können CUBE und ROLLUP Operatoren nachgebildet werden, indem man alle zulässige Kombinationen von Aggregationen explizit beim GROUPING SETS Operator auflistet.

Beispiel von Verwendung der GROUPING SETS:

<code class="sql">select street, city, country, sum(*) from sales group by grouping sets ((street, city), (country))
</code>

Liefert aggregierte Ergebnisse für alle (Straßen, Städte) und (Länder).

GROUPING SETS verwenden oft so genannte „verkettete Gruppierungen“ (concatenated groupings), die erlauben Gruppierungen von Werten nicht nur einzelner Attribute, sondern eindeutige Kombinationen von Werten mehrerer Attribute.

Materialized Views

Oracle hat die beste Unterstützung für Materialisierte-Sichten von allen getesteten Datenbanken (eigentlich kann man sogar sagen, nur beim Oracle sind Materialisierte-Sichten gescheit implementiert). Materialisierte Sichten können als Ergebnisse von beliebiger SQL Abfrage definiert werden. Aktualisierung der Sichten kann entweder automatisch oder manuell erfolgen. FAST REFRESH Funktion (durch Verwendung von “materialized view logs”) ermöglicht schnelle Aktualisierung von Daten nach Änderungen in Basis-Tabelle. Dazu gibt es viele weitere Features wie: “nicht blockierende Refreshes”, Komprimierung, “prebuild Tables”, Partitionierung, Query Rewrite usw.

Microsoft SQL Server verwendet statt Materialisierten Sichten so genannte “Indexed views”. Die “Indizierte Sichten” werden in der Datenbank gespeichert, jede Änderung in Basis-Tabelle führt zur Neuberechnung des Sichte. Es können nur einfache Sichten definiert werden, OUTER JOINS, UNION’s oder komplizierte Aggregationen sind nicht möglich. Vorteil von der Microsoft Lösung liegt in höherer Wahrscheinlichkeit, dass „Optimizer“ auf indexierter Sicht zugreift, falls Benutzer entsprechend aggregierte Sätze angefordert hat. Außerdem sind Daten in “Indizierten Sichten” immer konsistent mit Daten in Basis-Tabelle.

PostgreSQL und MySQL unterstützen in der Datenbank keine Materialisierte-Sichten. Für PostgreSQL existieren fertige Module, die Funktionen zum Anlegen und Verwalten von Materialisierte-Sichten definieren (ganz normale Tabellen mit Aggregierten Daten).

Analytische Funktionen

„Analytical Functions“ wurden im SQL:2003 Standard definiert. Ähnlich wie der GROUP BY Operator, arbeiten „Analytische Funktionen“ nicht mit einem Datensatz, sondern gleich mit mehreren Sätzen. Man definiert (mit Hilfe von speziellen Partieionierung-Regeln) Auschnitte in den Daten, auf denen Aggregationsfunktionen (MAX, MIN, AVG usw), oder spezielle analytische Funktionen (LAST, RANK, ROW_NUMBER usw) berechnet werden. Daten in den Ausschnitten können dabei sortiert (aufgelaufene Summe, Nummer des Satzes in der Gruppe usw.) und durch Definition von Bereichen (dieser Satz und zwei danach) zusätzlich filtriert werden. „Analytische Funktionen“ dürfen nicht mit GROUP BY Operator zusammen verwendet werden, dafür sind mehrere analytische Ausdrücke in selber Abfrage zulässig.

Merge Operator

Merge Operator erlaubt bedingter INSERT, UPDATE oder DELETE von Sätzen, abhängig davon, ob der Satz einem MATCH Kriterium entspricht (meistens Prüfung, ob der Satz bereits in der Tabelle vorhanden).
Oracle und SQL Server implementieren MERGE nach ANSI SQL:2003. Im MySQL gibt es IGNORE Option beim INSERT Operator und REPLACE Operator, die MERGE Funktionalität nachbilden. PostgreSQL hat keine MERGE Funktionalität.

Bitmapindexe

Bitmapindexe werden zum Indizieren von Dimensionalen Attributen gebraucht, weil sie bei großen Datenmengen und kleinem Anzahl eindeutiger Attributen höhere Entropie als B-Tree Indexe haben. Zusätzlich sind schnelle boolesche Operationen auf Indexen (AND, OR usw.) möglich, sogar auf Indexen von verschiedenen Attributen, was den Bitmap-Index für Datawarehouse-Typische „Star“ Schema interessant macht.
Für jeden eindeutigen Wert eines Attributes (oder mehreren Attributen) wird eine Bitmap-Liste erzeugt. Position vom Bit in der Liste referenziert eindeutig einen Satz in der Tabelle, dazu wird eine Zuordnung von Bit-Positionen zum Sätzen benötigt, die allerdings nur einmal pro Tabelle verwaltet werden kann. Wert von dem Bit in der Liste zeigt, ob dieser Attribut-Wert dem Listen-Wert gleich ist. Nach jeder Änderung der indexierten Werte, müssen betroffene Bitmap-Listen neu berechnet werden, was höheren Verwaltungsaufwand im Vergleich zum B-Tree Indexe verursacht und zum Locking Problemen führen kann.

Beispiel: ein Bitmapindex auf dem Attribut „Geschlecht“ wird zum zwei Listen in folgender Form führen:
M: 010010011100….
W: 101101100011….

Nur Oracle Datenbank hat die Unterstützung für Bitmapindexe. Im PostgreSQL gibt es (zusätzlich zum klassischen B-Tree Index) weitere Index-Typen, wie – „HASH“, das analog zum Bitmapindex funktioniert. Außerdem, Postgress kann „bitmap index scans“ mit verschiedenen Index-Typen durchführen.

Fazit

Für wirklich großen und komplexen Business Intelligenz Lösungen, passt optimal nur das Datenbank Verwaltungs-System von Oracle.

Für weniger anspruchsvolle Datenwarenhaus Projekte kann Datenbankveralungssystem von Microsoft eingesetzt werden. Besonders dank „Wizards“ und intuitiv verständlichen Instrumenten für Data/ETL-Prozess Modellierung, wird der Einstieg ins Datenwarenhaus-Welt beim SQL Server kürzer. Im Funktionsumfang und Skalierbarkeit kann SQL Server mit seinem großen Rivalen trotzdem nicht konkurrieren.

Die getesteten Open Source Datenbanken können zurzeit nur bedingt für Business-Intelligenz Projekte verwendet werden. Fehlenden BI-Typischen Funktionen kann man zwar oft nachbilden/simulieren und kostenlose Modellierungs-Werkzeuge gibt es inzwischen auch (Kettle, CloverETL, JasperETL usw.), Open Source Datenbanken können an kommerzielle Datenbanken nicht heranreichen. Es existieren trotzdem erfolgreiche BI Projekte auf Open Source Datenbanken.

Es gibt eine speziell optimierte Version von PostgresSQL mit dem Namen “Bizgress”, die vom “Bizgress Coalition” (Kinetic Networks, JasperSoft, Greenpum) entwickelt wird, mit dem Ziel – ein komplett offenes, kostenloses Datenbank für BI Lösungen zu schaffen. Für nächste Version vom PostgreSQL sind viele neue OLAP Features geplant. Also, PostgreSQL hat noch viel Potential, schließlich ist die weltweit größte Datenbank beim Yahoo auf PostgreSQL gebaut (2 Petabyte Daten von User Aktivitäten).

Obwohl MySQL nicht für OLAP konzipiert ist, unterstütz es erstaunlich viele OLAP Funktionen. Besonders in Kombination mit „Pentaho Open Source Business Intelligence“, kann MySQL als ernsthafter Konkurrent zum Microsoft Produkten angesehen werden. Seit dem Kauf von MySQL durch Sun, sind es auch größere Vorschritte in OLAP Richtung zu erwarten.

Wichtig: Fertige Datawarehouse Lösungen werden viele Produkt-spezifische Funktionen von eingesetztem Datenbankverwaltungsystem verwenden so, dass ein späterer Wechsel der Datenbank fast ausgeschlossen ist. Besonders beim Microsoft SQL Server, dessen proprietäre Transact-SQL manchmal stark von ANSI SQL und anderen Datenbanken abweicht.

Weitere Informationen

TCO-Übersicht
SNAPSHOT Projekt für PostgreSQL (materialized views)
Materialized views mit PL/pgSQL
Oracle udn SQL Server Vergrleich, SQL Tipps.
Oracle: Uderstanding Bitmap Indexes

Veröffentlicht in DWH