Important:The Google BigQuery → ClickHouse pipeline is currently in the alpha testing stage. The functionality is still in development. If you're meeting any difficulties in setting up or working with the pipeline, please let our support team know by writing at firstname.lastname@example.org or via the online chat.
The Google BigQuery → ClickHouse lets you automate data export to ClickHouse, a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP) from Yandex.
Thanks to our pipeline, you can:
- Automatically import to ClickHouse the data collect in BigQuery by any other integration
- Move sensitive and personal data from a third-party cloud to your own server and safely interact with it there
What you need to set up a Google BigQuery → ClickHouse pipeline
Before setting up the pipeline in OWOX BI, make sure you have:
- Data collected to Google BigQuery. For example website user behavior data uploaded via OWOX BI's Google Analytics → Google BigQuery pipeline
- Set up and working ClickHouse on your server. Read the complete guide on setting up and working with ClickHouse in its official documentation
- Created a database and a table in ClickHouse. During the pipeline creation, you'll need to specify where you want to upload the BigQuery data to
- An SQL query for getting data from a BigQuery table
Important:Before setting up the pipeline, make sure you have added to the ClickHouse table all the fields you are going to pull from the BigQuery table using the SQL query. If a field is specified in the query but is absent from the ClickHouse table or has a different data type, the pipeline won't transfer this field's value.
Note:By default, the pipeline uploads data from a BigQuery table into a ClickHouse table, without replacing the duplicate data. The logic behind the uploading and overwriting the data is managed solely in ClickHouse. To avoid the possible data duplication, before creating the pipeline, make sure your ClickHouse storage is set up according to your data storage needs.
Setting up the pipeline
On the OWOX BI main page, in the Pipeline section, click Create pipeline:
- Select Google BigQuery as the data source:
- Select ClickHouse as the pipeline destination:
- Provide OWOX BI with the access to a Google account that, in turn, has access to the Google BigQuery containing the data you want to export to ClickHouse:
- Select the Google BigQuery project that stores the data you want to export:
- Provide access to your ClickHouse. To do this, you need to specify the Host and the Port of your ClickHouse server, as well as the Username and the Password.
Important: As the Host field, use either an IP address or a domain name omitting "https://". Also, note that you can use only the HTTPS protocol to connect. The HTTP protocol is not supported because it's not secure and may compromise the security of your data.
- Finally, select the ClickHouse Database and the Table you want to export data to. Also, specify the export behavior, Summation or Overwrite:
- The pipeline is created, but its status is Blocked. Now, you need to send to our support team the SQL query that specifies the fields you want to pull from the BigQuery table to ClickHouse. Just email the query and the link to the created pipeline to email@example.com, and we'll do the rest.
- Once our support specialist has let you know that your query is implemented, go to the pipeline's page, click Activate, then click The query is ready.
Done! The Google BigQuery → ClickHouse pipeline is set up and is exporting data to your ClickHouse server.