ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

Learn how to write partitioned data by year and month to an S3 bucket in ClickHouse, using a custom path structure for organizing the data.


## Learn how to do partitioned writes by year and month on S3[​](#learn-how-to-do-partitioned-writes-by-year-and-month-on-s3 "Direct link to Learn how to do partitioned writes by year and month on S3")

I want to export data segregating the path in S3 bucket to follow a structure like:

- 2022
  - 1
  - 2
  - ...
  - 12
- 2021
  - 1
  - 2
  - ...
  - 12

and so on ...

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

Considering the ClickHouse table:

```
CREATE TABLE sample_data (
    `name` String,
    `age` Int,
    `time` DateTime
) ENGINE = MergeTree
ORDER BY
    name
```

Add 10000 entries:

```
INSERT INTO
    sample_data
SELECT
    *
FROM
    generateRandom(
        'name String, age Int, time DateTime',
        10,
        10,
        10
    )
LIMIT
    10000;
```

Run this to create the desired structure in s3 bucket `my_bucket` (note this example writes files in parquet format):

```
INSERT INTO
    FUNCTION s3(
        'https://s3-host:4321/my_bucket/{_partition_id}/file.parquet.gz',
        's3-access-key',
        's3-secret-access-key',
        Parquet,
        'name String, age Int, time DateTime'
    ) PARTITION BY concat(
        formatDateTime(time, '%Y'),
        '/',
        formatDateTime(time, '%m')
    )
SELECT
    name,
    age,
    time
FROM
    sample_data
Query id: 55adcf22-f6af-491e-b697-d09694bbcc56

Ok.

0 rows in set. Elapsed: 15.579 sec. Processed 10.00 thousand rows, 219.93 KB (641.87 rows/s., 14.12 KB/s.)
```

Mar 24, 2023 · One min read
title_wip

Learn how to capture server logs at the client level, even with different log settings, using the `send\_logs\_level` client setting.


## Learn how to capture server logs of queries at the client.[​](#learn-how-to-capture-server-logs-of-queries-at-the-client "Direct link to Learn how to capture server logs of queries at the client.")

A client can view the server logs - even at a different level than what the server log level is configured to - by setting the `send_logs_level` client setting.

For example, suppose the client runs:

```
SET send_logs_level = 'trace';
```

The client will receive trace logs even if the server has log level set to info.

One useful scenario is to use `send_logs_level` to monitor the insertion of rows into a `Distributed` table:

- Enable logs in `clickhouse-client` using `SET send_logs_level = 'trace';`
- Run your `INSERT` query
- Inserts into a distributed table are asynchronous by default. The data is written into a local buffer on disk, then sent to remote servers in background.
- Logs will be sent from all nodes participating in the query processing (distributed tracing)

