JDBC

Once connected, Doris will ingest metadata of databases and tables from the external data sources in order to enable quick access to external data.

1. Supported datas sources include MySQL, PostgreSQL, Oracle, SQLServer, Clickhouse and Doris.

Create Catalog

SinceVersion 1.2.0

1. MySQL

SinceVersion 1.2.2

1. PostgreSQL

    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:postgresql://127.0.0.1:5449/demo",
    "driver_url" = "postgresql-42.5.1.jar",
    "driver_class" = "org.postgresql.Driver"
);

As for data mapping from PostgreSQL to Doris, one Database in Doris corresponds to one schema in the specified database in PostgreSQL (for example, “demo” in jdbc_url above), and one Table in that Database corresponds to one table in that schema. To make it more intuitive, the mapping relations are as follows:

SinceVersion 1.2.2

1. Oracle

CREATE CATALOG jdbc_oracle PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin",
    "driver_url" = "ojdbc6.jar",
    "driver_class" = "oracle.jdbc.driver.OracleDriver"
);

As for data mapping from Oracle to Doris, one Database in Doris corresponds to one User (for example, “helowin” in jdbc_url above), and one Table in that Database corresponds to one table that the User has access to. In conclusion, the mapping relations are as follows:

SinceVersion 1.2.2

1. Clickhouse

CREATE CATALOG jdbc_clickhouse PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
    "driver_url" = "clickhouse-jdbc-0.3.2-patch11-all.jar",
    "driver_class" = "com.clickhouse.jdbc.ClickHouseDriver"
);

SinceVersion 1.2.2

1. SQLServer

As for data mapping from SQLServer to Doris, one Database in Doris corresponds to one schema in the specified database in SQLServer (for example, “doris_test” in jdbc_url above), and one Table in that Database corresponds to one table in that schema. The mapping relations are as follows:

SinceVersion dev6. Doris

Jdbc Catalog also support to connect another Doris database:

CREATE CATALOG doris_catalog PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:mysql://127.0.0.1:9030?useSSL=false",
    "driver_url" = "mysql-connector-java-5.1.47.jar",
    "driver_class" = "com.mysql.jdbc.Driver"
);

Currently, Jdbc Catalog only support to use 5.x version of JDBC jar package to connect another Doris database. If you use 8.x version of JDBC jar package, the data type of column may not be matched.

select * from mysql_table where k1 > 1000 and k3 ='term';

In some cases, the keywords in the database might be used as the field names. For queries to function normally in these cases, Doris will add escape characters to the field names and tables names in SQL statements based on the rules of different databases, such as (``) for MySQL, ([]) for SQLServer, and (“”) for PostgreSQL and Oracle. This might require extra attention on case sensitivity. You can view the query statements sent to these various databases via explain sql.

Write Data

SinceVersion 1.2.2After creating a JDBC Catalog in Doris, you can write data or query results to it using the `insert into` statement. You can also ingest data from one JDBC Catalog Table to another JDBC Catalog Table.

Example:

insert into mysql_table values(1, "doris");
insert into mysql_table select * from table;

Transaction

In Doris, data is written to External Tables in batches. If the ingestion process is interrupted, rollbacks might be required. That’s why JDBC Catalog Tables support data writing transactions. You can utilize this feature by setting the session variable: enable_odbc_transcation .

The transaction mechanism ensures the atomicity of data writing to JDBC External Tables, but it reduces performance to a certain extent. You may decide whether to enable transactions based on your own tradeoff.

PostgreSQL

SQLServer

Doris

FAQ

1. Are there any other databases supported besides MySQL, Oracle, PostgreSQL, SQLServer, and ClickHouse?

   Currently, Doris supports MySQL, Oracle, PostgreSQL, SQLServer, and ClickHouse. We are planning to expand this list. Technically, any databases that support JDBC access can be connected to Doris in the form of JDBC external tables. You are more than welcome to be a Doris contributor to expedite this effort.

2. Why does the error message “CAUSED BY: DataReadException: Zero date value prohibited” pop up when DateTime=”0000:00:00 00:00:00” while reading MySQL external tables?

   The options for this parameter include: EXCEPTION,CONVERT_TO_NULL,ROUND. Respectively, they mean to report error, convert to null, and round the DateTime to “0001-01-01 00:00:00” when encountering an illegal DateTime.

   You can add "jdbc_url"="jdbc:mysql://IP:PORT/doris_test?zeroDateTimeBehavior=convertToNull" to the URL.

3. Why do loading failures happen when reading MySQL or other external tables?

   For example:

   failed to load driver class com.mysql.jdbc.driver in either of hikariconfig class loader

   Such errors occur because the driver_class has been wrongly put when creating the Resource. The problem with the above example is the letter case. It should be corrected as "driver_class" = "com.mysql.jdbc.Driver".

4. How to fix communication link failures?

   If you run into the following errors:

   ERROR 1105 (HY000): errCode = 2, detailMessage = PoolInitializationException: Failed to initialize pool: Communications link failure
   The last packet successfully received from the server was 7 milliseconds ago.  The last packet sent successfully to the server was 4 milliseconds ago.
   CAUSED BY: CommunicationsException: Communications link failure
   The last packet successfully received from the server was 7 milliseconds ago.  The last packet sent successfully to the server was 4 milliseconds ago.
   CAUSED BY: SSLHandshakeExcepti

   Please check the be.out log of BE.

   If it contains the following message:

   You can add ?useSSL=false to the end of the JDBC connection string when creating Catalog. For example, "jdbc_url" = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false".

5. What to do with errors such as “CAUSED BY: SQLException OutOfMemoryError” when performing JDBC queries?