ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/faq/integration/index.md)

- [How do I export data from ClickHouse to a file?](https://clickhouse.com/docs/knowledgebase/file-export)
- [How to import JSON into ClickHouse?](/docs/integrations/data-formats/json/overview)
- [How do I connect Kafka to ClickHouse?](/docs/integrations/kafka)
- [Can I connect my Java application to ClickHouse?](/docs/integrations/jdbc/jdbc-with-clickhouse)
- [Can ClickHouse read tables from MySQL?](/docs/integrations/mysql)
- [Can ClickHouse read tables from PostgreSQL](/docs/integrations/postgresql/connecting-to-postgresql)
- [What if I have a problem with encodings when connecting to Oracle via ODBC?](/docs/faq/integration/oracle-odbc)

Don't see what you're looking for?

Check out our [Knowledge Base](/docs/knowledgebase) and also browse the many helpful articles found here in the documentation.

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/faq/integration/json-import.md)

ClickHouse supports a wide range of [data formats for input and output](/docs/interfaces/formats). There are multiple JSON variations among them, but the most commonly used for data ingestion is [JSONEachRow](/docs/interfaces/formats/JSONEachRow). It expects one JSON object per row, each object separated by a newline.

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

Using [HTTP interface](/docs/interfaces/http):

```
$ echo '{"foo":"bar"}' | curl 'http://localhost:8123/?query=INSERT%20INTO%20test%20FORMAT%20JSONEachRow' --data-binary @-
```

Using [CLI interface](/docs/interfaces/cli):

```
$ echo '{"foo":"bar"}'  | clickhouse-client --query="INSERT INTO test FORMAT JSONEachRow"
```

Instead of inserting data manually, you might consider to use an [integration tool](/docs/integrations) instead.

## Useful settings[​](#useful-settings "Direct link to Useful settings")

- `input_format_skip_unknown_fields` allows to insert JSON even if there were additional fields not present in table schema (by discarding them).
- `input_format_import_nested_json` allows to insert nested JSON objects into columns of [Nested](/docs/sql-reference/data-types/nested-data-structures/nested) type.

Note

Settings are specified as `GET` parameters for the HTTP interface or as additional command-line arguments prefixed with `--` for the `CLI` interface.

- [Examples](#examples)- [Useful settings](#useful-settings)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/faq/integration/oracle-odbc.md)

If you use Oracle as a source of ClickHouse external dictionaries via Oracle ODBC driver, you need to set the correct value for the `NLS_LANG` environment variable in `/etc/default/clickhouse`. For more information, see the [Oracle NLS\_LANG FAQ](https://www.oracle.com/technetwork/products/globalization/nls-lang-099431.html).

**Example**

```
NLS_LANG=RUSSIAN_RUSSIA.UTF8
```

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/faq/operations/index.md)

- [Which ClickHouse version should I use in production?](/docs/faq/operations/production)
- [Is it possible to deploy ClickHouse with separate storage and compute?](/docs/faq/operations/deploy-separate-storage-and-compute)
- [Is it possible to delete old records from a ClickHouse table?](/docs/faq/operations/delete-old-data)
- [How do I configure ClickHouse Keeper?](/docs/guides/sre/keeper/clickhouse-keeper)
- [Can ClickHouse integrate with LDAP?](/docs/guides/sre/configuring-ldap)
- [How do I configure users, roles and permissions in ClickHouse?](/docs/operations/access-rights)
- [Can you update or delete rows in ClickHouse?](/docs/guides/developer/mutations)
- [Does ClickHouse support multi-region replication?](/docs/faq/operations/multi-region-replication)

Don't see what you're looking for?

Check out our [Knowledge Base](/docs/knowledgebase) and also browse the many helpful articles found here in the documentation.

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/faq/operations/separate_storage.md)

The short answer is "yes".

Object storage (S3, GCS) can be used as the elastic primary storage backend for data in ClickHouse tables. [S3-backed MergeTree](/docs/integrations/s3) and [GCS-backed MergeTree](/docs/integrations/gcs) guides are published. Only metadata is stored locally on compute nodes in this configuration. You can easily upscale and downscale compute resources in this setup as additional nodes only need to replicate metadata.

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/faq/operations/multi-region-replication.md)

