Verwenden von Bitmaps zum Berechnen diskreter Werte für hierarchische Aggregationen¶

Einführung¶

Wenn Sie die Anzahl der diskreten Werte für hierarchische Aggregationen (z. B. mehrere Gruppierungssätze, Rollups oder Cubes) berechnen, können Sie die Berechnung beschleunigen, indem Sie eine Bitmap erstellen, die die Menge aller möglichen diskreten Werte repräsentiert, und diese Bitmap dann abfragen.

• In dieser Bitmap legen Sie die Bits fest, die den diskreten Werten in den Daten entsprechen.

• Bei der Berechnung der Anzahl der diskreten Werte verwenden Sie die Bitmap-Funktionen, um die Bits zu zählen, die in der Bitmap gesetzt sind (anstatt die Tabelle mit COUNT(DISTINCT <Ausdruck>) abzufragen).

Die Bitmap-Funktionen bieten unter den folgenden Bedingungen eine bessere Leistung als COUNT(DISTINCT <Ausdruck>):

• Die Abfrage führt eine hierarchische Aggregation aus (z. B. für mehrere Gruppierungssätze, Rollups oder Cubes), die diskrete Werte zählt.

  Im Gegensatz zu COUNT(DISTINCT <Ausdruck>) (das für jede Gruppe ausgeführt werden muss), können Sie Bitmaps zusammenstellen und wiederverwenden, indem Sie die Bitmap-Funktionen aufrufen. Dadurch können die Kosten für den Abfrageplan gesenkt werden.

• Der Wertebereich ist von hoher Dichte (z. B. wird der Wert durch eine Sequenz generiert).

  Wenn der Wertebereich von geringer Dichte ist, können Sie die Fensterfunktion DENSE_RANK verwenden, um den Wertebereich geringer Dichte in einen Wertebereich hoher Dichte umzuwandeln.

• Der Wertebereich ist klein. Ein großer Wertebereich kann mehrere Bitmaps erfordern, die zu groß für den Hauptspeicher sind und daher auf der Festplatte gespeichert werden müssen.

Um die Leistung weiter zu verbessern, können Sie die Berechnung dieser Bitmaps bereits im Voraus (z. B. in einer materialisierten Ansicht) und nicht erst während der Abfrage durchführen und dann die vorberechneten Bitmaps in der Abfrage verwenden.

Erläuterungen zum Identifizieren diskreter Werte mit Bitmaps¶

Eine Bitmap ist ein zusammenhängender Speicherbereich, der als Datentyp BINARY gespeichert wird. Eine Bitmap ist im Grunde eine Anordnung von Bits, die einzeln festgelegt werden können. Beispielsweise besteht eine 4-Byte-Bitmap aus 32 Bits (4 Bytes * 8 Bits pro Byte).

Für jeden möglichen diskreten Wert können Sie ein Bit in der Bitmap verwenden, um das Vorhandensein oder Nichtvorhandensein des diskreten Wertes in den Daten zu repräsentieren. Wenn beispielsweise die Werte 3 und 5 in den Daten vorhanden sind, können Sie das 3. und 5. Bit in der Bitmap auf 1 setzen. (Wenn die diskreten Werte keine numerischen Werte sind, müssen Sie die Werte auf numerische Werte abbilden).

Für die Bitmap-Funktionen in Snowflake ist die Standardgröße einer Bitmap 32.768 Bit (4 KiB). Beachten Sie, dass diese Größe nicht mit der physischen Größe des BINARY-Wertes übereinstimmt. Intern verwalten die Bitmap-Funktionen die physische Repräsentation der Bitmap, die nicht unbedingt eine echte Bitmap ist. (Die Funktionen könnten zum Beispiel einen Indexvektor verwenden.) Die physische Größe einer Bitmap kann zwischen 10 Bytes und 4.108 Bytes betragen.

Wenn die Anzahl der diskreten Werte größer ist als 32.768 Bits, werden mehrere Bitmaps benötigt, um alle Werte zu repräsentieren. Der Prozess der Aufteilung der Bits für diskrete Werte in verschiedene Bitmaps wird als Bucketisierung bezeichnet. Beispielsweise werden die Bits für die diskreten Werte im Bereich 1 bis 65.536 auf zwei getrennte Buckets aufgeteilt (bucketisiert). Die Bitmap in einem Bucket repräsentiert die Werte 1 bis 32.768, und die Bitmap im anderen Bucket repräsentiert die Werte 32.769 bis 65.536. Die Bitmap in jedem Bucket enthält eine Teilmenge der Bits, die die diskreten Werte repräsentieren.

Die folgende Abbildung zeigt die logische Repräsentation einer Bitmap. (Wie bereits erwähnt, kann die physische Repräsentation der Bitmap im BINARY-Wert unterschiedlich sein.)

Ein diskreter Wert wird durch die Kombination aus einem Bucket, der eine Bitmap enthält, und einem Bit, das in dieser Bitmap gesetzt ist, repräsentiert. Um den Bucket und das Bit zu identifizieren, das einen bestimmten Wert repräsentiert, verwenden Sie die folgenden Funktionen:

