CodingAirByteData Engineering
Extend and reuse an existing AirByte destination connector
My note for extending and reusing an existing AirByte target connector.
2 Min08 Dec, 2021

AirByte is an open-source ELT (Extract, Load, and Transformation) application. It heavily uses containerization for the deployment of its various components. On the local machine, we need docker to run it.

AirByte have impressive list of source and destination connectors available.

One of my use case data destinations is ClickHouse data warehouse and its destination connector is not yet (2021-12-08) available.

As per the documentation, It seems that creating a destination connector is a non-trivial job. It's a great idea to build an open-source ClickHouse destination connector. However, I tried avoiding the temptation to create one because of the required effort.

AirByte has MySql destination connector available. ClickHouse provides a MySQL connector for access from any MySQL client. We need to configure Clickhouse to give support for MySQL connector. Accessing ClickHouse from AirByte using its MySQL destination connector looks promising. However, when I tried connecting it from AirByte a small snag prevented it from making a connection.

Error Public Key Retrieval is not allowed

Error Public Key Retrieval is not allowed is thrown by AirByte.

The solution for this error is to pass property allowPublicKeyRetrieval with TRUE value to the underlying JDBC driver (AirByte uses JDBC MySQL drive). Unfortunately, the AirByte setting doesn't have any field to take these additional JDBC driver properties in the MySQL destination connector. However, we can copy, extend and deploy an existing Connector as per our need and add them into AirByte as a new source.

I git clone AirByte git repo at a local directory. All official code for source and destination connectors can be found in the directory airbyte/airbyte-integrations/connectors.

I went ahead inside destination-mysql and made some changes to the appropriate Java Source file to accommodate allowPublicKeyRetrieval=true properties.

Inside destination-mysql we have Dockerfile, I made the change in the

LABEL io.airbyte.name=geekmj/destination-mysql

It will help in getting a docker image with geekmj/destination-mysql name.

To build the docker image I run the following command:


# Must be run from the Airbyte project root
./gradlew :airbyte-integrations:connectors:destination-<name>:build

I published the generated image to Docker Repository.

In AirByte settings > Destinations I added a new connector with the appropriate name.

While creating a new destination I was able to see the newly added destination connector now.

When I tried connecting to ClickHouse now I started seeing a new error.

    
 SQL Error [62] [00000]: Code: 62. DB::Exception: Syntax error: failed at position 54 ('FULL'): FULL TABLES FROM test. Expected one of: TABLES, CLUSTER, CHANGED, GRANTS, CREATE, ACCESS, QUOTA, SETTINGS, CURRENT ROLES, PRIVILEGES, PROCESSLIST, CLUSTERS, DATABASES, CURRENT QUOTA, ENABLED ROLES, CREATE, DICTIONARIES, USERS, ROLES, SETTINGS PROFILES, PROFILES, ROW POLICIES, POLICIES, QUOTAS. (SYNTAX_ERROR) (version 21.11.4.14 (official build))
  Code: 62. DB::Exception: Syntax error: failed at position 54 ('FULL'): FULL TABLES FROM test. Expected one of: TABLES, CLUSTER, CHANGED, GRANTS, CREATE, ACCESS, QUOTA, SETTINGS, CURRENT ROLES, PRIVILEGES, PROCESSLIST, CLUSTERS, DATABASES, CURRENT QUOTA, ENABLED ROLES, CREATE, DICTIONARIES, USERS, ROLES, SETTINGS PROFILES, PROFILES, ROW POLICIES, POLICIES, QUOTAS. (SYNTAX_ERROR) (version 21.11.4.14 (official build))

I realized when we try connecting to ClickHouse using the JDBC MySQL connector it doesn't work as expected. While I can see the connection is established with the server but table level queries started failing.

Hence I decided to create a ClickHouse destination connector from scratch using the JDBC Clickhouse driver.

Mrityunjay
© 2021, All Rights Reserved
Made In India 🇮🇳 with ❤️
Quick Links