ADB ClickHouse Connector installation

Daria Barysheva

To install ADB ClickHouse Connector, follow the steps:

  1. Make sure that PXF and ADB ClickHouse Connector services are installed. For information on how to add services to ADB clusters, see Add services.

  2. Add the new custom profile Tkh_clickhouse:text to the PXF configuration file /var/lib/pxf/conf/pxf-profiles.xml on coordinator. To edit the file, you can use the vi or vim command:

    $ sudo vi /var/lib/pxf/conf/pxf-profiles.xml

    The content of the changed file can look as follows. In the given example, the file does not contain other profiles:

    <profiles>
    <profile>
        <name>Tkh_clickhouse:text</name>
        <description>Clickhouse</description>
        <plugins>
            <accessor>io.arenadata.tkh.pxf.TkhFdwAccessor</accessor>
            <resolver>io.arenadata.tkh.pxf.TkhResolver</resolver>
        </plugins>
    </profile>
</profiles>
    NOTE

    The directory, where PXF configuration files are located, is defined by the PXF_BASE environment variable. The default value is /var/lib/pxf. The path may differ in your environment.

  3. Log in under the default user gpadmin. All the commands listed in the subsequent steps should be performed on coordinator:

    $ sudo su - gpadmin

  4. Synchronize the PXF configuration on all hosts in the ADB cluster:

    $ pxf cluster sync

    Result:

    Syncing PXF configuration files from coordinator host to standby coordinator host and 2 segment hosts...
PXF configs synced successfully on 3 out of 3 hosts

  5. Restart PXF:

    $ pxf cluster restart

    Result:

    Restarting PXF on coordinator host, standby coordinator host, and 2 segment hosts...
PXF restarted successfully on 4 out of 4 hosts

  6. Connect to the database via psql (or any other client program):

    $ psql adb

  7. To check that the new extensions pxf_fdw and tkh_fdw are available, use the psql meta-command \dx:

    \dx

    The output contains the pxf_fdw and tkh_fdw extensions:

                                              List of installed extensions
       Name        | Version |   Schema   |                             Description
-------------------+---------+------------+---------------------------------------------------------------------
 adb_fdw           | 1.0     | public     | foreign-data wrapper for remote ADB servers
 arenadata_toolkit | 1.0     | public     | extension is used for manipulation of objects created by adb-bundle
 dblink            | 1.2     | public     | connect to other PostgreSQL databases from within a database
 gp_exttable_fdw   | 1.0     | pg_catalog | External Table Foreign Data Wrapper for Greengage
 gp_toolkit        | 1.8     | gp_toolkit | various GPDB administrative views/functions
 kadb_fdw          | 0.16    | kadb       | Kafka-ADB foreign data wrapper
 plpgsql           | 1.0     | pg_catalog | PL/pgSQL procedural language
 pxf               | 2.1     | public     | Extension which allows to access unmanaged data
 pxf_fdw           | 1.0     | public     | PXF Foreign Data Wrapper for Greengage
 tkh_fdw           | 1.0     | public     | ClickHouse Foreign Data Wrapper for Greenplum
(10 rows)

  8. You can also check that the new foreign data wrapper tkh_fdw is available using the following SQL query against the pg_catalog.pg_foreign_data_wrapper system table:

    SELECT * FROM pg_catalog.pg_foreign_data_wrapper;

    Result:

      oid  |     fdwname     | fdwowner | fdwhandler | fdwvalidator | fdwacl |                                         fdwoptions
-------+-----------------+----------+------------+--------------+--------+--------------------------------------------------------------------------------------------
 13702 | gp_exttable_fdw |       10 |      13700 |        13701 |        |
 17124 | dblink_fdw      |       10 |          0 |        17123 |        |
 17175 | adb_fdw         |       10 |      17173 |        17174 |        |
 17187 | jdbc_pxf_fdw    |       10 |      17185 |        17186 |        | {protocol=jdbc,"mpp_execute=all segments"}
 17188 | hdfs_pxf_fdw    |       10 |      17185 |        17186 |        | {protocol=hdfs,"mpp_execute=all segments"}
 17189 | hive_pxf_fdw    |       10 |      17185 |        17186 |        | {protocol=hive,"mpp_execute=all segments"}
 17190 | hbase_pxf_fdw   |       10 |      17185 |        17186 |        | {protocol=hbase,"mpp_execute=all segments"}
 17191 | s3_pxf_fdw      |       10 |      17185 |        17186 |        | {protocol=s3,"mpp_execute=all segments"}
 17192 | gs_pxf_fdw      |       10 |      17185 |        17186 |        | {protocol=gs,"mpp_execute=all segments"}
 17193 | adl_pxf_fdw     |       10 |      17185 |        17186 |        | {protocol=adl,"mpp_execute=all segments"}
 17194 | wasbs_pxf_fdw   |       10 |      17185 |        17186 |        | {protocol=wasbs,"mpp_execute=all segments"}
 17195 | file_pxf_fdw    |       10 |      17185 |        17186 |        | {protocol=file,"mpp_execute=all segments"}
 17201 | tkh_fdw         |       10 |      17200 |        17199 |        | {protocol=tkh_clickhouse,"mpp_execute=all segments",format=text}
 17210 | kadb_fdw        |       10 |      17209 |        17211 |        | {"mpp_execute=all segments",k_initial_offset=0,k_automatic_offsets=true,k_latency_ms=2000}
(14 rows)
Found a mistake? Seleсt text and press Ctrl+Enter to report it