> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-fbfa8bee.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Dataset containing 28 million rows of hacker news data.

# Hacker News dataset

> 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.

<h2 id="csv">
  CSV
</h2>

<Steps>
  <Step>
    <h3 id="download">
      Download CSV
    </h3>

    A CSV version of the dataset can be downloaded from our public [S3 bucket](https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz), or by running this command:

    ```bash theme={null}
    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.
  </Step>

  <Step>
    <h3 id="sampling">
      Sample the data
    </h3>

    [`clickhouse-local`](/concepts/features/tools-and-utilities/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:

    ```bash theme={null}
    clickhouse-local
    ```

    Next, run the following command to explore the data:

    ```sql title="Query" theme={null}
    SELECT *
    FROM file('hacknernews.csv.gz', CSVWithNames)
    LIMIT 2
    SETTINGS input_format_try_infer_datetimes = 0
    FORMAT Vertical
    ```

    ```response title="Response" theme={null}
    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`](/reference/functions/regular-functions/files#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.
  </Step>

  <Step>
    <h3 id="loading-the-data">
      Load the data with schema inference
    </h3>

    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`](/reference/functions/table-functions/url) function.
    The schema is automatically inferred:

    ```sql theme={null}
    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`](/reference/statements/describe-table) command allows us to understand these assigned types.

    ```sql title="Query" theme={null}
    DESCRIBE TABLE hackernews
    ```

    ```text title="Response" theme={null}
    ┌─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:

    ```sql theme={null}
    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!
  </Step>

  <Step>
    <h3 id="explore">
      Explore the data
    </h3>

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

    ```sql title="Query" theme={null}
    SELECT
        id,
        title,
        type,
        by,
        time,
        url,
        score
    FROM hackernews
    WHERE type = 'story'
    LIMIT 3
    FORMAT Vertical
    ```

    ```response title="Response" theme={null}
    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.
  </Step>

  <Step>
    <h3 id="define-a-schema">
      Define a schema
    </h3>

    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:

    ```sql title="Query" theme={null}
    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`.

    ```sql title="Query" theme={null}
    INSERT INTO hackernews FROM INFILE '/data/hacknernews.csv.gz' FORMAT CSVWithNames
    ```
  </Step>

  <Step>
    <h3 id="run-sample-queries">
      Run sample queries
    </h3>

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

    <h4 id="how-pervasive">
      How pervasive a topic is "ClickHouse" in Hacker News?
    </h4>

    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.

    ```sql title="Query" theme={null}
    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 title="Response" theme={null}
    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:

    ```sql title="Query" theme={null}
    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 title="Response" theme={null}
    ┌─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.

    <h4 id="top-commenters">
      Who are the top commenters on ClickHouse related articles?
    </h4>

    ```sql title="Query" theme={null}
    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 title="Response" theme={null}
    ┌─by──────────┬─comments─┐
    │ hodgesrm    │       78 │
    │ zX41ZdbW    │       45 │
    │ manigandham │       39 │
    │ pachico     │       35 │
    │ valyala     │       27 │
    └─────────────┴──────────┘
    ```

    <h4 id="comments-by-most-interest">
      Which comments generate the most interest?
    </h4>

    ```sql title="Query" theme={null}
    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 title="Response" theme={null}
    ┌─by───────┬─total_score─┬─total_sub_comments─┐
    │ zX41ZdbW │        571  │              50    │
    │ jetter   │        386  │              30    │
    │ hodgesrm │        312  │              50    │
    │ mechmind │        243  │              16    │
    │ tosh     │        198  │              12    │
    └──────────┴─────────────┴────────────────────┘
    ```
  </Step>
</Steps>

<h2 id="parquet">
  Parquet
</h2>

One of the strengths of ClickHouse is its ability to handle any number of [formats](/reference/formats/index).
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.

<Steps>
  <Step>
    <h3 id="insert-the-data">
      Insert the data
    </h3>

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

    ```sql theme={null}
    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')
    ```

    <Info>
      **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.
    </Info>

    Run the following command to view the inferred schema:

    ```response title="Response" theme={null}
    ┌─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:

    ```sql theme={null}
    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');
    ```
  </Step>

  <Step>
    <h3 id="add-skipping-index">
      Add a skipping-index to speed up queries
    </h3>

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

    ```sql title="Query" theme={null}
    SELECT count(*)
    FROM hackernews
    WHERE hasToken(lower(comment), 'ClickHouse');
    ```

    ```response title="Response" highlight={1} theme={null}
    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](/reference/engines/table-engines/mergetree-family/textindexes) 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:

    ```sql theme={null}
    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:

    ```sql title="Query" theme={null}
    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 title="Response" highlight={1} theme={null}
    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`](/reference/statements/explain) clause can be used to understand why the addition of this index
    improved the query around 3.4x.

    ```response text="Query" theme={null}
    EXPLAIN indexes = 1
    SELECT count(*)
    FROM hackernews
    WHERE hasToken(lower(comment), 'clickhouse')
    ```

    ```response title="Response" theme={null}
    ┌─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:

    ```sql title="Query" theme={null}
    SELECT count(*)
    FROM hackernews
    WHERE multiSearchAny(lower(comment), ['oltp', 'olap']);
    ```

    ```response title="Response" theme={null}
    ┌─count()─┐
    │    2177 │
    └─────────┘
    ```

    ```sql title="Query" theme={null}
    SELECT count(*)
    FROM hackernews
    WHERE hasToken(lower(comment), 'avx') AND hasToken(lower(comment), 'sve');
    ```

    ```response title="Response" theme={null}
    ┌─count()─┐
    │      22 │
    └─────────┘
    ```
  </Step>
</Steps>