ClickHouse/clickhouse-docs-embeddings huggingface dataset:
Best practices

ClickPipes provides a high-availability with an availability zone distributed architecture.
This requires scaling to at least two consumers.

Regardless number of running consumers, fault tolerance is available by design.
If a consumer or its underlying infrastructure fails,
the ClickPipe will automatically restart the consumer and continue processing messages.

### Benchmarks[​](#benchmarks "Direct link to Benchmarks")

Below are some informal benchmarks for ClickPipes for Kafka that can be used to get a general idea of the baseline performance. It's important to know that many factors can impact performance, including message size, data types, and data format. Your mileage may vary, and what we show here isn't a guarantee of actual performance.

Benchmark details:

- We used production ClickHouse Cloud services with enough resources to ensure that throughput wasn't bottlenecked by the insert processing on the ClickHouse side.
- The ClickHouse Cloud service, the Kafka cluster (Confluent Cloud), and the ClickPipe were all running in the same region (`us-east-2`).
- The ClickPipe was configured with a single L-sized replica (4 GiB of RAM and 1 vCPU).
- The sample data included nested data with a mix of `UUID`, `String`, and `Int` datatypes. Other datatypes, such as `Float`, `Decimal`, and `DateTime`, may be less performant.
- There was no appreciable difference in performance using compressed and uncompressed data.

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Replica Size Message Size Data Format Throughput|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | Large (L) 1.6kb JSON 63mb/s|  |  |  |  | | --- | --- | --- | --- | | Large (L) 1.6kb Avro 99mb/s | | | | | | | | | | | |

