ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

Learn how to resolve ingest failures caused by stricter grant checking introduced in ClickHouse 23.9 for tables using `async\_inserts`. Update grants to fix errors.


## Resolving Ingest Failures After ClickHouse 23.9 Release[​](#resolving-ingest-failures-after-clickhouse-239-release "Direct link to Resolving Ingest Failures After ClickHouse 23.9 Release")

The root cause of these permissions errors has been identified and is due to more strict checking of grants in the new release for [`async_inserts`](/docs/optimize/asynchronous-inserts).

To fix this issue, grants must be updated for your service to work. Check the error message indicating the missing grants and add those needed grants manually. The required additional grants for the tables using `async_inserts` will either be the `SELECT` or `dictGet` grant.

## Add necessary GRANTs to affected tables and dictionaries[​](#add-necessary-grants-to-affected-tables-and-dictionaries "Direct link to Add necessary GRANTs to affected tables and dictionaries")

```
-- Add SELECT grant permissions
GRANT SELECT ON mydb.insertTable TO insert_role WITH GRANT OPTION

-- Add dictGet grant permissions
GRANT dictGet ON mydb.insertDictionary TO insert_role
```

To learn more about the `GRANT` command, please see [this page](/docs/sql-reference/statements/grant).

If you are unable to perform this change, then please contact [ClickHouse Support](https://clickhouse.com/support/program) for assistance.

Nov 17, 2022 · One min read
title_wip

There are multiple JSON variations among them, but the most commonly used for data ingestion is JSONEachRow. It expects one JSON object per row, each object separated by a newline.


## Importing JSON into ClickHouse[​](#importing-json-into-clickhouse "Direct link to Importing JSON into ClickHouse")

ClickHouse supports a wide range of [data formats for input and output](https://clickhouse.com/docs/interfaces/formats/). There are multiple JSON variations among them, but the most commonly used for data ingestion is [JSONEachRow](https://clickhouse.com/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](https://clickhouse.com/docs/interfaces/http/):

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

Using [CLI interface](https://clickhouse.com/docs/interfaces/cli/):

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

Instead of inserting data manually, you might consider to use one of [client libraries](https://clickhouse.com/docs/interfaces/) 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](https://clickhouse.com/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.

Mar 20, 2023 · One min read
title_wip

A short example on how to extract base types from JSON


## JSON Extract example[​](#json-extract-example "Direct link to JSON Extract example")

This is just a short example that illustrates the use of [JSONExtract](https://clickhouse.com/docs/sql-reference/functions/json-functions) functions.

Create a table:

```
CREATE TABLE default.json_extract_example
(
    `rawJSON` String EPHEMERAL,
    `a1` String DEFAULT JSONExtractString(rawJSON, 'a1'),
    `a2` Boolean DEFAULT JSONExtractBool(rawJSON, 'a2'),
    `a3.aa1` Float DEFAULT JSONExtractFloat(JSONExtractRaw(rawJSON, 'a3'), 'aa1'),
    `a3.aa2` UInt8 DEFAULT JSONExtractUInt(JSONExtractRaw(rawJSON, 'a3'), 'aa2')
)
ENGINE = MergeTree
ORDER BY (a1, a2)
```

Add your JSON raw string:

```
INSERT INTO default.json_extract_example (rawJSON) VALUES ('{"a1": "XX", "a2": true, "a3":{"aa1":23.11,"aa2":12}}');
```

Query your data:

```
SELECT *
FROM json_extract_example
FORMAT Pretty
```

Yields:

```
┏━━━━┳━━━━━━┳━━━━━━━━┳━━━━━━━━┓
┃ a1 ┃ a2   ┃ a3.aa1 ┃ a3.aa2 ┃
┡━━━━╇━━━━━━╇━━━━━━━━╇━━━━━━━━┩
│ XX │ true │  23.11 │     12 │
└────┴──────┴────────┴────────┘
```

Each stored as the original JSON type:

```
SELECT
    toTypeName(a1),
    toTypeName(a2),
    toTypeName(a3.aa1),
    toTypeName(a3.aa2)
FROM default.json_extract_example
FORMAT Pretty
```

```
┏━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┓
┃ toTypeName(a1) ┃ toTypeName(a2) ┃ toTypeName(a3.aa1) ┃ toTypeName(a3.aa2) ┃
┡━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━┩
│ String         │ Bool           │ Float32            │ UInt8              │
└────────────────┴────────────────┴────────────────────┴────────────────────┘
```

Sep 1, 2021 · One min read
title_wip

Simple example flow for extracting JSON data using a landing table with a Materialized View


## Question[​](#question "Direct link to Question")

How do I work with JSON message using a source or landing table to extract with a Materialized View?  
How do I work with JSON without the experimental JSON Object?

## Answer[​](#answer "Direct link to Answer")

A common pattern to work with JSON data is to send the data to a landing table and use JSONExtract functions to pull the data onto a new table using a Materialized View trigger.
This is normally done in the following flow and pattern:

```
source data --> MergeTree table --> Materialized View (with base table) --> application/client
```

The landing table should have a `raw` string field where you would store the raw json. It should also have one to two other fields that can be used for management of that table so that it could be partitioned and trimmed as the data ages.

\*some integrations can add fields to the original data for example if using the ClickHouse Kafka Connector Sink.

Simplified example below:

- create the example database

```
create database db1;
```

- create a landing table where your raw json will be inserted:

```
create table db1.table2_json_raw
(
    id Int32,
    timestamp DateTime,
    raw String
)
engine = MergeTree()
order by timestamp;
```

- create the base table for the materialized view

```
create table db1.table2_json_mv_base
(
 id Int32,
 timestamp DateTime,
 raw_string String,
 custId Int8,
 custName String
)
engine = MergeTree()
order by timestamp;
```

- create the materialized view to the base table

```
create materialized view db1.table2_json_mv to db1.table2_json_mv_base
AS SELECT
 id,
 timestamp,
 raw as raw_string,
 simpleJSONExtractRaw(raw, 'customerId') as custId,
 simpleJSONExtractRaw(raw, 'customerName') as custName
 FROM
db1.table2_json_raw;
```

- insert some sample rows

```
 insert into db1.table2_json_raw
 values
 (1, '2024-05-16 00:00:00', '{"customerId":1, "customerName":"ABC"}'),
 (2, '2024-05-16 00:00:01', '{"customerId":2, "customerName":"XYZ"}');
```

- view the results from the extraction and the materialized view that would be used in the queries

```
clickhouse-cloud :) select * from db1.table2_json_mv;

SELECT *
FROM db1.table2_json_mv

Query id: 12655fd3-567a-4dfb-9ef7-abc4b11ad044

┌─id─┬───────────timestamp─┬─raw_string─────────────────────────────┬─custId─┬─custName─┐
│  1 │ 2024-05-16 00:00:00 │ {"customerId":1, "customerName":"ABC"} │ 1      │ "ABC"    │
│  2 │ 2024-05-16 00:00:01 │ {"customerId":2, "customerName":"XYZ"} │ 2      │ "XYZ"    │
└────┴─────────────────────┴────────────────────────────────────────┴────────┴──────────┘
```

Additional Reference links:  
Materialized Views: <https://clickhouse.com/docs/guides/developer/cascading-materialized-views>
Working with JSON: <https://clickhouse.com/docs/integrations/data-formats/json#other-approaches>
JSON functions: <https://clickhouse.com/docs/sql-reference/functions/json-functions>

May 20, 2024 · 2 min read
title_wip

```
SELECT *
FROM rawEvents
LIMIT 5
FORMAT Vertical
```

```
Row 1:
──────
id:        124842852
type:      142
comment:   Pere prlpz commented on "Plantilles Enciclopèdia Catalana" (Diria que no cal fer res als articles. Es pot actualitzar els enllaços que es facin servir a les referències (tot i que l'antic encara ha...)
timestamp: 2024-04-18 16:22:29.000
title_url: https://ca.wikipedia.org/wiki/Tema:Wu36d6vfsiuu4jsi
topic:     eqiad.mediawiki.recentchange
user:      Pere prlpz

Row 2:
──────
id:        2473996748
type:      categorize
comment:   [[:File:Ruïne van een poortgebouw, RP-T-1976-29-6(R).jpg]] removed from category
timestamp: 2024-04-18 16:21:20.000
title_url: https://commons.wikimedia.org/wiki/Category:Pieter_Moninckx
topic:     eqiad.mediawiki.recentchange
user:      Warburg1866

Row 3:
──────
id:        311828596
type:      categorize
comment:   [[:Cujo (película)]] añadida a la categoría
timestamp: 2024-04-18 16:21:21.000
title_url: https://es.wikipedia.org/wiki/Categor%C3%ADa:Pel%C3%ADculas_basadas_en_obras_de_Stephen_King
topic:     eqiad.mediawiki.recentchange
user:      Beta15

Row 4:
──────
id:        311828597
type:      categorize
comment:   [[:Cujo (película)]] eliminada de la categoría
timestamp: 2024-04-18 16:21:21.000
title_url: https://es.wikipedia.org/wiki/Categor%C3%ADa:Trabajos_basados_en_obras_de_Stephen_King
topic:     eqiad.mediawiki.recentchange
user:      Beta15

Row 5:
──────
id:        48494536
type:      categorize
comment:   [[:braiteremmo]] ajoutée à la catégorie
timestamp: 2024-04-18 16:21:21.000
title_url: https://fr.wiktionary.org/wiki/Cat%C3%A9gorie:Wiktionnaire:Exemples_manquants_en_italien
topic:     eqiad.mediawiki.recentchange
user:      Àncilu bot
```

9. Let's see what types of events are coming in:

```
SELECT
    type,
    count()
FROM rawEvents
GROUP BY type
```

```
   ┌─type───────┬─count()─┐
1. │ 142        │       1 │
2. │ new        │    1003 │
3. │ categorize │   12228 │
4. │ log        │    1799 │
5. │ edit       │   17142 │
   └────────────┴─────────┘
```

Let's define a materialized view chained to our current materialized view. We will keep track of some aggregated stats per minute:

```
CREATE TABLE byMinute
(
    `dateTime` DateTime64(3, 'UTC') NOT NULL,
    `users` AggregateFunction(uniq, String),
    `pages` AggregateFunction(uniq, String),
    `updates` AggregateFunction(sum, UInt32)
)
ENGINE = AggregatingMergeTree
ORDER BY dateTime;

CREATE MATERIALIZED VIEW byMinute_mv TO byMinute
AS SELECT
    toStartOfMinute(timestamp) AS dateTime,
    uniqState(user) AS users,
    uniqState(title_url) AS pages,
    sumState(toUInt32(1)) AS updates
FROM rawEvents
GROUP BY dateTime;
```

9. We will need **-Merge** functions to view the results:

```
SELECT
    dateTime AS dateTime,
    uniqMerge(users) AS users,
    uniqMerge(pages) AS pages,
    sumMerge(updates) AS updates
FROM byMinute
GROUP BY dateTime
ORDER BY dateTime DESC
LIMIT 10;
```

Apr 27, 2024 · 6 min read
title_wip

The short answer is \*\*"no"\*\*. The key-value workload is among top positions in the list of cases when \*\*NOT\*\* to use ClickHouse.


## The short answer is "no"[​](#the-short-answer-is-no "Direct link to The short answer is \"no\"")

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](https://clickhouse.com/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](https://clickhouse.com/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](https://clickhouse.com/docs/engines/table-engines/special/join) table engine instead of normal `MergeTree` tables and [joinGet](https://clickhouse.com/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).

Sep 1, 2021 · 2 min read
title_wip

Commands to build LLVM and clang on Linux.


## build LLVM and clang on Linux[​](#build-llvm-and-clang-on-linux "Direct link to build LLVM and clang on Linux")

In order to build and contribute to ClickHouse, you must use [LLVM](https://llvm.org/) and [Clang](https://clang.llvm.org/).

These are the commands to build the latest version of LLVM and Clang on Linux:

```
git clone [email protected]:llvm/llvm-project.git
mkdir llvm-build
cd llvm-build
cmake -GNinja -DCMAKE_BUILD_TYPE:STRING=Release -DLLVM_ENABLE_PROJECTS=all -DLLVM_TARGETS_TO_BUILD=all ../llvm-project/llvm
time ninja
sudo ninja install
```

Apr 22, 2024 · One min read
title_wip

Mapping of metrics used in system.dashboards to Prometheus metrics in system.custom\_metrics


## Mapping of metrics used in system.dashboards to Prometheus metrics in `system.custom_metrics`[​](#mapping-of-metrics-used-in-systemdashboards-to-prometheus-metrics-in-systemcustom_metrics "Direct link to mapping-of-metrics-used-in-systemdashboards-to-prometheus-metrics-in-systemcustom_metrics")

The table below provides the mapping for the metrics used in `system.dashboards` to Prometheus metrics in `system.custom_metrics`.  
This is useful for customers who want to monitor for the same metrics found in `system.dashboards`.

### Mapping table for metrics in system.dashboards to Prometheus metrics in system.custom\_metrics[​](#mapping-table-for-metrics-in-systemdashboards-to-prometheus-metrics-in-systemcustom_metrics "Direct link to Mapping table for metrics in system.dashboards to Prometheus metrics in system.custom_metrics")
title_wip

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
title_wip

+ ClickHouseProfileEvents\_DiskS3CreateMultipartUpload + ClickHouseProfileEvents\_DiskS3CompleteMultipartUpload|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Cloud overview Disk S3 read req/sec ClickHouseProfileEvents\_DiskS3GetObject + ClickHouseProfileEvents\_DiskS3HeadObject + ClickHouseProfileEvents\_DiskS3ListObjects|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Cloud overview FS cache hit rate ClickHouseProfileEvents\_CachedReadBufferReadFromCacheBytes / (ClickHouseProfileEvents\_CachedReadBufferReadFromCacheBytes + ClickHouseProfileEvents\_CachedReadBufferReadFromSourceBytes)|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Cloud overview Page cache hit rate greatest(0, (sum(ClickHouseProfileEvents\_OSReadChars) - sum(ClickHouseProfileEvents\_OSReadBytes)) / (sum(ClickHouseProfileEvents\_OSReadChars) + sum(ClickHouseProfileEvents\_ReadBufferFromS3Bytes)))|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Cloud overview Network receive bytes/sec ClickHouseProfileEvents\_NetworkReceiveBytes|  |  |  | | --- | --- | --- | | Cloud overview Network send bytes/sec ClickHouseProfileEvents\_NetworkSendBytes | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
title_wip

We can refer to systems like MapReduce as distributed computing systems in which the reduce operation is based on distributed sorting. The most common open-source solution in this class is Apache Hadoop.


## Why not use something like MapReduce?[​](#why-not-use-something-like-mapreduce "Direct link to Why not use something like MapReduce?")

We can refer to systems like MapReduce as distributed computing systems in which the reduce operation is based on distributed sorting. The most common open-source solution in this class is [Apache Hadoop](http://hadoop.apache.org).

These systems aren’t appropriate for online queries due to their high latency. In other words, they can’t be used as the back-end for a web interface. These types of systems aren’t useful for real-time data updates. Distributed sorting isn’t the best way to perform reduce operations if the result of the operation and all the intermediate results (if there are any) are located in the RAM of a single server, which is usually the case for online queries. In such a case, a hash table is an optimal way to perform reduce operations. A common approach to optimizing map-reduce tasks is pre-aggregation (partial reduce) using a hash table in RAM. The user performs this optimization manually. Distributed sorting is one of the main causes of reduced performance when running simple map-reduce tasks.

Most MapReduce implementations allow you to execute arbitrary code on a cluster. But a declarative query language is better suited to OLAP to run experiments quickly. For example, Hadoop has Hive and Pig. Also consider Cloudera Impala or Shark (outdated) for Spark, as well as Spark SQL, Presto, and Apache Drill. Performance when running such tasks is highly sub-optimal compared to specialized systems, but relatively high latency makes it unrealistic to use these systems as the backend for a web interface.

Sep 1, 2021 · 2 min read
title_wip

Learn the recommended maximum limits for databases, tables, partitions, and parts in a ClickHouse cluster to ensure optimal performance.


## Recommended Maximum Databases, Tables, Partitions, and Parts in ClickHouse[​](#recommended-maximum-databases-tables-partitions-and-parts-in-clickhouse "Direct link to Recommended Maximum Databases, Tables, Partitions, and Parts in ClickHouse")

We recommend having a maximum of 1000 databases and 5000 tables, 50000 partitions, and 100000 parts across all databases for a service.

Databases in ClickHouse are more of a *namespace* and have no performance impact; 1000 databases is a loose guideline. However, the number of tables would affect the service startup time, so we recommend limiting the number of tables or partitions. ClickHouse provides a warning if these thresholds are hit.

These limits are also applicable to ClickHouse Cloud.

Sep 1, 2021 · One min read
title_wip

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.


## The short answer is "yes"[​](#the-short-answer-is-yes "Direct link to The short answer is \"yes\"")

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](https://clickhouse.com/docs/engines/table-engines/mergetree-family/replication).

Apr 22, 2022 · One min read