ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

print("Initializing chatgpt-3.5-turbo model")
    model_name = "gpt-3.5-turbo"

    text_splitter = CharacterTextSplitter.from_tiktoken_encoder(
        model_name=model_name
    )

    texts = text_splitter.split_text(doc_results)

    docs = [Document(page_content=t) for t in texts]

    llm = ChatOpenAI(temperature=0, model_name=model_name)

    prompt_template = """
Write a concise summary of the following in not more than 10 sentences:


{text}


CONSCISE SUMMARY :
"""

    prompt = PromptTemplate(template=prompt_template, input_variables=["text"])

    num_tokens = num_tokens_from_string(doc_results, model_name)

    gpt_35_turbo_max_tokens = 4096
    verbose = False

    print("Summarizing search results retrieved from ClickHouse...")

    if num_tokens <= gpt_35_turbo_max_tokens:
        chain = load_summarize_chain(llm, chain_type="stuff", prompt=prompt, verbose=verbose)
    else:
        chain = load_summarize_chain(llm, chain_type="map_reduce", map_prompt=prompt, combine_prompt=prompt, verbose=verbose)

    summary = chain.run(docs)

    print(f"Summary from chatgpt-3.5: {summary}")
```

- [Introduction](#introduction)- [Dataset details](#dataset-details)- [Steps](#steps)
      - [Create table](#create-table)- [Load data](#load-table)- [Build a vector similarity index](#build-vector-similarity-index)- [Perform ANN search](#perform-ann-search)- [Generate embeddings for search query](#generating-embeddings-for-search-query)- [Summarization demo application](#summarization-demo-application)

Was this page helpful?
title_wip

You can now simply use:

```
SELECT encode_text('cat');
```

The first run will be slow because it loads the model, but repeated runs will be fast. We can then copy the output to `SET param_target=...` and can easily write queries. Alternatively, the `encode_text()` function can directly be used as a argument to the `cosineDistance` function :

```
SELECT url
FROM laion
ORDER BY cosineDistance(text_embedding, encode_text('a dog and a cat')) ASC
LIMIT 10
```

Note that the `encode_text()` UDF itself could require a few seconds to compute and emit the embedding vector.

### Image embeddings[​](#image-embeddings "Direct link to Image embeddings")

Image embeddings can be created similarly and we provide a Python script that can generate an embedding of an image stored locally as a file.

`encode_image.py`

```
#!/usr/bin/python3
#!Note: Change the above python3 executable location if a virtual env is being used.
import clip
import torch
import numpy as np
from PIL import Image
import sys

if __name__ == '__main__':
    device = "cuda" if torch.cuda.is_available() else "cpu"
    model, preprocess = clip.load("ViT-B/32", device=device)
    for text in sys.stdin:
        image = preprocess(Image.open(text.strip())).unsqueeze(0).to(device)
        with torch.no_grad():
            image_features = model.encode_image(image)[0].tolist()
            print(image_features)
        sys.stdout.flush()
```

`encode_image_function.xml`

```
<functions>
    <function>
        <type>executable_pool</type>
        <name>encode_image</name>
        <return_type>Array(Float32)</return_type>
        <argument>
            <type>String</type>
            <name>path</name>
        </argument>
        <format>TabSeparated</format>
        <command>encode_image.py</command>
        <command_read_timeout>1000000</command_read_timeout>
    </function>
</functions>
```

Fetch an example image to search :

```
# get a random image of a LEGO set
$ wget http://cdn.firstcry.com/brainbees/images/products/thumb/191325a.jpg
```

Then run this query to generate the embedding for above image :

```
SELECT encode_image('/path/to/your/image');
```

The complete search query is :

```
SELECT
    url,
    caption
FROM laion
ORDER BY cosineDistance(image_embedding, encode_image('/path/to/your/image')) ASC
LIMIT 10
```

- [Data preparation](#data-preparation)- [Create table](#create-table)- [Run a brute-force vector similarity search](#run-a-brute-force-vector-similarity-search)- [Run an approximate vector similarity search with a vector similarity index](#run-an-approximate-vector-similarity-search-with-a-vector-similarity-index)- [Creating embeddings with UDFs](#creating-embeddings-with-udfs)
          - [Text embeddings](#text-embeddings)- [Image embeddings](#image-embeddings)

Was this page helpful?
title_wip

Building and saving the index could even take a few hours for the full l00 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, url 
FROM laion_5b_100m
ORDER BY cosineDistance( vector, (SELECT vector FROM laion_5b_100m WHERE id = 9999) ) ASC
LIMIT 20
```

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")