- [Message Compression](#compression)- [Limitations](#limitations)- [Delivery semantics](#delivery-semantics)- [Authentication](#authentication)- [Warpstream Fetch Size](#warpstream-settings)
          - [IAM](#iam)- [Custom Certificates](#custom-certificates)- [Performance](#performance)
            - [Batching](#batching)- [Latency](#latency)- [Scaling](#scaling)- [Benchmarks](#benchmarks)

Was this page helpful?
Reference

Note that the `_raw_message` column is only recommended for JSON data.
For use cases where only the JSON string is required (such as using ClickHouse [`JsonExtract*`](/docs/sql-reference/functions/json-functions#jsonextract-functions) functions to
populate a downstream materialized view), it may improve ClickPipes performance to delete all the "non-virtual" columns.

- [Supported data sources](#supported-data-sources)- [Supported data formats](#supported-data-formats)- [Supported data types](#supported-data-types)
      - [Standard](#standard-types-support)- [Avro](#avro)- [Kafka virtual columns](#kafka-virtual-columns)

Was this page helpful?
title_wip

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

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  | | --- | --- | --- | --- | | [Integrating Amazon Kinesis with ClickHouse Cloud](/docs/integrations/clickpipes/kinesis) Seamlessly connect your Amazon Kinesis data sources to ClickHouse Cloud.|  |  | | --- | --- | | [Kinesis Role-Based Access](/docs/integrations/clickpipes/kinesis/auth) This article demonstrates how ClickPipes customers can leverage role-based access to authenticate with Amazon Kinesis and access their data streams securely. | | | | | |

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/mongodb/add_table.md)

There are scenarios where it would be useful to add specific tables to a pipe. This becomes a common necessity as your transactional or analytical workload scales.

## Steps to add specific tables to a ClickPipe[​](#add-tables-steps "Direct link to Steps to add specific tables to a ClickPipe")

This can be done by the following steps:

1. [Pause](/docs/integrations/clickpipes/mongodb/pause_and_resume) the pipe.
2. Click on Edit Table settings.
3. Locate your table - this can be done by searching it in the search bar.
4. Select the table by clicking on the checkbox.

  

5. Click update.
6. Upon successful update, the pipe will have statuses `Setup`, `Snapshot` and `Running` in that order. The table's initial load can be tracked in the **Tables** tab.

References

CDC for existing tables resumes automatically after the new table’s snapshot completes.

- [Steps to add specific tables to a ClickPipe](#add-tables-steps)

Was this page helpful?
title_wip

For replica set connection, you can set up PrivateLink for MongoDB with either VPC Resource or VPC Endpoint Service. If you go with VPC Resource, you would need to create a `GROUP` resource configuration, plus a `CHILD` resource configuration for each node in the cluster. If you go with VPC Endpoint Service, you would need to create a separate Endpoint Service (and a separate NLB) for each node in the cluster.

See [AWS PrivateLink for ClickPipes](/docs/integrations/clickpipes/aws-privatelink) documentation for more details. Please reach out to ClickHouse support for assistance.

- [Can I query for individual fields in the JSON datatype?](#can-i-query-for-individual-fields-in-the-json-datatype)- [How do I flatten the nested MongoDB documents in ClickHouse?](#how-do-i-flatten-the-nested-mongodb-documents-in-clickhouse)- [Can I connect MongoDB databases that don't have a public IP or are in private networks?](#can-i-connect-mongodb-databases-that-dont-have-a-public-ip-or-are-in-private-networks)- [What happens if I delete a database/table from my MongoDB database?](#what-happens-if-i-delete-a-database-table-from-my-mongodb-database)- [How does MongoDB CDC Connector handle transactions?](#how-does-mongodb-cdc-connector-handle-transactions)- [How do I handle `resume of change stream was not possible, as the resume point may no longer be in the oplog.` error?](#resume-point-may-no-longer-be-in-the-oplog-error)- [How is replication managed?](#how-is-replication-managed)- [Which read preference should I use?](#which-read-preference-should-i-use)- [Does the MongoDB ClickPipe support Sharded Cluster?](#does-the-mongodb-clickpipe-support-sharded-cluster)- [Does MongoDB ClickPipe support Amazon DocumentDB?](#documentdb-support)- [Does MongoDB ClickPipe support PrivateLink?](#privatelink-support)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/mongodb/pause_and_resume.md)

There are scenarios where it would be useful to pause a MongoDB ClickPipe. For example, you may want to run some analytics on existing data in a static state. Or, you might be performing upgrades on MongoDB. Here is how you can pause and resume a MongoDB ClickPipe.

## Steps to pause a MongoDB ClickPipe[​](#pause-clickpipe-steps "Direct link to Steps to pause a MongoDB ClickPipe")

1. In the Data Sources tab, click on the MongoDB ClickPipe you wish to pause.
2. Head over to the **Settings** tab.
3. Click on the **Pause** button.

4. A dialog box should appear for confirmation. Click on Pause again.

4. Head over to the **Metrics** tab.
5. Wait for the status of the pipe to be **Paused**.

## Steps to resume a MongoDB ClickPipe[​](#resume-clickpipe-steps "Direct link to Steps to resume a MongoDB ClickPipe")

1. In the Data Sources tab, click on the MongoDB ClickPipe you wish to resume. The status of the mirror should be **Paused** initially.
2. Head over to the **Settings** tab.
3. Click on the **Resume** button.

4. A dialog box should appear for confirmation. Click on Resume again.

5. Head over to the **Metrics** tab.
6. Wait for the status of the pipe to be **Running**.

- [Steps to pause a MongoDB ClickPipe](#pause-clickpipe-steps)- [Steps to resume a MongoDB ClickPipe](#resume-clickpipe-steps)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/mongodb/remove_table.md)

In some cases, it makes sense to exclude specific tables from a MongoDB ClickPipe - for example, if a table isn't needed for your analytics workload, skipping it can reduce storage and replication costs in ClickHouse.

## Steps to remove specific tables[​](#remove-tables-steps "Direct link to Steps to remove specific tables")

The first step is to remove the table from the pipe. This can be done by the following steps:

1. [Pause](/docs/integrations/clickpipes/mongodb/pause_and_resume) the pipe.
2. Click on Edit Table Settings.
3. Locate your table - this can be done by searching it in the search bar.
4. Deselect the table by clicking on the selected checkbox.

  

5. Click update.
6. Upon successful update, in the **Metrics** tab the status will be **Running**. This table will no longer be replicated by this ClickPipe.

- [Steps to remove specific tables](#remove-tables-steps)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/mongodb/resync.md)

### What does Resync do?[​](#what-mongodb-resync-do "Direct link to What does Resync do?")

Resync involves the following operations in order:

1. The existing ClickPipe is dropped, and a new "resync" ClickPipe is kicked off. Thus, changes to source table structures will be picked up when you resync.
2. The resync ClickPipe creates (or replaces) a new set of destination tables which have the same names as the original tables except with a `_resync` suffix.
3. Initial load is performed on the `_resync` tables.
4. The `_resync` tables are then swapped with the original tables. Soft deleted rows are transferred from the original tables to the `_resync` tables before the swap.

All the settings of the original ClickPipe are retained in the resync ClickPipe. The statistics of the original ClickPipe are cleared in the UI.

### Use cases for resyncing a ClickPipe[​](#use-cases-mongodb-resync "Direct link to Use cases for resyncing a ClickPipe")

Here are a few scenarios:

1. You may need to perform major schema changes on the source tables which would break the existing ClickPipe and you would need to restart. You can just click Resync after performing the changes.
2. Specifically for Clickhouse, maybe you needed to change the ORDER BY keys on the target tables. You can Resync to re-populate data into the new table with the right sorting key.

### Resync ClickPipe Guide[​](#guide-mongodb-resync "Direct link to Resync ClickPipe Guide")

1. In the Data Sources tab, click on the MongoDB ClickPipe you wish to resync.
2. Head over to the **Settings** tab.
3. Click on the **Resync** button.

4. A dialog box should appear for confirmation. Click on Resync again.
5. Head over to the **Metrics** tab.
6. Wait for the status of the pipe to be **Setup** or **Snapshot**.
7. The initial load of the resync can be monitored in the **Tables** tab - in the **Initial Load Stats** section.
8. Once the initial load is complete, the pipe will atomically swap the `_resync` tables with the original tables. During the swap, the status will be **Resync**.
9. Once the swap is complete, the pipe will enter the **Running** state and perform CDC if enabled.

- [What does Resync do?](#what-mongodb-resync-do)- [Use cases for resyncing a ClickPipe](#use-cases-mongodb-resync)- [Resync ClickPipe Guide](#guide-mongodb-resync)

Was this page helpful?
MongoDB Atlas source setup guide

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/mongodb/source/atlas.md)

## Configure oplog retention[​](#enable-oplog-retention "Direct link to Configure oplog retention")

Minimum oplog retention of 24 hours is required for replication. We recommend setting the oplog retention to 72 hours or longer to ensure that the oplog isn't truncated before the initial snapshot is completed. To set the oplog retention via UI:

1. Navigate to your cluster's `Overview` tab in the MongoDB Atlas console and click on the `Configuration` tab.

2. Click `Additional Settings` and scroll down to `More Configuration Options`.

3. Click `More Configuration Options` and set the minimum oplog window to `72 hours` or longer.

4. Click `Review Changes` to review, and then `Apply Changes` to deploy the changes.

## Configure a database user[​](#configure-database-user "Direct link to Configure a database user")

Once you're logged in to your MongoDB Atlas console, click `Database Access` under the Security tab in the left navigation bar. Click on "Add New Database User".

ClickPipes requires password authentication:

ClickPipes requires a user with the following roles:

- `readAnyDatabase`
- `clusterMonitor`

You can find them in the `Specific Privileges` section:

You can further specify the clusters/instances you wish to grant access to ClickPipes user:

## What's next?[​](#whats-next "Direct link to What's next?")

You can now [create your ClickPipe](/docs/integrations/clickpipes/mongodb) and start ingesting data from your MongoDB instance into ClickHouse Cloud.
Make sure to note down the connection details you used while setting up your MongoDB instance as you will need them during the ClickPipe creation process.

- [Configure oplog retention](#enable-oplog-retention)- [Configure a database user](#configure-database-user)- [What's next?](#whats-next)

Was this page helpful?
Generic MongoDB source setup guide

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/mongodb/source/generic.md)

References

If you use MongoDB Atlas, please refer to the specific guide [here](/docs/integrations/clickpipes/mongodb/source/atlas).

## Enable oplog retention[​](#enable-oplog-retention "Direct link to Enable oplog retention")

Minimum oplog retention of 24 hours is required for replication. We recommend setting the oplog retention to 72 hours or longer to ensure that the oplog isn't truncated before the initial snapshot is completed.

You can check your current oplog retention by running the following command in the MongoDB shell (you must have `clusterMonitor` role to run this command):

```
db.getSiblingDB("admin").serverStatus().oplogTruncation.oplogMinRetentionHours
```

To set the oplog retention to 72 hours, run the following command on each node in the replica set as an admin user:

```
db.adminCommand({
    "replSetResizeOplog" : 1,
    "minRetentionHours": 72
})
```

For more details on the `replSetResizeOplog` command and oplog retention, see [MongoDB documentation](https://www.mongodb.com/docs/manual/reference/command/replSetResizeOplog/).

## Configure a database user[​](#configure-database-user "Direct link to Configure a database user")

Connect to your MongoDB instance as an admin user and execute the following command to create a user for MongoDB CDC ClickPipes:

```
db.getSiblingDB("admin").createUser({
    user: "clickpipes_user",
    pwd: "some_secure_password",
    roles: ["readAnyDatabase", "clusterMonitor"],
})
```

Note

Make sure to replace `clickpipes_user` and `some_secure_password` with your desired username and password.

## What's next?[​](#whats-next "Direct link to What's next?")

You can now [create your ClickPipe](/docs/integrations/clickpipes/mongodb) and start ingesting data from your MongoDB instance into ClickHouse Cloud.
Make sure to note down the connection details you used while setting up your MongoDB instance as you will need them during the ClickPipe creation process.

- [Enable oplog retention](#enable-oplog-retention)- [Configure a database user](#configure-database-user)- [What's next?](#whats-next)

Was this page helpful?
Controlling the Syncing of a MongoDB ClickPipe

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/mongodb/controlling_sync.md)

This document describes how to control the sync of a MongoDB ClickPipe when the ClickPipe is in **CDC (Running) mode**.

## Overview[​](#overview "Direct link to Overview")

Database ClickPipes have an architecture that consists of two parallel processes - pulling from the source database and pushing to the target database. The pulling process is controlled by a sync configuration that defines how often the data should be pulled and how much data should be pulled at a time. By "at a time", we mean one batch - since the ClickPipe pulls and pushes data in batches.

There are two main ways to control the sync of a MongoDB ClickPipe. The ClickPipe will start pushing when one of the below settings kicks in.

### Sync interval[​](#interval "Direct link to Sync interval")

The sync interval of the pipe is the amount of time (in seconds) for which the ClickPipe will pull records from the source database. The time to push what we have to ClickHouse isn't included in this interval.

The default is **1 minute**.
Sync interval can be set to any positive integer value, but it is recommended to keep it above 10 seconds.

### Pull batch size[​](#batch-size "Direct link to Pull batch size")

The pull batch size is the number of records that the ClickPipe will pull from the source database in one batch. Records mean inserts, updates and deletes done on the collections that are part of the pipe.

The default is **100,000** records.
A safe maximum is 10 million.

### Configuring sync settings[​](#configuring "Direct link to Configuring sync settings")

You can set the sync interval and pull batch size when you create a ClickPipe or edit an existing one.
When creating a ClickPipe it will be seen in the second step of the creation wizard, as shown below:

When editing an existing ClickPipe, you can head over to the **Settings** tab of the pipe, pause the pipe and then click on **Configure** here:

This will open a flyout with the sync settings, where you can change the sync interval and pull batch size:

### Monitoring sync control behaviour[​](#monitoring "Direct link to Monitoring sync control behaviour")

You can see how long each batch takes in the **CDC Syncs** table in the **Metrics** tab of the ClickPipe. Note that the duration here includes push time and also if there are no rows incoming, the ClickPipe waits and the wait time is also included in the duration.

- [Overview](#overview)
  - [Sync interval](#interval)- [Pull batch size](#batch-size)- [Configuring sync settings](#configuring)- [Monitoring sync control behaviour](#monitoring)

Was this page helpful?
Resyncing specific tables

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/mongodb/table_resync.md)

There are scenarios where it would be useful to have specific tables of a pipe be re-synced. Some sample use-cases could be major schema changes on MongoDB, or maybe some data re-modelling on the ClickHouse.

While resyncing individual tables with a button click is a work-in-progress, this guide will share steps on how you can achieve this today in the MongoDB ClickPipe.

### 1. Remove the table from the pipe[​](#removing-table "Direct link to 1. Remove the table from the pipe")

This can be followed by following the [table removal guide](/docs/integrations/clickpipes/mongodb/removing_tables).

### 2. Truncate or drop the table on ClickHouse[​](#truncate-drop-table "Direct link to 2. Truncate or drop the table on ClickHouse")

This step is to avoid data duplication when we add this table again in the next step. You can do this by heading over to the **SQL Console** tab in ClickHouse Cloud and running a query.
Note that we have validation to block table addition if the table already exists in ClickHouse and isn't empty.

### 3. Add the table to the ClickPipe again[​](#add-table-again "Direct link to 3. Add the table to the ClickPipe again")

This can be followed by following the [table addition guide](/docs/integrations/clickpipes/mongodb/add_table).

- [1. Remove the table from the pipe](#removing-table)- [2. Truncate or drop the table on ClickHouse](#truncate-drop-table)- [3. Add the table to the ClickPipe again](#add-table-again)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/mysql/add_table.md)

There are scenarios where it would be useful to add specific tables to a pipe. This becomes a common necessity as your transactional or analytical workload scales.

## Steps to add specific tables to a ClickPipe[​](#add-tables-steps "Direct link to Steps to add specific tables to a ClickPipe")

This can be done by the following steps:

1. [Pause](/docs/integrations/clickpipes/mysql/pause_and_resume) the pipe.
2. Click on Edit Table settings.
3. Locate your table - this can be done by searching it in the search bar.
4. Select the table by clicking on the checkbox.

  

5. Click update.
6. Upon successful update, the pipe will have statuses `Setup`, `Snapshot` and `Running` in that order. The table's initial load can be tracked in the **Tables** tab.

References

CDC for existing tables resumes automatically after the new table’s snapshot completes.

- [Steps to add specific tables to a ClickPipe](#add-tables-steps)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/mysql/pause_and_resume.md)

There are scenarios where it would be useful to pause a MySQL ClickPipe. For example, you may want to run some analytics on existing data in a static state. Or, you might be performing upgrades on MySQL. Here is how you can pause and resume a MySQL ClickPipe.

## Steps to pause a MySQL ClickPipe[​](#pause-clickpipe-steps "Direct link to Steps to pause a MySQL ClickPipe")

1. In the Data Sources tab, click on the MySQL ClickPipe you wish to pause.
2. Head over to the **Settings** tab.
3. Click on the **Pause** button.

4. A dialog box should appear for confirmation. Click on Pause again.

4. Head over to the **Metrics** tab.
5. In around 5 seconds (and also on page refresh), the status of the pipe should be **Paused**.

## Steps to resume a MySQL ClickPipe[​](#resume-clickpipe-steps "Direct link to Steps to resume a MySQL ClickPipe")

1. In the Data Sources tab, click on the MySQL ClickPipe you wish to resume. The status of the mirror should be **Paused** initially.
2. Head over to the **Settings** tab.
3. Click on the **Resume** button.

4. A dialog box should appear for confirmation. Click on Resume again.

5. Head over to the **Metrics** tab.
6. In around 5 seconds (and also on page refresh), the status of the pipe should be **Running**.

- [Steps to pause a MySQL ClickPipe](#pause-clickpipe-steps)- [Steps to resume a MySQL ClickPipe](#resume-clickpipe-steps)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/mysql/remove_table.md)

In some cases, it makes sense to exclude specific tables from a MySQL ClickPipe - for example, if a table isn't needed for your analytics workload, skipping it can reduce storage and replication costs in ClickHouse.

## Steps to remove specific tables[​](#remove-tables-steps "Direct link to Steps to remove specific tables")

The first step is to remove the table from the pipe. This can be done by the following steps:

1. [Pause](/docs/integrations/clickpipes/mysql/pause_and_resume) the pipe.
2. Click on Edit Table Settings.
3. Locate your table - this can be done by searching it in the search bar.
4. Deselect the table by clicking on the selected checkbox.

  

5. Click update.
6. Upon successful update, in the **Metrics** tab the status will be **Running**. This table will no longer be replicated by this ClickPipe.

- [Steps to remove specific tables](#remove-tables-steps)

Was this page helpful?