The short answer is "yes". However, we recommend keeping latency between all regions/datacenters in two-digit range, otherwise write performance will suffer as it goes through distributed consensus protocol. For example, replication between US coasts will likely work fine, but between the US and Europe won't.

Configuration-wise there's no difference compared to single-region replication, simply use hosts that are located in different locations for replicas.

For more information, see [full article on data replication](/docs/engines/table-engines/mergetree-family/replication).

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/faq/use-cases/index.md)

- [Can I use ClickHouse as a time-series database?](/docs/knowledgebase/time-series)
- [Can I use ClickHouse as a key-value storage?](/docs/knowledgebase/key-value)

Don't see what you're looking for?

Check out our [Knowledge Base](/docs/knowledgebase) and also browse the many helpful articles found here in the documentation.

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/faq/use-cases/key-value.md)

The short answer is **"no"**. The key-value workload is among top positions in the list of cases when **NOT** to use ClickHouse. It's an [OLAP](/docs/faq/general/olap) system after all, while there are many excellent key-value storage systems out there.

However, there might be situations where it still makes sense to use ClickHouse for key-value-like queries. Usually, it's some low-budget products where the main workload is analytical in nature and fits ClickHouse well, but there's also some secondary process that needs a key-value pattern with not so high request throughput and without strict latency requirements. If you had an unlimited budget, you would have installed a secondary key-value database for this secondary workload, but in reality, there's an additional cost of maintaining one more storage system (monitoring, backups, etc.) which might be desirable to avoid.

If you decide to go against recommendations and run some key-value-like queries against ClickHouse, here are some tips:

- The key reason why point queries are expensive in ClickHouse is its sparse primary index of main [MergeTree table engine family](/docs/engines/table-engines/mergetree-family/mergetree). This index can't point to each specific row of data, instead, it points to each N-th and the system has to scan from the neighboring N-th row to the desired one, reading excessive data along the way. In a key-value scenario, it might be useful to reduce the value of N with the `index_granularity` setting.
- ClickHouse keeps each column in a separate set of files, so to assemble one complete row it needs to go through each of those files. Their count increases linearly with the number of columns, so in the key-value scenario, it might be worth avoiding using many columns and put all your payload in a single `String` column encoded in some serialization format like JSON, Protobuf, or whatever makes sense.
- There's an alternative approach that uses [Join](/docs/engines/table-engines/special/join) table engine instead of normal `MergeTree` tables and [joinGet](/docs/sql-reference/functions/other-functions#joinGet) function to retrieve the data. It can provide better query performance but might have some usability and reliability issues. Here's an [usage example](https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/00800_versatile_storage_join.sql#L49-L51).

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/faq/use-cases/time-series.md)

