> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

> The ClickHouse JDBC Bridge allows ClickHouse to access data from any external data source for which a JDBC driver is available

# Connecting ClickHouse to external data sources with JDBC

export const Image = ({img, alt, size = "lg"}) => {
  const normalizedSize = ["sm", "md", "lg"].includes(size) ? size : "lg";
  return <div className={`ch-image-${normalizedSize}`}>
      <Frame>
        <img src={img} alt={alt} />
      </Frame>
    </div>;
};

<Warning>
  clickhouse-jdbc-bridge contains experimental codes and is no longer supported. It may contain reliability and security vulnerabilities. Use it at your own risk.
</Warning>

<Note>
  Using JDBC requires the ClickHouse JDBC bridge, so you will need to use `clickhouse-local` on a local machine to stream the data from your database to ClickHouse Cloud. Visit the [**Using clickhouse-local**](/docs/get-started/migrate/other-methods/clickhouse-local-etl) page in the **Migrate** section of the docs for details.
</Note>

**Overview:** The <a href="https://github.com/ClickHouse/clickhouse-jdbc-bridge" target="_blank">ClickHouse JDBC Bridge</a> in combination with the [jdbc table function](/docs/reference/functions/table-functions/jdbc) or the [JDBC table engine](/docs/reference/engines/table-engines/integrations/jdbc) allows ClickHouse to access data from any external data source for which a <a href="https://en.wikipedia.org/wiki/JDBC_driver" target="_blank">JDBC driver</a> is available:

<Image img="https://mintcdn.com/private-7c7dfe99/dZZG_-B0EzCG8L4V/images/integrations/data-ingestion/dbms/jdbc-01.webp?fit=max&auto=format&n=dZZG_-B0EzCG8L4V&q=85&s=8c8ce1ebfc70dc985a24ae2eee2fe80f" size="lg" alt="ClickHouse JDBC Bridge architecture diagram" background="white" width="4098" height="1024" data-path="images/integrations/data-ingestion/dbms/jdbc-01.webp" />

This is handy when there is no native built-in [integration engine](/docs/reference/engines/table-engines/integrations/index), table function, or external dictionary for the external data source available, but a JDBC driver for the data source exists.

You can use the ClickHouse JDBC Bridge for both reads and writes. And in parallel for multiple external data sources, e.g. you can run distributed queries on ClickHouse across multiple external and internal data sources in real time.

In this lesson we will show you how easy it is to install, configure, and run the ClickHouse JDBC Bridge in order to connect ClickHouse with an external data source. We will use MySQL as the external data source for this lesson.

Let's get started!

<Info>
  **Prerequisites**

  You have access to a machine that has:

  1. a Unix shell and internet access
  2. <a href="https://www.gnu.org/software/wget/" target="_blank">wget</a> installed
  3. a current version of **Java** (e.g. <a href="https://openjdk.java.net" target="_blank">OpenJDK</a> Version >= 17) installed
  4. a current version of **MySQL** (e.g. <a href="https://www.mysql.com" target="_blank">MySQL</a> Version >=8) installed and running
  5. a current version of **ClickHouse** [installed](/docs/get-started/setup/install) and running
</Info>

<h2 id="install-the-clickhouse-jdbc-bridge-locally">
  Install the ClickHouse JDBC Bridge locally
</h2>

The easiest way to use the ClickHouse JDBC Bridge is to install and run it on the same host where also ClickHouse is running:<Image img="https://mintcdn.com/private-7c7dfe99/dZZG_-B0EzCG8L4V/images/integrations/data-ingestion/dbms/jdbc-02.webp?fit=max&auto=format&n=dZZG_-B0EzCG8L4V&q=85&s=9364c1999d98f229274346065ceb63bb" size="lg" alt="ClickHouse JDBC Bridge locally deployment diagram" background="white" width="4098" height="1084" data-path="images/integrations/data-ingestion/dbms/jdbc-02.webp" />

Let's start by connecting to the Unix shell on the machine where ClickHouse is running and create a local folder where we will later install the ClickHouse JDBC Bridge into (feel free to name the folder anything you like and put it anywhere you like):

```bash theme={null}
mkdir ~/clickhouse-jdbc-bridge
```

Now we download the <a href="https://github.com/ClickHouse/clickhouse-jdbc-bridge/releases/" target="_blank">current version</a> of the ClickHouse JDBC Bridge into that folder:

```bash theme={null}
cd ~/clickhouse-jdbc-bridge
wget https://github.com/ClickHouse/clickhouse-jdbc-bridge/releases/download/v2.0.7/clickhouse-jdbc-bridge-2.0.7-shaded.jar
```

In order to be able to connect to MySQL we're creating a named data source:

```bash theme={null}
cd ~/clickhouse-jdbc-bridge
mkdir -p config/datasources
touch config/datasources/mysql8.json
```

You can now copy and paste the following configuration into the file `~/clickhouse-jdbc-bridge/config/datasources/mysql8.json`:

```json theme={null}
{
  "mysql8": {
  "driverUrls": [
    "https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar"
  ],
  "jdbcUrl": "jdbc:mysql://<host>:<port>",
  "username": "<username>",
  "password": "<password>"
  }
}
```

