JDBC 外表

DeprecatedVersion 1.2.2

推荐使用 JDBC Catalog 访问 JDBC 外表。

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

参数说明：

注意：

如果你是本地路径方式，这里数据库驱动依赖的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测试

2.PostgreSQL测试

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测试

4.oracle测试

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

5.ClickHouse测试

类型匹配

各个数据库之间数据类型存在不同，这里列出了各个数据库中的类型和Doris之中数据类型匹配的情况。

MySQL

PostgreSQL

Oracle

SQL server

ClickHouse

注意：

• 对于ClickHouse里的一些特殊类型，如UUID,IPv4,IPv6,Enum8可以用Doris的Varchar/String类型来匹配,但是在显示上IPv4,IPv6会额外在数据最前面显示一个/,需要自己用split_part函数处理
• 对于ClickHouse的Geo类型Point,无法进行匹配

Q&A

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