*Note: Please see the blog [Working with Time series data in ClickHouse](https://clickhouse.com/blog/working-with-time-series-data-and-functions-ClickHouse) for additional examples of using ClickHouse for time series analysis.*

ClickHouse is a generic data storage solution for [OLAP](/docs/faq/general/olap) workloads, while there are many specialized [time-series database management systems](https://clickhouse.com/engineering-resources/what-is-time-series-database). Nevertheless, ClickHouse's [focus on query execution speed](/docs/concepts/why-clickhouse-is-so-fast) allows it to outperform specialized systems in many cases. There are many independent benchmarks on this topic out there, so we're not going to conduct one here. Instead, let's focus on ClickHouse features that are important to use if that's your use case.

First of all, there are **[specialized codecs](/docs/sql-reference/statements/create/table#specialized-codecs)** which make typical time-series. Either common algorithms like `DoubleDelta` and `Gorilla` or specific to ClickHouse like `T64`.

Second, time-series queries often hit only recent data, like one day or one week old. It makes sense to use servers that have both fast NVMe/SSD drives and high-capacity HDD drives. ClickHouse [TTL](/docs/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-ttl) feature allows to configure keeping fresh hot data on fast drives and gradually move it to slower drives as it ages. Rollup or removal of even older data is also possible if your requirements demand it.

Even though it's against ClickHouse philosophy of storing and processing raw data, you can use [materialized views](/docs/sql-reference/statements/create/view) to fit into even tighter latency or costs requirements.

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/getting-started/quick-start/index.mdx)

[### Cloud quick start

recommended

Get started with ClickHouse Cloud in minutes.
We'll walk you through creating a ClickHouse service,
connecting to ClickHouse and adding data to your service via
CDC, SQL console, local client or file upload.](/docs/getting-started/quick-start/cloud)[Get started](clickhouse.com/docs/getting-started/quick-start/cloud)

[### OSS quick start

Get started with OSS ClickHouse in minutes. You'll
learn how to install ClickHouse server and client locally, create a
table and insert some example data into it, and explore the many ways
you can get your own data into ClickHouse.](/docs/getting-started/quick-start/oss)[Get started](clickhouse.com/docs/getting-started/quick-start/oss)

[Not sure which to choose? Click here to learn about the differences between Cloud and OSS ClickHouse](/docs/cloud/overview)

Was this page helpful?
Geo Data using the Cell Tower Dataset

#### Specify the query used for the map[​](#specify-the-query-used-for-the-map "Direct link to Specify the query used for the map")

A deck.gl Scatterplot requires a longitude and latitude, and one or more filters can also be applied to the query. In this example two filters are applied, one for cell towers with UMTS radios, and one for the Mobile country code assigned to The Netherlands.

The fields `lon` and `lat` contain the longitude and latitude:

Add a filter with `mcc` = `204` (or substitute any other `mcc` value):

Add a filter with `radio` = `'UMTS'` (or substitute any other `radio` value, you can see the choices in the output of `DESCRIBE TABLE cell_towers`):

This is the full configuration for the chart that filters on `radio = 'UMTS'` and `mcc = 204`:

Click on **UPDATE CHART** to render the visualization.

### Add the charts to a **dashboard**[​](#add-the-charts-to-a-dashboard "Direct link to add-the-charts-to-a-dashboard")

This screenshot shows cell tower locations with LTE, UMTS, and GSM radios. The charts are all created in the same way, and they're added to a dashboard.

Tip

The data is also available for interactive queries in the [Playground](https://sql.clickhouse.com).

This [example](https://sql.clickhouse.com?query_id=UV8M4MAGS2PWAUOAYAAARM) will populate the username and even the query for you.

Although you can't create tables in the Playground, you can run all of the queries and even use Superset (adjust the host name and port number).

- [Goal](#goal)- [Get the dataset](#get-the-dataset)
    - [Load the sample data](#load-the-sample-data)- [Examine the schema of the cell\_towers table](#examine-the-schema-of-the-cell_towers-table)- [Run some example queries](#examples)- [Use case: incorporate geo data](#use-case)- [Review of the schema](#review-of-the-schema)- [Build visualizations with Apache Superset](#build-visualizations-with-apache-superset)- [Official ClickHouse connect driver](#official-clickhouse-connect-driver)- [Mapbox](#mapbox)- [Deploy Superset version 2.0.0](#deploy-superset-version-200)
                  - [Add your ClickHouse service as a Superset database](#add-your-clickhouse-service-as-a-superset-database)- [Add the table **cell\_towers** as a Superset **dataset**](#add-the-table-cell_towers-as-a-superset-dataset)- [Create some **charts**](#create-some-charts)- [Add the charts to a **dashboard**](#add-the-charts-to-a-dashboard)

Was this page helpful?
COVID-19 Open-Data

10. This query calculates the percentage of change in new cases each day, and includes a simple `increase` or `decrease` column in the result set:

```
WITH confirmed_lag AS (
  SELECT
    *,
    lagInFrame(new_confirmed) OVER(
      PARTITION BY location_key
      ORDER BY date
    ) AS confirmed_previous_day
  FROM covid19
),
confirmed_percent_change AS (
  SELECT
    *,
    COALESCE(ROUND((new_confirmed - confirmed_previous_day) / confirmed_previous_day * 100), 0) AS percent_change
  FROM confirmed_lag
)
SELECT
  date,
  new_confirmed,
  percent_change,
  CASE
    WHEN percent_change > 0 THEN 'increase'
    WHEN percent_change = 0 THEN 'no change'
    ELSE 'decrease'
  END AS trend
FROM confirmed_percent_change
WHERE location_key = 'US_DC';
```

The results look like

```
┌───────date─┬─new_confirmed─┬─percent_change─┬─trend─────┐
│ 2020-03-08 │             0 │            nan │ decrease  │
│ 2020-03-09 │             2 │            inf │ increase  │
│ 2020-03-10 │             0 │           -100 │ decrease  │
│ 2020-03-11 │             6 │            inf │ increase  │
│ 2020-03-12 │             0 │           -100 │ decrease  │
│ 2020-03-13 │             0 │            nan │ decrease  │
│ 2020-03-14 │             6 │            inf │ increase  │
│ 2020-03-15 │             1 │            -83 │ decrease  │
│ 2020-03-16 │             5 │            400 │ increase  │
│ 2020-03-17 │             9 │             80 │ increase  │
│ 2020-03-18 │             8 │            -11 │ decrease  │
│ 2020-03-19 │            32 │            300 │ increase  │
│ 2020-03-20 │             6 │            -81 │ decrease  │
│ 2020-03-21 │            21 │            250 │ increase  │
│ 2020-03-22 │            18 │            -14 │ decrease  │
│ 2020-03-23 │            21 │             17 │ increase  │
│ 2020-03-24 │            46 │            119 │ increase  │
│ 2020-03-25 │            48 │              4 │ increase  │
│ 2020-03-26 │            36 │            -25 │ decrease  │
│ 2020-03-27 │            37 │              3 │ increase  │
│ 2020-03-28 │            38 │              3 │ increase  │
│ 2020-03-29 │            59 │             55 │ increase  │
│ 2020-03-30 │            94 │             59 │ increase  │
│ 2020-03-31 │            91 │             -3 │ decrease  │
│ 2020-04-01 │            67 │            -26 │ decrease  │
│ 2020-04-02 │           104 │             55 │ increase  │
│ 2020-04-03 │           145 │             39 │ increase  │
```

Note

As mentioned in the [GitHub repo](https://github.com/GoogleCloudPlatform/covid-19-open-data), the dataset is no longer updated as of September 15, 2022.

Was this page helpful?
title_wip

Enter your question : Who won the golden boot
Salvatore Schillaci of Italy won the Golden Boot at the 1990 FIFA World Cup.


Enter a topic : Cricket world cup
Generating the embedding for 'Cricket world cup' and collecting 100 articles related to it from ClickHouse...

Enter your question : Which country has hosted the world cup most times
England and Wales have hosted the Cricket World Cup the most times, with the tournament being held in these countries five times - in 1975, 1979, 1983, 1999, and 2019.

$
```

Code:

```
import sys
import time
from openai import OpenAI
import clickhouse_connect

ch_client = clickhouse_connect.get_client(compress=False) # Pass ClickHouse credentials here
openai_client = OpenAI() # Set the OPENAI_API_KEY environment variable

def get_embedding(text, model):
  text = text.replace("\n", " ")
  return openai_client.embeddings.create(input = [text], model=model, dimensions=1536).data[0].embedding

while True:
    # Take the topic of interest from user
    print("Enter a topic : ", end="", flush=True)
    input_query = sys.stdin.readline()
    input_query = input_query.rstrip()

    # Generate an embedding vector for the search topic and query ClickHouse
    print("Generating the embedding for '" + input_query + "' and collecting 100 articles related to it from ClickHouse...");
    embedding = get_embedding(input_query,
                              model='text-embedding-3-large')

    params = {'v1':embedding, 'v2':100}
    result = ch_client.query("SELECT id,title,text FROM dbpedia ORDER BY cosineDistance(vector, %(v1)s) LIMIT %(v2)s", parameters=params)

    # Collect all the matching articles/documents
    results = ""
    for row in result.result_rows:
        results = results + row[2]

    print("\nEnter your question : ", end="", flush=True)
    question = sys.stdin.readline();

    # Prompt for the OpenAI Chat API
    query = f"""Use the below content to answer the subsequent question. If the answer cannot be found, write "I don't know."

Content:
\"\"\"
{results}
\"\"\"

Question: {question}"""

    GPT_MODEL = "gpt-3.5-turbo"
    response = openai_client.chat.completions.create(
        messages=[
        {'role': 'system', 'content': "You answer questions about {input_query}."},
        {'role': 'user', 'content': query},
       ],
       model=GPT_MODEL,
       temperature=0,
    )

    # Print the answer to the question!
    print(response.choices[0].message.content)
    print("\n")
```

- [Dataset details](#dataset-details)- [Create table](#create-table)- [Load table](#load-table)- [Semantic search](#semantic-search)- [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)- [Generating embeddings for search query](#generating-embeddings-for-search-query)- [Q&A demo application](#q-and-a-demo-application)

Was this page helpful?
Foursquare places

Additionally, two `UInt32` `MATERIALIZED` columns, `mercator_x` and `mercator_y` are created
that map the lat/lon coordinates to the [Web Mercator projection](https://en.wikipedia.org/wiki/Web_Mercator_projection)
for easier segmentation of the map into tiles:

```
mercator_x UInt32 MATERIALIZED 0xFFFFFFFF * ((longitude + 180) / 360),
mercator_y UInt32 MATERIALIZED 0xFFFFFFFF * ((1 / 2) - ((log(tan(((latitude + 90) / 360) * pi())) / 2) / pi())),
```

Let's break down what is happening above for each column.

**mercator\_x**

This column converts a longitude value into an X coordinate in the Mercator projection:

- `longitude + 180` shifts the longitude range from [-180, 180] to [0, 360]
- Dividing by 360 normalizes this to a value between 0 and 1
- Multiplying by `0xFFFFFFFF` (hex for maximum 32-bit unsigned integer) scales this normalized value to the full range of a 32-bit integer

**mercator\_y**

This column converts a latitude value into a Y coordinate in the Mercator projection:

- `latitude + 90` shifts latitude from [-90, 90] to [0, 180]
- Dividing by 360 and multiplying by pi() converts to radians for the trigonometric functions
- The `log(tan(...))` part is the core of the Mercator projection formula
- multiplying by `0xFFFFFFFF` scales to the full 32-bit integer range

Specifying `MATERIALIZED` makes sure that ClickHouse calculates the values for these
columns when we `INSERT` the data, without having to specify these columns (which aren't
part of the original data schema) in the `INSERT statement.

The table is ordered by `mortonEncode(mercator_x, mercator_y)` which produces a
Z-order space-filling curve of `mercator_x`, `mercator_y` in order to significantly
improve geospatial query performance. This Z-order curve ordering ensures data is
physically organized by spatial proximity:

```
ORDER BY mortonEncode(mercator_x, mercator_y)
```

Two `minmax` indices are also created for faster search:

```
INDEX idx_x mercator_x TYPE minmax,
INDEX idx_y mercator_y TYPE minmax
```

As you can see, ClickHouse has absolutely everything you need for real-time
mapping applications!

Run the following query to load the data:

```
INSERT INTO foursquare_mercator 
SELECT * FROM s3('s3://fsq-os-places-us-east-1/release/dt=2025-04-08/places/parquet/*')
```

## Visualizing the data[​](#data-visualization "Direct link to Visualizing the data")

To see what's possible with this dataset, check out [adsb.exposed](https://adsb.exposed/?dataset=Places&zoom=5&lat=52.3488&lng=4.9219).
adsb.exposed was originally built by co-founder and CTO Alexey Milovidov to visualize ADS-B (Automatic Dependent Surveillance-Broadcast)
flight data, which is 1000x times larger. During a company hackathon Alexey added the Foursquare data to the tool.

Some of our favourite visualizations are produced here below for you to enjoy.

- [Dataset](#dataset)- [Data exploration](#data-exploration)- [Loading the data into ClickHouse](#loading-the-data)- [Visualizing the data](#data-visualization)

Was this page helpful?
title_wip

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

Dataset contains all events on GitHub from 2011 to Dec 6 2020, the size is 3.1 billion records. Download size is 75 GB and it will require up to 200 GB space on disk if stored in a table with lz4 compression.

Full dataset description, insights, download instruction and interactive queries are posted in this [repository](https://github.com/ClickHouse/github-explorer)

Was this page helpful?