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 Master. 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 our case, 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. Login under the default user name gpadmin. All the commands listed in the subsequent steps should be performed on Master:

    $ sudo su - gpadmin

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

    $ pxf cluster sync

    The result:

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

  5. Restart PXF:

    $ pxf cluster restart

    The result:

    Restarting PXF on master host, standby master 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 gptkh, pxf_fdw, tkh_fdw are available, use the psql meta-command \dx:

    \dx

    The output contains the gptkh, pxf_fdw, tkh_fdw extensions:

                                              List of installed extensions
       Name        | Version |   Schema   |                             Description
-------------------+---------+------------+---------------------------------------------------------------------
 arenadata_toolkit | 1.0     | public     | extension is used for manipulation of objects created by adb-bundle
 dblink            | 1.1     | public     | connect to other PostgreSQL databases from within a database
 diskquota         | 2.2     | public     | Disk Quota Main Program
 gptkh             | 0.14    | public     | gptkh
 kadb_fdw          | 0.16    | kadb       | Kafka-ADB foreign data wrapper
 plpgsql           | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis           | 2.5.4   | public     | PostGIS geometry, geography, and raster spatial types and functions
 pxf               | 2.0     | public     | Extension which allows to access unmanaged data
 pxf_fdw           | 1.0     | public     | PXF Foreign Data Wrapper for Greenplum
 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;

    The result:

        fdwname    | fdwowner | fdwhandler | fdwvalidator | fdwacl |                                         fdwoptions
---------------+----------+------------+--------------+--------+--------------------------------------------------------------------------------------------
 dblink_fdw    |       10 |          0 |        16480 |        |
 kadb_fdw      |       10 |      20308 |        20310 |        | {"mpp_execute=all segments",k_initial_offset=0,k_automatic_offsets=true,k_latency_ms=2000}
 jdbc_pxf_fdw  |       10 |      20377 |        20378 |        | {protocol=jdbc,"mpp_execute=all segments"}
 hdfs_pxf_fdw  |       10 |      20377 |        20378 |        | {protocol=hdfs,"mpp_execute=all segments"}
 hive_pxf_fdw  |       10 |      20377 |        20378 |        | {protocol=hive,"mpp_execute=all segments"}
 hbase_pxf_fdw |       10 |      20377 |        20378 |        | {protocol=hbase,"mpp_execute=all segments"}
 s3_pxf_fdw    |       10 |      20377 |        20378 |        | {protocol=s3,"mpp_execute=all segments"}
 gs_pxf_fdw    |       10 |      20377 |        20378 |        | {protocol=gs,"mpp_execute=all segments"}
 adl_pxf_fdw   |       10 |      20377 |        20378 |        | {protocol=adl,"mpp_execute=all segments"}
 wasbs_pxf_fdw |       10 |      20377 |        20378 |        | {protocol=wasbs,"mpp_execute=all segments"}
 file_pxf_fdw  |       10 |      20377 |        20378 |        | {protocol=file,"mpp_execute=all segments"}
 tkh_fdw       |       10 |      20392 |        20391 |        | {protocol=tkh_clickhouse,"mpp_execute=all segments",format=text}
(12 rows)
Found a mistake? Seleсt text and press Ctrl+Enter to report it