Connect Airbyte to ClickHouse
Airbyte is an open-source data integration platform. It allows the creation of ELT data pipelines and is shipped with more than 140 out-of-the-box connectors. This step-by-step tutorial shows how to connect Airbyte to ClickHouse as a destination and load a sample dataset.1. Download and run Airbyteβ
Airbyte runs on Docker and uses
docker-compose
. Make sure to download and install the latest versions of Docker.Deploy Airbyte by cloning the official Github repository and running
docker-compose up
in your favorite terminal:git clone https://github.com/airbytehq/airbyte.git
cd airbyte
docker-compose upOnce you see the Airbyte banner in your terminal, you can connect to localhost:8000
note
Alternatively, you can signup and use Airbyte Cloud
2. Add ClickHouse as a destinationβ
In this section, we will display how to add a ClickHouse instance as a destination.
Start your ClickHouse server (Airbyte is compatible with ClickHouse version
21.8.10.19
or above):clickhouse-server start
Within Airbyte, select the "Destinations" page and add a new destination:
Pick a name for your destination and select ClickHouse from the "Destination type" drop-down list:
Fill out the "Set up the destination" form by providing your ClickHouse hostname and ports, database name, username and password and select if it's a TLS connection (equivalent to the
--secure
flag in theclickhouse-client
).Congratulations! you have now added ClickHouse as a destination in Airbyte.
note
In order to use ClickHouse as a destination, the user you'll use need to have the permissions to create databases, tables and insert rows. We recommend creating a dedicated user for Airbyte (eg. my_airbyte_user
) with the following permissions:
GRANT CREATE ON * TO my_airbyte_user;
3. Add a dataset as a sourceβ
The example dataset we will use is the New York City Taxi Data (on Github). For this tutorial, we will use a subset of this dataset which corresponds to the month of July 2021.
Within Airbyte, select the "Sources" page and add a new source of type file.
Fill out the "Set up the source" form by naming the source and providing the URL of the NYC Taxi July 2021 file (see below). Make sure to pick
csv
as file format,HTTPS Public Web
as Storage Provider andnyc_taxi_072021
as Dataset Name.https://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-07.csv
Congratulations! You have now added a source file in Airbyte.
4. Create a connection and load the dataset into ClickHouseβ
Within Airbyte, select the "Connections" page and add a new connection
Select "Use existing source" and select the New York City Taxi Data, the select "Use existing destination" and select you ClickHouse instance.
Fill out the "Set up the connection" form by choosing a Replication Frequency (we will use
manual
for this tutorial) and selectnyc_taxi_072021
as the stream you want to sync. Make sure you pickNormalized Tabular Data
as a Normalization.Now that the connection is created, click on "Sync now" to trigger the data loading (since we picked
Manual
as a Replication Frequency)
Your data will start loading, you can expand the view to see Airbyte logs and progress. Once the operation finishes, you'll see a
Completed successfully
message in the logs:Connect to your ClickHouse instance using your preferred SQL Client and check the resulting table:
SELECT *
FROM nyc_taxi_072021
LIMIT 10The response should look like:
Query id: 1dbe609f-9136-49cf-a642-51a2305e1027
ββextraββ¬βmta_taxββ¬βVendorIDββ¬βRatecodeIDββ¬βtip_amountββ¬βfare_amountββ¬βDOLocationIDββ¬βPULocationIDββ¬βpayment_typeββ¬βtolls_amountββ¬βtotal_amountββ¬βtrip_distanceββ¬βpassenger_countββ¬βstore_and_fwd_flagββ¬βcongestion_surchargeββ¬βtpep_pickup_datetimeββ¬βimprovement_surchargeββ¬βtpep_dropoff_datetimeββ¬β_airbyte_ab_idββββββββββββββββββββββββ¬βββββ_airbyte_emitted_atββ¬β_airbyte_normalized_atββ¬β_airbyte_nyc_taxi_072021_hashidβββ
β 3.5 β 0.5 β 1 β 1 β 0 β 11.5 β 237 β 162 β 2 β 0 β 15.8 β 2.3 β 1 β α΄Ία΅α΄Έα΄Έ β 2.5 β 2021-07-07 17:49:32 β 0.3 β 2021-07-07 18:04:30 β 00000005-a90c-41b7-8883-1ab75c0ad9da β 2022-03-16 13:02:50.000 β 2022-03-16 13:09:48 β DE8F3E68A49EC6CB00919501E6726335 β
β 0 β 0.5 β 2 β 1 β 10 β 23 β 256 β 233 β 1 β 0 β 36.3 β 5.4 β 1 β α΄Ία΅α΄Έα΄Έ β 2.5 β 2021-07-15 07:23:36 β 0.3 β 2021-07-15 07:50:28 β 00001877-58ba-4614-90d4-4e5eba3cd593 β 2022-03-16 13:04:46.000 β 2022-03-16 13:09:48 β 7915C6A4D33BCE7CF58D66CF1F2E1A61 β
β 0.5 β 0.5 β 2 β 1 β 5 β 30.5 β 138 β 137 β 1 β 6.55 β 45.85 β 10.93 β 1 β α΄Ία΅α΄Έα΄Έ β 2.5 β 2021-07-18 05:00:28 β 0.3 β 2021-07-18 05:18:54 β 00001885-d93e-49d7-a92c-c09fd49e8b39 β 2022-03-16 13:05:37.000 β 2022-03-16 13:09:48 β A7346163EA6D6F0CBBA562CE1C5F9401 β
β 2.5 β 0.5 β 1 β 1 β 0 β 5 β 100 β 186 β 2 β 0 β 8.3 β 1 β 1 β α΄Ία΅α΄Έα΄Έ β 2.5 β 2021-07-07 09:47:59 β 0.3 β 2021-07-07 09:52:13 β 000029d1-2e26-4d83-9efe-51cb182282d9 β 2022-03-16 13:02:42.000 β 2022-03-16 13:09:48 β C6389A8B2B6E24A74612F7FB53DAA9A0 β
β 1 β 0.5 β 2 β 1 β 4 β 19.5 β 13 β 161 β 1 β 0 β 27.8 β 5.06 β 3 β α΄Ία΅α΄Έα΄Έ β 2.5 β 2021-07-12 17:54:49 β 0.3 β 2021-07-12 18:17:43 β 00003433-6886-4267-b8a9-da1b366537c4 β 2022-03-16 13:04:06.000 β 2022-03-16 13:09:48 β 8E7C4E55F366901E4B6DFB02C3CAE838 β
β 0 β 0.5 β 2 β 1 β 0 β 7 β 233 β 140 β 2 β 0 β 10.3 β 1.3 β 1 β α΄Ία΅α΄Έα΄Έ β 2.5 β 2021-07-15 13:06:34 β 0.3 β 2021-07-15 13:13:24 β 000049ae-b0c8-4e07-a3e6-ea19916fb6c3 β 2022-03-16 13:04:51.000 β 2022-03-16 13:09:48 β 704F99F611D1A71713A4870406E28B54 β
β 3.5 β 0.5 β 1 β 1 β 9.8 β 35 β 138 β 230 β 1 β 0 β 49.1 β 9.9 β 0 β α΄Ία΅α΄Έα΄Έ β 2.5 β 2021-07-09 16:09:24 β 0.3 β 2021-07-09 16:45:15 β 00004cc2-868e-4465-a24b-7efcb5da8cd4 β 2022-03-16 13:03:20.000 β 2022-03-16 13:09:48 β 8AB6444AD089BA300B303447C4B70500 β
β 2.5 β 0.5 β 1 β 1 β 3 β 10 β 232 β 224 β 1 β 0 β 16.3 β 2.6 β 0 β α΄Ία΅α΄Έα΄Έ β 2.5 β 2021-07-06 15:21:57 β 0.3 β 2021-07-06 15:30:09 β 00005277-bc5f-4d1e-b116-d3777fef87f7 β 2022-03-16 13:02:33.000 β 2022-03-16 13:09:48 β AC5A4F12E7EC61116F146DE90375A74B β
β 0.5 β 0.5 β 2 β 1 β 2.34 β 6.5 β 42 β 41 β 1 β 0 β 10.14 β 1.02 β 1 β α΄Ία΅α΄Έα΄Έ β 0 β 2021-07-16 20:27:38 β 0.3 β 2021-07-16 20:33:46 β 0000571b-6698-43f4-878d-d0d3f91e40d1 β 2022-03-16 13:05:16.000 β 2022-03-16 13:09:48 β A447703038C0257801F7DA3CBBCA47CB β
β 0 β 0.5 β 2 β 1 β 0 β 24 β 232 β 48 β 2 β 0 β 27.3 β 6.74 β 1 β α΄Ία΅α΄Έα΄Έ β 2.5 β 2021-07-10 15:00:11 β 0.3 β 2021-07-10 15:27:38 β 000060b7-76b5-4d73-ae7f-0c475f69078b β 2022-03-16 13:03:35.000 β 2022-03-16 13:09:48 β 6A593070389760D2339DDBD76E913447 β
βββββββββ΄ββββββββββ΄βββββββββββ΄βββββββββββββ΄βββββββββββββ΄ββββββββββββββ΄βββββββββββββββ΄βββββββββββββββ΄βββββββββββββββ΄βββββββββββββββ΄βββββββββββββββ΄ββββββββββββββββ΄ββββββββββββββββββ΄βββββββββββββββββββββ΄βββββββββββββββββββββββ΄βββββββββββββββββββββββ΄ββββββββββββββββββββββββ΄ββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββββββββββ΄ββββββββββββββββββββββββββ΄βββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββββββSELECT count(*)
FROM nyc_taxi_072021The response is:
Query id: a9172d39-50f7-421e-8330-296de0baa67e
ββcount()ββ
β 2821515 β
βββββββββββ
Notice that Airbyte automatically inferred the data types and added 4 columns to the destination table. These columns are used by Airbyte to manage the replication logic and log the operations. More details are available in the Airbyte official documentation.
`_airbyte_ab_id` String,
`_airbyte_emitted_at` DateTime64(3, 'GMT'),
`_airbyte_normalized_at` DateTime,
`_airbyte_nyc_taxi_072021_hashid` StringNow that the dataset is loaded on your ClickHouse instance, you can create an new table and use more suitable ClickHouse data types (more details).
- Congratulations - you have successfully loaded the NYC taxi data into ClickHouse using Airbyte!