مجموعة بيانات Hacker News

في هذا الدليل، ستُدرِج 28 مليون صف من بيانات Hacker News في جدول ClickHouse باستخدام تنسيقي CSV وParquet، ثم تُشغِّل بعض الاستعلامات البسيطة لاستكشاف البيانات.

CSV

تنزيل CSV

يمكن تنزيل نسخة CSV من مجموعة البيانات من حاوية S3 العامة الخاصة بنا، أو بتشغيل هذا الأمر:

wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz

بحجم 4.6GB و28 مليون صف، من المفترض أن يستغرق تنزيل هذا الملف المضغوط من 5 إلى 10 دقائق.

أخذ عينة من البيانات

يتيح لك clickhouse-local إجراء معالجة سريعة للملفات المحلية دون الحاجة إلى نشر خادم ClickHouse وتهيئته.قبل تخزين أي بيانات في ClickHouse، لنأخذ عينة من الملف باستخدام clickhouse-local. من الطرفية شغّل:

clickhouse-local

بعد ذلك، نفّذ الأمر التالي لاستكشاف البيانات:

Query

SELECT *
FROM file('hacknernews.csv.gz', CSVWithNames)
LIMIT 2
SETTINGS input_format_try_infer_datetimes = 0
FORMAT Vertical

Response

Row 1:
──────
id:          344065
deleted:     0
type:        comment
by:          callmeed
time:        2008-10-26 05:06:58
text:        What kind of reports do you need?<p>ActiveMerchant just connects your app to a gateway for cc approval and processing.<p>Braintree has very nice reports on transactions and it's very easy to refund a payment.<p>Beyond that, you are dealing with Rails after all–it's pretty easy to scaffold out some reports from your subscriber base.
dead:        0
parent:      344038
poll:        0
kids:        []
url:
score:       0
title:
parts:       []
descendants: 0

Row 2:
──────
id:          344066
deleted:     0
type:        story
by:          acangiano
time:        2008-10-26 05:07:59
text:
dead:        0
parent:      0
poll:        0
kids:        [344111,344202,344329,344606]
url:         http://antoniocangiano.com/2008/10/26/what-arc-should-learn-from-ruby/
score:       33
title:       What Arc should learn from Ruby
parts:       []
descendants: 10

يوفر هذا الأمر الكثير من الإمكانات الدقيقة. يتيح لك العامل file قراءة الملف من قرص محلي، مع تحديد التنسيق CSVWithNames فقط. والأهم من ذلك، تُستنتج البنية تلقائيًا من محتويات الملف. لاحظ أيضًا أن clickhouse-local يستطيع قراءة الملف المضغوط، إذ يستنتج تنسيق gzip من امتداد الملف. يُستخدم التنسيق Vertical لتسهيل عرض البيانات لكل عمود.

حمّل البيانات باستخدام استدلال المخطط

أبسط أداة لتحميل البيانات وأكثرها قوة هي clickhouse-client: عميل سطر أوامر أصلي غني بالميزات. ولتحميل البيانات، يمكنك مرة أخرى الاستفادة من استدلال المخطط، مع الاعتماد على ClickHouse لتحديد أنواع الأعمدة.شغّل الأمر التالي لإنشاء جدول وإدراج البيانات مباشرةً من ملف CSV بعيد، مع الوصول إلى المحتوى عبر الدالة url. ويُستدل على المخطط تلقائيًا:

CREATE TABLE hackernews ENGINE = MergeTree ORDER BY tuple
(
) EMPTY AS SELECT * FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz', 'CSVWithNames');

يؤدي هذا إلى إنشاء جدول فارغ باستخدام المخطط المُستنتَج من البيانات. يتيح لنا الأمر DESCRIBE TABLE فهم الأنواع التي جرى تعيينها.

Query

DESCRIBE TABLE hackernews

Response

