Tkhemali Connector 1.X usage examples

Daria Barysheva

This article describes how to transfer data from ADB to a distributed table in ADQM via Tkhemali Connector 1.X. The following prerequisites are met:

  • The ADQM cluster is installed according to the Online installation guide on the following hosts: dev-adqm-01, dev-adqm-02, dev-adqm-03, dev-adqm-04.

  • The default database exists in ADQM.

  • The default user exists in ADQM.

  • The default_cluster cluster is defined in the remote_servers section of the config.xml file in ADQM. For more information, see Typical cluster in the ADQM documentation.

  • All ADB nodes have access to the 8123 HTTP port on all ADQM nodes through which you plan to load data.

  1. Connect to the default database of the ADQM cluster under the default user (for example, via clickhouse-client). Create the following tables in the given order.

    • The test_local local table:

      CREATE TABLE test_local ON CLUSTER default_cluster
(
    id Int32,
    value_string String
)
ENGINE = ReplicatedMergeTree('/clickhouse/test/shard-{shard}', 'replica-{replica}')
ORDER BY id;

    • The test_distr distributed table:

      CREATE TABLE test_distr ON CLUSTER default_cluster AS default.test_local
ENGINE = Distributed(default_cluster, default, test_local, rand());

  2. Connect to the adb database of the ADB cluster under the gpadmin user (for example, via psql). Create a table for test data with the structure identical to ADQM tables:

    CREATE TABLE test (id INT, value_string TEXT)
WITH (appendoptimized=true, orientation=column, compresstype=zstd, compresslevel=1)
DISTRIBUTED BY (id);

  3. Add some data to the test table:

    INSERT INTO test
SELECT gen, 'Some text #' || gen::text
FROM generate_series(1, 50) gen;
    Content of the test table
     id | value_string
----+---------------
  1 | Some text #1
  2 | Some text #2
  3 | Some text #3
  4 | Some text #4
  5 | Some text #5
  6 | Some text #6
  7 | Some text #7
  8 | Some text #8
  9 | Some text #9
 10 | Some text #10
 11 | Some text #11
 12 | Some text #12
 13 | Some text #13
 14 | Some text #14
 15 | Some text #15
 16 | Some text #16
 17 | Some text #17
 18 | Some text #18
 19 | Some text #19
 20 | Some text #20
 21 | Some text #21
 22 | Some text #22
 23 | Some text #23
 24 | Some text #24
 25 | Some text #25
 26 | Some text #26
 27 | Some text #27
 28 | Some text #28
 29 | Some text #29
 30 | Some text #30
 31 | Some text #31
 32 | Some text #32
 33 | Some text #33
 34 | Some text #34
 35 | Some text #35
 36 | Some text #36
 37 | Some text #37
 38 | Some text #38
 39 | Some text #39
 40 | Some text #40
 41 | Some text #41
 42 | Some text #42
 43 | Some text #43
 44 | Some text #44
 45 | Some text #45
 46 | Some text #46
 47 | Some text #47
 48 | Some text #48
 49 | Some text #49
 50 | Some text #50
(50 rows)

  4. In ADB, create an external table with the structure identical to the test table:

    CREATE WRITABLE EXTERNAL TABLE test_ext (LIKE test)
LOCATION ('pxf://default.test_distr_tmp_$?PROFILE=TKH&URL=dev-adqm-01:8123,dev-adqm-02:8123,dev-adqm-03:8123,dev-adqm-04:8123')
FORMAT 'TEXT'
ENCODING 'UTF8';
    IMPORTANT

    Since staging tables are used in the example, the _tmp_$ postfix is added to the name of the ADQM target table in the LOCATION clause.

  5. Load data from the test ADB table to the test_distr ADQM table using the txn function. Note that you need to explicitly specify a transaction via BEGIN and COMMIT:

    BEGIN;
SELECT txn('INSERT INTO test_ext SELECT * FROM test;');
COMMIT;

    The result:

    BEGIN
 txn
-----

(1 row)

COMMIT

  6. In ADQM, check that data from ADB has been successfully transferred:

    SELECT * FROM test_distr;
    Result
    ┌─id─┬─value_string──┐
