ClickHouse/clickhouse-docs-embeddings huggingface dataset:
ClickPipes & Language Clients

## Language client integrations[​](#language-client-integrations "Direct link to Language client integrations")

ClickHouse offers a number of language client integrations, for which the documentation for each is linked below.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [C++](/docs/interfaces/cpp) C++ Client Library and userver Asynchronous Framework|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [C#](/docs/integrations/csharp) Learn how to connect your C# projects to ClickHouse.|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Go](/docs/integrations/go) Learn how to connect your Go projects to ClickHouse.|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [JavaScript](/docs/integrations/javascript) Learn how to connect your JS projects to ClickHouse with the official JS client.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [Java](/docs/integrations/java) Learn more about several integrations for Java and ClickHouse.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Python](/docs/integrations/python) Learn how to connect your Python projects to ClickHouse.|  |  |  |  | | --- | --- | --- | --- | | [Rust](/docs/integrations/rust) Learn how to connect your Rust projects to ClickHouse.|  |  | | --- | --- | | [Third-party clients](/docs/interfaces/third-party/client-libraries) Learn more about client libraries from third party developers. | | | | | | | | | | | | | | | | | |

In addition to ClickPipes, language clients, ClickHouse supports a host of other integrations, spanning core integrations,
partner integrations and community integrations.
For a complete list see the ["Integrations"](/docs/integrations) section of the docs.

- [Managed integration pipelines for ClickHouse Cloud](#clickpipes)- [Language client integrations](#language-client-integrations)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/cloud/reference/08_settings.md)

To specify settings for your ClickHouse Cloud service for a specific [user](/docs/operations/access-rights#user-account-management) or [role](/docs/operations/access-rights#role-management), you must use [SQL-driven Settings Profiles](/docs/operations/access-rights#settings-profiles-management). Applying Settings Profiles ensures that the settings you configure persist, even when your services stop, idle, and upgrade. To learn more about Settings Profiles, please see [this page](/docs/operations/settings/settings-profiles).

Please note that XML-based Settings Profiles and [configuration files](/docs/operations/configuration-files) are currently not supported for ClickHouse Cloud.

To learn more about the settings you can specify for your ClickHouse Cloud service, please see all possible settings by category in [our docs](/docs/operations/settings).

Was this page helpful?
Core Concepts

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/managing-data/core-concepts/index.md)

In this section of the documentation,
you will learn some of the core concepts of how ClickHouse works.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Table parts](/docs/parts) Learn what table parts are in ClickHouse.|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Table partitions](/docs/partitions) Learn what table partitions are and what they're used for.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [Table part merges](/docs/merges) Learn what table part merges are and what they're used for.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Table shards and replicas](/docs/shards) Learn what table shards and replicas are and what they're used for.|  |  |  |  | | --- | --- | --- | --- | | [Primary indexes](/docs/primary-indexes) Introduces ClickHouse's sparse primary index and how it helps efficiently skip unnecessary data during query execution. Explains how the index is built and used, with examples and tools for observing its effect. Links to a deep dive for advanced use cases and best practices.|  |  | | --- | --- | | [Architectural Overview](/docs/academic_overview) A concise academic overview of all components of the ClickHouse architecture, based on our VLDB 2024 scientific paper. | | | | | | | | | | | | | |

Was this page helpful?
Delete Mutations

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/managing-data/deleting-data/delete_mutations.mdx)

Delete mutations refers to `ALTER` queries that manipulate table data through delete. Most notably they're queries like `ALTER TABLE DELETE`, etc. Performing such queries will produce new mutated versions of the data parts. This means that such statements would trigger a rewrite of whole data parts for all data that was inserted before the mutation, translating to a large amount of write requests.

References

For deletes, you can avoid these large amounts of write requests by using specialised table engines like [ReplacingMergeTree](/docs/guides/replacing-merge-tree) or [CollapsingMergeTree](/docs/engines/table-engines/mergetree-family/collapsingmergetree) instead of the default MergeTree table engine.

```
ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE WHERE filter_expr
```