┌─name────────┬─type─────────────────────┬
│ id          │ Nullable(Float64)        │
│ deleted     │ Nullable(Float64)        │
│ type        │ Nullable(String)         │
│ by          │ Nullable(String)         │
│ time        │ Nullable(String)         │
│ text        │ Nullable(String)         │
│ dead        │ Nullable(Float64)        │
│ parent      │ Nullable(Float64)        │
│ poll        │ Nullable(Float64)        │
│ kids        │ Array(Nullable(Float64)) │
│ url         │ Nullable(String)         │
│ score       │ Nullable(Float64)        │
│ title       │ Nullable(String)         │
│ parts       │ Array(Nullable(Float64)) │
│ descendants │ Nullable(Float64)        │
└─────────────┴──────────────────────────┴

لإدخال البيانات إلى هذا الجدول، استخدم الأمر INSERT INTO, SELECT. وباستخدام الدالة url، ستُبث البيانات مباشرةً من عنوان URL:

INSERT INTO hackernews SELECT *
FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz', 'CSVWithNames')

لقد نجحت في إدراج 28 مليون صف في ClickHouse باستخدام أمر واحد!

استعراض البيانات

احصل على عيّنة من قصص Hacker News وبعض الأعمدة المحددة بتشغيل الاستعلام التالي:

Query

SELECT
    id,
    title,
    type,
    by,
    time,
    url,
    score
FROM hackernews
WHERE type = 'story'
LIMIT 3
FORMAT Vertical

Response

Row 1:
──────
id:    2596866
title:
type:  story
by:
time:  1306685152
url:
score: 0

Row 2:
──────
id:    2596870
title: WordPress capture users last login date and time
type:  story
by:    wpsnipp
time:  1306685252
url:   http://wpsnipp.com/index.php/date/capture-users-last-login-date-and-time/
score: 1

Row 3:
──────
id:    2596872
title: Recent college graduates get some startup wisdom
type:  story
by:    whenimgone
time:  1306685352
url:   http://articles.chicagotribune.com/2011-05-27/business/sc-cons-0526-started-20110527_1_business-plan-recession-college-graduates
score: 1

في حين أن استنتاج المخطط أداة ممتازة للاستكشاف الأولي للبيانات، فإنه يعمل وفق مبدأ «أفضل جهد ممكن»، ولا يُعد بديلاً طويل الأمد عن تحديد مخطط أمثل لبياناتك.

عرّف مخططًا

من التحسينات الواضحة والمباشرة تحديد نوع لكل حقل. بالإضافة إلى تعريف حقل الوقت بالنوع DateTime، نحدّد نوعًا مناسبًا لكل حقل من الحقول أدناه بعد حذف مجموعة البيانات الحالية. في ClickHouse، يُحدَّد المفتاح الأساسي للبيانات عبر عبارة ORDER BY.يساعد اختيار الأنواع المناسبة وتحديد الأعمدة التي ينبغي تضمينها في عبارة ORDER BY على تحسين سرعة الاستعلام والضغط.شغّل الاستعلام أدناه لحذف المخطط القديم وإنشاء المخطط المُحسَّن:

Query

DROP TABLE IF EXISTS hackernews;

CREATE TABLE hackernews
(
    `id` UInt32,
    `deleted` UInt8,
    `type` Enum('story' = 1, 'comment' = 2, 'poll' = 3, 'pollopt' = 4, 'job' = 5),
    `by` LowCardinality(String),
    `time` DateTime,
    `text` String,
    `dead` UInt8,
    `parent` UInt32,
    `poll` UInt32,
    `kids` Array(UInt32),
    `url` String,
    `score` Int32,
    `title` String,
    `parts` Array(UInt32),
    `descendants` Int32
)
    ENGINE = MergeTree
ORDER BY id

بعد تحسين المخطّط، يمكنك الآن إدراج البيانات من نظام الملفات المحلي. وباستخدام clickhouse-client مرة أخرى، أدرِج الملف عبر عبارة INFILE مع تعليمة INSERT INTO صريحة.

