Amazon 고객 리뷰 | ClickHouse Docs

이 데이터셋에는 Amazon 제품에 대한 고객 리뷰가 1억 5천만 건 이상 포함되어 있습니다. 데이터는 AWS S3에 있는 snappy 압축 Parquet 파일로 제공되며, 전체 크기는 49GB(압축 기준)입니다. 이제 이를 ClickHouse에 삽입하는 단계를 차례대로 살펴보겠습니다.

데이터셋 불러오기

데이터를 ClickHouse에 삽입하지 않고도 원본 위치에서 바로 쿼리할 수 있습니다. 어떤 모습인지 확인할 수 있도록 몇 개의 행을 가져오겠습니다:

SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/amazon_reviews/amazon_reviews_2015.snappy.parquet', NOSIGN)
LIMIT 3

행은 다음과 같습니다:

Row 1:
──────
review_date:       16462
marketplace:       US
customer_id:       25444946 -- 25.44 million
review_id:         R146L9MMZYG0WA
product_id:        B00NV85102
product_parent:    908181913 -- 908.18 million
product_title:     XIKEZAN iPhone 6 Plus 5.5 inch Waterproof Case, Shockproof Dirtproof Snowproof Full Body Skin Case Protective Cover with Hand Strap & Headphone Adapter & Kickstand
product_category:  Wireless
star_rating:       4
helpful_votes:     0
total_votes:       0
vine:              false
verified_purchase: true
review_headline:   case is sturdy and protects as I want
review_body:       I won't count on the waterproof part (I took off the rubber seals at the bottom because the got on my nerves). But the case is sturdy and protects as I want.

Row 2:
──────
review_date:       16462
marketplace:       US
customer_id:       1974568 -- 1.97 million
review_id:         R2LXDXT293LG1T
product_id:        B00OTFZ23M
product_parent:    951208259 -- 951.21 million
product_title:     Season.C Chicago Bulls Marilyn Monroe No.1 Hard Back Case Cover for Samsung Galaxy S5 i9600
product_category:  Wireless
star_rating:       1
helpful_votes:     0
total_votes:       0
vine:              false
verified_purchase: true
review_headline:   One Star
review_body:       Cant use the case because its big for the phone. Waist of money!

Row 3:
──────
review_date:       16462
marketplace:       US
customer_id:       24803564 -- 24.80 million
review_id:         R7K9U5OEIRJWR
product_id:        B00LB8C4U4
product_parent:    524588109 -- 524.59 million
product_title:     iPhone 5s Case, BUDDIBOX [Shield] Slim Dual Layer Protective Case with Kickstand for Apple iPhone 5 and 5s
product_category:  Wireless
star_rating:       4
helpful_votes:     0
total_votes:       0
vine:              false
verified_purchase: true
review_headline:   but overall this case is pretty sturdy and provides good protection for the phone
review_body:       The front piece was a little difficult to secure to the phone at first, but overall this case is pretty sturdy and provides good protection for the phone, which is what I need. I would buy this case again.

ClickHouse에 이 데이터를 저장할 amazon_reviews라는 이름의 새 MergeTree 테이블을 정의하겠습니다:

CREATE DATABASE amazon

CREATE TABLE amazon.amazon_reviews
(
    `review_date` Date,
    `marketplace` LowCardinality(String),
    `customer_id` UInt64,
    `review_id` String,
    `product_id` String,
    `product_parent` UInt64,
    `product_title` String,
    `product_category` LowCardinality(String),
    `star_rating` UInt8,
    `helpful_votes` UInt32,
    `total_votes` UInt32,
    `vine` Bool,
    `verified_purchase` Bool,
    `review_headline` String,
    `review_body` String,
    PROJECTION helpful_votes
    (
        SELECT *
        ORDER BY helpful_votes
    )
)
ENGINE = MergeTree
ORDER BY (review_date, product_category)

