JDBC External Table

DeprecatedVersion 1.2.2

Please use JDBC Catalog to access JDBC data sources.

SinceVersion 1.2.0

By creating JDBC External Tables, Doris can access external tables via JDBC, the standard database access inferface. This allows Doris to visit various databases without tedious data ingestion, and give full play to its own OLAP capabilities to perform data analysis on external tables:

1. Multiple data sources can be connected to Doris;
2. It enables Join queries across Doris and other data sources and thus allows more complex analysis.

This topic introduces how to use JDBC External Tables in Doris.

Create JDBC External Table in Doris

See CREATE TABLE for syntax details.

1. Create JDBC External Table by Creating JDBC_Resource

1. CREATE EXTERNAL RESOURCE jdbc_resource
2. properties (
3. "type"="jdbc",
4. "user"="root",
5. "password"="123456",
6. "jdbc_url"="jdbc:mysql://192.168.0.1:3306/test?useCursorFetch=true",
7. "driver_url"="http://IP:port/mysql-connector-java-5.1.47.jar",
8. "driver_class"="com.mysql.jdbc.Driver"
9. );

11. CREATE EXTERNAL TABLE `baseall_mysql` (
12. `k1` tinyint(4) NULL,
13. `k2` smallint(6) NULL,
14. `k3` int(11) NULL,
15. `k4` bigint(20) NULL,
16. `k5` decimal(9, 3) NULL
17. ) ENGINE=JDBC
18. PROPERTIES (
19. "resource" = "jdbc_resource",
20. "table" = "baseall",
21. "table_type"="mysql"
22. );

Parameter Description:

Note:

For local testing, please make sure you put the Jar file package in the FE and BE nodes, too.

SinceVersion 1.2.1

In Doris 1.2.1 and newer versions, if you have put the driver in the jdbc_drivers directory of FE/BE, you can simply specify the file name in the driver URL: "driver_url" = "mysql-connector-java-5.1.47.jar", and the system will automatically find the file in the jdbc_drivers directory.

Query

1. 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 viaexplain sql`.

Write Data

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

1. insert into mysql_table values(1, "doris");
2. 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 External Tables support data writing transactions. You can utilize this feature by setting the session variable: enable_odbc_transcation (ODBC transactions are also controlled by this variable).

1. set enable_odbc_transcation = true;

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.

1.MySQL Test

2.PostgreSQL Test

1. CREATE EXTERNAL RESOURCE jdbc_pg
2. properties (
3. "type"="jdbc",
4. "user"="postgres",
5. "password"="123456",
6. "jdbc_url"="jdbc:postgresql://127.0.0.1:5442/postgres?currentSchema=doris_test",
7. "driver_url"="http://127.0.0.1:8881/postgresql-42.5.0.jar",
8. "driver_class"="org.postgresql.Driver"
9. );

11. CREATE EXTERNAL TABLE `ext_pg` (
12. `k1` int
13. ) ENGINE=JDBC
14. PROPERTIES (
15. "resource" = "jdbc_pg",
16. "table" = "pg_tbl",
17. "table_type"="postgresql"
18. );

3.SQLServer Test

4.Oracle Test

Test information on more versions will be provided in the future.

5.ClickHouse Test

Type Mapping

The followings list how data types in different databases are mapped in Doris.

MySQL

PostgreSQL

Oracle

SQL server

ClickHouse

Note:

• Some data types in ClickHouse, such as UUID, IPv4, IPv6, and Enum8, will be mapped to Varchar/String in Doris. IPv4 and IPv6 will be displayed with an / as a prefix. You can use the split_part function to remove the / .
• The Point Geo type in ClickHouse cannot be mapped in Doris by far.

Q&A

See the FAQ section in JDBC.