Yandex Managed Service for ClickHouse® integration with an external Oracle database via ClickHouse® JDBC Bridge

With ClickHouse® JDBC Bridge, you can:

• Query a table in an external Oracle database using the JDBC table function.
• Create ClickHouse® tables linked to corresponding tables in an external Oracle database, using the JDBC table engine.

Get your cloud readyGet your cloud ready

Sign up for Yandex Cloud and create a billing account:

1. Navigate to the management console and log in to Yandex Cloud or create a new account.
2. On the Yandex Cloud Billing page, make sure you have a billing account linked and it has the ACTIVE or TRIAL_ACTIVE status. If you do not have a billing account, create one and link a cloud to it.

If you have an active billing account, you can create or select a folder for your infrastructure on the cloud page.

Learn more about clouds and folders here.

Required paid resourcesRequired paid resources

The solution support costs include:

• Fee for a Managed Service for ClickHouse® cluster: using computing resources allocated to hosts (including ZooKeeper hosts) and disk space (see Managed Service for ClickHouse® pricing).
• NAT gateway fee if public access is not enabled for cluster hosts (see Virtual Private Cloud pricing).
• Fee for using public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).

Set up the infrastructureSet up the infrastructure

1. Create a security group and configure it.

   Add the following egress rule:

   • Port range: 0-65535.
   • Protocol: TCP.
   • Source: CIDR.
   • CIDR blocks: 0.0.0.0/0.

   This rule allows all outgoing traffic, enabling ClickHouse® JDBC Bridge to connect to external databases such as Oracle.

2. Create a Managed Service for ClickHouse® cluster.

   When creating a cluster, specify the security group you prepared earlier.

   Under DBMS settings, click Settings and add the jdbcBridge option with the following configuration:

   • Host: Oracle database server’s IP address.
   • Port: 9019.

3. If you do not plan to enable public access to your Managed Service for ClickHouse® cluster, create a NAT gateway for the subnet where your cluster will reside.

Prepare the external Oracle databasePrepare the external Oracle database

1. Make sure the host where your Oracle database is installed allows connection on ports 9019 and 1521.

2. Connect to your Oracle host and download the ojdbc8 JDBC driver into the /opt/drivers directory:

   sudo mkdir -p /opt/drivers && \
   curl -s https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc8/ | grep -oP '(?<=href=")[0-9][^/]+(?=/")' | sort -V | tail -n1 | xargs -I{} sudo curl -o /opt/drivers/ojdbc8-{}.jar https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc8/{}/ojdbc8-{}.jar
   

3. Install Docker Engine.

4. Run ClickHouse® JDBC Bridge:

   docker run -d --name jdbc_bridge --network host -v /opt/drivers:/app/drivers clickhouse/jdbc-bridge
   

   If your ClickHouse® JDBC Bridge container is already running, restart it to load the new drivers:

   docker container restart jdbc_bridge
   

5. Connect to the external Oracle database as SYSDBA. For a local connection, you can use SQL Plus:

   sqlplus / as sysdba
   

6. Switch to the target Pluggable Database (PDB), e.g., PDB1:

   ALTER SESSION SET CONTAINER = PDB1;
   

7. Create the JDBC_USER account for Managed Service for ClickHouse® cluster access:

   CREATE USER JDBC_USER IDENTIFIED BY <user_password>
   DEFAULT TABLESPACE USERS
   TEMPORARY TABLESPACE TEMP
   QUOTA UNLIMITED ON USERS;
   

   Where <user_password> is your password for the JDBC_USER account.

   Tip

   To connect to different PDBs, create a common user with a username prefixed by C##, e.g., C##JDBC_USER.

8. Grant permissions required for ClickHouse® JDBC Bridge to run:

   GRANT CONNECT, RESOURCE TO JDBC_USER;
   

9. Connect using the account you created:

   CONNECT JDBC_USER/<user_password>@<Oracle_DB_host>/PDB1;
   

   Where:

   • <password>: JDBC_USER password.
   • <Oracle_DB_host>: Oracle database server’s IP address.

10. Create a test table named CUSTOMERS:

    CREATE TABLE CUSTOMERS (
      NAME       VARCHAR2(100),
      EMAIL      VARCHAR2(100)
    );
    

11. Add test data:

    INSERT INTO CUSTOMERS (NAME, EMAIL) VALUES ('Angela Smith', 'angela@example.ru');
    INSERT INTO CUSTOMERS (NAME, EMAIL) VALUES ('Bob Johnson', 'bob@example.ru');
    INSERT INTO CUSTOMERS (NAME, EMAIL) VALUES ('Charlie Brown', 'charlie@example.ru');
    
    COMMIT;
    

12. Verify the data has been added successfully:

    SELECT * FROM CUSTOMERS;
    

Query the data using the JDBC table functionQuery the data using the JDBC table function

1. Connect to the Managed Service for ClickHouse® cluster.

2. Send a query to the external Oracle database using the JDBC table function:

   SELECT * FROM jdbc('jdbc:oracle:thin:JDBC_USER/<user_password>@<Oracle_DB_host>:1521/PDB1', 'SELECT * FROM CUSTOMERS');
   

   Where:

   • <password>: JDBC_USER password.
   • <Oracle_DB_host>: Oracle database server’s IP address.

   If successful, the query will return data from the external Oracle database.

Create a table using the JDBC table engineCreate a table using the JDBC table engine

With the JDBC table engine, you can:

• Query data using the SELECT statement.

• Add new rows using the INSERT INTO statement, with the following limitations:

  • The schema of the table in the Managed Service for ClickHouse® cluster database must exactly match the source table schema.
  • The INSERT INTO operation is the only available method for writing data. Modifying existing data with statements such as UPDATE, DELETE, or ALTER is not supported.
  • The fields with auto-generated values, e.g., DEFAULT, GENERATED BY, SYSDATE, etc., will not be populated if you omit them during insertion.

To use the JDBC table engine:

1. Connect to the Managed Service for ClickHouse® cluster.

2. Create a table with the JDBC table engine that links to an external Oracle table.

   CREATE TABLE oracle_customers
   ENGINE = JDBC(
     'jdbc:oracle:thin:JDBC_USER/<user_password>@<Oracle_DB_host>:1521/XEPDB1',
     'JDBC_USER',
     'CUSTOMERS'
   )
   AS SELECT *
   FROM jdbc(
     'jdbc:oracle:thin:JDBC_USER/<user_password>@<Oracle_DB_host>:1521/XEPDB1',
     'JDBC_USER',
     'SELECT * FROM CUSTOMERS'
   )
   LIMIT 0;
   

   When creating the target table, you can specify only a subset of the source table’s fields. However, in this case you will not be able to use the INSERT INTO statement to add new rows.

3. Check the result:

   SELECT * FROM oracle_customers;
   

4. Insert a new row:

   INSERT INTO oracle_customers (NAME, EMAIL)
     VALUES ('Alice Wonderland', 'alice@example.ru');
   

   ClickHouse® JDBC Bridge opens and closes a transaction automatically. You do not need to execute the COMMIT statement.

5. Check the result again and compare it with the previous output:

   SELECT * FROM oracle_customers;
   

   If the insert operation is successful, the output will include the new row.

Delete the resources you createdDelete the resources you created

Some resources are not free of charge. Delete the resources you no longer need to avoid paying for them:

• Delete the Managed Service for ClickHouse® cluster.
• Delete the NAT gateway.

ClickHouse® is a registered trademark of ClickHouse, Inc.