Berechnung der Anzahl diskreter Werte für hierarchische Aggregationen

Um die Anzahl der Zeilen zu berechnen, die diskrete Werte haben, können Sie die Funktion SQL COUNT aufrufen und das Schlüsselwort DISTINCT verwenden. Wenn Sie nur eine ungefähre Anzahl diskreter Werte benötigen, können Sie die HyperLogLog-Funktionen (z. B. APPROX_COUNT_DISTINCT) verwenden. Weitere Details dazu finden Sie unter Schätzen der Anzahl diskreter Werte.

Wenn Sie diskrete Werte für hierarchische Aggregationen (z. B. mehrere Gruppierungssätze, Rollups oder Cubes) zählen, können Sie die Verarbeitungsleistung verbessern, indem Sie anstelle von COUNT(DISTINCT <Ausdruck>) Bitmaps erstellen und verwenden.

Unter diesem Thema wird erläutert, wie Sie Bitmaps erstellen und zur Berechnung der Anzahl diskreter Werte verwenden.

Unter diesem Thema:

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.)

Logical representation of a bitmap

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.

select bitmap_bucket_number(32769), bitmap_bit_position(32769);
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 |
+-----------+----------------------+

Beachten Sie, dass der in der Spalte BITMAP angezeigte Binärwert keine tatsächliche Bitmap ist. Die physische Darstellung der Bitmap wird von den Bitmap-Funktionen verwaltet und kann sich je nach Anzahl der repräsentierten Werte ändern. Sie sollten nicht erwarten, dass der Binärwert der Bitmap in einem bestimmten Format vorliegt.

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.

Berechnung der Anzahl diskreter Werte aus den Bitmaps

Um die Gesamtzahl der diskreten Werte aus den Bitmaps zu erhalten, rufen Sie BITMAP_BIT_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

Angenommen, Sie möchten den folgenden Befehl ausführen, um die Anzahl diskreter Werte in Spalte zu zählen:

-- Original query using COUNT(DISTINCT <expression>)
SELECT COUNT(DISTINCT <col>) FROM <table>;

Sie können diese Abfrage ändern und stattdessen Bitmap-Funktionen verwenden:

-- The same query using bitmap functions
SELECT SUM(cnt) FROM
(
SELECT
  BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(<col>))) cnt
FROM <table>
GROUP BY BITMAP_BUCKET_NUMBER(<col>)
);

Wenn der Wertebereich in Spalte 0 bis 32.768 ist, können Sie stattdessen diese einfachere Anweisung verwenden:

-- If the full value range of <col> fits into the bitmap:
-- MIN(<col>) >= 0 AND MAX(<col>) < 32,768
SELECT
  BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(<col>))
FROM <table>;

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

Angenommen, Sie möchten den folgenden Befehl ausführen, um die Anzahl diskreter Werte in Spalte nach Schlüssel zu zählen:

-- Original query using COUNT(DISTINCT <expression>)
SELECT COUNT(DISTINCT <col>) FROM <table> GROUP BY <keys>;

Sie können diese Abfrage ändern und stattdessen Bitmap-Funktionen verwenden:

-- The same query using bitmap functions
SELECT <keys>, SUM(cnt) FROM
(
SELECT
  <keys>,
  BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(<col>))) cnt
FROM <table>
GROUP BY <keys>, BITMAP_BUCKET_NUMBER(<col>)
)
GROUP BY <keys>;

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

Bitmap-Funktionen sind bei Verwendung von GROUP BY ROLLUP-Aggregatabfragen noch effizienter. Bitmaps sind zusammensetzbar (im Gegensatz zu COUNT(DISTINCT <Ausdruck>)), was den Verarbeitungsaufwand und damit die Ausführungszeiten verringert.

Angenommen, Sie möchten den folgenden Befehl ausführen, um ein Rollup für die Anzahl diskreter Werte in Spalte nach Schlüsseln auszuführen:

-- Original query using COUNT(DISTINCT <expression>)
SELECT COUNT(DISTINCT <col>) FROM <table> GROUP BY ROLLUP(<keys>);

Sie können diese Abfrage ändern und stattdessen Bitmap-Funktionen verwenden:

-- The same query using bitmap functions
SELECT <keys>, SUM(cnt) FROM
(
SELECT
  <keys>,
  BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(<col>))) cnt
  FROM <table>
  GROUP BY ROLLUP(<keys>), BITMAP_BUCKET_NUMBER(<col>)
)
GROUP BY <keys>;

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.

-- precomputation table
CREATE TABLE precompute AS
SELECT
  <dim1>,
  <dim2>,
  BITMAP_BUCKET_NUMBER(<col>) bucket,
  BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(<col>)) bmp
FROM <table>
GROUP BY 1, 2, 3;

-- compute final aggregate for <dim1> and <dim2>
SELECT
  <dim1>,
  <dim2>,
  SUM(BITMAP_COUNT(bmp))
FROM precompute
GROUP BY 1, 2;

-- compute final aggregate for <dim1>-only
SELECT <dim1>, SUM(cnt) FROM
(
SELECT
  <dim1>,
  BITMAP_COUNT(BITMAP_OR_AGG(bmp)) cnt
FROM precompute
GROUP BY 1, bucket
)
GROUP BY 1;

-- compute final aggregate for <dim2>-only
SELECT <dim2>, SUM(cnt) FROM
(
SELECT
  <dim2>,
  BITMAP_COUNT(BITMAP_OR_AGG(bmp)) cnt
FROM precompute
GROUP BY 1, bucket
)
GROUP BY 1;