Configuring ClickHouse JDBC Bridge for Oracle Database Connectivity

ClickHouse can utilize the JDBC Bridge technology to directly access various databases, including Oracle. This approach enables seamless data integration between ClickHouse and external database systems.

Implementation Plan

The JDBC Bridge functions as a client-facing technology that operates as a server component, facilitating connections between ClickHouse and external databases.

Installation and Configuration

Required Components

ClickHouse JDBC Bridge

Download from: https://github.com/ClickHouse/clickhouse-jdbc-bridge
While you can theoretically download the source code and build it with Maven, pre-packaged versions are available, likely created using Maven's shade plugin.

Oracle JDBC Driver

Download the appropriate Oracle driver package (ojdbc8-12.2.0.1.jar) from https://mvnrepository.com and place it in the /data/clickhouse-jdbc-bridge/drivers directory. This version is compatible with Oracle 11g.

Directory Structure Setup

The JDBC Bridge has specific directory requirements. The relative positions must be maintained, though the parent directory location is flexible:


└── clickhouse-jdbc-bridge
        ├── clickhouse-jdbc-bridge-2.1.0-shaded.jar
        ├── config
        │   └── datasources
        │       └── oracle.json
        └── drivers
            └── ojdbc8-12.2.0.1.jar

Create the necessary directories with appropriate permissions:


sudo mkdir -p /opt/clickhouse/clickhouse-jdbc-bridge
sudo chown -R bigdata:bigdata /opt/clickhouse/clickhouse-jdbc-bridge
cd /opt/clickhouse/clickhouse-jdbc-bridge
mkdir -p config/datasources
mkdir drivers

Alternatively, execute all commands in one line:


sudo mkdir -p /opt/clickhouse/clickhouse-jdbc-bridge; sudo mkdir -p /opt/clickhouse/clickhouse-jdbc-bridge/config/datasources; sudo mkdir -p /opt/clickhouse/clickhouse-jdbc-bridge/drivers; sudo chown -R bigdata:bigdata /opt/clickhouse/clickhouse-jdbc-bridge

The final directory structure should appear as follows:


/opt
└── clickhouse
    └── clickhouse-jdbc-bridge
        ├── clickhouse-jdbc-bridge-2.1.0-shaded.jar
        ├── config
        │   └── datasources
        │       └── oracle.json
        └── drivers
            └── ojdbc8-12.2.0.1.jar

Configuration File Setup

Navigate to the datasources directory:


cd /opt/clickhouse/clickhouse-jdbc-bridge/config/datasources

Important Notes:

The oracle.json filename and its root node name must match
In the jdbcUrl, use "/" for databases and ":" for GIDs. Incorrect usage will result in errors
The connectionTestQuery must be an empty string to avoid potential issues, as Oracle may not support this test
Colons in configuration should not be followed by spaces
The connectTimeout and socketTimeout parameters cannot be used, as they will cause connection failures despite no startup errors
The serverTimezone parameter is not supported; instead, use "timezone":"GTM+8"

Create the oracle.json configuration file with the following content:


{
  "oracle": {
      "driverUrls": [
          "/opt/clickhouse/clickhouse-jdbc-bridge/drivers/ojdbc8-12.2.0.1.jar"
       ],
      "driverClassName": "oracle.jdbc.driver.OracleDriver",
      "jdbcUrl": "jdbc:oracle:thin:@xx.xxx.0.70:1521/ods",
      "username": "your_username",
      "password": "your_password",
      "connectionTestQuery": "",
      "timezone":"GTM+8"
   }
}

Start the JDBC Bridge service:


cd /opt/clickhouse/clickhouse-jdbc-bridge/
nohup java -jar clickhouse-jdbc-bridge-2.1.0-shaded.jar &
tail -f nohup.out

JDBC Configuration Details

When configuring via JDBC URL:


jdbc:mysql://59.110.137.112:3306/answer?useUnicode=true&characterEncoding=utf8
&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
&nullCatalogMeansCurrent=true

When configuring through files or code:

Set the timezone by adding the serverTimezone parameter (e.g., serverTimezone=GMT%2B8, where %2B represents the + symbol)
GMT+8 refers to GMT+8 (East 8th Zone, Beijing time)
Asia/Shanghai represents Shanghai time, which is also in the GMT+8 timezone

ClickHouse Server Configuration

Edit the config.xml file in the /etc/clickhouse-server directory. Locate the following code, remove any comments, and modify the host to match the IP address where the clickhouse-jdbc-bridge-2.1.0-shaded.jar is executed:


<jdbc_bridge>
   <host>localhost</host>
   <port>9019</port>
</jdbc_bridge>

Usage

Starting the Service

Launch the JDBC Bridge:


nohup java -jar clickhouse-jdbc-bridge-2.1.0-shaded.jar &
tail -f nohup.out

Restarting ClickHouse

After configuration changes, restart the ClickHouse server:


systemctl restart clickhouse-server
systemctl restart clickhouse-server2

Verification

Check the configured external data sources:


select * from jdbc('','show datasources')

Test the bridge connection with a sample Oracle query:


select * from jdbc('oracle','SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left FROM DUAL')

Tags: ClickHouse jdbc-bridge Oracle database-connectivity data-integration

Posted on Sat, 22 Aug 2026 16:01:27 +0000 by cpace1983

Freaks City