Query

INSERT INTO hackernews FROM INFILE '/data/hacknernews.csv.gz' FORMAT CSVWithNames

تشغيل استعلامات نموذجية

فيما يلي بعض نماذج الاستعلامات لتستلهم منها أفكاراً لكتابة استعلاماتك الخاصة.

ما مدى شيوع موضوع “ClickHouse” في Hacker News؟

يوفر حقل score مقياسًا لشعبية القصص، في حين يمكن استخدام حقل id ومعامل الدمج || لإنشاء رابط للمنشور الأصلي.

Query

SELECT
    time,
    score,
    descendants,
    title,
    url,
    'https://news.ycombinator.com/item?id=' || toString(id) AS hn_url
FROM hackernews
WHERE (type = 'story') AND (title ILIKE '%ClickHouse%')
ORDER BY score DESC
LIMIT 5 FORMAT Vertical

Response

Row 1:
──────
time:        1632154428
score:       519
descendants: 159
title:       ClickHouse, Inc.
url:         https://github.com/ClickHouse/ClickHouse/blob/master/website/blog/en/2021/clickhouse-inc.md
hn_url:      https://news.ycombinator.com/item?id=28595419

Row 2:
──────
time:        1614699632
score:       383
descendants: 134
title:       ClickHouse as an alternative to Elasticsearch for log storage and analysis
url:         https://pixeljets.com/blog/clickhouse-vs-elasticsearch/
hn_url:      https://news.ycombinator.com/item?id=26316401

Row 3:
──────
time:        1465985177
score:       243
descendants: 70
title:       ClickHouse – high-performance open-source distributed column-oriented DBMS
url:         https://clickhouse.yandex/reference_en.html
hn_url:      https://news.ycombinator.com/item?id=11908254

Row 4:
──────
time:        1578331410
score:       216
descendants: 86
title:       ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC
url:         https://www.altinity.com/blog/2020/1/1/clickhouse-cost-efficiency-in-action-analyzing-500-billion-rows-on-an-intel-nuc
hn_url:      https://news.ycombinator.com/item?id=21970952

Row 5:
──────
time:        1622160768
score:       198
descendants: 55
title:       ClickHouse: An open-source column-oriented database management system
url:         https://github.com/ClickHouse/ClickHouse
hn_url:      https://news.ycombinator.com/item?id=27310247

هل يُنتج ClickHouse ضوضاءً أكثر بمرور الوقت؟ يتضح هنا الفائدة من تعريف الحقل time بوصفه DateTime، إذ يتيح استخدام نوع البيانات المناسب توظيف الدالة toYYYYMM():

Query

SELECT
   toYYYYMM(time) AS monthYear,
   bar(count(), 0, 120, 20)
FROM hackernews
WHERE (type IN ('story', 'comment')) AND ((title ILIKE '%ClickHouse%') OR (text ILIKE '%ClickHouse%'))
GROUP BY monthYear
ORDER BY monthYear ASC

Response

