Отзывы покупателей Amazon

Этот датасет содержит более 150 млн отзывов покупателей о товарах Amazon. Данные хранятся в файлах формата Parquet, сжатых алгоритмом Snappy, в AWS S3; общий сжатый объём — 49 ГБ. Далее рассмотрим шаги по загрузке этого датасета в ClickHouse.

Примечание

Приведённые ниже запросы выполнялись на Production-инстансе ClickHouse Cloud. Для получения дополнительной информации см. раздел "Playground specifications".

Загрузка набора данных

1. Не загружая данные в ClickHouse, мы можем обращаться к ним напрямую. Давайте выберем несколько строк, чтобы посмотреть, как они выглядят:

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

Строки имеют следующий вид:

Строка 1:
──────
review_date:       16462
marketplace:       US
customer_id:       25444946 -- 25,44 миллиона
review_id:         R146L9MMZYG0WA
product_id:        B00NV85102
product_parent:    908181913 -- 908,18 миллиона
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:   чехол прочный и защищает как надо
review_body:       Не буду рассчитывать на водонепроницаемость (снял резиновые уплотнители снизу, потому что они действовали на нервы). Но чехол прочный и защищает как надо.

Строка 2:
──────
review_date:       16462
marketplace:       US
customer_id:       1974568 -- 1,97 миллиона
review_id:         R2LXDXT293LG1T
product_id:        B00OTFZ23M
product_parent:    951208259 -- 951,21 миллиона
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:   Одна звезда
review_body:       Не могу использовать чехол, потому что он велик для телефона. Выброшенные деньги!

Строка 3:
──────
review_date:       16462
marketplace:       US
customer_id:       24803564 -- 24,80 миллиона
review_id:         R7K9U5OEIRJWR
product_id:        B00LB8C4U4
product_parent:    524588109 -- 524,59 миллиона
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:   но в целом этот чехол достаточно прочный и обеспечивает хорошую защиту телефона
review_body:       Переднюю часть поначалу было немного сложно закрепить на телефоне, но в целом этот чехол достаточно прочный и обеспечивает хорошую защиту телефона, что мне и нужно. Купил бы этот чехол снова.

2. Давайте создадим новую таблицу типа MergeTree с именем amazon_reviews для хранения этих данных в ClickHouse:

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)

3. Следующая команда INSERT использует табличную функцию s3Cluster, которая позволяет обрабатывать несколько файлов S3 параллельно на всех узлах кластера. Мы также используем подстановочный символ (wildcard), чтобы вставить любые файлы, имена которых начинаются с 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')

Совет

В ClickHouse Cloud имя кластера — default. Замените default на имя вашего кластера... или используйте табличную функцию s3 (вместо s3Cluster), если кластера у вас нет.

5. Этот запрос выполняется недолго — в среднем около 300 000 строк в секунду. Примерно через 5 минут или около того вы должны увидеть все вставленные строки:

SELECT formatReadableQuantity(count())
FROM amazon.amazon_reviews

6. Давайте посмотрим, сколько места занимают наши данные:

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

Объём исходных данных составлял около 70 ГБ, но после сжатия в ClickHouse они занимают около 30 ГБ.

Примеры запросов

7. Давайте выполним несколько запросов. Ниже приведены 10 наиболее полезных отзывов в этом наборе данных:

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

Примечание

Этот запрос использует проекцию для повышения производительности.

8. Вот топ‑10 товаров на Amazon по количеству отзывов:

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

9. Вот средние рейтинги отзывов по месяцам для каждого товара (реальный вопрос на собеседовании в 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;

10. Вот общее число голосов по категориям товаров. Этот запрос выполняется быстро, потому что product_category входит в первичный ключ:

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

11. Найдём товары, в отзывах на которые слово «awful» встречается чаще всего. Это большая задача — нужно разобрать более 151 млн строк в поисках одного слова:

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;

Обратите внимание на время выполнения запроса для такого большого объёма данных. Результаты тоже любопытно почитать!

12. Мы можем выполнить тот же запрос ещё раз, только на этот раз будем искать 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;