비트맵을 사용하여 계층적 집계에 대한 고유 값 계산하기

계층적 집계(예: 여러 그룹화 세트, 롤업 또는 큐브)의 고유 값을 계산하는 경우에는 고유 값을 나타내는 비트맵을 생성하고 이러한 비트맵에서 고유 값의 수를 계산하여 성능을 향상할 수 있습니다. 이 접근 방식을 사용하는 것이 COUNT(DISTINCT <식>) 을 사용하는 것보다 빠를 수 있습니다.

이 항목에서는 비트맵을 사용하여 고유 값의 수를 계산하는 방법을 설명합니다.

고유 값의 수를 계산하는 다른 기법은 고유 값의 수 계산하기 섹션을 참조하십시오.

이 항목의 내용:

소개

계층적 집계(예: 여러 그룹화 세트, 롤업 또는 큐브)에 대한 고유 값 개수를 계산할 때, 가능한 일련의 모든 고유 값을 나타내는 비트맵을 만들고 쿼리하면 보다 빠르게 계산할 수 있습니다.

  • 이 비트맵에서, 데이터에 있는 고유 값에 해당하는 비트를 설정합니다.

  • 고유 값 개수를 계산할 때는 (COUNT(DISTINCT <식>) 를 사용하여 테이블을 쿼리하는 대신) 비트맵에 설정된 비트를 계산하는 비트맵 함수를 사용합니다.

다음과 같은 조건에서 비트맵 함수는 COUNT(DISTINCT <식>) 에 비해 더 효율적일 수 있습니다

  • 쿼리가 고유 값을 카운트하는 계층적 집계(예: 여러 그룹화 세트, 롤업 또는 큐브를 위한)를 수행합니다.

    각 그룹에 대해 실행해야 하는 COUNT(DISTINCT <식>) 과 달리, 비트맵 함수를 호출하면 배트맵을 작성하고 비트맵을 재사용할 수 있습니다. 따라서 쿼리 계획의 비용을 줄일 수 있습니다.

  • 값 범위의 밀도가 높습니다(예: 값이 시퀀스별로 생성됨).

    값 범위의 밀도가 낮을 경우, DENSE_RANK 윈도우 함수를 사용하여 밀도가 낮은 값 범위를 밀도가 높은 값 범위로 변환할 수 있습니다.

  • 값 범위는 좁습니다. 값이 광범위하면 여러 개의 비트맵이 필요할 수 있으며, 이는 기본 메모리에 적합하지 않으므로 디스크에 저장해야 합니다.

또한, 성능을 더욱 개선하려면, 쿼리를 수행하는 동안이 아닌 쿼리를 수행하기 전에 미리 이러한 비트맵을 계산하여(예: 구체화된 뷰에서), 미리 계산된 비트맵을 쿼리에 사용하면 됩니다.

비트맵에서 고유 값을 식별하는 방법 이해하기

비트맵은 BINARY 데이터 타입으로 저장되는 연속 메모리 조각입니다. 비트맵은 효과적으로 개별 설정이 가능한 비트의 배열입니다. 예를 들어, 4바이트 비트맵은 32비트(4바이트 * 바이트당 8비트)로 구성됩니다.

각각의 가능한 고유 값에 대하여, 비트맵의 비트를 사용하여 데이터에 고유 값이 있는지 또는 없는지를 나타낼 수 있습니다. 예를 들어, 데이터에 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 값의 물리적인 비트맵 표현은 다를 수 있습니다.)

Logical representation of a bitmap

고유 값은 비트맵이 포함된 버킷과 해당 비트맵에 설정된 비트의 조합으로 표시됩니다. 특정 값을 나타내는 버킷과 비트를 식별하려면 다음 함수를 사용합니다.

  • BITMAP_BUCKET_NUMBER 를 호출하여 값에 대한 비트를 소유하는 비트맵이 포함된 버킷을 식별합니다.

  • BITMAP_BIT_POSITION 을 호출하여 값에 대한 비트맵 내에서 비트의 0부터 시작하는 위치를 식별합니다.

예를 들어, 숫자 값 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에 대한 비트를 한 번만 설정하며 3이 포함된 추가 행에 대한 비트 값은 변경하지 않습니다.

예를 들어, 숫자 값의 열이 포함된 다음 테이블을 만듭니다. 둘 중 1개의 값이 32768보다 큰 고유 값 2개를 삽입합니다.

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 열에는 실제 비트맵이 아닐 수도 있는 비트맵의 물리적 표현이 포함됩니다. 이 예에서 열에 비트맵을 나타내는 인덱스 벡터가 포함됩니다.

인덱스 벡터는 비트맵 함수가 비트맵의 물리적 표현을 저장하는 한 가지 방법입니다. 비트맵으로 표현되는 값의 수에 따라, 비트맵 함수는 비트맵에 대한 다른 물리적 표현을 사용할 수 있습니다.

비트맵의 바이너리 값이 특정 형식으로 저장될 것으로 예상해서는 안 됩니다. 어떤 비트가 설정되었는지 확인하려면 바이너리 값을 직접 검사하는 대신 비트맵 함수를 사용하십시오.

값이 동일한 추가 행을 삽입해도 결과 비트맵은 변경되지 않습니다. BITMAP_CONSTRUCT_AGG 함수는 고유 값에 대한 비트를 한 번만 설정합니다.

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
언어: 한국어