┌─monthYear─┬─bar(count(), 0, 120, 20)─┐
│    201606 │ ██▎                      │
│    201607 │ ▏                        │
│    201610 │ ▎                        │
│    201612 │ ▏                        │
│    201701 │ ▎                        │
│    201702 │ █                        │
│    201703 │ ▋                        │
│    201704 │ █                        │
│    201705 │ ██                       │
│    201706 │ ▎                        │
│    201707 │ ▎                        │
│    201708 │ ▏                        │
│    201709 │ ▎                        │
│    201710 │ █▌                       │
│    201711 │ █▌                       │
│    201712 │ ▌                        │
│    201801 │ █▌                       │
│    201802 │ ▋                        │
│    201803 │ ███▏                     │
│    201804 │ ██▏                      │
│    201805 │ ▋                        │
│    201806 │ █▏                       │
│    201807 │ █▌                       │
│    201808 │ ▋                        │
│    201809 │ █▌                       │
│    201810 │ ███▌                     │
│    201811 │ ████                     │
│    201812 │ █▌                       │
│    201901 │ ████▋                    │
│    201902 │ ███                      │
│    201903 │ ▋                        │
│    201904 │ █                        │
│    201905 │ ███▋                     │
│    201906 │ █▏                       │
│    201907 │ ██▎                      │
│    201908 │ ██▋                      │
│    201909 │ █▋                       │
│    201910 │ █                        │
│    201911 │ ███                      │
│    201912 │ █▎                       │
│    202001 │ ███████████▋             │
│    202002 │ ██████▌                  │
│    202003 │ ███████████▋             │
│    202004 │ ███████▎                 │
│    202005 │ ██████▏                  │
│    202006 │ ██████▏                  │
│    202007 │ ███████▋                 │
│    202008 │ ███▋                     │
│    202009 │ ████                     │
│    202010 │ ████▌                    │
│    202011 │ █████▏                   │
│    202012 │ ███▋                     │
│    202101 │ ███▏                     │
│    202102 │ █████████                │
│    202103 │ █████████████▋           │
│    202104 │ ███▏                     │
│    202105 │ ████████████▋            │
│    202106 │ ███                      │
│    202107 │ █████▏                   │
│    202108 │ ████▎                    │
│    202109 │ ██████████████████▎      │
│    202110 │ ▏                        │
└───────────┴──────────────────────────┘

يبدو أن شعبية “ClickHouse” في تزايد مستمر مع مرور الوقت.

من هم أكثر المعلّقين نشاطاً على المقالات المتعلقة بـ ClickHouse؟

Query

SELECT
   by,
   count() AS comments
FROM hackernews
WHERE (type IN ('story', 'comment')) AND ((title ILIKE '%ClickHouse%') OR (text ILIKE '%ClickHouse%'))
GROUP BY by
ORDER BY comments DESC
LIMIT 5

Response

┌─by──────────┬─comments─┐
│ hodgesrm    │       78 │
│ zX41ZdbW    │       45 │
│ manigandham │       39 │
│ pachico     │       35 │
│ valyala     │       27 │
└─────────────┴──────────┘

ما التعليقات التي تُثير أكبر قدر من الاهتمام؟

Query

SELECT
  by,
  sum(score) AS total_score,
  sum(length(kids)) AS total_sub_comments
FROM hackernews
WHERE (type IN ('story', 'comment')) AND ((title ILIKE '%ClickHouse%') OR (text ILIKE '%ClickHouse%'))
GROUP BY by
ORDER BY total_score DESC
LIMIT 5

Response

┌─by───────┬─total_score─┬─total_sub_comments─┐
│ zX41ZdbW │        571  │              50    │
│ jetter   │        386  │              30    │
│ hodgesrm │        312  │              50    │
│ mechmind │        243  │              16    │
│ tosh     │        198  │              12    │
└──────────┴─────────────┴────────────────────┘

Parquet

من نقاط قوة ClickHouse قدرته على التعامل مع طيف واسع من التنسيقات. ويمثّل CSV حالة استخدام شبه مثالية، لكنه ليس الخيار الأكثر كفاءة لتبادل البيانات. بعد ذلك، ستحمّل البيانات من ملف Parquet، وهو تنسيق عمودي فعّال. يوفّر Parquet مجموعة محدودة من الأنواع، ويجب على ClickHouse الالتزام بها، كما أن معلومات الأنواع هذه تكون مُرمَّزة في التنسيق نفسه. وسيؤدي الاستدلال على الأنواع في ملف Parquet حتمًا إلى مخطط يختلف قليلًا عن المخطط الخاص بملف CSV.

أدرِج البيانات

شغّل query التالية لقراءة البيانات نفسها بتنسيق Parquet، باستخدام دالة url مرة أخرى لقراءة البيانات عن بُعد:

DROP TABLE IF EXISTS hackernews;

