环境
-
doris 需要版本1.2以上
-
jar包 根据不同的数据源 需要不同的jar包 https://mvnrepository.com/
a. MySQL mysql-connector-java-5.1.47.jar
b. PostgreSQL postgresql-42.5.1.jar
c. Oracle ojdbc8.jar
d. Clickhouse clickhouse-jdbc-0.3.2-patch11-all.jar
e. SQLServer mssql-jdbc-11.2.3.jre8.jar -
jar包位置
a. 文件名：需将 Jar 包预先存放在 FE 和 BE 部署目录的 jdbc_drivers/jdbc/ 目录下。系统会自动在这个目录下寻找。该目录的位置，也可以由 fe.conf 和 be.conf 中的 jdbc_drivers_dir 配置修改。
b. 本地绝对路径。如file:///路径/mysql-connector-java-5.1.47.jar。需将 Jar 包预先存放在所有 FE/BE 节点指定的路径下。
c. Http 地址。如：https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/mysql-connector-java-5.1.47.jar。系统会从这个 http 地址下载 Driver 文件。仅支持无认证的 http 服务。
JDBC 外表
JDBC External Table Of Doris 提供了Doris通过数据库访问的标准接口(JDBC)来访问外部表，外部表省去了繁琐的数据导入工作，也省去了之前ODBC繁杂的驱动安装部署及版本匹配问题，兼容性更好，操作更简单，让Doris可以具有了访问各式数据库的能力，并借助Doris本身的OLAP的能力来解决外部表的数据分析问题：
- 支持各种数据源接入Doris
- 支持Doris与各种数据源中的表联合查询，进行更加复杂的分析操作
创建 JDBC Resource
Doris 目前支持的JDBC数据源有：MySQL，Oracle，PostgreSQL，SQLServer，Clickhouse
JDBC 的相关参数如下：
- type：指定类型为jdbc 其他类型可参考官网
- https://doris.apache.org/zh-CN/docs/dev/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-RESOURCE?_highlight=resource
- user：连接数据库使用的用户名
- password：连接数据库使用的密码
- jdbc_url: 连接到指定数据库的标识符
- driver_url: jdbc驱动包的url
- driver_class: jdbc驱动类
- resource：在Doris中建立外表时依赖的资源名，对应上步创建资源时的名字。
- table：在Doris中建立外表时，与外部数据库相映射的表名
- table_type：在Doris中建立外表时，该表来自那个数据库。例如mysql,postgresql,sqlserver,oracle
CREATE [EXTERNAL] RESOURCE "resource_name"PROPERTIES ("key"="value", ...);
mysql
CREATE EXTERNAL RESOURCE jdbc_resource
properties (
"type"="jdbc",
"user"="root",
"password"="root",
"jdbc_url"="jdbc:mysql://192.168.31.128:3306/doris_jdbc",
"driver_url"="file:///opt/app/jdbc/mysql-connector-java-8.0.28.jar",
"driver_class"="com.mysql.jdbc.Driver"
);
CREATE TABLE `order_mysql` (
emp_no INT NOT NULL, -- 编号
birth_date DATE NOT NULL, -- 生日
first_name VARCHAR(14) NOT NULL, -- 姓
last_name VARCHAR(16) NOT NULL, -- 名
gender VARCHAR(16), -- 性别
hire_date DATE NOT NULL -- 入职时间
) ENGINE=JDBC -- 指定jdbc连接
PROPERTIES (
"resource" = "jdbc_resource", -- 在Doris中建立外表时依赖的资源名，对应上步创建资源时的名字。
"table" = "employee", -- 在Doris中建立外表时，与外部数据库相映射的表名。
"table_type"="mysql" -- Doris中建立外表时，该表来自那个数据库。例如mysql,postgresql,sqlserver,oracle
);
oracle
CREATE EXTERNAL RESOURCE jdbc_oracle
PROPERTIES (
"type"="jdbc",
"user"="system",
"password"="root",
"jdbc_url" = "jdbc:oracle:thin:@192.168.31.1:1521:ORCL",
"driver_url" = "file:///opt/app/jdbc/ojdbc10-19.18.0.0.jar",
"driver_class" = "oracle.jdbc.driver.OracleDriver"
);
CREATE TABLE PERSONS (
PERSON_ID VARCHAR(14) NOT NULL, -- 编号
FIRST_NAME VARCHAR(14) NOT NULL , -- 姓
LAST_NAME VARCHAR(14) NOT NULL -- 名
) ENGINE=JDBC
PROPERTIES (
"resource" = "jdbc_oracle",
"table" = "PERSONS",
"table_type"="oracle"
);
Resource 简单语法
展示当前用户拥有权限的所有
SHOW RESOURCES;
仅 root 或 admin 用户可以删除资源删除 注意：正在使用的 ODBC/S3 资源无法删除
DROP RESOURCE 'resource_name'
仅 root 或 admin 用户可以修改资源。
ALTER RESOURCE 'resource_name'PROPERTIES ("key"="value", ...);
Catalog
官网
https://doris.apache.org/zh-CN/docs/dev/lakehouse/multi-catalog/jdbc
CATALOG 简单语法
查看 catlog
show catalogs;
切换catlog
switch jdbc_mysql
删除catlog
DROP catalog jdbc_mysql
| 参数 | 是否必须 | 默认值 | 说明 |
|---|---|---|---|
| user | 是 | 对应数据库的用户名 | |
| password | 是 | 对应数据库的密码 | |
| jdbc_url | 是 | JDBC连接串 | |
| driver_url | 是 | JDBCDriver Jar 包名称 | |
| driver_class | 是 | JDBCDriver Class 名称 | |
| only_specified_database | 否 | "false" | 指定是否只同步指定的 |
| databaselower_case_table_names | 否 | "false" | 是否以小写的形式同步jdbc外部数据源的表名 |
| include_database_list | 否 | "" | 当only_specified_database=true时，指定同步多个database，以','分隔。db名称是大小写敏感的。 |
| exclude_database_list | 否 | "" | 当only_specified_database=true时，指定不需要同步的多个database，以','分割。db名称是大小写敏感的。 |
- only_specified_database: 在jdbc连接时可以指定链接到哪个database/schema, 如：mysql中jdbc_url中可以指定database, pg的jdbc_url中可以指定currentSchema。
- include_database_list: 仅在only_specified_database=true时生效，指定需要同步的 database，以','分割，db名称是大小写敏感的。
- exclude_database_list: 仅在only_specified_database=true时生效，指定不需要同步的多个database，以','分割，db名称是大小写敏感的。
- 当
include_database_list和exclude_database_list有重合的database配置时，exclude_database_list会优先生效。
MySQL
1、创建CATALOG
CREATE CATALOG jdbc_mysql PROPERTIES (
"type"="jdbc", -- 固定类型
"user"="root", -- 账号
"password"="123456", -- 密码
"jdbc_url" = "jdbc:mysql://127.0.0.1:3306/demo", -- jdbc:mysql://ip:port/库
"driver_url" = "mysql-connector-java-5.1.47.jar", -- jar包位置 有三种方式
"driver_class" = "com.mysql.jdbc.Driver"
)
2、查看CATALOG 并且切换
-- 查看 yes 表示你在那个catalog 下
show catalogs;
--切换
switch jdbc_mysql;
3、查看 jdbc catalog 下的数据库
show databases;
4、操作jdbc catalog数据库下的表
use doris_jdbc;
select * FROM doris_jdbc.employee
5、创建Doris的OLAP表将MySQL的表通过下面这种方式导入到Doris表里
insert into <doris_table> select * from <mysql_table>
6、 通过JDBC Mutil Catalog方式将MySQL表和Doris表进行关联分析
create table internal.jdbc_6_8.mysql_emp
PROPERTIES("replication_num" = "1")
as select * from jdbc_mysql.emp.employee
oracle
CREATE CATALOG jdbc_oracle PROPERTIES (
"type"="jdbc", -- 固定类型
"user"="root",-- 账号
"password"="123456", -- 密码
"jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin", -- jdbc:oracle:thin:@ip:port:库
"driver_url" = "ojdbc8.jar", -- jar包位置 有三种方式：ojdbc8 以上
"driver_class" = "oracle.jdbc.driver.OracleDriver"
-- JDBCDriver Class 名称
);
数据查询
由于可能存在使用数据库内部的关键字作为字段名，为解决这种状况下仍能正确查询，所以在SQL语句中，会根据各个数据库的标准自动在字段名与表名上加上转义符。例如 MYSQL(``)、PostgreSQL("")、SQLServer([])、ORACLE("")，所以此时可能会造成字段名的大小写敏感，具体可以通过explain sql，查看转义后下发到各个数据库的查询语句。
#目标catalog.目标database.目标table
select * from mysql_catalog.mysql_database.mysql_table where k1 > 1000 and k3 ='term';
数据写入
在Doris中建立JDBC Catalog后，可以通过insert into语句直接写入数据，也可以将Doris执行完查询之后的结果写入JDBC Catalog，或者是从一个JDBC外表将数据导入另一个JDBC外表。
insert into 目标catalog.目标database.目标table
insert into mysql_catalog.mysql_database.mysql_table values(1, "doris");
insert into mysql_catalog.mysql_database.mysql_table select * from table;
事务
- Doris的数据是由一组batch的方式写入外部表的，如果中途导入中断，之前写入数据可能需要回滚。所以JDBC外表支持数据写入时的事务，事务的支持需要通过设置
session variable: enable_odbc_transcation。 - 事务保证了JDBC外表数据写入的原子性，但是一定程度上会降低数据写入的性能，可以考虑酌情开启该功能。
set enable_odbc_transcation = true;