다음 INSERT 명령은 s3Cluster 테이블 함수를 사용하며, 이를 통해 클러스터의 모든 노드를 활용해 여러 S3 파일을 병렬로 처리할 수 있습니다. 또한 https://datasets-documentation.s3.eu-west-3.amazonaws.com/amazon_reviews/amazon_reviews_*.snappy.parquet로 시작하는 모든 파일을 삽입하기 위해 와일드카드를 사용합니다:

INSERT INTO amazon.amazon_reviews SELECT *
FROM s3Cluster('default', 
'https://datasets-documentation.s3.eu-west-3.amazonaws.com/amazon_reviews/amazon_reviews_*.snappy.parquet', NOSIGN)

이 쿼리는 오래 걸리지 않으며, 평균적으로 초당 약 300,000행을 처리합니다. 5분 정도 지나면 모든 행이 삽입된 것을 확인할 수 있습니다:

SELECT formatReadableQuantity(count())
FROM amazon.amazon_reviews

데이터가 얼마나 많은 공간을 사용하고 있는지 확인해 보겠습니다:

SELECT
    disk_name,
    formatReadableSize(sum(data_compressed_bytes) AS size) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes) AS usize) AS uncompressed,
    round(usize / size, 2) AS compr_rate,
    sum(rows) AS rows,
    count() AS part_count
FROM system.parts
WHERE (active = 1) AND (table = 'amazon_reviews')
GROUP BY disk_name
ORDER BY size DESC

원본 데이터는 약 70G였지만, ClickHouse에서 압축하면 약 30G 정도를 차지합니다.

예시 쿼리

몇 가지 쿼리를 실행해 보겠습니다. 다음은 데이터셋에서 가장 유용하다고 평가된 리뷰 상위 10개입니다:

SELECT
    product_title,
    review_headline
FROM amazon.amazon_reviews
ORDER BY helpful_votes DESC
LIMIT 10

다음은 Amazon에서 리뷰 수가 가장 많은 제품 상위 10개입니다:

SELECT
    any(product_title),
    count()
FROM amazon.amazon_reviews
GROUP BY product_id
ORDER BY 2 DESC
LIMIT 10;

다음은 각 제품의 월별 평균 리뷰 평점입니다(실제 Amazon 면접 질문입니다!):

SELECT
    toStartOfMonth(review_date) AS month,
    any(product_title),
    avg(star_rating) AS avg_stars
FROM amazon.amazon_reviews
GROUP BY
    month,
    product_id
ORDER BY
    month DESC,
    product_id ASC
LIMIT 20;

다음은 제품 카테고리별 총 투표 수입니다. product_category가 프라이머리 키(primary key)에 포함되어 있으므로 이 쿼리는 빠르게 실행됩니다:

SELECT
    sum(total_votes),
    product_category
FROM amazon.amazon_reviews
GROUP BY product_category
ORDER BY 1 DESC

이제 리뷰에서 "awful" 이라는 단어가 가장 자주 등장하는 제품을 찾아보겠습니다. 이는 상당히 큰 작업입니다. 단어 하나를 찾기 위해 1억 5,100만 개가 넘는 문자열을 파싱해야 합니다:

SELECT
    product_id,
    any(product_title),
    avg(star_rating),
    count() AS count
FROM amazon.amazon_reviews
WHERE position(review_body, 'awful') > 0
GROUP BY product_id
ORDER BY count DESC
LIMIT 50;

이처럼 많은 데이터에서도 쿼리 시간이 어떻게 나오는지 확인해 보세요. 결과를 읽는 재미도 쏠쏠합니다!

이번에는 리뷰에서 awesome을 검색하는 것만 빼고, 동일한 쿼리를 다시 실행할 수 있습니다:

SELECT 
    product_id,
    any(product_title),
    avg(star_rating),
    count() AS count
FROM amazon.amazon_reviews
WHERE position(review_body, 'awesome') > 0
GROUP BY product_id
ORDER BY count DESC
LIMIT 50;