ビットマップを使用した、階層的な集計の異なる値の計算

階層的な集計（例: 複数のグループ化セット、ロールアップ、キューブ）の異なる値をカウントする場合は、異なる値を表すビットマップを作成し、これらのビットマップから異なる値の数を計算すると、パフォーマンスを向上させることができます。このアプローチを使用すると、 COUNT(DISTINCT <式>) を使用するよりも高速になります。

このトピックでは、ビットマップを使用して異なる値をカウントする方法について説明します。

異なる値をカウントする他の手法については、 異なる値の数の計算 をご参照ください。

このトピックの内容:

紹介

階層的な集計（例: 複数のグループ化セット、ロールアップ、キューブ）の異なる値の数を計算する場合は、考えられるすべての異なる値のセットを表すビットマップを生成してクエリを実行すると、計算を高速化できます。

  • このビットマップでは、データに存在する異なる値に対応するビットを設定します。

  • 異なる値の数を計算するときは、ビットマップ関数を使用して（COUNT(DISTINCT <式>) でテーブルをクエリするのではなく）、ビットマップに設定されているビットをカウントします。

ビットマップ関数は、次の条件下で COUNT(DISTINCT <式>) よりも優れたパフォーマンスを発揮します。

  • クエリは、異なる値をカウントする階層的な集計（例: 複数のグループ化セット、ロールアップ、またはキューブ）を実行します。

    COUNT(DISTINCT <式>) （各グループに対して実行が必要）とは異なり、ビットマップ関数を呼び出すことでビットマップを作成して再利用できます。これにより、クエリプランのコストを削減できます。

  • 値の範囲は密です（例: 値はシーケンスによって生成）

    値の範囲がスパースの場合は、 DENSE_RANK ウィンドウ関数を使用して、スパースな値の範囲を密な値の範囲に変換できます。

  • 値の範囲は狭くなります。値の範囲が広いと、メインメモリに収まらない複数のビットマップが必要になる場合があり、ディスクに保存する必要が生じます。

加えて、パフォーマンスをさらに向上させるために、クエリ中ではなく、事前に（例: マテリアライズドビュー内で）これらのビットマップを計算できます。また、これらの事前計算されたビットマップをクエリで使用できます。

異なる値をビットマップが識別する方法を理解

ビットマップは、 BINARY データ型として保存される連続したメモリです。ビットマップは事実上、個別に設定できるビットの配列です。たとえば、4バイトのビットマップは32ビット（4バイト * 8ビット/バイト）で構成されます。

可能性のある異なる値ごとに、ビットマップのビットを使用して、データ内の異なる値の有無を表すことができます。たとえば、値3と5がデータに存在する場合は、ビットマップの3番目と5番目のビットを1に設定できます。（異なる値が数値でない場合は、値を数値にマップする必要があります。）

Snowflakeのビットマップ関数の場合、ビットマップのデフォルトサイズは32,768ビット（4 KiB）です。このサイズは、 BINARY 値の物理サイズに対応していないことに注意してください。内部的には、ビットマップ関数はビットマップの物理的表現を管理しますが、これは実際のビットマップではない場合があります。（たとえば、関数はインデックスベクトルを使用する場合があります。）ビットマップの物理サイズは、10バイトから4108バイトまでさまざまです。

異なる値の数が32,768ビットより大きい場合は、すべての値を表すために複数のビットマップが必要です。異なる値のビットを異なるビットマップに分割するプロセスは、バケット化と呼ばれます。たとえば、1～65,536の範囲にある異なる値のビットは、2つの別々のバケットにバケット化されます。一方のバケットのビットマップは値1～32,768を表し、もう一方のバケットのビットマップは値32,769～65,536を表します。各バケットのビットマップには、異なる値を表すビットのサブセットが含まれています。

次の図は、ビットマップの論理表現を示しています。（前述のように、 BINARY 値のビットマップの物理的表現は異なる場合があります。）

ビットマップの論理表現

異なる値は、ビットマップを含むバケットとそのビットマップに設定されているビットの組み合わせで表されます。特定の値を表すバケットとビットを識別するには、次の関数を使用します。

  • BITMAP_BUCKET_NUMBER を呼び出して、値のビットを持つビットマップを含むバケットを識別します。

  • BITMAP_BIT_POSITION を呼び出して、値のビットマップ内にあるビットのゼロベースの位置を識別します。

たとえば、数値1は、ビットマップ1の位置0のビットで表されます。

select bitmap_bucket_number(1), bitmap_bit_position(1);

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

数値32,768は、ビットマップ1の位置32,767のビットで表されます。

select bitmap_bucket_number(32768), bitmap_bit_position(32768);

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

別の例として、数値32,769は、ビットマップ2の位置0のビットで表されます。

select bitmap_bucket_number(32769), bitmap_bit_position(32769);

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

ビットマップの作成

考えられるすべての異なる値を表すビットマップを作成するには、 SELECT ステートメントで BITMAP_CONSTRUCT_AGG 関数を呼び出します。

  1. BITMAP_BIT_POSITION によって返された列の値を BITMAP_CONSTRUCT_AGG 関数に渡します。

  2. SELECT ステートメントで BITMAP_BUCKET_NUMBER を選択し、 GROUP BY を使用して特定のビットマップ（「バケット番号」で識別）の結果を集計します。