The `LAION 5b` dataset embedding vectors were generated using `OpenAI CLIP` model `ViT-L/14`.

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

To install the `clip` package, please refer to the [OpenAI GitHub repository](https://github.com/openai/clip).

```
import torch
import clip
import numpy as np
import sys
import clickhouse_connect

device = "cuda" if torch.cuda.is_available() else "cpu"
model, preprocess = clip.load("ViT-L/14", device=device)

# Search for images that contain both a dog and a cat
text = clip.tokenize(["a dog and a cat"]).to(device)

with torch.no_grad():
    text_features = model.encode_text(text)
    np_arr = text_features.detach().cpu().numpy()

    # Pass ClickHouse credentials here
    chclient = clickhouse_connect.get_client()

    params = {'v1': list(np_arr[0])}
    result = chclient.query("SELECT id, url FROM laion_5b_100m ORDER BY cosineDistance(vector, %(v1)s) LIMIT 100",
                            parameters=params)

    # Write the results to a simple HTML page that can be opened in the browser. Some URLs may have become obsolete.
    print("<html>")
    for r in result.result_rows:
        print("<img src = ", r[1], 'width="200" height="200">')
    print("</html>")
```

The result of the above search is shown below:

- [Introduction](#introduction)- [Dataset details](#dataset-details)- [Steps](#steps)
      - [Create table](#create-table)- [Load data](#load-table)- [Run a brute-force vector similarity search](#run-a-brute-force-vector-similarity-search)- [Build a vector similarity index](#build-vector-similarity-index)- [Perform ANN search](#perform-ann-search)- [Generate embeddings for search query](#generating-embeddings-for-search-query)

Was this page helpful?
title_wip

On the source server:

```
CREATE TABLE trips_mergetree_x3 AS trips_mergetree_third ENGINE = Distributed(perftest, default, trips_mergetree_third, rand());
```

The following query redistributes data:

```
INSERT INTO trips_mergetree_x3 SELECT * FROM trips_mergetree;
```

This takes 2454 seconds.

On three servers:

Q1: 0.212 seconds.
Q2: 0.438 seconds.
Q3: 0.733 seconds.
Q4: 1.241 seconds.

No surprises here, since the queries are scaled linearly.

We also have the results from a cluster of 140 servers:

Q1: 0.028 sec.
Q2: 0.043 sec.
Q3: 0.051 sec.
Q4: 0.072 sec.

In this case, the query processing time is determined above all by network latency.
We ran queries using a client located in a different datacenter than where the cluster was located, which added about 20 ms of latency.

## Summary[​](#summary "Direct link to Summary")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| servers Q1 Q2 Q3 Q4|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1, E5-2650v2 0.490 1.224 2.104 3.593|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 3, E5-2650v2 0.212 0.438 0.733 1.241|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1, AWS c5n.4xlarge 0.249 1.279 1.738 3.527|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1, AWS c5n.9xlarge 0.130 0.584 0.777 1.811|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 3, AWS c5n.9xlarge 0.057 0.231 0.285 0.641|  |  |  |  |  | | --- | --- | --- | --- | --- | | 140, E5-2650v2 0.028 0.043 0.051 0.072 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

- [Create the table trips](#create-the-table-trips)- [Load the data directly from object storage](#load-the-data-directly-from-object-storage)- [Sample queries](#sample-queries)- [Download of prepared partitions](#download-of-prepared-partitions)- [Results on single server](#results-on-single-server)- [Summary](#summary)

Was this page helpful?
NYPD Complaint Data

6 rows in set. Elapsed: 0.008 sec. Processed 208.99 thousand rows, 209.43 KB (27.14 million rows/s., 27.20 MB/s.)
```

## Next steps[​](#next-steps "Direct link to Next steps")

[A Practical Introduction to Sparse Primary Indexes in ClickHouse](/docs/guides/best-practices/sparse-primary-indexes) discusses the differences in ClickHouse indexing compared to traditional relational databases, how ClickHouse builds and uses a sparse primary index, and indexing best practices.

- [Prerequisites](#prerequisites)
  - [A note about the commands described in this guide](#a-note-about-the-commands-described-in-this-guide)- [Familiarize yourself with the TSV file](#familiarize-yourself-with-the-tsv-file)
    - [Look at the fields in the source TSV file](#look-at-the-fields-in-the-source-tsv-file)- [Determine the proper schema](#determine-the-proper-schema)- [DateTime fields](#datetime-fields)- [Make a plan](#make-a-plan)- [Concatenate the date and time fields](#concatenate-the-date-and-time-fields)- [Convert the date and time String to a DateTime64 type](#convert-the-date-and-time-string-to-a-datetime64-type)- [Create a table](#create-a-table)
            - [`ORDER BY` and `PRIMARY KEY` clauses](#order-by-and-primary-key-clauses)- [Finding the primary key of a table](#finding-the-primary-key-of-a-table)- [Preprocess and import data](#preprocess-import-data)
              - [`clickhouse-local` arguments used](#clickhouse-local-arguments-used)- [Validate the data](#validate-data)- [Run some queries](#run-queries)
                  - [Query 1. Compare the number of complaints by month](#query-1-compare-the-number-of-complaints-by-month)- [Query 2. Compare total number of complaints by borough](#query-2-compare-total-number-of-complaints-by-borough)- [Next steps](#next-steps)

Was this page helpful?
Analyzing Stack Overflow data with ClickHouse

3 rows in set. Elapsed: 4.779 sec. Processed 298.80 million rows, 3.16 GB (62.52 million rows/s., 661.05 MB/s.)
Peak memory usage: 6.05 GiB.
```

## Attribution[​](#attribution "Direct link to Attribution")

We thank Stack Overflow for providing this data under the `cc-by-sa 4.0` license, acknowledging their efforts and the original source of the data at <https://archive.org/details/stackexchange>.

- [Pre-prepared data](#pre-prepared-data)
  - [Posts](#posts)- [Votes](#votes)- [Comments](#comments)- [Users](#users)- [Badges](#badges)- [PostLinks](#postlinks)- [PostHistory](#posthistory)- [Original dataset](#original-dataset)
    - [Download](#download)- [Convert to JSON](#convert-to-json)- [Example queries](#example-queries)
      - [Most popular tags on Stack Overflow](#most-popular-tags-on-stack-overflow)- [User with the most answers (active accounts)](#user-with-the-most-answers-active-accounts)- [ClickHouse related posts with the most views](#clickhouse-related-posts-with-the-most-views)- [Most controversial posts](#most-controversial-posts)- [Attribution](#attribution)

Was this page helpful?
Star Schema Benchmark (SSB, 2009)

Denormalized table:

```
SELECT
    toYear(LO_ORDERDATE) AS year,
    S_NATION,
    P_CATEGORY,
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE
    C_REGION = 'AMERICA'
    AND S_REGION = 'AMERICA'
    AND (year = 1997 OR year = 1998)
    AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
GROUP BY
    year,
    S_NATION,
    P_CATEGORY
ORDER BY
    year ASC,
    S_NATION ASC,
    P_CATEGORY ASC;
```

### Q4.3[​](#q4-3 "Direct link to Q4.3")

```
SELECT
    D_YEAR,
    S_CITY,
    P_BRAND,
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM
    date,
    customer,
    supplier,
    part,
    lineorder
WHERE
    LO_CUSTKEY = C_CUSTKEY
    AND LO_SUPPKEY = S_SUPPKEY
    AND LO_PARTKEY = P_PARTKEY
    AND LO_ORDERDATE = D_DATEKEY
    AND C_REGION = 'AMERICA'
    AND S_NATION = 'UNITED STATES'
    AND (D_YEAR = 1997 OR D_YEAR = 1998)
    AND P_CATEGORY = 'MFGR#14'
GROUP BY
    D_YEAR,
    S_CITY,
    P_BRAND
ORDER BY
    D_YEAR,
    S_CITY,
    P_BRAND
```

Denormalized table:

```
SELECT
    toYear(LO_ORDERDATE) AS year,
    S_CITY,
    P_BRAND,
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM
    lineorder_flat
WHERE
    C_REGION = 'AMERICA'
    AND S_NATION = 'UNITED STATES'
    AND (year = 1997 OR year = 1998)
    AND P_CATEGORY = 'MFGR#14'
GROUP BY
    year,
    S_CITY,
    P_BRAND
ORDER BY
    year ASC,
    S_CITY ASC,
    P_BRAND ASC;
```

- [Data generation](#data-generation)- [Create tables](#create-tables)- [Import data](#import-data)- [Denormalized table](#denormalized-table)- [Queries](#queries)
          - [Q1.1](#q1-1)- [Q1.2](#q1-2)- [Q1.3](#q1-3)- [Q2.1](#q2-1)- [Q2.2](#q2-2)- [Q2.3](#q2-3)- [Q3.1](#q3-1)- [Q3.2](#q3-2)- [Q3.3](#q3-3)- [Q3.4](#q3-4)- [Q4.1](#q4-1)- [Q4.2](#q4-2)- [Q4.3](#q4-3)

Was this page helpful?
TPC-H (1999)

-- Scaling factor 100
INSERT INTO nation SELECT * FROM s3('https://clickhouse-datasets.s3.amazonaws.com/h/100/nation.tbl.gz', NOSIGN, CSV) SETTINGS format_csv_delimiter = '|', input_format_defaults_for_omitted_fields = 1, input_format_csv_empty_as_default = 1;
INSERT INTO region SELECT * FROM s3('https://clickhouse-datasets.s3.amazonaws.com/h/100/region.tbl.gz', NOSIGN, CSV) SETTINGS format_csv_delimiter = '|', input_format_defaults_for_omitted_fields = 1, input_format_csv_empty_as_default = 1;
INSERT INTO part SELECT * FROM s3('https://clickhouse-datasets.s3.amazonaws.com/h/100/part.tbl.gz', NOSIGN, CSV) SETTINGS format_csv_delimiter = '|', input_format_defaults_for_omitted_fields = 1, input_format_csv_empty_as_default = 1;
INSERT INTO supplier SELECT * FROM s3('https://clickhouse-datasets.s3.amazonaws.com/h/100/supplier.tbl.gz', NOSIGN, CSV) SETTINGS format_csv_delimiter = '|', input_format_defaults_for_omitted_fields = 1, input_format_csv_empty_as_default = 1;
INSERT INTO partsupp SELECT * FROM s3('https://clickhouse-datasets.s3.amazonaws.com/h/100/partsupp.tbl.gz', NOSIGN, CSV) SETTINGS format_csv_delimiter = '|', input_format_defaults_for_omitted_fields = 1, input_format_csv_empty_as_default = 1;
INSERT INTO customer SELECT * FROM s3('https://clickhouse-datasets.s3.amazonaws.com/h/100/customer.tbl.gz', NOSIGN, CSV) SETTINGS format_csv_delimiter = '|', input_format_defaults_for_omitted_fields = 1, input_format_csv_empty_as_default = 1;
INSERT INTO orders SELECT * FROM s3('https://clickhouse-datasets.s3.amazonaws.com/h/100/orders.tbl.gz', NOSIGN, CSV) SETTINGS format_csv_delimiter = '|', input_format_defaults_for_omitted_fields = 1, input_format_csv_empty_as_default = 1;
INSERT INTO lineitem SELECT * FROM s3('https://clickhouse-datasets.s3.amazonaws.com/h/100/lineitem.tbl.gz', NOSIGN, CSV) SETTINGS format_csv_delimiter = '|', input_format_defaults_for_omitted_fields = 1, input_format_csv_empty_as_default = 1;
```

## Queries[​](#queries "Direct link to Queries")

The 22 TPC-H queries can be found [here](https://github.com/ClickHouse/ClickHouse/tree/master/tests/benchmarks/tpc-h/queries) in the ClickHouse repository.

To get SQL standard compatible behavior and expected results, apply the settings from [`settings.json`](https://github.com/ClickHouse/ClickHouse/blob/master/tests/benchmarks/tpc-h/settings.json).
See the [README](https://github.com/ClickHouse/ClickHouse/blob/master/tests/benchmarks/tpc-h/README.md) for known issues and notes on specific queries.

**Correctness**

The result of the queries agrees with the official results unless mentioned otherwise. To verify, generate a TPC-H database with scale
factor = 1 (`dbgen`, see above) and compare with the [expected results in tpch-kit](https://github.com/gregrahn/tpch-kit/tree/master/dbgen/answers).

- [Data Generation and Import](#data-generation-and-import)- [Queries](#queries)

Was this page helpful?
title_wip

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

The dataset contains 0.5 trillion records.

See the video from FOSDEM 2023: <https://www.youtube.com/watch?v=JlcI2Vfz_uk>

And the presentation: <https://presentations.clickhouse.com/fosdem2023/>

Data source: <https://dumps.wikimedia.org/other/pageviews/>

Getting the list of links:

```
for i in {2015..2023}; do
  for j in {01..12}; do
    echo "${i}-${j}" >&2
    curl -sSL "https://dumps.wikimedia.org/other/pageviews/$i/$i-$j/" \
      | grep -oE 'pageviews-[0-9]+-[0-9]+\.gz'
  done
done | sort | uniq | tee links.txt
```

Downloading the data:

```
sed -r 's!pageviews-([0-9]{4})([0-9]{2})[0-9]{2}-[0-9]+\.gz!https://dumps.wikimedia.org/other/pageviews/\1/\1-\2/\0!' \
  links.txt | xargs -P3 wget --continue
```

(it will take about 3 days)

Creating a table:

```
CREATE TABLE wikistat
(
    time DateTime CODEC(Delta, ZSTD(3)),
    project LowCardinality(String),
    subproject LowCardinality(String),
    path String CODEC(ZSTD(3)),
    hits UInt64 CODEC(ZSTD(3))
)
ENGINE = MergeTree
ORDER BY (path, time);
```

Loading the data:

```
clickhouse-local --query "
  WITH replaceRegexpOne(_path, '^.+pageviews-(\\d{4})(\\d{2})(\\d{2})-(\\d{2})(\\d{2})(\\d{2}).gz$', '\1-\2-\3 \4-\5-\6')::DateTime AS time, 
       extractGroups(line, '^([^ \\.]+)(\\.[^ ]+)? +([^ ]+) +(\\d+) +(\\d+)$') AS values
  SELECT 
    time, 
    values[1] AS project,
    values[2] AS subproject,
    values[3] AS path,
    (values[4])::UInt64 AS hits
  FROM file('pageviews*.gz', LineAsString)
  WHERE length(values) = 5 FORMAT Native
" | clickhouse-client --query "INSERT INTO wikistat FORMAT Native"
```

Or loading the cleaning data:

```
INSERT INTO wikistat WITH
    parseDateTimeBestEffort(extract(_file, '^pageviews-([\\d\\-]+)\\.gz$')) AS time,
    splitByChar(' ', line) AS values,
    splitByChar('.', values[1]) AS projects
SELECT
    time,
    projects[1] AS project,
    projects[2] AS subproject,
    decodeURLComponent(values[2]) AS path,
    CAST(values[3], 'UInt64') AS hits
FROM s3(
    'https://clickhouse-public-datasets.s3.amazonaws.com/wikistat/original/pageviews*.gz',
    LineAsString)
WHERE length(values) >= 3
```

Was this page helpful?
ClickHouse playground

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

[ClickHouse Playground](https://sql.clickhouse.com) allows people to experiment with ClickHouse by running queries instantly, without setting up their server or cluster.
Several example datasets are available in Playground.

You can make queries to Playground using any HTTP client, for example [curl](https://curl.haxx.se) or [wget](https://www.gnu.org/software/wget/), or set up a connection using [JDBC](/docs/interfaces/jdbc) or [ODBC](/docs/interfaces/odbc) drivers. More information about software products that support ClickHouse is available [here](/docs/integrations).

## Credentials[​](#credentials "Direct link to Credentials")

|  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Value|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | HTTPS endpoint `https://play.clickhouse.com:443/`| Native TCP endpoint `play.clickhouse.com:9440`| User `explorer` or `play`| Password (empty) | | | | | | | | | |

## Limitations[​](#limitations "Direct link to Limitations")

The queries are executed as a read-only user. It implies some limitations:

- DDL queries aren't allowed
- INSERT queries aren't allowed

The service also have quotas on its usage.

## Examples[​](#examples "Direct link to Examples")

HTTPS endpoint example with `curl`:

```
curl "https://play.clickhouse.com/?user=explorer" --data-binary "SELECT 'Play ClickHouse'"
```

TCP endpoint example with [CLI](/docs/interfaces/cli):

```
clickhouse client --secure --host play.clickhouse.com --user explorer
```

## Playground specifications[​](#specifications "Direct link to Playground specifications")

our ClickHouse Playground is running with the following specifications:

- Hosted on Google Cloud (GCE) in the US Central region (US-Central-1)
- 3-replica setup
- 256 GiB of storage and 59 virtual CPUs each.

- [Credentials](#credentials)- [Limitations](#limitations)- [Examples](#examples)- [Playground specifications](#specifications)

Was this page helpful?