title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/google-dataflow/templates/bigquery-to-clickhouse.md)

The BigQuery to ClickHouse template is a batch pipeline that ingests data from a BigQuery table into a ClickHouse table.
The template can read the entire table or filter specific records using a provided SQL query.

- [Pipeline requirements](#pipeline-requirements)- [Template parameters](#template-parameters)- [Source and target tables schema](#source-and-target-tables-schema)- [Data type mapping](#data-types-mapping)- [Running the Template](#running-the-template)- [Troubleshooting](#troubleshooting)- [Template source code](#template-source-code)

## Pipeline requirements[​](#pipeline-requirements "Direct link to Pipeline requirements")

- The source BigQuery table must exist.
- The target ClickHouse table must exist.
- The ClickHouse host must be accessible from the Dataflow worker machines.

## Template parameters[​](#template-parameters "Direct link to Template parameters")

  
  

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Name Parameter Description Required Notes|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `jdbcUrl` The ClickHouse JDBC URL in the format `jdbc:clickhouse://<host>:<port>/<schema>`. ✅ Don't add the username and password as JDBC options. Any other JDBC option could be added at the end of the JDBC URL. For ClickHouse Cloud users, add `ssl=true&sslmode=NONE` to the `jdbcUrl`.| `clickHouseUsername` The ClickHouse username to authenticate with. ✅ |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `clickHousePassword` The ClickHouse password to authenticate with. ✅ |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `clickHouseTable` The target ClickHouse table into which data will be inserted. ✅ |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `maxInsertBlockSize` The maximum block size for insertion, if we control the creation of blocks for insertion (ClickHouseIO option). A `ClickHouseIO` option.| `insertDistributedSync` If setting is enabled, insert query into distributed waits until data will be sent to all nodes in cluster. (ClickHouseIO option). A `ClickHouseIO` option.| `insertQuorum` For INSERT queries in the replicated table, wait writing for the specified number of replicas and linearize the addition of the data. 0 - disabled. A `ClickHouseIO` option. This setting is disabled in default server settings.| `insertDeduplicate` For INSERT queries in the replicated table, specifies that deduplication of inserting blocks should be performed. A `ClickHouseIO` option.| `maxRetries` Maximum number of retries per insert. A `ClickHouseIO` option.| `InputTableSpec` The BigQuery table to read from. Specify either `inputTableSpec` or `query`. When both are set, the `query` parameter takes precedence. Example: `<BIGQUERY_PROJECT>:<DATASET_NAME>.<INPUT_TABLE>`. Reads data directly from BigQuery storage using the [BigQuery Storage Read API](https://cloud.google.com/bigquery/docs/reference/storage). Be aware of the [Storage Read API limitations](https://cloud.google.com/bigquery/docs/reference/storage#limitations).| `outputDeadletterTable` The BigQuery table for messages that failed to reach the output table. If a table doesn't exist, it is created during pipeline execution. If not specified, `<outputTableSpec>_error_records` is used. For example, `<PROJECT_ID>:<DATASET_NAME>.<DEADLETTER_TABLE>`. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `query` The SQL query to use to read data from BigQuery. If the BigQuery dataset is in a different project than the Dataflow job, specify the full dataset name in the SQL query, for example: `<PROJECT_ID>.<DATASET_NAME>.<TABLE_NAME>`. Defaults to [GoogleSQL](https://cloud.google.com/bigquery/docs/introduction-sql) unless `useLegacySql` is true. You must specify either `inputTableSpec` or `query`. If you set both parameters, the template uses the `query` parameter. Example: `SELECT * FROM sampledb.sample_table`.| `useLegacySql` Set to `true` to use legacy SQL. This parameter only applies when using the `query` parameter. Defaults to `false`. |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `queryLocation` Needed when reading from an authorized view without the underlying table's permission. For example, `US`. |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `queryTempDataset` Set an existing dataset to create the temporary table to store the results of the query. For example, `temp_dataset`. |  |  |  |  | | --- | --- | --- | --- | | `KMSEncryptionKey` If reading from BigQuery using the query source, use this Cloud KMS key to encrypt any temporary tables created. For example, `projects/your-project/locations/global/keyRings/your-keyring/cryptoKeys/your-key`. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

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