• Rufen Sie BITMAP_BUCKET_NUMBER auf, um den Bucket zu identifizieren, der die Bitmap enthält, die das Bit für den Wert enthält.

• Rufen Sie BITMAP_BIT_POSITION auf, um die nullbasierte Position des Bits innerhalb der Bitmap für den Wert zu ermitteln.

Beispielsweise wird der numerische Wert „1“ durch das Bit an der Position 0 der Bitmap 1 wie folgt repräsentiert:

select bitmap_bucket_number(1), bitmap_bit_position(1);

+-------------------------+------------------------+
| BITMAP_BUCKET_NUMBER(1) | BITMAP_BIT_POSITION(1) |
|-------------------------+------------------------|
|                       1 |                      0 |
+-------------------------+------------------------+

Der numerische Wert 32.768 wird durch das Bit an Position 32.767 der Bitmap 1 repräsentiert:

select bitmap_bucket_number(32768), bitmap_bit_position(32768);

+-----------------------------+----------------------------+
| BITMAP_BUCKET_NUMBER(32768) | BITMAP_BIT_POSITION(32768) |
|-----------------------------+----------------------------|
|                           1 |                      32767 |
+-----------------------------+----------------------------+

In einem weiteren Beispiel wird der numerische Wert 32.769 durch das Bit an Position 0 der Bitmap 2 repräsentiert:

select bitmap_bucket_number(32769), bitmap_bit_position(32769);

+-----------------------------+----------------------------+
| BITMAP_BUCKET_NUMBER(32769) | BITMAP_BIT_POSITION(32769) |
|-----------------------------+----------------------------|
|                           2 |                          0 |
+-----------------------------+----------------------------+

Erstellen von Bitmaps¶

Um Bitmaps zu erstellen, die alle möglichen diskreten Werte repräsentieren, rufen Sie die Funktion BITMAP_CONSTRUCT_AGG in einer SELECT-Anweisung auf:

1. Übergeben Sie den von BITMAP_BIT_POSITION zurückgegebenen Wert für die Spalte an die Funktion BITMAP_CONSTRUCT_AGG.

2. Wählen Sie über die SELECT-Anweisung BITMAP_BUCKET_NUMBER aus, und verwenden Sie GROUP BY, um die Ergebnisse für eine bestimmte Bitmap (identifiziert durch die Bucketnummer) zu aggregieren.

BITMAP_CONSTRUCT_AGG ist eine Aggregationsfunktion. Aggregation bedeutet in diesem Kontext, dass das Bit für einen diskreten Wert gesetzt wird, wenn eine Zeile diesen diskreten Wert aufweist. Wenn mehrere Zeilen den Wert „3“ enthalten, setzt BITMAP_CONSTRUCT_AGG das Bit für „3“ nur einmal, und ändert den Wert des Bits für die weiteren Zeilen, die „3“ enthalten, nicht.

Erstellen Sie zum Beispiel die folgende Tabelle, die eine Spalte mit numerischen Werten enthält. Fügen Sie zwei unterschiedliche Werte ein, von denen einer größer als 32.768 ist.

CREATE OR REPLACE TABLE bitmap_test_values (val INT);
insert into bitmap_test_values values (1), (32769);

Führen Sie den folgenden Befehl aus, um Bitmaps mit Bits zu erzeugen, die die diskreten Werte repräsentieren:

-- Display the bitmap in hexadecimal
alter session set binary_output_format='hex';

select bitmap_bucket_number(val) as bitmap_id,
    bitmap_construct_agg(bitmap_bit_position(val)) as bitmap
    from bitmap_test_values
    group by bitmap_id;

+-----------+----------------------+
| BITMAP_ID | BITMAP               |
|-----------+----------------------|
|         1 | 00010000000000000000 |
|         2 | 00010000000000000000 |
+-----------+----------------------+

Das Einfügen weiterer Zeilen mit denselben Werten führt zu keiner Änderung der resultierenden Bitmap. Die Funktion BITMAP_CONSTRUCT_AGG setzt das Bit für einen diskreten Wert nur einmal.

insert into bitmap_test_values values (32769), (32769), (1);

select bitmap_bucket_number(val) as bitmap_id,
    bitmap_construct_agg(bitmap_bit_position(val)) as bitmap
    from bitmap_test_values
    group by bitmap_id;

+-----------+----------------------+
| BITMAP_ID | BITMAP               |
|-----------+----------------------|
|         1 | 00010000000000000000 |
|         2 | 00010000000000000000 |
+-----------+----------------------+

Das Einfügen anderer diskreter Werte erzeugt eine andere Bitmap, in der die entsprechenden Bits für diese Werte gesetzt sind.

insert into bitmap_test_values values (2), (3), (4);

select bitmap_bucket_number(val) as bitmap_id,
    bitmap_construct_agg(bitmap_bit_position(val)) as bitmap
    from bitmap_test_values
    group by bitmap_id;

+-----------+----------------------+
| BITMAP_ID | BITMAP               |
|-----------+----------------------|
|         1 | 00040000010002000300 |
|         2 | 00010000000000000000 |
+-----------+----------------------+

Aggregieren von Bitmaps¶

