Hacker News dataset

Insert the data

### Insert the data[​](#insert-the-data "Direct link to 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:

```
┌─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');
```

Add a skipping-index to speed up queries

### Add a skipping-index to speed up queries[​](#add-skipping-index "Direct link to Add a skipping-index to speed up queries")

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

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

```
#highlight-next-line
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](/docs/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:

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

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

```
#highlight-next-line
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`](/docs/sql-reference/statements/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')
```

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

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

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

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

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

- [CSV](#csv)
  - [Download CSV](#download)- [Sample the data](#sampling)- [Load the data with schema inference](#loading-the-data)- [Explore the data](#explore)- [Define a schema](#define-a-schema)- [Run sample queries](#run-sample-queries)- [Parquet](#parquet)
    - [Insert the data](#insert-the-data)- [Add a skipping-index to speed up queries](#add-skipping-index)

Was this page helpful?