Hacker News dataset

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/getting-started/example-datasets/hacker-news.md)

> 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[​](#csv "Direct link to CSV")

Download CSV

### Download CSV[​](#download "Direct link to Download CSV")

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:

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

Sample the data

### Sample the data[​](#sampling "Direct link to Sample the data")

[`clickhouse-local`](/docs/operations/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:

```
clickhouse-local
```

Next, run the following command to explore the data:

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

```
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`](/docs/sql-reference/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.

Load the data with schema inference

### Load the data with schema inference[​](#loading-the-data "Direct link to 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`](https://clickhouse.com/docs/en/sql-reference/table-functions/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`](/docs/sql-reference/statements/describe-table) command allows us to understand these assigned types.

```
DESCRIBE TABLE hackernews
```

```
┌─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!

Explore the data

### Explore the data[​](#explore "Direct link to Explore the data")

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

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

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

Define a schema

### Define a schema[​](#define-a-schema "Direct link to 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:

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

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

Run sample queries

### Run sample queries[​](#run-sample-queries "Direct link to 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?[​](#how-pervasive "Direct link to 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.