Skip to main content
Skip to main content

JDBC External Table

JDBC External Table

Tips

This feature is supported since the Apache Doris 1.2.2 version

Please use JDBC Catalog to access JDBC data sources, this function will no longer be maintained after version 1.2.2.

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:

::tip Tips This feature is supported since the Apache Doris 1.2 version :::

  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​

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

CREATE EXTERNAL TABLE `baseall_mysql` (
`k1` tinyint(4) NULL,
`k2` smallint(6) NULL,
`k3` int(11) NULL,
`k4` bigint(20) NULL,
`k5` decimal(9, 3) NULL
) ENGINE=JDBC
PROPERTIES (
"resource" = "jdbc_resource",
"table" = "baseall",
"table_type"="mysql"
);

Parameter Description:

ParameterDescription
type"jdbc"; required; specifies the type of the Resource
userUsername for accessing the external database
passwordPassword of the user
jdbc_urlJDBC URL protocol, including the database type, IP address, port number, and database name; Please be aware of the different formats of different database protocols. For example, MySQL: "jdbc:mysql://127.0.0.1:3306/test?useCursorFetch=true".
driver_classClass of the driver used to access the external database. For example, to access MySQL data: com.mysql.jdbc.Driver.
driver_urlDriver URL for downloading the Jar file package that is used to access the external database, for example, http://IP:port/mysql-connector-java-5.1.47.jar. For local stand-alone testing, you can put the Jar file package in a local path: "driver_url"="file:///home/disk1/pathTo/mysql-connector-java-5.1.47.jar"; for local multi-machine testing, please ensure the consistency of the paths.
resourceName of the Resource that the Doris External Table depends on; should be the same as the name set in Resource creation.
tableName of the external table to be mapped in Doris
table_typeThe database from which the external table comes, such as mysql, postgresql, sqlserver, and oracle.

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​

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​

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.

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 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).

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​

MySQL VersionMySQL JDBC Driver Version
8.0.30mysql-connector-java-5.1.47.jar

2.PostgreSQL Test​

PostgreSQL VersionPostgreSQL JDBC Driver Version
14.5postgresql-42.5.0.jar
CREATE EXTERNAL RESOURCE jdbc_pg
properties (
"type"="jdbc",
"user"="postgres",
"password"="123456",
"jdbc_url"="jdbc:postgresql://127.0.0.1:5442/postgres?currentSchema=doris_test",
"driver_url"="http://127.0.0.1:8881/postgresql-42.5.0.jar",
"driver_class"="org.postgresql.Driver"
);

CREATE EXTERNAL TABLE `ext_pg` (
`k1` int
) ENGINE=JDBC
PROPERTIES (
"resource" = "jdbc_pg",
"table" = "pg_tbl",
"table_type"="postgresql"
);

3.SQLServer Test​

SQLServer VersionSQLServer JDBC Driver Version
2022mssql-jdbc-11.2.0.jre8.jar

4.Oracle Test​

Oracle VersionOracle JDBC Driver Version
11ojdbc6.jar

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

5.ClickHouse Test​

ClickHouse VersionClickHouse JDBC Driver Version
22clickhouse-jdbc-0.3.2-patch11-all.jar
22clickhouse-jdbc-0.4.1-all.jar

6.Sap Hana Test​

Sap Hana VersionSap Hana JDBC Driver Version
2.0ngdbc.jar
CREATE EXTERNAL RESOURCE jdbc_hana
properties (
"type"="jdbc",
"user"="SYSTEM",
"password"="SAPHANA",
"jdbc_url" = "jdbc:sap://localhost:31515/TEST",
"driver_url" = "file:///path/to/ngdbc.jar",
"driver_class" = "com.sap.db.jdbc.Driver"
);

CREATE EXTERNAL TABLE `ext_hana` (
`k1` int
) ENGINE=JDBC
PROPERTIES (
"resource" = "jdbc_hana",
"table" = "TEST.HANA",
"table_type"="sap_hana"
);

7.Trino Test​

Trino VersionTrino JDBC Driver Version
389trino-jdbc-389.jar
CREATE EXTERNAL RESOURCE jdbc_trino
properties (
"type"="jdbc",
"user"="hadoop",
"password"="",
"jdbc_url" = "jdbc:trino://localhost:8080/hive",
"driver_url" = "file:///path/to/trino-jdbc-389.jar",
"driver_class" = "io.trino.jdbc.TrinoDriver"
);

