postgreSQL demo.

[subkanthi]

1. Get the latest release by executing the following script.

cd clickhouse-sink-connector/sink-connector-lightweight/docker
./getLatestRelease.sh
The latest release is: 2.4.0 published on: 2024-10-17T23:31:37Z
****** Run the following command to set the environment variable pointing to the latest version ******
****************************************************************************************************
 export CLICKHOUSE_SINK_CONNECTOR_LT_IMAGE=altinity/clickhouse-sink-connector:2.4.0-lt
****************************************************************************************************

2. Run the export command to set the version.

export CLICKHOUSE_SINK_CONNECTOR_LT_IMAGE=altinityinfra/clickhouse-sink-connector:926-7af155550916ca12b849d8f5a03f8b43f95f40e4-lt

3. Start the sink connector with the postgres configuration.

cd clickhouse-sink-connector/sink-connector-lightweight/docker
docker-compose -f docker-compose-postgres.yml up

or start the components separately.

docker-compose -f docker-compose-postgres.yml start postgres
docker-compose -f docker-compose-postgres.yml start clickhouse

docker-compose -f docker-compose-postgres.yml start clickhouse-sink-connector-lt

4. Login to the postgresql container.

docker exec -it <container_id> bash

psql -U root public

Set Extended Display mode

\x

Observe the tables that are in the public schema.

public=# \d+;
                                 List of relations
 Schema | Name | Type  | Owner | Persistence | Access method | Size  | Description 
--------+------+-------+-------+-------------+---------------+-------+-------------
 public | tm   | table | root  | permanent   | heap          | 16 kB | 
 public | tm2  | table | root  | permanent   | heap          | 16 kB | 
(2 rows)

5. Login to the clickhouse container.

docker exec -it <container_id> bash

clickhouse-client
use public;

21a8b3aac901 :) show tables;

SHOW TABLES

Query id: 2ae901f6-fa4b-42fd-b195-26370743b2d2

┌─name─┐
│ tm   │
│ tm2  │
└──────┘

2 rows in set. Elapsed: 0.001 sec. 

Schema

public=# \d public.tm2;
                            Table "public.tm2"
    Column     |           Type           | Collation | Nullable | Default 
---------------+--------------------------+-----------+----------+---------
 buying_price  | numeric                  |           |          | 
 selling_price | bigint                   |           |          | 
 schedule      | character varying(222)   |           |          | 
 selling_date  | timestamp with time zone |           |          | 
 response      | jsonb                    |           |          | 
 items         | text[]                   |           |          | 
 prices        | integer[]                |           |          | 
Publications:
    "dbz_publication"

** ClickHouse**

use test;
8df39a36759e :) show create table tm2;

SHOW CREATE TABLE tm2

Query id: 4436ec68-b1f6-4be1-9510-90a5c300eda2

┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE public.tm2
(
    `buying_price` Nullable(Decimal(64, 18)),
    `selling_price` Nullable(Int64),
    `schedule` Nullable(String),
    `selling_date` Nullable(DateTime64(6)),
    `response` Nullable(String),
    `items` Array(String),
    `prices` Array(Int32),
    `_version` UInt64,
    `_sign` UInt8
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}', _version, _sign)
ORDER BY tuple()
SETTINGS index_granularity = 8192 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.002 sec. 

You will see that the tables tm and tm2 are replicated which highlights the initial snapshot feature (replication of data that exists before the connector was started).

Lets confirm the data.

6. Lets test the real-time replication feature(switch to postgreSQL container), insert a new row to the public.tm2 table.

INSERT INTO public.tm2 (
    buying_price,
    selling_price,
    schedule,
    selling_date,
    response,
    items,
    prices
)
VALUES (
    123.45,                              -- Numeric value for buying_price
    200,                                 -- Bigint value for selling_price
    'Weekly schedule',                   -- String (up to 222 characters) for schedule
    '2024-11-16 15:30:00+00',           -- Timestamp with timezone for selling_date
    '{"status": "success"}',             -- JSONB value for response
    ARRAY['item1', 'item2'],             -- Text array for items
    ARRAY[100, 150, 200]                -- Integer array for prices
);