CREATE TABLE hackernews
ENGINE = MergeTree
ORDER BY id
SETTINGS allow_nullable_key = 1 EMPTY AS
SELECT *
FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.parquet', 'Parquet');

INSERT INTO hackernews SELECT *
FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.parquet', 'Parquet');

مفاتيح NULL في Parquetيستنتج النظام مخططًا يجعل الأعمدة من النوع Nullable، لذا يلزم allow_nullable_key رغم أن مجموعة البيانات هذه لا تحتوي على أي معرّفات NULL.

شغّل الأمر التالي لعرض المخطط المستنتج:

Query

DESCRIBE TABLE hackernews;

Response

┌─name────────┬─type───────────────────┬
│ id          │ Nullable(Int64)        │
│ deleted     │ Nullable(UInt8)        │
│ type        │ Nullable(String)       │
│ by          │ Nullable(String)       │
│ time        │ Nullable(Int64)        │
│ text        │ Nullable(String)       │
│ dead        │ Nullable(UInt8)        │
│ parent      │ Nullable(Int64)        │
│ poll        │ Nullable(Int64)        │
│ kids        │ Array(Nullable(Int64)) │
│ url         │ Nullable(String)       │
│ score       │ Nullable(Int32)        │
│ title       │ Nullable(String)       │
│ parts       │ Array(Nullable(Int64)) │
│ descendants │ Nullable(Int32)        │
└─────────────┴────────────────────────┴

تستخدم الخطوات المتبقية أسماء أعمدة أوضح، مثل author وcomment، لذا تابع باستخدام مخطط مُحدَّد يدويًا. احذف أولًا الجدول المُستنتَج، ثم أنشئ الجدول وأدرج البيانات مباشرةً من حاوية S3 العامة:

DROP TABLE IF EXISTS hackernews;

CREATE TABLE hackernews
(
    `id` UInt64,
    `deleted` UInt8,
    `type` String,
    `author` String,
    `timestamp` DateTime,
    `comment` String,
    `dead` UInt8,
    `parent` UInt64,
    `poll` UInt64,
    `children` Array(UInt32),
    `url` String,
    `score` UInt32,
    `title` String,
    `parts` Array(UInt32),
    `descendants` UInt32
)
ENGINE = MergeTree
ORDER BY (type, author);

INSERT INTO hackernews
SELECT * FROM s3(
        'https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.parquet',
        NOSIGN,
        'Parquet',
        'id UInt64,
         deleted UInt8,
         type String,
         by String,
         time DateTime,
         text String,
         dead UInt8,
         parent UInt64,
         poll UInt64,
         kids Array(UInt32),
         url String,
         score UInt32,
         title String,
         parts Array(UInt32),
         descendants UInt32');

أضِف فهرسًا نصيًا لتسريع البحث

لمعرفة عدد التعليقات التي تشير إلى “ClickHouse”، نفِّذ الاستعلام التالي:

Query

SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'clickhouse');

Response

┌─count()─┐
│    1145 │
└─────────┘

1 row in set. Elapsed: 3.251 sec. Processed 28.74 million rows, 9.60 GB (8.84 million rows/s., 2.95 GB/s.)

بعد ذلك، أنشئ فهرسًا نصيًا للعمود comment لتسريع هذا الاستعلام. يستخدم الفهرس النصي فهرسًا معكوسًا يربط الرموز المميزة بالصفوف التي تحتوي عليها. يقسّم محلّل الرموز splitByNonAlpha النص عند المحارف غير الأبجدية الرقمية. يستخدم الفهرس والاستعلامات lower(comment) مع مصطلحات بحث بأحرف صغيرة، بحيث لا تتأثر المطابقة بحالة الأحرف. يجب أن يتطابق تعبير الاستعلام مع التعبير المفهرس.شغّل الأوامر التالية لإنشاء الفهرس:

ALTER TABLE hackernews
    ADD INDEX comment_idx lower(comment)
    TYPE text(tokenizer = splitByNonAlpha);

