Integration with Jitsu, an evaluation for self deploy

Jitsu allows us to integrate multiple flavors of data sources, to consolidate. Here we will create a simple case, where we import some data from a MySQL source to a PostgreSQL instance.

The environment is simple:

git clone https://github.com/jitsucom/jitsu.git
cd jitsu

#Ubuntu/macOS
chmod -R 777 compose-data/

docker-compose up

It will ask you for your e-mail and password. Please take note of both, because at this moment we have not set up the SMTP server yet.

Ref: https://jitsu.com/docs/deployment/deploy-with-docker/docker-compose

If you have not installed the PostgreSQL, the best way is to use the Chocolatey https://chocolatey.org/.

choco install postgresql

We are using a local database with containers. Then it is necessary to allow access to the database for the container’s gateway.

Open file pg_hba.conf in directory C:\Program Files\PostgreSQL\14\data\.

The IP is the host.docker.internal resolver IP.

Add the line below at the end:

host    all             all             172.23.175.119/32       scram-sha-256

Destiny database configuration.

Origin database configuration.

Create some dummies tables for visualizing the way that CDC works:

About MySQL integration https://docs.airbyte.com/integrations/sources/mysql/
About CDC mechanism https://docs.airbyte.com/understanding-airbyte/cdc/

For complete sync.

-- CREATE TABLE "table_test" -----------------------------------
CREATE TABLE `table_test`(
    `indice` VarChar( 255 ) CHARACTER SET armscii8 COLLATE armscii8_general_ci NOT NULL )
CHARACTER SET = armscii8
COLLATE = armscii8_general_ci
ENGINE = InnoDB;
-- -------------------------------------------------------------

For incremental by time (Not have a primary key)

-- CREATE TABLE "table_test_inc_update_at" ---------------------
CREATE TABLE `table_test_inc_update_at`(
    `Name` VarChar( 50 ) CHARACTER SET armscii8 COLLATE armscii8_general_ci NOT NULL,
    `Desc` Int( 0 ) NOT NULL,
    `Serial` VarChar( 10 ) CHARACTER SET armscii8 COLLATE armscii8_general_ci NOT NULL,
    `created_at` Timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DateTime NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ( `ID` ) )
CHARACTER SET = armscii8
COLLATE = armscii8_general_ci
ENGINE = InnoDB;
-- -------------------------------------------------------------

For incremental by primary key.

-- CREATE TABLE "table_test_incremental" -----------------------
CREATE TABLE `table_test_incremental`(
    `ID` VarChar( 20 ) CHARACTER SET armscii8 COLLATE armscii8_general_ci NOT NULL,
    `Name` VarChar( 50 ) CHARACTER SET armscii8 COLLATE armscii8_general_ci NOT NULL,
    `Desc` Int( 0 ) NOT NULL,
    `Serial` VarChar( 10 ) CHARACTER SET armscii8 COLLATE armscii8_general_ci NOT NULL,
    PRIMARY KEY ( `ID` ) )
CHARACTER SET = armscii8
COLLATE = armscii8_general_ci
ENGINE = InnoDB;
-- -------------------------------------------------------------