Wenn Sie verschiedene Bitmaps in demselben Bucket aggregieren müssen (identifiziert durch die Bucketnummer, die von BITMAP_BUCKET_NUMBER zurückgegeben wird), rufen Sie BITMAP_OR_AGG auf.

Berechnen der Anzahl diskreter Werte aus den Bitmaps¶

Um die Gesamtzahl der diskreten Werte aus den Bitmaps zu erhalten, rufen Sie BITMAP_COUNT auf und übergeben eine mit BITMAP_CONSTRUCT_AGG oder BITMAP_OR_AGG erstellte Bitmap.

Beispiel:

select bitmap_bucket_number(val) as bitmap_id,
    bitmap_count(bitmap_construct_agg(bitmap_bit_position(val))) as distinct_values
    from bitmap_test_values
    group by bitmap_id;

+-----------+-----------------+
| BITMAP_ID | DISTINCT_VALUES |
|-----------+-----------------|
|         1 |               4 |
|         2 |               1 |
+-----------+-----------------+

Verwenden von Bitmaps zur Verbesserung der Abfrageleistung¶

Die folgenden Beispiele zeigen, wie die Bitmap-Funktionen als Alternative zu COUNT(DISTINCT <Ausdruck>) verwendet werden kann.

• Beispiel 1: Zählen der diskreten Werte in einer einzelnen Tabelle

• Beispiel 2: Verwenden von GROUP BY zur Berechnung der Anzahl nach Gruppen

• Beispiel 3: Verwenden von GROUP BY ROLLUP für Rollup von Zählungen nach Gruppe

SELECT
  COUNT(DISTINCT my_column)
FROM my_table;

SELECT SUM(cnt) FROM (
  SELECT
    BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(my_column))) cnt
  FROM my_table
  GROUP BY BITMAP_BUCKET_NUMBER(my_table)
);

SELECT
  my_key_1,
  my_key_2,
  COUNT(DISTINCT my_column)
FROM my_table
GROUP BY my_key_1, my_key_2;

SELECT my_key_1, my_key_2, SUM(cnt) FROM (
  SELECT
    my_key_1,
    my_key_2,
    BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(my_column))) cnt
  FROM my_table
  GROUP BY my_key_1, my_key_2, BITMAP_BUCKET_NUMBER(my_column)
)
GROUP BY my_key_1, my_key_2;

SELECT
  my_key_1,
  my_key_2,
  COUNT(DISTINCT my_column)
FROM my_table
GROUP BY ROLLUP(my_key_1, my_key_2);

SELECT my_key_1, my_key_2, SUM(cnt) FROM (
  SELECT
    my_key_1,
    my_key_2,
    BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(my_column))) cnt
  FROM my_table
  GROUP BY ROLLUP(my_key_1, my_key_2), BITMAP_BUCKET_NUMBER(my_column)
)
GROUP BY my_key_1, my_key_2;

Vorberechnung der Bitmaps¶

Um die Leistung zu verbessern, können Sie die Anzahl diskreter Werte einer Tabelle oder materialisierten Ansicht vorberechnen.

Angenommen, Ihr Data Warehouse enthält eine Faktentabelle mit mehreren Dimensionen. Sie können eine materialisierte Ansicht definieren, die die Bitmaps konstruiert, um eine grobe Vorberechnung oder Voraggregation durchzuführen, bevor die endgültigen Aggregate oder Cubes berechnet werden, die die Verwendung von COUNT(DISTINCT <Ausdruck>) erfordern.

Im folgenden Beispiel wird eine Tabelle erstellt, die die Bitmaps enthält. Diese Tabelle wird dann verwendet, um die Anzahl diskreter Werte zu berechnen, die nach verschiedenen Dimensionen aggregiert werden.

Mit der folgenden Anweisung wird eine Tabelle mit dem Namen precompute erstellt, die die Bitmaps und Bucket-Informationen enthält:

CREATE TABLE precompute AS
SELECT
  my_dimension_1,
  my_dimension_2,
  BITMAP_BUCKET_NUMBER(my_column) bucket,
  BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(my_column)) bmp
FROM my_table
GROUP BY 1, 2, 3;

Mit der folgenden Anweisung werden die Aggregate für my_dimension_1 und my_dimension_2 berechnet:

SELECT
  my_dimension_1,
  my_dimension_2,
  SUM(BITMAP_COUNT(bmp))
FROM precompute
GROUP BY 1, 2;

Die folgende Anweisung berechnet das Aggregat nur für my_dimension_1:

SELECT my_dimension_1, SUM(cnt) FROM (
  SELECT
    my_dimension_1,
    BITMAP_COUNT(BITMAP_OR_AGG(bmp)) cnt
  FROM precompute
  GROUP BY 1, bucket
)
GROUP BY 1;

Die folgende Anweisung berechnet das Aggregat nur für my_dimension_2:

SELECT my_dimension_2, SUM(cnt) FROM (
  SELECT
    my_dimension_2,
    BITMAP_COUNT(BITMAP_OR_AGG(bmp)) cnt
  FROM precompute
  GROUP BY 1, bucket
)
GROUP BY 1;