ClickHouse/clickhouse-docs-embeddings huggingface dataset:
Optimizing for S3 Insert and Read Performance

0 rows in set. Elapsed: 52.992 sec. Processed 59.82 million rows, 24.03 GB (1.13 million rows/s., 453.50 MB/s.)
Peak memory usage: 26.57 GiB.
```

### Optimize on insert[​](#optimize-on-insert "Direct link to Optimize on insert")

In ClickHouse, the `optimize_on_insert` setting controls whether data parts are merged during the insert process. When enabled (`optimize_on_insert = 1` by default), small parts are merged into larger ones as they're inserted, improving query performance by reducing the number of parts that need to be read. However, this merging adds overhead to the insert process, potentially slowing down high-throughput insertions.

Disabling this setting (`optimize_on_insert = 0`) skips merging during inserts, allowing data to be written more quickly, especially when handling frequent small inserts. The merging process is deferred to the background, allowing for better insert performance but temporarily increasing the number of small parts, which may slow down queries until the background merge completes. This setting is ideal when insert performance is a priority, and the background merge process can handle optimization efficiently later. As shown below, disabling setting can improve insert throughput:

```
SELECT *
FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/by_month/*.parquet')
SETTINGS parallel_distributed_insert_select = 2, min_insert_block_size_rows = 0, max_insert_threads = 4, min_insert_block_size_bytes = 2863311530, insert_deduplicate = 0, optimize_on_insert = 0

0 rows in set. Elapsed: 49.688 sec. Processed 59.82 million rows, 24.03 GB (1.20 million rows/s., 483.66 MB/s.)
```

## Misc notes[​](#misc-notes "Direct link to Misc notes")

- For low memory scenarios, consider lowering `max_insert_delayed_streams_for_parallel_write` if inserting into S3.

- [Insert Mechanics (single node)](#insert-mechanics-single-node)
  - [Insert block size](#insert-block-size)- [Insert parallelism](#insert-parallelism)- [Measuring performance](#measuring-performance)- [Impact of hardware size](#impact-of-hardware-size)- [Region locality](#region-locality)- [Formats](#formats)- [Example dataset](#example-dataset)- [Using threads for reads](#using-threads-for-reads)- [Tuning threads and block size for inserts](#tuning-threads-and-block-size-for-inserts)- [Scaling with resources and nodes](#scaling-with-resources-and-nodes)
                  - [Vertical scaling](#vertical-scaling)- [Horizontal scaling](#horizontal-scaling)- [Further tuning](#further-tuning)
                    - [Disable de-duplication](#disable-de-duplication)- [Optimize on insert](#optimize-on-insert)- [Misc notes](#misc-notes)

Was this page helpful?
Connecting Splunk to ClickHouse

Our final dashboard should look like this:

## Time series data[​](#time-series-data "Direct link to Time series data")

Splunk has hundreds of built-in functions that dashboards can use for visualization and presentation of time series data. This example will combine SQL + SPL to create a query that can work with time series data in Splunk

```
dbxquery query="SELECT time, orig_h, duration
FROM "demo"."conn" WHERE time >= now() - interval 1 HOURS" connection="chc"
| eval time = strptime(time, "%Y-%m-%d %H:%M:%S.%3Q")
| eval _time=time
| timechart avg(duration) as duration by orig_h
| eval duration=round(duration/60)
| sort - duration:
```

## Learn more[​](#learn-more "Direct link to Learn more")

If you'd like to find more information about Splunk DB Connect and how to build dashboards, please visit the [Splunk documentation](https://docs.splunk.com/Documentation).

- [Goal​](#goal)- [Prerequisites](#prerequisites)- [Install and configure DB Connect on Splunk Enterprise](#install-and-configure-db-connect-on-splunk-enterprise)- [Configure JDBC for ClickHouse](#configure-jdbc-for-clickhouse)- [Connect Splunk search to ClickHouse](#connect-splunk-search-to-clickhouse)- [Run a SQL query](#run-a-sql-query)- [Create a dashboard](#create-a-dashboard)- [Time series data](#time-series-data)- [Learn more](#learn-more)

Was this page helpful?
title_wip

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

This section describes how to integrate ClickHouse with various common database management, analysis and visualization tools.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Tool Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [DataGrip](/docs/integrations/datagrip) Powerful database IDE|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [DBeaver](/docs/integrations/dbeaver) Database administration and development tool|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [DbVisualizer](/docs/integrations/dbvisualizer) Database management tool for developers, DBAs, and analysts|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [Jupyter Notebooks](/docs/integrations/jupysql) Interactive notebooks for code, visualizations, and text|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [QStudio](/docs/integrations/qstudio) Free, open-source SQL GUI client|  |  |  |  | | --- | --- | --- | --- | | [TABLUM.IO](/docs/integrations/tablumio) Cloud-based data visualization platform|  |  | | --- | --- | | [marimo](/docs/integrations/marimo) Open-source reactive notebook for Python with SQL built-in | | | | | | | | | | | | | | | |

Was this page helpful?
SQL Console

## Sharing queries[​](#sharing-queries "Direct link to Sharing queries")

The SQL console enables you to share queries with your team. When a query is shared, all members of the team can see and edit the query. Shared queries are a great way to collaborate with your team.

To share a query, click the 'Share' button in the query toolbar.

A dialog will open, allowing you to share the query with all members of a team. If you have multiple teams, you can select which team to share the query with.

In some scenarios, it may be necessary to adjust the axis scales for each field independently. This can also be accomplished in the 'Advanced' section of the chart configuration pane by specifying min and max values for an axis range. As an example, the above chart looks good, but in order to demonstrate the correlation between our `trip_total` and `fare_total` fields, the axis ranges need some adjustment:

- [Exploring tables](#exploring-tables)
  - [Viewing table list and schema info](#viewing-table-list-and-schema-info)- [Exploring table data](#exploring-table-data)- [Inspecting cell data](#inspecting-cell-data)- [Filtering and sorting tables](#filtering-and-sorting-tables)
    - [Sorting a table](#sorting-a-table)- [Filtering a table](#filtering-a-table)- [Filtering and sorting together](#filtering-and-sorting-together)- [Creating a query from filters and sorts](#creating-a-query-from-filters-and-sorts)- [Creating and running a query](#creating-and-running-a-query)
      - [Creating a query](#creating-a-query)- [Running a query](#running-a-query)- [Canceling a query](#canceling-a-query)- [Saving a query](#saving-a-query)- [Using GenAI to manage queries](#using-genai-to-manage-queries)
        - [Table setup](#table-setup)- [Debugging](#debugging)- [Advanced querying features](#advanced-querying-features)
          - [Searching query results](#searching-query-results)- [Adjusting pagination settings](#adjusting-pagination-settings)- [Exporting query result data](#exporting-query-result-data)- [Visualizing query data](#visualizing-query-data)
            - [Creating charts](#creating-charts)- [Customizing charts](#customizing-charts)- [Sharing queries](#sharing-queries)

Was this page helpful?
Connect Streamkap to ClickHouse

For better performance on
large tables, especially if you don’t need to read all the columns and for one-off analytical queries, you can use the argMax function to manually select the latest record for each primary key:

```
SELECT key,
       argMax(col1, version) AS col1,
       argMax(col2, version) AS col2
FROM t
WHERE <your predicates>
GROUP BY key;
```

For production use cases and concurrent recurrent end user queries, Materialized Views can be used to model the data to better fit the downstream access patterns.

## Further Reading[​](#further-reading "Direct link to Further Reading")

- [Streamkap Website](https://streamkap.com/)
- [Streamkap Documentation for ClickHouse](https://docs.streamkap.com/clickhouse)
- [Blog: Streaming with Change Data Capture to ClickHouse](https://streamkap.com/blog/streaming-with-change-data-capture-to-clickhouse)
- [ClickHouse Documentation: ReplacingMergeTree](https://streamkap.com/blog/streaming-with-change-data-capture-to-clickhouse)

- [Key Features](#key-features)- [Getting Started](#started)
    - [Prerequisites](#prerequisites)- [Step 1: Configure the Source in Streamkap](#configure-clickhouse-source)- [Step 2: Configure the ClickHouse Destination](#configure-clickhouse-dest)- [Step 3: Create and Run the Pipeline](#run-pipeline)- [Step 4: Verify the Data in ClickHouse](#verify-data-clickhoouse)- [How it Works with ClickHouse](#how-it-works-with-clickhouse)
      - [Table Engine and Data Handling](#table-engine-data-handling)- [Metadata Columns](#metadata-columns)- [Querying the Latest Data](#query-latest-data)- [Further Reading](#further-reading)

Was this page helpful?
Connecting Tableau to ClickHouse

## Install the connector manually[​](#install-the-connector-manually "Direct link to Install the connector manually")

In case you use an outdated Tableau Desktop version that doesn't include the connector by default, you can install it manually by following these steps:

1. Download the latest taco file from [Tableau Exchange](https://exchange.tableau.com/products/1064)
2. Place the taco file in
   - macOS: `~/Documents/My Tableau Repository/Connectors`
   - Windows: `C:\Users\[Windows User]\Documents\My Tableau Repository\Connectors`
3. Restart Tableau Desktop, if your setup went successfully, you will set the connector under the `New Data Source` section.

## Connection and analysis tips[​](#connection-and-analysis-tips "Direct link to Connection and analysis tips")

For more guidance on optimizing your Tableau-ClickHouse integration,
please visit [Connection Tips](/docs/integrations/tableau/connection-tips) and [Analysis Tips](/docs/integrations/tableau/analysis-tips).

## Tests[​](#tests "Direct link to Tests")

The connector is being tested with the [TDVT framework](https://tableau.github.io/connector-plugin-sdk/docs/tdvt) and currently maintains a 97% coverage ratio.

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

You can connect Tableau to ClickHouse using the generic ODBC/JDBC ClickHouse driver. However, this
connector streamlines the connection setup process. If you have any issues with the connector, feel free to reach out
on [GitHub](https://github.com/ClickHouse/clickhouse-tableau-connector-jdbc/issues).

- [Setup required prior usage](#setup-required-prior-usage)- [Configure a ClickHouse data source in Tableau](#configure-a-clickhouse-data-source-in-tableau)- [Building Visualizations in Tableau](#building-visualizations-in-tableau)- [Install the connector manually](#install-the-connector-manually)- [Connection and analysis tips](#connection-and-analysis-tips)- [Tests](#tests)- [Summary](#summary)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/sql-clients/tablum.md)

Community Maintained

## Open the TABLUM.IO startup page[​](#open-the-tablumio-startup-page "Direct link to Open the TABLUM.IO startup page")

Note

You can install a self-hosted version of TABLUM.IO on your Linux server in docker.

## 1. Sign up or sign in to the service[​](#1-sign-up-or-sign-in-to-the-service "Direct link to 1. Sign up or sign in to the service")

First, sign up to TABLUM.IO using your email or use a quick-login via accounts in Google or Facebook.

## 2. Add a ClickHouse connector[​](#2-add-a-clickhouse-connector "Direct link to 2. Add a ClickHouse connector")

Gather your ClickHouse connection details, navigate to the **Connector** tab, and fill in the host URL, port, username, password, database name, and connector's name. After completing these fields, click on **Test connection** button to validate the details and then click on **Save connector for me** to make it persistent.

Tip

Make sure that you specify the correct **HTTP** port and toggle **SSL** mode according to your connection details.

Tip

Typically, the port is 8443 when using TLS or 8123 when not using TLS.

## 3. Select the connector[​](#3-select-the-connector "Direct link to 3. Select the connector")

Navigate to the **Dataset** tab. Select recently created ClickHouse connector in the dropdown. In the right panel, you will see the list of available tables and schemas.

## 4. Input a SQL query and run it[​](#4-input-a-sql-query-and-run-it "Direct link to 4. Input a SQL query and run it")

Type a query in the SQL Console and press **Run Query**. The results will be displayed as a spreadsheet.

Tip

Right-click on the column name to open the dropdown menu with sort, filter and other actions.

Note

With TABLUM.IO you can

- create and utilise multiple ClickHouse connectors within your TABLUM.IO account,
- run queries on any loaded data regardless of the data source,
- share the results as a new ClickHouse database.

## Learn more[​](#learn-more "Direct link to Learn more")

Find more information about TABLUM.IO at <https://tablum.io>.

- [Open the TABLUM.IO startup page](#open-the-tablumio-startup-page)- [1. Sign up or sign in to the service](#1-sign-up-or-sign-in-to-the-service)- [2. Add a ClickHouse connector](#2-add-a-clickhouse-connector)- [3. Select the connector](#3-select-the-connector)- [4. Input a SQL query and run it](#4-input-a-sql-query-and-run-it)- [Learn more](#learn-more)

Was this page helpful?
Tools

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

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [SQL Client](/docs/integrations/sql-clients) How to integrate ClickHouse with various common database management, analysis and visualization tools|  |  |  |  | | --- | --- | --- | --- | | [Data Integrations](/docs/integrations/tools/data-integrations) Data integrations for ClickHouse|  |  | | --- | --- | | [Misc](/docs/integrations/misc) Miscellaneous tooling for ClickHouse | | | | | | | |

Was this page helpful?
Integrating Vector with ClickHouse

```
SELECT trim(LEADING '"' FROM '"GET')
```

The time string has a leading `[`, and is also not in a format that ClickHouse can parse into a date.
However, if we change the separator from a colon (**:**) to a comma (**,**) then the parsing works great:

```
SELECT parseDateTimeBestEffort(replaceOne(trim(LEADING '[' FROM '[12/Oct/2021:15:32:43'), ':', ' '))
```

We're now ready to define the materialized view.
The definition below includes `POPULATE`, which means the existing rows in **access\_logs** will be processed and inserted right away.
Run the following SQL statement:

```
CREATE MATERIALIZED VIEW nginxdb.access_logs_view
(
  RemoteAddr String,
  Client String,
  RemoteUser String,
  TimeLocal DateTime,
  RequestMethod String,
  Request String,
  HttpVersion String,
  Status Int32,
  BytesSent Int64,
  UserAgent String
)
ENGINE = MergeTree()
ORDER BY RemoteAddr
POPULATE AS
WITH
  splitByWhitespace(message) as split,
  splitByRegexp('\S \d+ "([^"]*)"', message) as referer
SELECT
  split[1] AS RemoteAddr,
  split[2] AS Client,
  split[3] AS RemoteUser,
  parseDateTimeBestEffort(replaceOne(trim(LEADING '[' FROM split[4]), ':', ' ')) AS TimeLocal,
  trim(LEADING '"' FROM split[6]) AS RequestMethod,
  split[7] AS Request,
  trim(TRAILING '"' FROM split[8]) AS HttpVersion,
  split[9] AS Status,
  split[10] AS BytesSent,
  trim(BOTH '"' from referer[2]) AS UserAgent
FROM
  (SELECT message FROM nginxdb.access_logs)
```

Now verify it worked.
You should see the access logs nicely parsed into columns:

```
SELECT * FROM nginxdb.access_logs_view
```

Note

The lesson above stored the data in two tables, but you could change the initial `nginxdb.access_logs` table to use the [`Null`](/docs/engines/table-engines/special/null) table engine.
The parsed data will still end up in the `nginxdb.access_logs_view` table, but the raw data won't be stored in a table.

> By using Vector, which only requires a simple install and quick configuration, you can send logs from an Nginx server to a table in ClickHouse. By using a materialized view, you can parse those logs into columns for easier analytics.

- [Create a database and table](#1-create-a-database-and-table)- [Configure Nginx](#2--configure-nginx)- [Configure Vector](#3-configure-vector)- [Parse the Logs](#4-parse-the-logs)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/interfaces/formats/Arrow/ArrowStream.md)

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Input Output Alias|  |  |  | | --- | --- | --- | | ✔ ✔  | | | | | |

## Description[​](#description "Direct link to Description")

`ArrowStream` is Apache Arrow's "stream mode" format. It is designed for in-memory stream processing.

## Example usage[​](#example-usage "Direct link to Example usage")

## Format settings[​](#format-settings "Direct link to Format settings")

- [Description](#description)- [Example usage](#example-usage)- [Format settings](#format-settings)

Was this page helpful?
title_wip

```
CREATE TABLE topic1_stream
(
    field1 String,
    field2 String
)
ENGINE = Kafka()
SETTINGS
kafka_broker_list = 'kafka-broker',
kafka_topic_list = 'topic1',
kafka_group_name = 'group1',
kafka_format = 'AvroConfluent',
format_avro_schema_registry_url = 'https://<username>:<password>@schema-registry-url';
```

## Troubleshooting[​](#troubleshooting "Direct link to Troubleshooting")

To monitor ingestion progress and debug errors with the Kafka consumer, you can query the [`system.kafka_consumers` system table](/docs/operations/system-tables/kafka_consumers). If your deployment has multiple replicas (e.g., ClickHouse Cloud), you must use the [`clusterAllReplicas`](/docs/sql-reference/table-functions/cluster) table function.

```
SELECT * FROM clusterAllReplicas('default',system.kafka_consumers)
ORDER BY assignments.partition_id ASC;
```

If you run into schema resolution issues, you can use [kafkacat](https://github.com/edenhill/kafkacat) with [clickhouse-local](/docs/operations/utilities/clickhouse-local) to troubleshoot:

```
$ kafkacat -b kafka-broker  -C -t topic1 -o beginning -f '%s' -c 3 | clickhouse-local   --input-format AvroConfluent --format_avro_schema_registry_url 'http://schema-registry' -S "field1 Int64, field2 String"  -q 'select *  from table'
1 a
2 b
3 c
```

- [Description](#description)- [Data type mapping](#data-type-mapping)- [Format settings](#format-settings)- [Examples](#examples)
        - [Using a schema registry](#using-a-schema-registry)- [Troubleshooting](#troubleshooting)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/interfaces/formats/Buffers.md)

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Input Output Alias|  |  |  | | --- | --- | --- | | ✔ ✔  | | | | | |

## Description[​](#description "Direct link to Description")

`Buffers` is a very simple binary format for **ephemeral** data exchange, where both the consumer and producer already know the schema and column order.

Unlike [Native](/docs/interfaces/formats/Native), it does **not** store column names, column types, or any extra metadata.

In this format, data is written and read by [blocks](/docs/development/architecture#block) in a binary format. Buffers uses the same per-column binary representation as the [Native](/docs/interfaces/formats/Native) format and respects the same Native format settings.

For each block, the following sequence is written:

1. Number of columns (UInt64, little-endian).
2. Number of rows (UInt64, little-endian).
3. For each column:

- Total byte size of the serialized column data (UInt64, little-endian).
- Serialized column data bytes, exactly as in the [Native](/docs/interfaces/formats/Native) format.

## Example usage[​](#example-usage "Direct link to Example usage")

Write to a file:

```
SELECT
    number AS num,
    number * number AS num_square
FROM numbers(10)
INTO OUTFILE 'squares.buffers'
FORMAT Buffers;
```

Read back with an explicit column types:

```
SELECT
    *
FROM file(
    'squares.buffers',
    'Buffers',
    'col_1 UInt64, col_2 UInt64'
);
```

```
  ┌─col_1─┬─col_2─┐
  │     0 │     0 │
  │     1 │     1 │
  │     2 │     4 │
  │     3 │     9 │
  │     4 │    16 │
  │     5 │    25 │
  │     6 │    36 │
  │     7 │    49 │
  │     8 │    64 │
  │     9 │    81 │
  └───────┴───────┘
```

If you have a table with same column types, you can populate it directly:

```
CREATE TABLE number_squares
(
    a UInt64,
    b UInt64
) ENGINE = Memory;

INSERT INTO number_squares
FROM INFILE 'squares.buffers'
FORMAT Buffers;
```

Inspect the table:

```
SELECT * FROM number_squares;
```

```
  ┌─a─┬──b─┐
  │ 0 │  0 │
  │ 1 │  1 │
  │ 2 │  4 │
  │ 3 │  9 │
  │ 4 │ 16 │
  │ 5 │ 25 │
  │ 6 │ 36 │
  │ 7 │ 49 │
  │ 8 │ 64 │
  │ 9 │ 81 │
  └───┴────┘
```

## Format settings[​](#format-settings "Direct link to Format settings")

- [Description](#description)- [Example usage](#example-usage)- [Format settings](#format-settings)

Was this page helpful?