CREATE EXTERNAL TABLE `ext_trino` (
`k1` int
) ENGINE=JDBC
PROPERTIES (
"resource" = "jdbc_trino",
"table" = "hive.test",
"table_type"="trino"
);

8.OceanBase Test​

OceanBase VersionOceanBase JDBC Driver Version
3.2.3oceanbase-client-2.4.2.jar
CREATE EXTERNAL RESOURCE jdbc_oceanbase
properties (
"type"="jdbc",
"user"="root",
"password"="",
"jdbc_url" = "jdbc:oceanbase://localhost:2881/test",
"driver_url" = "file:///path/to/oceanbase-client-2.4.2.jar",
"driver_class" = "com.oceanbase.jdbc.Driver",
"oceanbase_mode" = "mysql" or "oracle"
);

CREATE EXTERNAL TABLE `ext_oceanbase` (
`k1` int
) ENGINE=JDBC
PROPERTIES (
"resource" = "jdbc_oceanbase",
"table" = "test.test",
"table_type"="oceanbase"
);

Note:

When creating an OceanBase external table, you only need to specify the oceanbase mode parameter when creating a resource, and the table type of the table to be created is oceanbase

Type Mapping​

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

MySQL​

MySQLDoris
BOOLEANBOOLEAN
BIT(1)BOOLEAN
TINYINTTINYINT
SMALLINTSMALLINT
INTINT
BIGINTBIGINT
BIGINT UNSIGNEDLARGEINT
VARCHARVARCHAR
DATEDATE
FLOATFLOAT
DATETIMEDATETIME
DOUBLEDOUBLE
DECIMALDECIMAL

PostgreSQL​

PostgreSQLDoris
BOOLEANBOOLEAN
SMALLINTSMALLINT
INTINT
BIGINTBIGINT
VARCHARVARCHAR
DATEDATE
TIMESTAMPDATETIME
REALFLOAT
FLOATDOUBLE
DECIMALDECIMAL

Oracle​

OracleDoris
VARCHARVARCHAR
DATEDATETIME
SMALLINTSMALLINT
INTINT
REALDOUBLE
FLOATDOUBLE
NUMBERDECIMAL

SQL server​

SQLServerDoris
BITBOOLEAN
TINYINTTINYINT
SMALLINTSMALLINT
INTINT
BIGINTBIGINT
VARCHARVARCHAR
DATEDATE
DATETIMEDATETIME
REALFLOAT
FLOATDOUBLE
DECIMALDECIMAL

ClickHouse​

ClickHouseDoris
BooleanBOOLEAN
StringSTRING
Date/Date32DATE/DATEV2
DateTime/DateTime64DATETIME/DATETIMEV2
Float32FLOAT
Float64DOUBLE
Int8TINYINT
Int16/UInt8SMALLINT
Int32/UInt16INT
Int64/Uint32BIGINT
Int128/UInt64LARGEINT
Int256/UInt128/UInt256STRING
DecimalDECIMAL/DECIMALV3/STRING
Enum/IPv4/IPv6/UUIDSTRING
Array(T)ARRAY\<T>

Note:

  • For Array types in ClickHouse, use Doris's Array type to match them. For basic types in an Array, see Basic type matching rules. Nested arrays are not supported.
  • 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.

SAP HANA​

SAP_HANADoris
BOOLEANBOOLEAN
TINYINTTINYINT
SMALLINTSMALLINT
INTERGERINT
BIGINTBIGINT
SMALLDECIMALDECIMAL/DECIMALV3
DECIMALDECIMAL/DECIMALV3
REALFLOAT
DOUBLEDOUBLE
DATEDATE/DATEV2
TIMETEXT
TIMESTAMPDATETIME/DATETIMEV2
SECONDDATEDATETIME/DATETIMEV2
VARCHARTEXT
NVARCHARTEXT
ALPHANUMTEXT
SHORTTEXTTEXT
CHARCHAR
NCHARCHAR

Trino​

TrinoDoris
booleanBOOLEAN
tinyintTINYINT
smallintSMALLINT
integerINT
bigintBIGINT
decimalDECIMAL/DECIMALV3
realFLOAT
doubleDOUBLE
dateDATE/DATEV2
timestampDATETIME/DATETIMEV2
varcharTEXT
charCHAR
arrayARRAY
othersUNSUPPORTED

OceanBase​

For MySQL mode, please refer to MySQL type mapping For Oracle mode, please refer to Oracle type mapping

Q&A​

See the FAQ section in JDBC.