JDBC Bridge

Elena Dvoryadkina

Contents

Installation
Configuration
Usage
- jdbc table function
- JDBC table engine
Example

ClickHouse JDBC Bridge allows ADQM to access data from external data sources that support the JDBC interface.

Installation

ClickHouse JDBC Bridge in ADQM is a component of the ADQMDB service that you can install in the same way as other components. The Add components article explains how to deploy ADQM components on cluster hosts from the ADCM interface.

IMPORTANT

If you install the ClickHouse JDBC Bridge component, add it to all hosts of the cluster where the Clickhouse Server component is installed.

Configuration

To be able to connect ADQM to an external data source via JDBC Bridge, define this data source under a certain name in the datasource.json field located on the ADQMDB service’s configuration page (in the JDBC Bridge configuration section that is visible when the Show advanced option is activated).

By default, the JDBC Bridge configuration contains definitions of two data sources (postgres13 for PostgreSQL and ch-server for ClickHouse):

[
    {
        "$schema": "./datasource.jschema",
        "postgres13": {
            "converter": {
                "mappings": [
                    {
                        "nativeType": "bool",
                        "toType": "String"
                    }
                ]
            },
            "driverUrls": [
                "drivers/postgresql/postgresql-jdbc.jar"
            ],
            "driverClassName": "org.postgresql.Driver",
            "jdbcUrl": "jdbc:postgresql://localhost/test",
            "username": "",
            "password": "",
            "initializationFailTimeout": 0,
            "minimumIdle": 0,
            "maximumPoolSize": 10
        }
    },
    {
        "$schema": "./datasource.jschema",
        "ch-server": {
            "driverUrls": [
                "drivers/clickhouse/clickhouse-jdbc.jar"
            ],
            "driverClassName": "ru.yandex.clickhouse.ClickHouseDriver",
            "jdbcUrl": "jdbc:clickhouse://ch-server:8123/system",
            "username": "default",
            "password": "",
            "initializationFailTimeout": 0,
            "minimumIdle": 0,
            "maximumPoolSize": 10
        }
    }
]

You can modify these configurations or use them as templates to describe new data sources, following the JSON format. For example, for the PostgreSQL data source configuration above, you need to set the real username and password values, and also change the jdbcUrl key value — replace localhost with the host on which PostgreSQL is installed and type a name of the required database instead of test.

Below is a general data source configuration template for convenience — you can copy and paste it into the datasource.json field, replacing placeholders with real data that corresponds to your external DBMS:

{
  "<datasource_name>": {
  "driverUrls": [
    "<datasource_jdbc_driver_url>"
  ],
  "jdbcUrl": "jdbc:<driver_name>://<host>:<port>/<database>",
  "username": "<username>",
  "password": "<password>"
  }
}

where:

<datasource_name> — arbitrary name of a data source (you can pass this name to the jdbc table function or to the JDBC table engine to specify the data source);
<datasource_jdbc_driver_url> — URL from which ADQM can download a JDBC driver for the specified DBMS;
<driver_name> — JDBC driver name;
<host>:<port> — host and port where the DBMS is installed;
<database> — name of a database in the DBMS (this element is optional — if you skip it, specify a database explicitly each time you connect to the external data source from ADQM);
<username> and <password> — credentials for access to the DBMS.

IMPORTANT

After you have set necessary data source configurations, do not forget to click Save and execute the Reconfig and restart action for the ADQMDB service to save changes and restart the service.

The following query returns a list of named data sources configured for JDBC Bridge:

SELECT name FROM jdbc('', 'show datasources');

The output can look like:

┌─name────────┐
│ ch-server   │
│ self_server │
│ postgres13  │
│ script      │
└─────────────┘

Usage

ADQM can interact with an external DBMS via the JDBC interface through the jdbc table function or JDBC table engine.

jdbc table function

The jdbc table function returns a table connected to an external data source via a JDBC driver. The basic syntax of a query that calls the jdbc function can be written as follows:

SELECT ... FROM jdbc('<datasource>', ['<external_database_name | schema>'], '<external_table_name | query>');

where:

<datasource> — external data source name set in the JDBC Bridge configuration;
<external_database_name | schema> — name of a database in the external data source or table schema (this parameter is optional);
<external_table_name | query> — name of a table in the external database or query that selects data from the external table (the query should be in the native format of the specified external data source).

JDBC table engine

ADQM also provides the JDBC table engine for integration with external data sources via the JDBC interface. The general query syntax for creating a JDBC table is:

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<jdbc_table_name> (<column_name> <column_type>, ...)
ENGINE = JDBC('<datasource>', '<external_database_name>', '<external_table_name | query>');

where:

