이 섹션에는 Amazon Redshift에서 HyperLogLog를 사용하는 예제가 포함되어 있습니다.
주제
예: 하위 쿼리에서 카디널리티 반환
다음 예에서는 Sales라는 테이블에 대한 하위 쿼리의 각 스케치에 대한 카디널리티를 반환합니다.
CREATE TABLE Sales (customer VARCHAR, country VARCHAR, amount BIGINT);
INSERT INTO Sales VALUES ('David Joe', 'Greece', 14.5), ('David Joe', 'Greece', 19.95), ('John Doe', 'USA', 29.95), ('John Doe', 'USA', 19.95), ('George Spanos', 'Greece', 9.95), ('George Spanos', 'Greece', 2.95);다음 쿼리는 각 국가의 고객에 대한 HLL 스케치를 생성하고 카디널리티를 추출합니다. 이는 각 국가의 고유 고객을 보여줍니다.
SELECT hll_cardinality(sketch), country
FROM (SELECT hll_create_sketch(customer) AS sketch, country
FROM Sales
GROUP BY country) AS hll_subquery;
hll_cardinality | country
----------------+---------
1 | USA
2 | Greece
...예: 하위 쿼리의 결합된 스케치에서 HLLSKETCH 형식 반환
다음 예에서는 하위 쿼리의 개별 스케치 조합을 나타내는 단일 HLLSKETCH 형식을 반환합니다. 스케치는 HLL_COMBINE 집계 함수를 사용하여 결합됩니다.
SELECT hll_combine(sketch)
FROM (SELECT hll_create_sketch(customer) AS sketch
FROM Sales
GROUP BY country) AS hll_subquery
hll_combine
--------------------------------------------------------------------------------------------
{"version":1,"logm":15,"sparse":{"indices":[29808639,35021072,47612452],"values":[1,1,1]}}
(1 row)
예: 여러 스케치를 결합하여 HyperLogLog 스케치 반환
다음 예의 경우 테이블 page-users에 사용자가 지정된 웹 사이트에서 방문한 각 페이지에 대해 사전 집계된 스케치가 저장되어 있다고 가정합니다. 이 테이블의 각 행에는 방문한 페이지를 표시하는 모든 사용자 ID를 나타내는 HyperLogLog 스케치가 포함되어 있습니다.
page_users
-- +----------------+-------------+--------------+
-- | _PARTITIONTIME | page | sketch |
-- +----------------+-------------+--------------+
-- | 2019-07-28 | homepage | CHAQkAQYA... |
-- | 2019-07-28 | Product A | CHAQxPnYB... |
-- +----------------+-------------+--------------+다음 예에서는 미리 집계된 여러 스케치를 결합하고 단일 스케치를 생성합니다. 이 스케치는 각 스케치가 캡슐화하는 집합적 카디널리티를 캡슐화합니다.
SELECT hll_combine(sketch) as sketch
FROM page_users출력 결과는 다음과 비슷합니다.
-- +-----------------------------------------+
-- | sketch |
-- +-----------------------------------------+
-- | CHAQ3sGoCxgCIAuCB4iAIBgTIBgqgIAgAwY.... |
-- +-----------------------------------------+새 스케치가 생성되면 다음과 같이 HLL_CARDINALITY 함수를 사용하여 집합적 고유 값을 얻을 수 있습니다.
SELECT hll_cardinality(sketch)
FROM (
SELECT
hll_combine(sketch) as sketch
FROM page_users
) AS hll_subquery
출력 결과는 다음과 비슷합니다.
-- +-------+
-- | count |
-- +-------+
-- | 54356 |
-- +-------+예: 외부 테이블을 사용하여 S3 데이터에 대한 HyperLogLog 스케치 생성
다음 예에서는 카디널리티 추정을 위해 Amazon S3 직접 액세스하지 않도록 HyperLogLog 스케치를 캐시합니다.
Amazon S3 데이터를 보유하도록 정의된 외부 테이블에서 HyperLogLog 스케치를 사전 집계하고 캐시할 수 있습니다. 이렇게 하면 기본 데이터에 액세스하지 않고도 카디널리티 추정을 추출할 수 있습니다.
예를 들어 탭으로 구분된 텍스트 파일 집합을 Amazon S3로 언로드했다고 가정합니다. 다음 쿼리를 실행하여 spectrum이라는 Amazon Redshift 외부 스키마에서 sales라는 외부 테이블을 정의합니다. 이 예에서 Amazon S3 버킷은 미국 동부(버지니아 북부) AWS 리전에 있습니다.
create external table spectrum.sales(
salesid integer,
listid integer,
sellerid smallint,
buyerid smallint,
eventid integer,
dateid integer,
qtysold integer,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp)
row format delimited
fields terminated by '\t' stored as textfile
location 's3://redshift-downloads/tickit/spectrum/sales/';임의의 날짜에 항목을 구매한 개별 구매자를 계산한다고 가정합니다. 이를 위해 다음 예에서는 연중 매일 구매자 ID에 대한 스케치를 생성하고 Amazon Redshift 테이블 hll_sales에 결과를 저장합니다.
CREATE TABLE hll_sales AS
SELECT saletime, hll_create_sketch(buyerid) AS sketch
FROM spectrum.sales
GROUP BY saletime;
SELECT TOP 5 * FROM hll_sales;출력 결과는 다음과 비슷합니다.
-- hll_sales
-- | saletime | sketch |
-- +-----------------+---------------------------------------------------------------------+
-- | 7/22/2008 8:30 | {"version":1,"logm":15,"sparse":{"indices":[9281416],"values":[1]}}
-- | 2/19/2008 0:38 | {"version":1,"logm":15,"sparse":{"indices":[48735497],"values":[3]}}
-- | 11/5/2008 4:49 | {"version":1,"logm":15,"sparse":{"indices":[27858661],"values":[1]}}
-- | 10/27/2008 4:08 | {"version":1,"logm":15,"sparse":{"indices":[65295430],"values":[2]}}
-- | 2/16/2008 9:37 | {"version":1,"logm":15,"sparse":{"indices":[56869618],"values":[2]}}
-- +---------------- +---------------------------------------------------------------------+다음 쿼리는 2008년 추수 감사절 후 금요일에 항목을 구매한 예상 개별 구매자 수를 보여줍니다.
SELECT hll_cardinality(hll_combine(sketch)) as distinct_buyers
FROM hll_sales
WHERE trunc(saletime) = '2008-11-28'; 출력 결과는 다음과 비슷합니다.
distinct_buyers
---------------
386특정 날짜 범위에 항목을 구매한 고유한 사용자 수를 원한다고 가정합니다. 추수 감사절 후 금요일부터 다음 월요일까지를 예로 들 수 있습니다. 이를 얻기 위해 다음 쿼리는 hll_combine 집계 함수를 사용합니다. 이 함수를 사용하면 선택한 범위의 하루 이상 항목을 구매한 구매자가 이중으로 계산되는 것을 방지할 수 있습니다.
SELECT hll_cardinality(hll_combine(sketch)) as distinct_buyers
FROM hll_sales
WHERE saletime BETWEEN '2008-11-28' AND '2008-12-01';출력 결과는 다음과 비슷합니다.
distinct_buyers
---------------
1166hll_sales 테이블을 최신 상태로 유지하려면 하루가 끝날 때 다음 쿼리를 실행합니다. 이렇게 하면 오늘 항목을 구매한 구매자의 ID를 기반으로 HyperLogLog 스케치가 생성되어 hll_sales 테이블에 추가됩니다.
INSERT INTO hll_sales
SELECT saletime, hll_create_sketch(buyerid)
FROM spectrum.sales
WHERE TRUNC(saletime) = to_char(GETDATE(), 'YYYY-MM-DD')
GROUP BY saletime;