跳到主要内容
跳到主要内容

JDBC 外表

JDBC 外表

DeprecatedVersion 1.2.2

推荐使用 JDBC Catalog 访问 JDBC 外表,1.2.2 版本后将不再维护该功能。

SinceVersion 1.2.0

JDBC External Table Of Doris 提供了 Doris 通过数据库访问的标准接口 (JDBC) 来访问外部表,外部表省去了繁琐的数据导入工作,让 Doris 可以具有了访问各式数据库的能力,并借助 Doris 本身的 OLAP 的能力来解决外部表的数据分析问题:

  1. 支持各种数据源接入 Doris
  2. 支持 Doris 与各种数据源中的表联合查询,进行更加复杂的分析操作

本文档主要介绍该功能的使用方式等。

Doris 中创建 JDBC 的外表

具体建表语法参照:CREATE TABLE

1. 通过 JDBC_Resource 来创建 JDBC 外表

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"
);

参数说明:

参数说明
type"jdbc", 必填项标志资源类型
user访问外表数据库所使的用户名
password该用户对应的密码信息
jdbc_urlJDBC 的 URL 协议,包括数据库类型,IP 地址,端口号和数据库名,不同数据库协议格式不一样。例如 mysql: "jdbc:mysql://127.0.0.1:3306/test?useCursorFetch=true"。
driver_class访问外表数据库的驱动包类名,例如 mysql 是:com.mysql.jdbc.Driver.
driver_url用于下载访问外部数据库的 jar 包驱动 URL。http://IP:port/mysql-connector-java-5.1.47.jar。本地单机测试时,可将 jar 包放在本地路径下,"driver_url"="file:///home/disk1/pathTo/mysql-connector-java-5.1.47.jar",多机时需保证具有完全相同的路径信息。
resource在 Doris 中建立外表时依赖的资源名,对应上步创建资源时的名字。
table在 Doris 中建立外表时,与外部数据库相映射的表名。
table_type在 Doris 中建立外表时,该表来自那个数据库。例如 mysql,postgresql,sqlserver,oracle

注意:

如果你是本地路径方式,这里数据库驱动依赖的 jar 包,FE、BE 节点都要放置

SinceVersion 1.2.1

在 1.2.1 及之后的版本中,可以将 driver 放到 FE/BE 的 jdbc_drivers 目录下,并直接指定文件名,如:"driver_url" = "mysql-connector-java-5.1.47.jar"。系统会自动在 jdbc_drivers 目录寻找文件。

查询用法

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

由于可能存在使用数据库内部的关键字作为字段名,为解决这种状况下仍能正确查询,所以在 SQL 语句中,会根据各个数据库的标准自动在字段名与表名上加上转义符。例如 MYSQL(``)、PostgreSQL("")、SQLServer([])、ORACLE(""),所以此时可能会造成字段名的大小写敏感,具体可以通过 explain sql,查看转义后下发到各个数据库的查询语句。

数据写入

在 Doris 中建立 JDBC 外表后,可以通过 insert into 语句直接写入数据,也可以将 Doris 执行完查询之后的结果写入 JDBC 外表,或者是从一个 JDBC 外表将数据导入另一个 JDBC 外表。

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

事务

Doris 的数据是由一组 batch 的方式写入外部表的,如果中途导入中断,之前写入数据可能需要回滚。所以 JDBC 外表支持数据写入时的事务,事务的支持需要通过设置 session variable: enable_odbc_transcation (ODBC 事务也受此变量控制)。

set enable_odbc_transcation = true; 

事务保证了 JDBC 外表数据写入的原子性,但是一定程度上会降低数据写入的性能,可以考虑酌情开启该功能。

1.Mysql 测试

Mysql 版本Mysql JDBC 驱动版本
8.0.30mysql-connector-java-5.1.47.jar

2.PostgreSQL 测试

PostgreSQL 版本PostgreSQL JDBC 驱动版本
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 测试

SQLserver 版本SQLserver JDBC 驱动版本
2022mssql-jdbc-11.2.0.jre8.jar

4.oracle 测试

Oracle 版本Oracle JDBC 驱动版本
11ojdbc6.jar

目前只测试了这一个版本其他版本测试后补充

5.ClickHouse 测试

ClickHouse 版本ClickHouse JDBC 驱动版本
22clickhouse-jdbc-0.3.2-patch11-all.jar
22clickhouse-jdbc-0.4.1-all.jar

6.Sap Hana 测试

Sap Hana 版本Sap Hana JDBC 驱动版本
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 测试

Trino 版本Trino JDBC 驱动版本
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 测试

OceanBase 版本OceanBase JDBC 驱动版本
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"
);

注意:

在创建 OceanBase 外表时,只需在创建 Resource 时指定oceanbase_mode参数,创建外表的 table_type 为 oceanbase。

类型匹配

各个数据库之间数据类型存在不同,这里列出了各个数据库中的类型和 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>

注意:

  • 对于 ClickHouse 里的 Array 类型,可用 Doris 的 Array 类型来匹配,Array 内的基础类型匹配参考基础类型匹配规则即可,不支持嵌套 Array
  • 对于 ClickHouse 里的一些特殊类型,如 UUID,IPv4,IPv6,Enum8 可以用 Doris 的 Varchar/String 类型来匹配,但是在显示上 IPv4,IPv6 会额外在数据最前面显示一个/,需要自己用split_part函数处理
  • 对于 ClickHouse 的 Geo 类型 Point,无法进行匹配

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

MySQL 模式请参考 MySQL 类型映射 Oracle 模式请参考 Oracle 类型映射

Q&A

请参考 JDBC Catalog 中的 常见问题一节。