To check the status of distributed inserts, check the [`system.distribution_queue` table](https://clickhouse.com/docs/operations/system-tables/distribution_queue/). This table contains information about local files that are in the queue to be sent to the shards. These local files contain new parts that are created by inserting new data into the `Distributed` table in asynchronous mode.

Mar 1, 2023 · One min read
[Knowledge Base](/docs/knowledgebase)/

[Tags](/docs/knowledgebase/tags)

# Knowledge Base

RecentGrouped by tags

## C[​](#C "Direct link to C")

- [Community2](/docs/knowledgebase/tags/community)- [Concepts8](/docs/knowledgebase/tags/concepts)- [Core Data Concepts6](/docs/knowledgebase/tags/core-data-concepts)

---

## D[​](#D "Direct link to D")

- [Data Export5](/docs/knowledgebase/tags/data-export)- [Data Formats9](/docs/knowledgebase/tags/data-formats)- [Data Ingestion5](/docs/knowledgebase/tags/data-ingestion)- [Data Modelling7](/docs/knowledgebase/tags/data-modelling)- [Data Sources1](/docs/knowledgebase/tags/data-sources)- [Deleting Data1](/docs/knowledgebase/tags/deleting-data)- [Deployments and Scaling6](/docs/knowledgebase/tags/deployments-and-scaling)

---

## E[​](#E "Direct link to E")

- [Errors and Exceptions13](/docs/knowledgebase/tags/errors-and-exceptions)

---

## F[​](#F "Direct link to F")

- [Functions4](/docs/knowledgebase/tags/functions)

---

## L[​](#L "Direct link to L")

- [Language Clients2](/docs/knowledgebase/tags/language-clients)

---

## M[​](#M "Direct link to M")

- [Managing Cloud14](/docs/knowledgebase/tags/managing-cloud)- [Managing Data2](/docs/knowledgebase/tags/managing-data)

---

## N[​](#N "Direct link to N")

- [Native Clients and Interfaces7](/docs/knowledgebase/tags/native-clients-and-interfaces)

---

## P[​](#P "Direct link to P")

- [Performance and Optimizations15](/docs/knowledgebase/tags/performance-and-optimizations)

---

## S[​](#S "Direct link to S")

- [Security and Authentication8](/docs/knowledgebase/tags/security-and-authentication)- [Server Admin5](/docs/knowledgebase/tags/server-admin)- [Settings9](/docs/knowledgebase/tags/settings)- [System Tables5](/docs/knowledgebase/tags/system-tables)

---

## T[​](#T "Direct link to T")

- [Tools and Utilities9](/docs/knowledgebase/tags/tools-and-utilities)- [Troubleshooting2](/docs/knowledgebase/tags/troubleshooting)

---

## U[​](#U "Direct link to U")

- [Use Cases6](/docs/knowledgebase/tags/use-cases)

---
title_wip

Learn how to use ClickHouse for vector search, including storing embeddings and searching with distance functions like cosine similarity.

Oct 26, 2023 · 2 min read

Understand the key differences between official ClickHouse builds and 3rd-party builds, including updates, compatibility, and security considerations.

Jun 26, 2023 · 2 min read

Curious what ClickHouse means? Take a look inside this knowledge base article to find out!

Sep 1, 2021 · One min read

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

Sep 1, 2021 · 2 min read

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.

Sep 1, 2021 · 2 min read

OLAP stands for Online Analytical Processing. It is a broad term that can be looked at from two perspectives: technical and business.

Sep 1, 2021 · 3 min read

First of all, let’s discuss why people ask this question in the first place. There are two key reasons...

Sep 1, 2021 · 6 min read

ClickHouse is a generic data storage solution for OLAP workloads, while there are many specialized time-series database management systems.

Sep 1, 2021 · 2 min read
title_wip

Learn about the different part types (Wide vs Compact) and storage formats (Full vs Packed) in ClickHouse, and how they affect performance.

Feb 3, 2026 · 6 min read

Discusses the differences between OPTIMIZE FINAL and FINAL, and when to use and avoid them.

Jul 20, 2025 · 2 min read

ClickHouse doesn't have a PIVOT clause, but we can get close to this functionality using aggregate function combinators. Let's see how to do this using the UK housing prices dataset.

Dec 11, 2024 · 7 min read

ClickHouse Keeper improves upon ZooKeeper with features like reduced disk space usage, faster recovery, and less memory consumption, offering better performance for ClickHouse clusters.

Jun 7, 2023 · 2 min read

TTL rules in ClickHouse are eventually applied, and you can control when they are executed using the `merge\_with\_ttl\_timeout` setting. Learn how to force TTL application and manage background threads for TTL execution.

May 2, 2023 · 3 min read

A columnar database stores the data of each column independently. This allows reading data from disk only for those columns that are used in any given query.

Sep 1, 2021 · 2 min read
title_wip

How to output send logs level traces to file using the clickhouse-client

Nov 21, 2024 · 3 min read

Learn how to write partitioned data by year and month to an S3 bucket in ClickHouse, using a custom path structure for organizing the data.

Mar 24, 2023 · One min read

Learn various methods to export data from ClickHouse, including `INTO OUTFILE`, the File table engine, and command-line redirection.

Mar 22, 2023 · 2 min read

Learn how to export PostgreSQL data to Parquet, CSV, or JSON formats using `clickhouse-local` with various examples.

Mar 22, 2023 · 3 min read

Learn how to use the `clickhouse-local` tool to export MySQL data into formats like Parquet, CSV, or JSON quickly and efficiently.

Mar 21, 2023 · 2 min read
title_wip

“Importing GeoJSON with a deeply nested object array“

Dec 18, 2024 · 5 min read

Learn how to load JSON messages from Apache Kafka directly into a single JSON column in ClickHouse using the Kafka table engine and JSON data type.

Nov 6, 2024 · 4 min read

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

May 20, 2024 · 2 min read

Are you getting the error "Cannot append data in format Parquet to file" error in ClickHouse? Let's take a look at how to resolve it.

Mar 25, 2023 · 2 min read

Learn how to import JSON array objects into ClickHouse and perform advanced queries using JSON functions and array operations.

Mar 24, 2023 · 6 min read

Learn how to use ClickHouse's `clickhouse-local` tool to easily convert Parquet files to CSV or JSON formats.

Mar 22, 2023 · 3 min read

Learn how to export PostgreSQL data to Parquet, CSV, or JSON formats using `clickhouse-local` with various examples.

Mar 22, 2023 · 3 min read

Learn how to use the `clickhouse-local` tool to export MySQL data into formats like Parquet, CSV, or JSON quickly and efficiently.

Mar 21, 2023 · 2 min read

A short example on how to extract base types from JSON

Sep 1, 2021 · One min read
title_wip

Knowledgebase article on how to insert all rows from one table to another.

Dec 17, 2024 · 2 min read

Learn how to load JSON messages from Apache Kafka directly into a single JSON column in ClickHouse using the Kafka table engine and JSON data type.

Nov 6, 2024 · 4 min read

Learn how to ingest data from a Kafka topic into ClickHouse using the Kafka table engine, materialized views, and MergeTree tables.

Apr 27, 2024 · 6 min read

Learn the basics of using the S3 table engine in ClickHouse to ingest and query Parquet files from an S3 bucket, including setup, access permissions, and data import examples.

Mar 22, 2023 · 3 min read

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.

Mar 20, 2023 · One min read
title_wip

In this guide, we'll learn how to add a column to an existing table.

Dec 18, 2024 · 4 min read

Knowledgebase article on how to filter a ClickHouse table by an array-column.

Dec 17, 2024 · 3 min read

ClickHouse doesn't have a PIVOT clause, but we can get close to this functionality using aggregate function combinators. Let's see how to do this using the UK housing prices dataset.

Dec 11, 2024 · 7 min read

There is an issue where data in dictionaries may not be visible immediately after creation.

Apr 12, 2024 · 2 min read

Learn how to create a ClickHouse dictionary using string keys and values from a MergeTree table as the source, with examples of setup and usage.

Jul 10, 2023 · One min read

This KB article explores whether Materialized Views are inserted synchronously

Mar 1, 2023 · 2 min read

Learn how to check if a projection is used in ClickHouse queries by testing with sample data and using EXPLAIN to verify projection usage.

Jul 10, 2022 · 2 min read
title_wip

This article explains how to recover data when using replicated tables in atomic databases in ClickHouse and disks/storage on one of the replica is lost/currupted.

Nov 19, 2025 · 2 min read

How to create a table that can query multiple remote clusters

Jul 3, 2024 · 3 min read

Learn how to search across nodes for tables with a wildcard.

Apr 17, 2024 · 3 min read

Learn how to use `ON CLUSTER` and `clusterAllReplicas` to execute SYSTEM statements and queries across all nodes in a ClickHouse Cloud service.

Mar 1, 2023 · One min read

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.

Apr 22, 2022 · One min read

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

Sep 1, 2021 · One min read
title_wip

Troubleshooting memory limit exceeded errors for a query

Jul 25, 2025 · 3 min read

ODBC authentication failed error when using the Power BI ClickHouse connector

Jul 10, 2024 · 2 min read

Troubleshooting access issues with ClickHouse Cloud services, including IP Access List configuration

Jul 2, 2024 · One min read

Learn how to resolve the MacOS developer verification error when running ClickHouse commands, using either System Settings or the terminal.

Jan 9, 2024 · 2 min read

Handing the an exception thrown when altering user settings

Aug 26, 2023 · One min read

Resolve the TOO\_MANY\_PARTS error in ClickHouse during an `INSERT...SELECT` by tuning expert-level settings for larger blocks and increasing partition thresholds.

Jul 21, 2023 · 2 min read

Learn how to resolve Docker capability warnings for `CAP\_IPC\_LOCK` and `CAP\_SYS\_NICE` when running ClickHouse in a container.

Jun 7, 2023 · 2 min read

Learn how to resolve the SSL Exception CERTIFICATE\_VERIFY\_FAILED error.

May 2, 2023 · One min read

This article explains how to resolve the DB::Exception error related to intersecting parts in ClickHouse, often caused by a race condition or manual intervention in the ZooKeeper data.

May 2, 2023 · 2 min read

Are you getting the error "Cannot append data in format Parquet to file" error in ClickHouse? Let's take a look at how to resolve it.

Mar 25, 2023 · 2 min read
title_wip

Learn how to fix timeout errors when using `remote` or `remoteSecure` table functions in ClickHouse by adjusting the connection timeout settings.

Mar 20, 2023 · 2 min read

Learn how to address the "Too many parts" error in ClickHouse by optimizing insert rates, configuring MergeTree settings, and managing partitions effectively.

Mar 20, 2023 · 3 min read

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.

Nov 17, 2022 · One min read
title_wip

Knowledgebase article on how to filter a ClickHouse table by an array-column.

Dec 17, 2024 · 3 min read

Simple example to illustrate how to use array join to extract and query varying attributes using map keys and values

Jun 21, 2024 · 4 min read

Learn how to validate that two ClickHouse queries produce identical result-sets using hash functions and comparison techniques.

May 4, 2023 · 2 min read

Learn how to use filtered aggregates in ClickHouse with `-If` and `-Distinct` aggregate combinators to simplify query syntax and enhance analytics.

Mar 1, 2023 · One min read
This article shows you how you can opt out of crash report collection on ClickHouse Cloud

Feb 4, 2026 · 2 min read

Setup steps to expose a private MSK via MSK multi-VPC connectivity to ClickPipes.

Apr 15, 2025 · 2 min read

Setup steps to expose a private RDS via AWS PrivateLink to ClickPipes.

Nov 27, 2024 · 4 min read

How to connect to ClickHouse and ClickHouse Cloud using SSH Keys

Jul 25, 2024 · 2 min read

Troubleshooting access issues with ClickHouse Cloud services, including IP Access List configuration

Jul 2, 2024 · One min read

There is an issue where data in dictionaries may not be visible immediately after creation.

Apr 12, 2024 · 2 min read

Learn how to use the ClickHouse Cloud API to check if your service is stopped, idle, or running without waking it up.

Nov 16, 2023 · One min read

Quotas and Query Complexity are powerful ways to limit and restrict what users can do in ClickHouse. This KB article shows examples on how to apply these two different approaches.

Oct 25, 2023 · 7 min read

Queries to help grant specific permissions to users.

Sep 28, 2023 · One min read

Let's learn how to change your billing address in ClickHouse Cloud.

Sep 8, 2023 · One min read
title_wip

Learn how to query ClickHouse's `system.role\_grants` to find users assigned to roles and roles assigned to specific users.

Sep 7, 2023 · 3 min read

Learn how to start, stop, and resume a ClickHouse Cloud service using API endpoints and cURL commands.

Jun 7, 2023 · 2 min read

How to enforce limit on max query execution time

Jul 10, 2022 · One min read

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.

Apr 22, 2022 · One min read
title_wip

Covers a common reason why a primary key is not used in ordering and how we can confirm

Dec 12, 2024 · 6 min read

Learn how to profile ClickHouse using LLVM's XRay instrumentation profiler, visualize traces, and analyze performance.

Nov 13, 2024 · 4 min read

Tips and tricks on optimizing basic data types in ClickHouse

Jul 2, 2024 · 4 min read

Learn how to ensure data consistency when reading from ClickHouse, whether you're connected to the same node or a random node.

Feb 14, 2024 · 2 min read

Learn how to check if query cache is being utilized in ClickHouse using `clickhouse-client` trace logs or SQL commands.

Jun 7, 2023 · 7 min read

Learn how to use the `FORMAT Null` option in ClickHouse to measure query processing time without returning any rows to the client.

Jun 7, 2023 · One min read

Learn how to use the `system.query\_log` table to find the most memory-intensive queries in ClickHouse, with examples for clustered and standalone setups.

Jun 7, 2023 · 2 min read

Learn how to calculate the ratio of empty or zero values in every column of a ClickHouse table to optimize sparse column serialization.

May 18, 2023 · 2 min read

Learn how to use the `query\_log` table in ClickHouse to identify the most memory and CPU-intensive queries across distributed nodes.

Mar 26, 2023 · 6 min read

The new setting `allow\_asynchronous\_read\_from\_io\_pool\_for\_merge\_tree` allows the number of reading threads (streams) to be higher than the number of threads in the rest of the query execution pipeline.

Mar 1, 2023 · 4 min read
title_wip

Learn how to configure the Global Thread pool in ClickHouse by adjusting settings like `max\_thread\_pool\_size`, `thread\_pool\_queue\_size`, and `max\_thread\_pool\_free\_size`.

Mar 1, 2023 · One min read

A query to compare metrics between two queries in ClickHouse.

Jan 14, 2023 · 7 min read

Learn how to optimize Map column lookups in ClickHouse for better query performance by materializing specific keys as standalone columns.

Oct 30, 2022 · 3 min read

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

Sep 1, 2021 · One min read

First of all, let’s discuss why people ask this question in the first place. There are two key reasons...

Sep 1, 2021 · 6 min read
title_wip

Learn how to set up a custom DNS alias for your instance using a reverse proxy

May 16, 2025 · 2 min read

Setup steps to expose a private MSK via MSK multi-VPC connectivity to ClickPipes.

Apr 15, 2025 · 2 min read

Learn how to set up SSL for a single ClickHouse server using Let's Encrypt, including certificate issuance, configuration, and validation.

Dec 11, 2024 · 6 min read

Setup steps to expose a private RDS via AWS PrivateLink to ClickPipes.

Nov 27, 2024 · 4 min read

How to connect to ClickHouse and ClickHouse Cloud using SSH Keys

Jul 25, 2024 · 2 min read

Queries to help grant specific permissions to users.

Sep 28, 2023 · One min read

Learn about row-level and column-level access restrictions in ClickHouse and ClickHouse Cloud, and how to implement role-based access control (RBAC) with policies.

Aug 13, 2023 · One min read

Learn how to resolve the SSL Exception CERTIFICATE\_VERIFY\_FAILED error.

May 2, 2023 · One min read
title_wip

Learn how to set up a custom DNS alias for your instance using a reverse proxy

May 16, 2025 · 2 min read

Learn how to remove the default user when running ClickHouse Server.

Jan 16, 2025 · 2 min read

Learn how to query ClickHouse's `system.role\_grants` to find users assigned to roles and roles assigned to specific users.

Sep 7, 2023 · 3 min read

ClickHouse provides introspection tools like `system.stack\_trace` for inspecting what code is currently running on each server thread, helping with debugging and performance monitoring.

Mar 24, 2023 · 3 min read

Learn how to capture server logs at the client level, even with different log settings, using the `send\_logs\_level` client setting.

Mar 1, 2023 · One min read
title_wip

Learn why the ClickHouse developers chose to set a verbose logging level by default.

Jan 10, 2025 · 2 min read

This article explains how to change the prompt in your Clickhouse client and clickhouse-local terminal window from :) to a prefix followed by :)

