Hacker News dataset

Dataset containing 28 million rows of hacker news data.

In this tutorial, you’ll insert 28 million rows of Hacker News data into a ClickHouse table from both CSV and Parquet formats and run some simple queries to explore the data.

CSV

1. Download CSV

   A CSV version of the dataset can be downloaded from our public S3 bucket, or by running this command:

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

   At 4.6GB, and 28m rows, this compressed file should take 5-10 minutes to download.

2. Sample the data

   clickhouse-local allows you to perform fast processing on local files without having to deploy and configure the ClickHouse server.

   Before storing any data in ClickHouse, let’s sample the file using clickhouse-local. From the console run:

   clickhouse-local
   

   Next, run the following command to explore the data:

   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
   

   There are a lot of subtle capabilities in this command. The file operator allows you to read the file from a local disk, specifying only the format CSVWithNames. Most importantly, the schema is automatically inferred for you from the file contents. Note also how clickhouse-local is able to read the compressed file, inferring the gzip format from the extension. The Vertical format is used to more easily see the data for each column.

3. Load the data with schema inference

   The simplest and most powerful tool for data loading is the clickhouse-client: a feature-rich native command-line client. To load data, you can again exploit schema inference, relying on ClickHouse to determine the types of the columns.

   Run the following command to create a table and insert the data directly from the remote CSV file, accessing the contents via the url function. The schema is automatically inferred:

   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');
   

   This creates an empty table using the schema inferred from the data. The DESCRIBE TABLE command allows us to understand these assigned types.

   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)        │
   └─────────────┴──────────────────────────┴
   

   To insert the data into this table, use the INSERT INTO, SELECT command. Together with the url function, data will be streamed directly from the URL:

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

   You’ve successfully inserted 28 million rows into ClickHouse with a single command!

4. Explore the data

   Sample the Hacker News stories and specific columns by running the following query:

   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
   

   While schema inference is a great tool for initial data exploration, it is “best effort” and not a long-term substitute for defining an optimal schema for your data.

5. Define a schema

   An obvious immediate optimization is to define a type for each field. In addition to declaring the time field as a DateTime type, we define an appropriate type for each of the fields below after dropping our existing dataset. In ClickHouse the primary key id for the data is defined via the ORDER BY clause.

   Selecting appropriate types and choosing which columns to include in the ORDER BY clause will help to improve query speed and compression.

   Run the query below to drop the old schema and create the improved schema:

   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
   

   With an optimized schema, you can now insert the data from the local file system. Again using clickhouse-client, insert the file using the INFILE clause with an explicit INSERT INTO.

   Query

   INSERT INTO hackernews FROM INFILE '/data/hacknernews.csv.gz' FORMAT CSVWithNames
   
6. Run sample queries

   Some sample queries are presented below to give you inspiration for writing your own queries.

   How pervasive a topic is “ClickHouse” in Hacker News?

   The score field provides a metric of popularity for stories, while the id field and || concatenation operator can be used to produce a link to the original post.

   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
   

   Is ClickHouse generating more noise over time? Here the usefulness of defining the time field as a DateTime is shown, as using a proper data type allows you to use the toYYYYMM() function:

   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 │ ▏                        │
   └───────────┴──────────────────────────┘
   

   It looks like “ClickHouse” is growing in popularity with time.

   Who are the top commenters on ClickHouse related articles?

   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 │
   └─────────────┴──────────┘
   

   Which comments generate the most interest?

   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

One of the strengths of ClickHouse is its ability to handle any number of formats. CSV represents a rather ideal use case, and isn’t the most efficient for data exchange.

Next, you’ll load the data from a Parquet file which is an efficient column-oriented format.

Parquet has minimal types, which ClickHouse needs to respect, and this type information is encoded in the format itself. Type inference on a Parquet file will invariably lead to a slightly different schema than the one for the CSV file.

1. Insert the data

   Run the following query to read the same data in Parquet format, again using the url function to read the remote data:

   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 keys with Parquet

   As a condition of the Parquet format, we have to accept that keys might be NULL, even though they aren’t in the data.

   Run the following command to view the inferred schema:

   Query

   ┌─name────────┬─type───────────────────┬
   │ id          │ Nullable(Int64)        │
   │ deleted     │ Nullable(UInt8)        │
   │ type        │ 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)        │
   └─────────────┴────────────────────────┴
   

   As before with the CSV file, you can specify the schema manually for greater control over the chosen types and insert the data directly from s3:

   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',
           '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');
   
2. Add a skipping-index to speed up queries

   To find out how many comments mention “ClickHouse”, run the following query:

   Query

   SELECT count(*)
   FROM hackernews
   WHERE hasToken(lower(comment), 'ClickHouse');
   

   Response

   1 row in set. Elapsed: 0.843 sec. Processed 28.74 million rows, 9.75 GB (34.08 million rows/s., 11.57 GB/s.)
   ┌─count()─┐
   │     516 │
   └─────────┘
   

   Next, you’ll create an inverted index on the “comment” column in order to speed this query up. Note that lowercase comments will be indexed to find terms independent of casing.

   Run the following commands to create the index:

   ALTER TABLE hackernews ADD INDEX comment_idx(lower(comment)) TYPE inverted;
   ALTER TABLE hackernews MATERIALIZE INDEX comment_idx;
   

   Materialization of the index takes a while (to check if the index was created, use the system table system.data_skipping_indices).

   Run the query again once the index has been created:

   Query

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

   Notice how the query now took only 0.248 seconds with the index, down from 0.843 seconds previously without it:

   Response

   1 row in set. Elapsed: 0.248 sec. Processed 4.54 million rows, 1.79 GB (18.34 million rows/s., 7.24 GB/s.)
   ┌─count()─┐
   │    1145 │
   └─────────┘
   

   The EXPLAIN clause can be used to understand why the addition of this index improved the query around 3.4x.

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

   Response

   ┌─explain─────────────────────────────────────────┐
   │ Expression ((Projection + Before ORDER BY))     │
   │   Aggregating                                   │
   │     Expression (Before GROUP BY)                │
   │       Filter (WHERE)                            │
   │         ReadFromMergeTree (default.hackernews)  │
   │         Indexes:                                │
   │           PrimaryKey                            │
   │             Condition: true                     │
   │             Parts: 4/4                          │
   │             Granules: 3528/3528                 │
   │           Skip                                  │
   │             Name: comment_idx                   │
   │             Description: inverted GRANULARITY 1 │
   │             Parts: 4/4                          │
   │             Granules: 554/3528                  │
   └─────────────────────────────────────────────────┘
   

   Notice how the index allowed skipping of a substantial number of granules to speed up the query.

   It’s also possible to now efficiently search for one, or all of multiple terms:

   Query

   SELECT count(*)
   FROM hackernews
   WHERE multiSearchAny(lower(comment), ['oltp', 'olap']);
   

   Response

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

   Query

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

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