ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

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

### What does Resync do?[​](#what-mysql-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-mysql-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.

Note

You can resync multiple times, however please account for the load on the source database when you resync.

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

1. In the Data Sources tab, click on the MySQL 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. In around 5 seconds (and also on page refresh), the status of the pipe should 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-mysql-resync-do)- [Use cases for resyncing a ClickPipe](#use-cases-mysql-resync)- [Resync ClickPipe Guide](#guide-mysql-resync)

Was this page helpful?
Resyncing specific tables

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/mysql/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 MySQL, 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 MySQL 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/mysql/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/mysql/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/postgres/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/postgres/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?
Deduplication strategies (using CDC)

```
CREATE VIEW posts_view AS SELECT * FROM posts FINAL WHERE _peerdb_is_deleted=0;
CREATE VIEW users_view AS SELECT * FROM users FINAL WHERE _peerdb_is_deleted=0;
CREATE VIEW votes_view AS SELECT * FROM votes FINAL WHERE _peerdb_is_deleted=0;
CREATE VIEW comments_view AS SELECT * FROM comments FINAL WHERE _peerdb_is_deleted=0;
```

Then, we can query the views using the same query we would use in PostgreSQL.

```
-- Most viewed posts
SELECT
    sum(viewcount) AS viewcount,
    owneruserid
FROM posts_view
WHERE owneruserid > 0
GROUP BY owneruserid
ORDER BY viewcount DESC
LIMIT 10
```

#### Refreshable materialized view[​](#refreshable-material-view "Direct link to Refreshable materialized view")

Another approach is to use a [refreshable materialized view](/docs/materialized-view/refreshable-materialized-view), which enables you to schedule query execution for deduplicating rows and storing the results in a destination table. With each scheduled refresh, the destination table is replaced with the latest query results.

The key advantage of this method is that the query using the FINAL keyword runs only once during the refresh, eliminating the need for subsequent queries on the destination table to use FINAL.

However, a drawback is that the data in the destination table is only as up-to-date as the most recent refresh. That said, for many use cases, refresh intervals ranging from several minutes to a few hours may be sufficient.

```
-- Create deduplicated posts table 
CREATE TABLE deduplicated_posts AS posts;

-- Create the Materialized view and schedule to run every hour
CREATE MATERIALIZED VIEW deduplicated_posts_mv REFRESH EVERY 1 HOUR TO deduplicated_posts AS 
SELECT * FROM posts FINAL WHERE _peerdb_is_deleted=0
```

Then, you can query the table `deduplicated_posts` normally.

```
SELECT
    sum(viewcount) AS viewcount,
    owneruserid
FROM deduplicated_posts
WHERE owneruserid > 0
GROUP BY owneruserid
ORDER BY viewcount DESC
LIMIT 10;
```

- [How does data get replicated?](#how-does-data-get-replicated)
  - [PostgreSQL logical decoding](#PostgreSQL-logical-decoding)- [ReplacingMergeTree](#replacingmergetree)- [Illustrative example](#illustrative-example)- [Deduplicate using FINAL Keyword](#deduplicate-using-final-keyword)- [Query like with Postgres](#query-like-with-postgres)

Was this page helpful?
Postgres Generated Columns: Gotchas and Best Practices

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

When using PostgreSQL's generated columns in tables that are being replicated, there are some important considerations to keep in mind. These gotchas can affect the replication process and data consistency in your destination systems.

## The problem with generated columns[​](#the-problem-with-generated-columns "Direct link to The problem with generated columns")

1. **Not Published via `pgoutput`:** Generated columns aren't published through the `pgoutput` logical replication plugin. This means that when you're replicating data from PostgreSQL to another system, the values of generated columns aren't included in the replication stream.
2. **Issues with Primary Keys:** If a generated column is part of your primary key, it can cause deduplication problems on the destination. Since the generated column values aren't replicated, the destination system won't have the necessary information to properly identify and deduplicate rows.
3. **Issues with schema changes**: If you add a generated column to a table that is already being replicated, the new column won't be populated in the destination - as Postgres doesn't give us the RelationMessage for the new column. If you then add a new non-generated column to the same table, the ClickPipe, when trying to reconcile the schema, won't be able to find the generated column in the destination, leading to a failure in the replication process.

## Best practices[​](#best-practices "Direct link to Best practices")

To work around these limitations, consider the following best practices:

1. **Recreate Generated Columns on the Destination:** Instead of relying on the replication process to handle generated columns, it's recommended to recreate these columns on the destination using tools like dbt (data build tool) or other data transformation mechanisms.
2. **Avoid Using Generated Columns in Primary Keys:** When designing tables that will be replicated, it's best to avoid including generated columns as part of the primary key.

## Upcoming improvements to UI[​](#upcoming-improvements-to-ui "Direct link to Upcoming improvements to UI")

In upcoming versions, we're planning to add a UI to help you with the following:

1. **Identify Tables with Generated Columns:** The UI will have a feature to identify tables that contain generated columns. This will help you understand which tables are affected by this issue.
2. **Documentation and Best Practices:** The UI will include best practices for using generated columns in replicated tables, including guidance on how to avoid common pitfalls.

- [The problem with generated columns](#the-problem-with-generated-columns)- [Best practices](#best-practices)- [Upcoming improvements to UI](#upcoming-improvements-to-ui)

Was this page helpful?
title_wip

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

There is an upcoming maintenance window for Postgres ClickPipes scheduled on:

- **Date:** 17 April 2025
- **Time:** 07:00 AM - 08:00 AM UTC

During this time, your Postgres Pipes will experience a brief downtime.
The ClickPipes will be available again after the maintenance window and will resume normal operations.

Was this page helpful?
Pausing and Resuming a Postgres ClickPipe

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

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

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

1. In the Data Sources tab, click on the Postgres 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**.

Note

Pausing a Postgres ClickPipe won't pause the growth of replication slots.

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

1. In the Data Sources tab, click on the Postgres 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 Postgres ClickPipe](#pause-clickpipe-steps)- [Steps to resume a Postgres 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/postgres/remove_table.md)

In some cases, it makes sense to exclude specific tables from a Postgres 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/postgres/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/postgres/resync.md)

### What does Resync do?[​](#what-postgres-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-postgres-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.
3. The replication slot of the ClickPipe is invalidated: Resync creates a new ClickPipe and a new slot on the source database.

Note

You can resync multiple times, however please account for the load on the source database when you resync,
since initial load with parallel threads is involved each time.

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

1. In the Data Sources tab, click on the Postgres 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. In around 5 seconds (and also on page refresh), the status of the pipe should 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-postgres-resync-do)- [Use cases for resyncing a ClickPipe](#use-cases-postgres-resync)- [Resync ClickPipe Guide](#guide-postgres-resync)

Was this page helpful?
Schema Changes Propagation Support

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

ClickPipes for Postgres can detect schema changes in the source tables and, in some cases, automatically propagate the changes to the destination tables. The way each DDL operation is handled is documented below:

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Schema Change Type Behaviour|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Adding a new column (`ALTER TABLE ADD COLUMN ...`) Propagated automatically once the table gets an insert/update/delete. The new columns will be populated for all rows replicated after the schema change|  |  |  |  | | --- | --- | --- | --- | | Adding a new column with a default value (`ALTER TABLE ADD COLUMN ... DEFAULT ...`) Propagated automatically once the table gets an insert/update/delete. The new columns will be populated for all rows replicated after the schema change, but existing rows won't show the default value without a full table refresh|  |  | | --- | --- | | Dropping an existing column (`ALTER TABLE DROP COLUMN ...`) Detected, but **not** propagated. The dropped columns will be populated with `NULL` for all rows replicated after the schema change | | | | | | | |

Note that column addition will be propagated at the end of a batch's sync, which could occur after the sync interval or pull batch size is reached. More information on controlling syncs [here](/docs/integrations/clickpipes/postgres/sync_control)

Was this page helpful?
Resyncing specific tables

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/postgres/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 Postgres, 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 Postgres 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/postgres/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.

Alternatively, if you need to keep the old table you can simply rename it. This is also helpful when the table is very big and the drop operation might take some time.

```
RENAME TABLE table_A TO table_A_bak;
```

### 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/postgres/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?
Importing from various data formats to ClickHouse

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

In this section of the docs, you can find examples for loading from various file types.

### [**Binary**](/docs/integrations/data-formats/binary-native)[​](#binary "Direct link to binary")

Export and load binary formats such as ClickHouse Native, MessagePack, Protocol Buffers and Cap'n Proto.

### [**CSV and TSV**](/docs/integrations/data-formats/csv-tsv)[​](#csv-and-tsv "Direct link to csv-and-tsv")

Import and export the CSV family, including TSV, with custom headers and separators.

### [**JSON**](/docs/integrations/data-formats/json/overview)[​](#json "Direct link to json")

Load and export JSON in various formats including as objects and line delimited NDJSON.

### [**Parquet data**](/docs/integrations/data-formats/parquet)[​](#parquet-data "Direct link to parquet-data")

Handle common Apache formats such as Parquet and Arrow.

### [**SQL data**](/docs/integrations/data-formats/sql)[​](#sql-data "Direct link to sql-data")

Need a SQL dump to import into MySQL or Postgresql? Look no further.

If you're looking to connect a BI tool like Grafana, Tableau and others, check out the [Visualize category](/docs/integrations/data-visualization) of the docs.

- [**Binary**](#binary)- [**CSV and TSV**](#csv-and-tsv)- [**JSON**](#json)- [**Parquet data**](#parquet-data)- [**SQL data**](#sql-data)

Was this page helpful?
Using native and binary formats in ClickHouse

```
@0xec8ff1a10aa10dbe;

struct PathStats {
  path @0 :Text;
  month @1 :UInt32;
  hits @2 :UInt32;
}
```

Now we can import and export using [CapnProto](/docs/interfaces/formats/CapnProto) format and this schema:

```
SELECT
    path,
    CAST(month, 'UInt32') AS month,
    hits
FROM some_data
INTO OUTFILE 'capnp.bin'
FORMAT CapnProto
SETTINGS format_schema = 'schema:PathStats'
```

Note that we had to cast the `Date` column as `UInt32` to [match corresponding types](/docs/interfaces/formats/CapnProto#data_types-matching-capnproto).

## Other formats[​](#other-formats "Direct link to Other formats")

ClickHouse introduces support for many formats, both text, and binary, to cover various scenarios and platforms. Explore more formats and ways to work with them in the following articles:

- [CSV and TSV formats](/docs/integrations/data-formats/csv-tsv)
- [Parquet](/docs/integrations/data-formats/parquet)
- [JSON formats](/docs/integrations/data-formats/json/overview)
- [Regex and templates](/docs/integrations/data-formats/templates-regexp)
- **Native and binary formats**
- [SQL formats](/docs/integrations/data-formats/sql)

And also check [clickhouse-local](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local) - a portable full-featured tool to work on local/remote files without starting ClickHouse server.

- [Exporting in a Native ClickHouse format](#exporting-in-a-native-clickhouse-format)
  - [Importing from a Native format](#importing-from-a-native-format)- [Native format compression](#native-format-compression)- [Exporting to RowBinary](#exporting-to-rowbinary)
    - [Exploring RowBinary files](#exploring-rowbinary-files)- [Importing from RowBinary files](#importing-from-rowbinary-files)- [Importing single binary value using RawBLOB](#importing-single-binary-value-using-rawblob)
      - [Exporting RawBLOB data](#exporting-rawblob-data)- [MessagePack](#messagepack)- [Protocol Buffers](#protocol-buffers)- [Cap'n Proto](#capn-proto)- [Other formats](#other-formats)

Was this page helpful?