│  5 │ Some text #5  │
│  8 │ Some text #8  │
│ 16 │ Some text #16 │
│ 17 │ Some text #17 │
│ 18 │ Some text #18 │
│ 20 │ Some text #20 │
│ 21 │ Some text #21 │
│ 27 │ Some text #27 │
│ 32 │ Some text #32 │
│ 34 │ Some text #34 │
│ 35 │ Some text #35 │
│ 41 │ Some text #41 │
│ 42 │ Some text #42 │
└────┴───────────────┘
┌─id─┬─value_string──┐
│  1 │ Some text #1  │
│  4 │ Some text #4  │
│  7 │ Some text #7  │
│ 11 │ Some text #11 │
│ 19 │ Some text #19 │
│ 26 │ Some text #26 │
│ 28 │ Some text #28 │
│ 31 │ Some text #31 │
│ 36 │ Some text #36 │
│ 45 │ Some text #45 │
│ 46 │ Some text #46 │
└────┴───────────────┘
┌─id─┬─value_string──┐
│  2 │ Some text #2  │
│  3 │ Some text #3  │
│  6 │ Some text #6  │
│  9 │ Some text #9  │
│ 10 │ Some text #10 │
│ 12 │ Some text #12 │
│ 13 │ Some text #13 │
│ 14 │ Some text #14 │
│ 15 │ Some text #15 │
│ 22 │ Some text #22 │
│ 23 │ Some text #23 │
│ 24 │ Some text #24 │
│ 25 │ Some text #25 │
│ 29 │ Some text #29 │
│ 30 │ Some text #30 │
│ 33 │ Some text #33 │
│ 37 │ Some text #37 │
│ 38 │ Some text #38 │
│ 39 │ Some text #39 │
│ 40 │ Some text #40 │
│ 43 │ Some text #43 │
│ 44 │ Some text #44 │
│ 47 │ Some text #47 │
│ 48 │ Some text #48 │
│ 49 │ Some text #49 │
│ 50 │ Some text #50 │
└────┴───────────────┘
50 rows in set. Elapsed: 0.004 sec.

Without staging tables

  1. Follow steps 1-3 that are described in the previous section (if you did not complete it before).

  2. Delete data from the test_local table in ADQM:

    ALTER TABLE default.test_local ON CLUSTER default_cluster DELETE WHERE true;

  3. In ADB, create an external table with a structure identical to the test table:

    CREATE WRITABLE EXTERNAL TABLE test_ext2 (LIKE test)
LOCATION ('pxf://default.test_distr?PROFILE=TKH&URL=dev-adqm-01:8123,dev-adqm-02:8123,dev-adqm-03:8123,dev-adqm-04:8123')
FORMAT 'TEXT'
ENCODING 'UTF8';
    IMPORTANT

    Since staging tables are not used in the example, the target table name is specified without any postfixes in the LOCATION clause.

  4. Load data from the test ADB table to the test_distr ADQM table using the INSERT query against the test_ext2 external table:

    INSERT INTO test_ext2 SELECT * FROM test;

    The result:

    INSERT 0 50

  5. In ADQM, check that data from ADB has been successfully transferred:

    SELECT * FROM test_distr;
    Result
    ┌─id─┬─value_string──┐
│  1 │ Some text #1  │
│  2 │ Some text #2  │
│  3 │ Some text #3  │
│  8 │ Some text #8  │
│ 12 │ Some text #12 │
│ 13 │ Some text #13 │
│ 14 │ Some text #14 │
│ 17 │ Some text #17 │
│ 18 │ Some text #18 │
│ 23 │ Some text #23 │
│ 25 │ Some text #25 │
│ 26 │ Some text #26 │
│ 29 │ Some text #29 │
│ 31 │ Some text #31 │
│ 35 │ Some text #35 │
│ 36 │ Some text #36 │
│ 38 │ Some text #38 │
│ 39 │ Some text #39 │
│ 42 │ Some text #42 │
│ 43 │ Some text #43 │
│ 46 │ Some text #46 │
│ 48 │ Some text #48 │
│ 49 │ Some text #49 │
│ 50 │ Some text #50 │
└────┴───────────────┘
┌─id─┬─value_string─┐
│  5 │ Some text #5 │
└────┴──────────────┘
┌─id─┬─value_string──┐
│ 32 │ Some text #32 │
└────┴───────────────┘
┌─id─┬─value_string──┐
│  9 │ Some text #9  │
│ 10 │ Some text #10 │
│ 16 │ Some text #16 │
│ 19 │ Some text #19 │
│ 21 │ Some text #21 │
│ 22 │ Some text #22 │
│ 24 │ Some text #24 │
│ 27 │ Some text #27 │
│ 37 │ Some text #37 │
│ 40 │ Some text #40 │
│ 45 │ Some text #45 │
│ 47 │ Some text #47 │
└────┴───────────────┘
┌─id─┬─value_string──┐
│  4 │ Some text #4  │
│  6 │ Some text #6  │
│  7 │ Some text #7  │
│ 11 │ Some text #11 │
│ 15 │ Some text #15 │
│ 20 │ Some text #20 │
│ 28 │ Some text #28 │
│ 30 │ Some text #30 │
│ 33 │ Some text #33 │
│ 34 │ Some text #34 │
│ 41 │ Some text #41 │
│ 44 │ Some text #44 │
└────┴───────────────┘

50 rows in set. Elapsed: 0.004 sec.
Found a mistake? Seleсt text and press Ctrl+Enter to report it