7. Switch to clickhouse container, observe the row that got replicated.

21a8b3aac901 :) select * from tm2 \G;

SELECT *
FROM tm2

Query id: 51c2a219-6e8c-4d05-97de-b4bf08192a8f

┌─buying_price─┬─selling_price─┬─schedule─┬───────────────selling_date─┬─response─────────┬─items─────────────┬─prices──┬────────────_version─┬─_sign─┐
│      1222.22 │         23232 │ sdsdsd   │ 2010-12-31 18:00:00.000000 │ {"key": "value"} │ ['text1','text2'] │ [1,2,3] │ 1732042035228000003 │     0 │
└──────────────┴───────────────┴──────────┴────────────────────────────┴──────────────────┴───────────────────┴─────────┴─────────────────────┴───────┘
┌─buying_price─┬─selling_price─┬─schedule────────┬───────────────selling_date─┬─response──────────────┬─items─────────────┬─prices────────┬────────────_version─┬─_sign─┐
│       123.45 │           200 │ Weekly schedule │ 2024-11-16 09:30:00.000000 │ {"status": "success"} │ ['item1','item2'] │ [100,150,200] │ 1732042576384000006 │     0 │
└──────────────┴───────────────┴─────────────────┴────────────────────────────┴───────────────────────┴───────────────────┴───────────────┴─────────────────────┴───────┘

2 rows in set. Elapsed: 0.002 sec. 

8. Delete a row:
   Before deleting in postgres, check the rows in clickhouse.

select * from public.tm \G;

public=# select id from public.tm;
                  id                  
--------------------------------------
 9cb52b2a-8ef2-4987-8856-c79a1b2c2f71
 9cb52b2a-8ef2-4987-8856-c79a1b2c2f73
(2 rows)

public=# delete from public.tm where id='9cb52b2a-8ef2-4987-8856-c79a1b2c2f71';
DELETE 1

9. Observe that the row is deleted from clickhouse.

8df39a36759e :) select * from public.tm \G;

SELECT *
FROM public.tm
FINAL

Query id: 56ba775f-cc56-48c2-8f82-102badc6acf1

┌─id───────────────────────────────────┬─secid────────────────────────────────┬─acc_id───────────────────────────────┬─ccatz─┬─tcred─┬─────am─┬───────────────────set_date─┬────────────────────created─┬────────────────────updated─┬─events_id────────────────────────────┬─events_transaction_id────────────────┬─events_status─┬─events_payment_snapshot─┬─────────────events_created─┬─vid──────────────────────────────────┬─vtid─────────────────────────────────┬─vstatus───┬─vamount─┬───────────────────vcreated─┬─vbilling_currency─┬────────────_version─┬─_sign─┐
│ 9cb52b2a-8ef2-4987-8856-c79a1b2c2f73 │ 9cb52b2a-8ef2-4987-8856-c79a1b2c2f72 │ 9cb52b2a-8ef2-4987-8856-c79a1b2c2f72 │ IDR   │ true  │ 200000 │ 2022-10-16 16:53:15.019570 │ 2022-10-16 16:53:15.019570 │ 2022-10-16 16:53:15.019570 │ b4763f4a-2e3f-41ae-9715-4ab113e2f53c │ 9cb52b2a-8ef2-4987-8856-c79a1b2c2f72 │ ᴺᵁᴸᴸ          │ ᴺᵁᴸᴸ                    │ 2022-10-16 16:53:15.019570 │ 9cb52b2a-8ef2-4987-8856-c79a1b2c2f71 │ 9cb52b2a-8ef2-4987-8856-c79a1b2c2f72 │ COMPLETED │  200000 │ 2022-10-16 16:53:15.019570 │ IDR               │ 1732118547261000002 │     0 │
└──────────────────────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┴───────┴───────┴────────┴────────────────────────────┴────────────────────────────┴────────────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┴───────────────┴─────────────────────────┴────────────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┴───────────┴─────────┴────────────────────────────┴───────────────────┴─────────────────────┴───────┘

1 row in set. Elapsed: 0.003 sec.