title_wip

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

## Introduction[​](#introduction "Direct link to Introduction")

The [Hacker News dataset](https://news.ycombinator.com/) contains 28.74 million
postings and their vector embeddings. The embeddings were generated using [SentenceTransformers](https://sbert.net/) model [all-MiniLM-L6-v2](https://huggingface.co/sentence-transformers/all-MiniLM-L6-v2). The dimension of each embedding vector is `384`.

This dataset can be used to walk through the design, sizing and performance aspects for a large scale,
real world vector search application built on top of user generated, textual data.

## Dataset details[​](#dataset-details "Direct link to Dataset details")

The complete dataset with vector embeddings is made available by ClickHouse as a single `Parquet` file in a [S3 bucket](https://clickhouse-datasets.s3.amazonaws.com/hackernews-miniLM/hackernews_part_1_of_1.parquet)

We recommend users first run a sizing exercise to estimate the storage and memory requirements for this dataset by referring to the [documentation](/docs/engines/table-engines/mergetree-family/annindexes).

## Steps[​](#steps "Direct link to Steps")

Create table

### Create table[​](#create-table "Direct link to Create table")

Create the `hackernews` table to store the postings & their embeddings and associated attributes:

```
CREATE TABLE hackernews
(
    `id` Int32,
    `doc_id` Int32,
    `text` String,
    `vector` Array(Float32),
    `node_info` Tuple(
        start Nullable(UInt64),
        end Nullable(UInt64)),
    `metadata` String,
    `type` Enum8('story' = 1, 'comment' = 2, 'poll' = 3, 'pollopt' = 4, 'job' = 5),
    `by` LowCardinality(String),
    `time` DateTime,
    `title` String,
    `post_score` Int32,
    `dead` UInt8,
    `deleted` UInt8,
    `length` UInt32
)
ENGINE = MergeTree
ORDER BY id;
```

The `id` is just an incrementing integer. The additional attributes can be used in predicates to understand
vector similarity search combined with post-filtering/pre-filtering as explained in the [documentation](/docs/engines/table-engines/mergetree-family/annindexes)

Load data

### Load data[​](#load-table "Direct link to Load data")

To load the dataset from the `Parquet` file, run the following SQL statement:

```
INSERT INTO hackernews SELECT * FROM s3('https://clickhouse-datasets.s3.amazonaws.com/hackernews-miniLM/hackernews_part_1_of_1.parquet');
```

Inserting 28.74 million rows into the table will take a few minutes.

Build a vector similarity index

### Build a vector similarity index[​](#build-vector-similarity-index "Direct link to Build a vector similarity index")

Run the following SQL to define and build a vector similarity index on the `vector` column of the `hackernews` table:

```
ALTER TABLE hackernews ADD INDEX vector_index vector TYPE vector_similarity('hnsw', 'cosineDistance', 384, 'bf16', 64, 512);

ALTER TABLE hackernews MATERIALIZE INDEX vector_index SETTINGS mutations_sync = 2;
```

The parameters and performance considerations for index creation and search are described in the [documentation](/docs/engines/table-engines/mergetree-family/annindexes).
The statement above uses values of 64 and 512 respectively for the HNSW hyperparameters `M` and `ef_construction`.
You need to carefully select optimal values for these parameters by evaluating index build time and search results quality
corresponding to selected values.

Building and saving the index could even take a few minutes/hour for the full 28.74 million dataset, depending on the number of CPU cores available and the storage bandwidth.

Perform ANN search

### Perform ANN search[​](#perform-ann-search "Direct link to Perform ANN search")

Once the vector similarity index has been built, vector search queries will automatically use the index:

```
SELECT id, title, text
FROM hackernews
ORDER BY cosineDistance( vector, <search vector>)
LIMIT 10
```

The first time load of the vector index into memory could take a few seconds/minutes.

Generate embeddings for search query

### Generate embeddings for search query[​](#generating-embeddings-for-search-query "Direct link to Generate embeddings for search query")

[Sentence Transformers](https://www.sbert.net/) provide local, easy to use embedding
models for capturing the semantic meaning of sentences and paragraphs.

The dataset in this HackerNews dataset contains vector emebeddings generated from the
[all-MiniLM-L6-v2](https://huggingface.co/sentence-transformers/all-MiniLM-L6-v2) model.

An example Python script is provided below to demonstrate how to programmatically generate
embedding vectors using `sentence_transformers1 Python package. The search embedding vector is then passed as an argument to the [`cosineDistance()`](/sql-reference/functions/distance-functions#cosineDistance) function in the` SELECT` query.

```
from sentence_transformers import SentenceTransformer
import sys

import clickhouse_connect

print("Initializing...")

model = SentenceTransformer('sentence-transformers/all-MiniLM-L6-v2')

chclient = clickhouse_connect.get_client() # ClickHouse credentials here

while True:
    # Take the search query from user
    print("Enter a search query :")
    input_query = sys.stdin.readline();
    texts = [input_query]

    # Run the model and obtain search vector
    print("Generating the embedding for ", input_query);
    embeddings = model.encode(texts)

    print("Querying ClickHouse...")
    params = {'v1':list(embeddings[0]), 'v2':20}
    result = chclient.query("SELECT id, title, text FROM hackernews ORDER BY cosineDistance(vector, %(v1)s) LIMIT %(v2)s", parameters=params)
    print("Results :")
    for row in result.result_rows:
        print(row[0], row[2][:100])
        print("---------")
```

An example of running the above Python script and similarity search results are shown below
(only 100 characters from each of the top 20 posts are printed):

```
Initializing...

Enter a search query :
Are OLAP cubes useful

Generating the embedding for  "Are OLAP cubes useful"

Querying ClickHouse...

Results :