Nov 16, 2023 · 3 min read

Handing the an exception thrown when altering user settings

Aug 26, 2023 · One min read

Resolve the TOO\_MANY\_PARTS error in ClickHouse during an `INSERT...SELECT` by tuning expert-level settings for larger blocks and increasing partition thresholds.

Jul 21, 2023 · 2 min read

A collection of handy queries for troubleshooting ClickHouse, including monitoring table sizes, long-running queries, and errors.

Mar 17, 2023 · 4 min read

The new setting `allow\_asynchronous\_read\_from\_io\_pool\_for\_merge\_tree` allows the number of reading threads (streams) to be higher than the number of threads in the rest of the query execution pipeline.

Mar 1, 2023 · 4 min read

Learn how to define settings in ClickHouse for individual queries, client sessions, or specific users using `SET` and `ALTER USER` commands.

Mar 1, 2023 · 2 min read

Learn how to use the `skip\_check\_for\_incorrect\_settings` option to allow ClickHouse to start even when user-level settings are specified incorrectly.

Mar 1, 2023 · One min read

How to enforce limit on max query execution time

Jul 10, 2022 · One min read
title_wip

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

Jul 23, 2024 · 3 min read

Learn how to query ClickHouse's `system.role\_grants` to find users assigned to roles and roles assigned to specific users.

Sep 7, 2023 · 3 min read

Monitor the number of active or queued mutations in ClickHouse, especially when performing `ALTER` or `UPDATE` operations. Use the `system.mutations` table for tracking mutations.

Jun 7, 2023 · One min read

This article explains how to resolve the DB::Exception error related to intersecting parts in ClickHouse, often caused by a race condition or manual intervention in the ZooKeeper data.

May 2, 2023 · 2 min read

Learn how to query ClickHouse logs to identify all queries involving Materialized Views within a specified time range.

Mar 24, 2023 · 2 min read