BITMAP_CONSTRUCT_AGG は、集計関数です。このコンテキストでの集計とは、いずれかの行に異なる値がある場合に、異なる値のビットを設定することを意味します。複数の行に値3が含まれている場合、 BITMAP_CONSTRUCT_AGG は3に対するビットを1回設定するだけで、3を含む追加の行のビットに対する値は変更しません。

たとえば、数値の列を含む次のテーブルを作成します。2つの異なる値を挿入します。そのうちの1つは32768より大きい値です。

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

次のコマンドを実行して、異なる値を表すビットを含むビットマップを生成します。

-- 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 |
+-----------+----------------------+
Copy

注釈

BITMAP 列には、ビットマップの物理的な表現が含まれていますが、これは必ずしも実際のビットマップではありません。この例では、列にビットマップを表すインデックスベクトルが含まれています。

インデックスベクトルは、ビットマップ関数がビットマップの物理的表現を保存する1つの方法です。ビットマップによって表される値の数に応じて、ビットマップ関数は、ビットマップに対して異なる物理表現を使用できます。

ビットマップのバイナリ値が特定の形式で保存されることは期待できません。設定されているビットを判別するには、（バイナリ値を自分で調べるのではなく）ビットマップ関数を使用します。

同じ値で追加の行を挿入しても、結果のビットマップは変更されません。 BITMAP_CONSTRUCT_AGG 関数は、異なる値のビットを1回だけ設定します。

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 |
+-----------+----------------------+
Copy

他の異なる値を挿入すると、それらの値に対応するビットが設定された別のビットマップが生成されます。

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 |
+-----------+----------------------+
Copy

ビットマップの集計

同じバケット（BITMAP_BUCKET_NUMBER によって返されるバケット番号で識別）に異なるビットマップを集計する必要がある場合は、 BITMAP_OR_AGG を呼び出します。

ビットマップからの異なる値の数を計算

ビットマップから異なる値の総数を取得するには、 BITMAP_COUNT を呼び出し、 BITMAP_CONSTRUCT_AGG または BITMAP_OR_AGG によって作成されたビットマップを渡します。

例:

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 |
+-----------+-----------------+
Copy

ビットマップの使用による、クエリのパフォーマンスの向上

次の例は、 COUNT(DISTINCT <式>) の代わりにビットマップ関数を使用する方法を示しています。

例1: 単一のテーブル内にある異なる値のカウント

my_column の異なる値の数をカウントするとします。次のテーブルでは、このタスクを実行するための SQL ステートメントを COUNT(DISTINCT expression) およびビットマップ関数と比較しています。

COUNT(DISTINCT <式>)の例

ビットマップ関数の使用例

 
SELECT
  COUNT(DISTINCT my_column)
FROM my_table;
Copy
 
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)
);
Copy

my_column の値の範囲が0から32,768の場合は、代わりに次の簡単なステートメントを使用できます。

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

例2: GROUP BY を使用した、グループごとのカウントの計算

my_key_1 と my_key_2 により、 my_column 内の異なる値の数をカウントするとします。次のテーブルでは、このタスクを実行するための SQL ステートメントを COUNT(DISTINCT expression) およびビットマップ関数と比較しています。

COUNT(DISTINCT <式>)の例

ビットマップ関数の使用例

 
SELECT
  my_key_1,
  my_key_2,
  COUNT(DISTINCT my_column)
FROM my_table
GROUP BY my_key_1, my_key_2;
Copy
 
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;
Copy

例3: GROUP BY ROLLUP を使用した、グループごとのカウントのロールアップ

ビットマップ関数は、 GROUP BY ROLLUP 集計クエリに対してさらに効率的に機能します。ビットマップは（COUNT(DISTINCT <式>) とは対照的に）コンポーザブルであるため、計算作業が少なくなり、実行時間が短くなります。

my_key_1 と my_key_2 により、 my_column 内の異なる値の数をロールアップするとします。次のテーブルでは、このタスクを実行するための SQL ステートメントを COUNT(DISTINCT expression) およびビットマップ関数と比較しています。

COUNT(DISTINCT <式>)の例

ビットマップ関数の使用例

 
SELECT
  my_key_1,
  my_key_2,
  COUNT(DISTINCT my_column)
FROM my_table
GROUP BY ROLLUP(my_key_1, my_key_2);
Copy
 
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;
Copy

ビットマップの事前計算

パフォーマンスを向上させるために、テーブルまたはマテリアライズドビューの異なる値のカウントを事前計算できます。

たとえば、データウェアハウスに複数のディメンションを持つファクトテーブルが含まれているとします。 COUNT(DISTINCT <式>) を必要とする最終的な集計またはキューブを計算する前に、ビットマップを構築して粒度の荒い事前計算または事前集計を実行するマテリアライズドビューを定義できます。

次の例では、ビットマップを含むテーブルを作成し、このテーブルを使用して、さまざまなディメンションで集計された異なる値の数を計算します。

次のステートメントは、ビットマップとバケット情報を含む precompute という名前のテーブルを作成します。

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;
Copy

次のステートメントは、 my_dimension_1 と my_dimension_2 の集計を計算します。

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

次のステートメントは、 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;
Copy

次のステートメントは、 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;
Copy
言語: 日本語