<column_name> <column_type>, … — column definitions for a table to be created (the schema of the JDBC table in ADQM should be consistent with the schema of the connected <external_table_name> table or with a table returned by the <query> query — i.e. names and order of columns should be the same, and column data types should be compatible);
<datasource> — external data source name specified in the JDBC Bridge configuration;
<external_database_name> — name of a database in the external data source (leave this parameter empty — '', if the data source configuration already includes the database name or you explicitly specify database for the source table within the query passed as the third parameter of the JDBC engine);
<external_table_name | query> — name of a table in the external_db_name database or query that selects data from the external table (for example, SELECT * FROM test_table WHERE column_1 = 1).

Example

On the MariaDB server, create a table in the mariadb_db database :

CREATE TABLE mariadb_table (id INT PRIMARY KEY, date DATETIME);

Insert test data into the table:

INSERT INTO mariadb_table VALUES (1, "2023-09-25 11:12:13"), (2, "2023-09-26 12:13:14"), (3, "2023-09-27 13:14:15");

In ADCM, add the following definition of the MariaDB data source to the datasource.json parameter of the JDBC Bridge configuration (replace <host>:<port> with the MariaDB server address, and <user_name> and <user_password> with a name and password of a user that accesses the MariaDB database):

{
    "$schema": "./datasource.jschema",
    "mariadb10": {
        "driverUrls": [
        "https://repo1.maven.org/maven2/org/mariadb/jdbc/mariadb-java-client/2.7.4/mariadb-java-client-2.7.4.jar"
        ],
        "driverClassName": "org.mariadb.jdbc.Driver",
        "jdbcUrl": "jdbc:mariadb://<host>:<port>",
        "dataSource": {
        "user": "<user_name>",
        "password": "<user_password>"
        },
        "initializationFailTimeout": 0,
        "minimumIdle": 0,
        "maximumPoolSize": 10
    }
}

Click Save and execute the Reconfig and restart action for the ADQMDB service to save information on the MariaDB data source in the JDBC Bridge configuration and restart the service.

Data source configuration for JDBC Bridge

Connect ADQM to the mariadb_table table of the external mariadb_db database through the jdbc table function or the JDBC table engine — in both cases, pass the name of the data source configured on the previous step as the first parameter.

jdbc table function:

SELECT * FROM jdbc('mariadb10', 'mariadb_db', 'mariadb_table');

┌─id─┬────────────────date─┐
│  1 │ 2023-09-25 11:12:13 │
│  2 │ 2023-09-26 12:13:14 │
│  3 │ 2023-09-27 13:14:15 │
└────┴─────────────────────┘

SELECT * FROM jdbc('mariadb10', 'SELECT * FROM mariadb_db.mariadb_table WHERE id >= 2');

┌─id─┬────────────────date─┐
│  2 │ 2023-09-26 12:13:14 │
│  3 │ 2023-09-27 13:14:15 │
└────┴─────────────────────┘

SELECT * FROM jdbc('mariadb10', 'test_dates DateTime', 'SELECT date as test_dates FROM mariadb_db.mariadb_table');

┌──────────test_dates─┐
│ 2023-09-25 11:12:13 │
│ 2023-09-26 12:13:14 │
│ 2023-09-27 13:14:15 │
└─────────────────────┘

JDBC table engine:

CREATE TABLE jdbc_table (id Int32, date DateTime) ENGINE JDBC('mariadb10', 'mariadb_db', 'mariadb_table');

SELECT * FROM jdbc_table;

┌─id─┬────────────────date─┐
│  1 │ 2023-09-25 11:12:13 │
│  2 │ 2023-09-26 12:13:14 │
│  3 │ 2023-09-27 13:14:15 │
└────┴─────────────────────┘

CREATE TABLE jdbc_table_1 (test_IDs Int32, test_dates DateTime)
ENGINE JDBC('mariadb10', '', 'SELECT id as test_IDs, date as test_dates FROM mariadb_db.mariadb_table');

SELECT * FROM jdbc_table_1;

┌─test_IDs─┬──────────test_dates─┐
│        1 │ 2023-09-25 11:12:13 │
│        2 │ 2023-09-26 12:13:14 │
│        3 │ 2023-09-27 13:14:15 │
└──────────┴─────────────────────┘

Make sure that JDBC Bridge allows also writing data from ADQM to an external database. To do this, add a new data record to the ADQM table:

INSERT INTO jdbc_table VALUES (5, '2023-09-28 14:15:16');

Then check the table content on the MariaDB server:

SELECT * FROM mariadb_table;

+----+---------------------+
| id | date                |
+----+---------------------+
|  1 | 2023-09-25 11:12:13 |
|  2 | 2023-09-26 12:13:14 |
|  3 | 2023-09-27 13:14:15 |
|  5 | 2023-09-28 14:15:16 |
+----+---------------------+

Found a mistake? Seleсt text and press Ctrl+Enter to report it