title_wip

Note

Default values for all `ClickHouseIO` parameters can be found in [`ClickHouseIO` Apache Beam Connector](/docs/integrations/apache-beam#clickhouseiowrite-parameters)

## Source and target tables schema[​](#source-and-target-tables-schema "Direct link to Source and target tables schema")

To effectively load the BigQuery dataset into ClickHouse, the pipeline performs a column inference process with the following phases:

1. The templates build a schema object based on the target ClickHouse table.
2. The templates iterate over the BigQuery dataset, and attempts to match columns based on their names.

  

References

Having said that, your BigQuery dataset (either table or query) must have the exact same column names as your ClickHouse
target table.

## Data type mapping[​](#data-types-mapping "Direct link to Data type mapping")

The BigQuery types are converted based on your ClickHouse table definition. Therefore, the above table lists the
recommended mapping you should have in your target ClickHouse table (for a given BigQuery table/query):

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| BigQuery Type ClickHouse Type Notes|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [**Array Type**](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#array_type) [**Array Type**](/docs/sql-reference/data-types/array) The inner type must be one of the supported primitive data types listed in this table.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [**Boolean Type**](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#boolean_type) [**Bool Type**](/docs/sql-reference/data-types/boolean) |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [**Date Type**](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#date_type) [**Date Type**](/docs/sql-reference/data-types/date) |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [**Datetime Type**](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#datetime_type) [**Datetime Type**](/docs/sql-reference/data-types/datetime) Works as well with `Enum8`, `Enum16` and `FixedString`.| [**String Type**](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#string_type) [**String Type**](/docs/sql-reference/data-types/string) In BigQuery all Int types (`INT`, `SMALLINT`, `INTEGER`, `BIGINT`, `TINYINT`, `BYTEINT`) are aliases to `INT64`. We recommend you setting in ClickHouse the right Integer size, as the template will convert the column based on the defined column type (`Int8`, `Int16`, `Int32`, `Int64`).| [**Numeric - Integer Types**](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#numeric_types) [**Integer Types**](/docs/sql-reference/data-types/int-uint) In BigQuery all Int types (`INT`, `SMALLINT`, `INTEGER`, `BIGINT`, `TINYINT`, `BYTEINT`) are aliases to `INT64`. We recommend you setting in ClickHouse the right Integer size, as the template will convert the column based on the defined column type (`Int8`, `Int16`, `Int32`, `Int64`). The template will also convert unassigned Int types if used in ClickHouse table (`UInt8`, `UInt16`, `UInt32`, `UInt64`).| [**Numeric - Float Types**](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#numeric_types) [**Float Types**](/docs/sql-reference/data-types/float) Supported ClickHouse types: `Float32` and `Float64` | | | | | | | | | | | | | | | | | | | | | | | |

## Running the Template[​](#running-the-template "Direct link to Running the Template")

The BigQuery to ClickHouse template is available for execution via the Google Cloud CLI.

Note

Be sure to review this document, and specifically the above sections, to fully understand the template's configuration
requirements and prerequisites.

- Google Cloud Console- Google Cloud CLI

Sign in to your Google Cloud Console and search for DataFlow.

1. Press the `CREATE JOB FROM TEMPLATE` button
2. Once the template form is open, enter a job name and select the desired region.
3. In the `DataFlow Template` input, type `ClickHouse` or `BigQuery`, and select the `BigQuery to ClickHouse` template
4. Once selected, the form will expand to allow you to provide additional details:
   - The ClickHouse server JDBC url, with the following format `jdbc:clickhouse://host:port/schema`.
   - The ClickHouse username.
   - The ClickHouse target table name.

  

Note

The ClickHouse password option is marked as optional, for use cases where there is no password configured.
To add it, please scroll down to the `Password for ClickHouse Endpoint` option.

5. Customize and add any BigQuery/ClickHouseIO related configurations, as detailed in
   the [Template Parameters](#template-parameters) section

### Install & Configure `gcloud` CLI[​](#install--configure-gcloud-cli "Direct link to install--configure-gcloud-cli")

- If not already installed, install the [`gcloud` CLI](https://cloud.google.com/sdk/docs/install).
- Follow the `Before you begin` section
  in [this guide](https://cloud.google.com/dataflow/docs/guides/templates/using-flex-templates#before-you-begin) to set
  up the required configurations, settings, and permissions for running the DataFlow template.

### Run command[​](#run-command "Direct link to Run command")

Use the [`gcloud dataflow flex-template run`](https://cloud.google.com/sdk/gcloud/reference/dataflow/flex-template/run)
command to run a Dataflow job that uses the Flex Template.

Below is an example of the command:

```
gcloud dataflow flex-template run "bigquery-clickhouse-dataflow-$(date +%Y%m%d-%H%M%S)" \
 --template-file-gcs-location "gs://clickhouse-dataflow-templates/bigquery-clickhouse-metadata.json" \
 --parameters inputTableSpec="<bigquery table id>",jdbcUrl="jdbc:clickhouse://<clickhouse host>:<clickhouse port>/<schema>?ssl=true&sslmode=NONE",clickHouseUsername="<username>",clickHousePassword="<password>",clickHouseTable="<clickhouse target table>"
```

### Command breakdown[​](#command-breakdown "Direct link to Command breakdown")

- **Job Name:** The text following the `run` keyword is the unique job name.
- **Template File:** The JSON file specified by `--template-file-gcs-location` defines the template structure and
  details about the accepted parameters. The mention file path is public and ready to use.
- **Parameters:** Parameters are separated by commas. For string-based parameters, enclose the values in double quotes.

### Expected response[​](#expected-response "Direct link to Expected response")

After running the command, you should see a response similar to the following:

```
job:
  createTime: '2025-01-26T14:34:04.608442Z'
  currentStateTime: '1970-01-01T00:00:00Z'
  id: 2025-01-26_06_34_03-13881126003586053150
  location: us-central1
  name: bigquery-clickhouse-dataflow-20250126-153400
  projectId: ch-integrations
  startTime: '2025-01-26T14:34:04.608442Z'
```

### Monitor the job[​](#monitor-the-job "Direct link to Monitor the job")

Navigate to the [Dataflow Jobs tab](https://console.cloud.google.com/dataflow/jobs) in your Google Cloud Console to
monitor the status of the job. You'll find the job details, including progress and any errors:

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

### Memory limit (total) exceeded error (code 241)[​](#code-241-dbexception-memory-limit-total-exceeded "Direct link to Memory limit (total) exceeded error (code 241)")

This error occurs when ClickHouse runs out of memory while processing large batches of data. To resolve this issue: