Skip to main content

Extend and reuse an existing AirByte destination connector

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 has an impressive list of source and destination connectors available.

One of my use case data destinations is the 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 a MySql destination connector available. ClickHouse provides a MySQL connector for access from any MySQL client. We need to configure Clickhouse to give support for the 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 is thrown by AirByte.

The solution for this error is to pass a 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.

Comments

Post a Comment

Popular posts from this blog

Java Functional Interface

1. What is Functional Interface An interface with only one abstract method is called Functional Interface. Pre Java 8, there was no concept of Functional Interface. However, interfaces like Comparator and Runnable have only one abstract method. Hence they fell into the Functional Interface category in Java 8 unintentionally. 2. Example 2.1 getSellingPrice method snippet from App.Java package org.geekmj.java8; public class App { public static void main(String[] args) { // } private static float getSellingPrice(float cost, float couponDiscount, DiscounterFunctionalInterface discounter) { return cost - couponDiscount - discounter.discount(cost); } } Here, we have a method getSellingPrice , with three arguments cost , couponDiscount and discounter . discounter is of type DiscounterFunctionalInterface . It calculates the selling price for an item. 2.2 DiscounterFunctionalInterface.java package org.geekmj.java8; @FunctionalInterface public interface DiscounterFunctiona...
Post a Comment
Read more

Useful SFTP terminal commands

First Step - Connect to a remote computer sftp -i ~/.ssh/LightsailDefaultKey-ap-south-1.pem ubuntu@52.66.180.155 Similar to ssh command. Output: Connected to 52.66.180.155. sftp> Remote and Local computer commands When we relay basic terminal commands at sftp> like 'pwd , ls , cd , and it is executed at the remote computer. Basic terminal commands above when prefixed with l like lpwd , lls , lcd and etc. it is executed at a local computer. For e.g. List all files in the present working directory at the remote computer: sftp> ls List all files in the present working directory at the local computer: sftp> lls Sending a local computer file to present the working directory of a remote computer sftp> put /Users/mj/Downloads/test.png Output Uploading /Users/mj/Downloads/test.png to /home/ubuntu/test.png /Users/mj/Downloads/tech-mrit-prod.ovpn 100% 13KB 382.9KB/s 00:00 References How To Use SFTP to Securely Transfer Files with a Re...
Post a Comment
Read more

Understanding Type Checking

A few examples of types in the context of programming language can be integer, float, character, string, array, etc.  When a program executes then data flow between instructions and values of specific types are assigned to a variable after some operation. It's important for the system to verify if the correct types are used as operands in operations. For e.g. In a sum operation, the expectation for operands to be of numeric type. The program's execution should fail in the case there is inconsistency. We can classify programming languages into two categories based as per their ability to cater to type safety: Dynamically Typed Language Statically Typed Language
Post a Comment
Read more