<Note>
  in the config file above

  * you're free to use any name you like for the datasource, we used `mysql8`
  * in the value for the `jdbcUrl` you need to replace `<host>`, and `<port>` with appropriate values according to your running MySQL instance, e.g. `"jdbc:mysql://localhost:3306"`
  * you need to replace `<username>` and `<password>` with your MySQL credentials, if you don't use a password, you can delete the `"password": "<password>"` line in the config file above
  * in the value for `driverUrls` we just specified a URL from which the <a href="https://repo1.maven.org/maven2/mysql/mysql-connector-java/" target="_blank">current version</a> of the MySQL JDBC driver can be downloaded. That's all we have to do, and the ClickHouse JDBC Bridge will automatically download that JDBC driver (into a OS specific directory).
</Note>

<br />

Now we're ready to start the ClickHouse JDBC Bridge:

```bash theme={null}
cd ~/clickhouse-jdbc-bridge
java -jar clickhouse-jdbc-bridge-2.0.7-shaded.jar
```

<Note>
  We started the ClickHouse JDBC Bridge in foreground mode. In order to stop the Bridge you can bring the Unix shell window from above in foreground and press `CTRL+C`.
</Note>

<h2 id="use-the-jdbc-connection-from-within-clickhouse">
  Use the JDBC connection from within ClickHouse
</h2>

ClickHouse can now access MySQL data by either using the [jdbc table function](/docs/reference/functions/table-functions/jdbc) or the [JDBC table engine](/docs/reference/engines/table-engines/integrations/jdbc).

The easiest way to execute the following examples is to copy and paste them into the [`clickhouse-client`](/docs/concepts/features/interfaces/cli) or into the [Play UI](/docs/concepts/features/interfaces/http).

* jdbc Table Function:

```sql theme={null}
SELECT * FROM jdbc('mysql8', 'mydatabase', 'mytable');
```

<Note>
  As the first parameter for the jdbc table function we're using the name of the named data source that we configured above.
</Note>

* JDBC Table Engine:

```sql theme={null}
CREATE TABLE mytable (
     <column> <column_type>,
     ...
)
ENGINE = JDBC('mysql8', 'mydatabase', 'mytable');

SELECT * FROM mytable;
```

<Note>
  As the first parameter for the jdbc engine clause we're using the name of the named data source that we configured above

  The schema of the ClickHouse JDBC engine table and schema of the connected MySQL table must be aligned, e.g. the column names and order must be the same, and the column data types must be compatible
</Note>

<h2 id="install-the-clickhouse-jdbc-bridge-externally">
  Install the ClickHouse JDBC Bridge externally
</h2>

For a distributed ClickHouse cluster (a cluster with more than one ClickHouse host) it makes sense to install and run the ClickHouse JDBC Bridge externally on its own host:

<Image img="https://mintcdn.com/private-7c7dfe99/dZZG_-B0EzCG8L4V/images/integrations/data-ingestion/dbms/jdbc-03.webp?fit=max&auto=format&n=dZZG_-B0EzCG8L4V&q=85&s=41da834be9976ffa52a09c078e9c4c76" size="lg" alt="ClickHouse JDBC Bridge external deployment diagram" background="white" width="4098" height="2356" data-path="images/integrations/data-ingestion/dbms/jdbc-03.webp" />

This has the advantage that each ClickHouse host can access the JDBC Bridge. Otherwise the JDBC Bridge would need to be installed locally for each ClickHouse instance that is supposed to access external data sources via the Bridge.

In order to install the ClickHouse JDBC Bridge externally, we do the following steps:

1. We install, configure and run the ClickHouse JDBC Bridge on a dedicated host by following the steps described in section 1 of this guide.

2. On each ClickHouse Host we add the following configuration block to the <a href="/docs/concepts/features/configuration/server-config/configuration-files" target="_blank">ClickHouse server configuration</a> (depending on your chosen configuration format, use either the XML or YAML version):

<Tabs>
  <Tab title="XML">
    ```xml theme={null}
    <jdbc_bridge>
       <host>JDBC-Bridge-Host</host>
       <port>9019</port>
    </jdbc_bridge>
    ```
  </Tab>

  <Tab title="YAML">
    ```yaml theme={null}
    jdbc_bridge:
        host: JDBC-Bridge-Host
        port: 9019
    ```
  </Tab>
</Tabs>

<Note>
  * you need to replace `JDBC-Bridge-Host` with the hostname or ip address of the dedicated ClickHouse JDBC Bridge host
  * we specified the default ClickHouse JDBC Bridge port `9019`, if you're using a different port for the JDBC Bridge then you must adapt the configuration above accordingly
</Note>

[//]: # "## 4. Additional Info"

[//]: #

[//]: # "TODO: "

[//]: # "- mention that for jdbc table function it is more performant (not two queries each time) to also specify the schema as a parameter"

[//]: #

[//]: # "- mention ad hoc query vs table query, saved query, named query"

[//]: #

[//]: # "- mention insert into "