Deletes data matching the specified filtering expression. Implemented as a [mutation](/docs/sql-reference/statements/alter#mutations).

Note

The `ALTER TABLE` prefix makes this syntax different from most other systems supporting SQL. It is intended to signify that unlike similar queries in OLTP databases this is a heavy operation not designed for frequent use. `ALTER TABLE` is considered a heavyweight operation that requires the underlying data to be merged before it is deleted. For MergeTree tables, consider using the [`DELETE FROM` query](/docs/sql-reference/statements/delete), which performs a lightweight delete and can be considerably faster.

The `filter_expr` must be of type `UInt8`. The query deletes rows in the table for which this expression takes a non-zero value.

One query can contain several commands separated by commas.

The synchronicity of the query processing is defined by the [mutations\_sync](/docs/operations/settings/settings#mutations_sync) setting. By default, it is asynchronous.

**See also**

- [Mutations](/docs/sql-reference/statements/alter#mutations)
- [Synchronicity of ALTER Queries](/docs/sql-reference/statements/alter#synchronicity-of-alter-queries)
- [mutations\_sync](/docs/operations/settings/settings#mutations_sync) setting

## Related content[​](#related-content "Direct link to Related content")

- Blog: [Handling Updates and Deletes in ClickHouse](https://clickhouse.com/blog/handling-updates-and-deletes-in-clickhouse)

- [Related content](#related-content)

Was this page helpful?
title_wip

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

In this section of the documentation,
we will explore how to delete data in ClickHouse.

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Overview](/docs/managing-data/deleting-data/overview) Provides an overview of the various ways to delete data in ClickHouse.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [Lightweight deletes](/docs/guides/developer/lightweight-delete) Learn how to use the Lightweight Delete to delete data.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Delete mutations](/docs/managing-data/delete_mutations) Learn about Delete Mutations.|  |  |  |  | | --- | --- | --- | --- | | [Truncate table](/docs/managing-data/truncate) Learn about how to use Truncate, which allows the data in a table or database to be removed, while preserving its existence.|  |  | | --- | --- | | [Drop partitions](/docs/managing-data/drop_partition) Learn about Dropping Partitions in ClickHouse. | | | | | | | | | | | |

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/managing-data/drop_partition.mdx)

## Background[​](#background "Direct link to Background")

Partitioning is specified on a table when it is initially defined via the `PARTITION BY` clause. This clause can contain a SQL expression on any columns, the results of which will define which partition a row is sent to.

The data parts are logically associated with each partition on disk and can be queried in isolation. For the example below, we partition the `posts` table by year using the expression `toYear(CreationDate)`. As rows are inserted into ClickHouse, this expression will be evaluated against each row and routed to the resulting partition if it exists (if the row is the first for a year, the partition will be created).

```
 CREATE TABLE posts
(
        `Id` Int32 CODEC(Delta(4), ZSTD(1)),
        `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
        `AcceptedAnswerId` UInt32,
        `CreationDate` DateTime64(3, 'UTC'),
...
        `ClosedDate` DateTime64(3, 'UTC')
)
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CreationDate)
PARTITION BY toYear(CreationDate)
```

Read about setting the partition expression in a section [How to set the partition expression](/docs/sql-reference/statements/alter/partition#how-to-set-partition-expression).

In ClickHouse, users should principally consider partitioning to be a data management feature, not a query optimization technique. By separating data logically based on a key, each partition can be operated on independently e.g. deleted. This allows users to move partitions, and thus subsets, between [storage tiers](/docs/integrations/s3#storage-tiers) efficiently on time or [expire data/efficiently delete from the cluster](/docs/sql-reference/statements/alter/partition).

## Drop partitions[​](#drop-partitions "Direct link to Drop partitions")

`ALTER TABLE ... DROP PARTITION` provides a cost-efficient way to drop a whole partition.

```
ALTER TABLE table_name [ON CLUSTER cluster] DROP PARTITION|PART partition_expr
```

This query tags the partition as inactive and deletes data completely, approximately in 10 minutes. The query is replicated – it deletes data on all replicas.

In example, below we remove posts from 2008 for the earlier table by dropping the associated partition.

```
SELECT DISTINCT partition
FROM system.parts
WHERE `table` = 'posts'

┌─partition─┐
│ 2008      │
│ 2009      │
│ 2010      │
│ 2011      │
│ 2012      │
│ 2013      │
│ 2014      │
│ 2015      │
│ 2016      │
│ 2017      │
│ 2018      │
│ 2019      │
│ 2020      │
│ 2021      │
│ 2022      │
│ 2023      │
│ 2024      │
└───────────┘

17 rows in set. Elapsed: 0.002 sec.

ALTER TABLE posts
(DROP PARTITION '2008')

0 rows in set. Elapsed: 0.103 sec.
```

- [Background](#background)- [Drop partitions](#drop-partitions)

Was this page helpful?
Update mutations

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/managing-data/updating-data/update_mutations.mdx)

Update mutations refers to `ALTER` queries that manipulate table data through updates. Most notably they're queries like `ALTER TABLE UPDATE`, etc. Performing such queries will produce new mutated versions of the data parts. This means that such statements would trigger a rewrite of whole data parts for all data that was inserted before the mutation, translating to a large amount of write requests.

References

For updates, you can avoid these large amounts of write requests by using specialised table engines like [ReplacingMergeTree](/docs/guides/replacing-merge-tree) or [CollapsingMergeTree](/docs/engines/table-engines/mergetree-family/collapsingmergetree) instead of the default MergeTree table engine.

```
ALTER TABLE [db.]table [ON CLUSTER cluster] UPDATE column1 = expr1 [, ...] [IN PARTITION partition_id] WHERE filter_expr
```

Manipulates data matching the specified filtering expression. Implemented as a [mutation](/docs/sql-reference/statements/alter#mutations).

Note

The `ALTER TABLE` prefix makes this syntax different from most other systems supporting SQL. It is intended to signify that unlike similar queries in OLTP databases this is a heavy operation not designed for frequent use.

The `filter_expr` must be of type `UInt8`. This query updates values of specified columns to the values of corresponding expressions in rows for which the `filter_expr` takes a non-zero value. Values are cast to the column type using the `CAST` operator. Updating columns that are used in the calculation of the primary or the partition key is not supported.

One query can contain several commands separated by commas.

The synchronicity of the query processing is defined by the [mutations\_sync](/docs/operations/settings/settings#mutations_sync) setting. By default, it is asynchronous.

**See also**

- [Mutations](/docs/sql-reference/statements/alter#mutations)
- [Synchronicity of ALTER Queries](/docs/sql-reference/statements/alter#synchronicity-of-alter-queries)
- [mutations\_sync](/docs/operations/settings/settings#mutations_sync) setting
- [Lightweight `UPDATE`](/docs/sql-reference/statements/update) - Alternative lightweight update using patch parts
- [`APPLY PATCHES`](/docs/sql-reference/statements/alter/apply-patches) - Manually apply patches from lightweight updates

## Related content[​](#related-content "Direct link to Related content")

- Blog: [Handling Updates and Deletes in ClickHouse](https://clickhouse.com/blog/handling-updates-and-deletes-in-clickhouse)

- [Related content](#related-content)

Was this page helpful?
title_wip

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

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  | | --- | --- | --- | --- | | [Incremental materialized view](/docs/materialized-view/incremental-materialized-view) Allow users to shift the cost of computation from query time to insert time, resulting in faster `SELECT` queries.| [Refreshable materialized view](/docs/materialized-view/refreshable-materialized-view) Conceptually similar to incremental materialized views but require the periodic execution of the query over the full dataset - the results of which are stored in a target table for querying. | | | | | |

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/cloud/onboard/02_migrate/01_migration_guides/03_bigquery/index.md)

In this section of the docs, learn more about the similarities and differences between BigQuery and ClickHouse Cloud, as well as why you might want to migrate and how to do so.

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [BigQuery vs ClickHouse Cloud](/docs/migrations/bigquery/biquery-vs-clickhouse-cloud) The way resources are organized in ClickHouse Cloud is similar to BigQuery's resource hierarchy. We describe the specific differences in this article.|  |  |  |  | | --- | --- | --- | --- | | [Migrating from BigQuery to ClickHouse Cloud](/docs/migrations/bigquery/migrating-to-clickhouse-cloud) Learn about why you might want to migrate from BigQuery to ClickHouse Cloud.|  |  | | --- | --- | | [Loading Data](/docs/migrations/bigquery/loading-data) A guide showing you how to migrate data from BigQuery to ClickHouse. | | | | | | | |

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/cloud/onboard/02_migrate/01_migration_guides/02_postgres/index.md)

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Overview](/docs/migrations/postgresql/overview) Introduction page for this section|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Connecting to PostgreSQL](/docs/integrations/postgresql/connecting-to-postgresql) This page covers the following options for integrating PostgreSQL with ClickHouse: ClickPipes, PeerDB, PostgreSQL table engine, MaterializedPostgreSQL database engine.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [Migrating data](/docs/migrations/postgresql/dataset) Part 1 of a guide on migrating from PostgreSQL to ClickHouse. Using a practical example, it demonstrates how to efficiently carry out the migration with a real-time replication (CDC) approach. Many of the concepts covered are also applicable to manual bulk data transfers from PostgreSQL to ClickHouse.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Rewriting PostgreSQL Queries](/docs/migrations/postgresql/rewriting-queries) Part 2 of a guide on migrating from PostgreSQL to ClickHouse. Using a practical example, it demonstrates how to efficiently carry out the migration with a real-time replication (CDC) approach. Many of the concepts covered are also applicable to manual bulk data transfers from PostgreSQL to ClickHouse.|  |  |  |  | | --- | --- | --- | --- | | [Data modeling techniques](/docs/migrations/postgresql/data-modeling-techniques) Part 3 of a guide on migrating from PostgreSQL to ClickHouse. Using a practical example, it demonstrates how to model data in ClickHouse if migrating from PostgreSQL.|  |  | | --- | --- | | [Appendix](/docs/migrations/postgresql/appendix) Additional information relevant to migrating from PostgreSQL | | | | | | | | | | | | | |

Was this page helpful?
Data modeling techniques

11 rows in set. Elapsed: 0.004 sec.
```

### When to use projections[​](#when-to-use-projections "Direct link to When to use projections")

Projections are an appealing feature for new users as they're automatically
maintained as data is inserted. Furthermore, queries can just be sent to a single
table where the projections are exploited where possible to speed up the response
time.

This is in contrast to materialized views, where the user has to select the
appropriate optimized target table or rewrite their query, depending on the filters.
This places greater emphasis on user applications and increases client-side complexity.

Despite these advantages, projections come with some [inherent limitations](/docs/data-modeling/projections#when-to-use-projections)
which you should be aware of and thus should be deployed sparingly.

We recommend using projections when:

- A complete reordering of the data is required. While the expression in the
  projection can, in theory, use a `GROUP BY,` materialized views are more
  effective for maintaining aggregates. The query optimizer is also more likely
  to exploit projections that use a simple reordering, i.e., `SELECT * ORDER BY x`.
  You can select a subset of columns in this expression to reduce storage footprint.
- Users are comfortable with the associated increase in storage footprint and
  overhead of writing data twice. Test the impact on insertion speed and
  [evaluate the storage overhead](/docs/data-compression/compression-in-clickhouse).

Note

Since version 25.5, ClickHouse supports the virtual column `_part_offset` in
projections. This unlocks a more space-efficient way to store projections.

For more details see ["Projections"](/docs/data-modeling/projections)

## Denormalization[​](#denormalization "Direct link to Denormalization")

Since Postgres is a relational database, its data model is heavily [normalized](https://en.wikipedia.org/wiki/Database_normalization), often involving hundreds of tables. In ClickHouse, denormalization can be beneficial at times to optimize JOIN performance.

You can refer to this [guide](/docs/data-modeling/denormalization) that shows the benefits of denormalizing the Stack Overflow dataset in ClickHouse.

This concludes our basic guide if you're migrating from Postgres to ClickHouse. We recommend reading the [guide for modeling data in ClickHouse](/docs/data-modeling/schema-design) to learn more about advanced ClickHouse features.

- [Primary (Ordering) Keys in ClickHouse](#primary-ordering-keys-in-clickhouse)
  - [How are ClickHouse Primary keys different?](#how-are-clickhouse-primary-keys-different)- [Choosing an ordering key](#choosing-an-ordering-key)- [Partitions](#partitions)
    - [Applications of partitions](#applications-of-partitions)- [Recommendations for partitions](#recommendations-for-partitions)- [Materialized views vs projections](#materialized-views-vs-projections)
      - [When to use projections](#when-to-use-projections)- [Denormalization](#denormalization)

Was this page helpful?