ALTER TABLE hackernews
    MATERIALIZE INDEX comment_idx
    SETTINGS mutations_sync = 2;

تقوم عملية المادية بإنشاء الفهرس للبيانات الموجودة. ينتظر الإعداد mutations_sync حتى تكتمل عملية المادية. يمكنك التحقق من تعريف الفهرس في جدول system.data_skipping_indices.شغّل الاستعلام نفسه مرة أخرى بعد اكتمال مادية الفهرس:

Query

SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'clickhouse');

Response

┌─count()─┐
│    1145 │
└─────────┘

1 row in set. Elapsed: 0.019 sec. Processed 4.48 million rows, 4.48 MB (232.23 million rows/s., 232.23 MB/s.)

تظل النتيجة نفسها لأن الفهرس يغيّر طريقة عثور ClickHouse على الصفوف المطابقة، وليس الصفوف التي تُعدّ مطابقة. يعالج الاستعلام المفهرس بيانات أقل بكثير ويكتمل أسرع بكثير. استخدم EXPLAIN للتأكد من أن ClickHouse يخطط لاستخدام الفهرس:

Query

EXPLAIN indexes = 1
SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'clickhouse');

Response

Output: count()

Aggregating
│  Keys:
│  Aggregates: count()
│  Skip merging: 0
└──Filter
   │  Filter column: __text_index_comment_idx_hasAnyTokens_7b9491bd22343c822f64c95a9bda20f9
   └──ReadFromMergeTree (default.hackernews)
         Read type: Default
         Parts: 4 | Granules: 547
         Output: __text_index_comment_idx_hasAnyTokens_7b9491bd22343c822f64c95a9bda20f9
         Indexes:
           PrimaryKey
             Condition: true
             Parts: 4/4
             Granules: 3527/3527
           Skip
             Name: comment_idx
             Description: text GRANULARITY 100000000
             Condition: (mode: Any; tokens: ["clickhouse"])
             Parts: 4/4
             Granules: 547/3527
           Ranges: 437

يوضح الإدخال comment_idx أن ClickHouse يخطط لاستخدام فهرس النص. في هذا المثال، تختار الخطة 547 من أصل 3527 وحدة حبيبية، ما يقلل بدرجة كبيرة كمية البيانات التي يجري فحصها.يمكنك أيضًا البحث عن أي رمز مميز أو جميع الرموز المميزة المتعددة. تطابق هذه الدوال الرموز المميزة الكاملة التي ينتجها محلل الرموز المميزة الخاص بالفهرس. استخدم hasAnyTokens عندما يلزم تطابق رمز مميز واحد على الأقل:

Query

SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'oltp olap');

Response

┌─count()─┐
│    2020 │
└─────────┘

استخدم hasAllTokens عندما يلزم تطابق جميع الوحدات النصية، بأي ترتيب:

Query

SELECT count(*)
FROM hackernews
WHERE hasAllTokens(lower(comment), 'avx sve');

Response

┌─count()─┐
│      22 │
└─────────┘

Get started

Setup

Sample datasets

Migrate

CSV

ما مدى شيوع موضوع “ClickHouse” في Hacker News؟

من هم أكثر المعلّقين نشاطاً على المقالات المتعلقة بـ ClickHouse؟

ما التعليقات التي تُثير أكبر قدر من الاهتمام؟

Parquet

​CSV

​ما مدى شيوع موضوع “ClickHouse” في Hacker News؟

​من هم أكثر المعلّقين نشاطاً على المقالات المتعلقة بـ ClickHouse؟

​ما التعليقات التي تُثير أكبر قدر من الاهتمام؟

​Parquet

CSV

ما مدى شيوع موضوع “ClickHouse” في Hacker News؟

من هم أكثر المعلّقين نشاطاً على المقالات المتعلقة بـ ClickHouse؟

ما التعليقات التي تُثير أكبر قدر من الاهتمام؟

Parquet