Doris通过ODBC连接MYSQL

1.概述

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

1. 支持各种数据源接入Doris
2. 支持Doris与各种数据源中的表联合查询，进行更加复杂的分析操作
3. 通过insert into将Doris执行的查询结果写入外部的数据源

本文主要介绍Doris ODBC的安装使用方式

2.命令行安装unixODBC

[root@hadoop102 lib]# yum install -y unixODBC unixODBC-devel libtool-ltdl libtool-ltdl-devel

# 检查unixODBC安装情况
[root@hadoop102 lib]# odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

3.ODBC驱动安装

3.1 安装Mysql ODBC驱动

Mysql版本	ODBC驱动版本
8.0.27	8.0.27,8.026
5.7.36	5.3.11,5.3.13
5.6.51	5.3.11,5.3.13
5.5.62	5.3.11,5.3.13

Mysql ODBC驱动下载地址：https://dev.mysql.com/downloads/connector/odbc

这里我们下载的是RPM安装包：mysql-connector-odbc-5.3.11-1.el7.x86_64.rpm

# 上传到/opt/software下, 然后执行
[root@hadoop102 software]# rpm -ivh mysql-connector-odbc-5.3.11-1.el7.x86_64.rpm 
警告：mysql-connector-odbc-5.3.11-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-connector-odbc-5.3.11-1.el7################################# [100%]
Success: Usage count is 1
Success: Usage count is 1

[root@hadoop102 software]# myodbc-installer -d -l
PostgreSQL
MySQL
FreeTDS
MySQL ODBC 5.3 Unicode Driver
MySQL ODBC 5.3 ANSI Driver

3.2 登记MySQL驱动

vim /etc/odbcinst.ini

[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1
 
[MySQL ODBC 5.3 Unicode Driver]
Driver=/usr/lib64/libmyodbc5w.so
UsageCount=1

[MySQL ODBC 5.3 ANSI Driver]
Driver=/usr/lib64/libmyodbc5a.so
UsageCount=1 

3.3 配置MYSQL数据链接信息

vim /etc/odbc.ini

[mysql]
Description     = Data source MySQL
Driver          = MySQL ODBC 5.3 Unicode Driver
Server          = 172.26.203.210
Host            = 172.26.203.210
Database        = test
Port            = 3306
User            = root
Password        = root

一般是通过Unicode方式连接

其他参数按mysql server 的设置。Database 选择已经建立好的数据库。

3.4 测试ODBC连接

[root@hadoop102 software]# isql -v mysql
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from student;
+---------------------+
| count(*)            |
+---------------------+
| 1229                |
+---------------------+
SQLRowCount returns 1
1 rows fetched
SQL> 

一切正常

4. 配置Doris be ODBC

Doris 使用ODBC连接mysql或者其他数据库，只需要在BE节点安装和配置ODBC即可

修改BE节点odbc配置信息

 ├── bin
 │   ├── be.pid
 │   ├── start_be.sh
 │   └── stop_be.sh
 ├── conf
 │   ├── be.conf
 │   └── odbcinst.ini   //修改这个文件的配置信息
 ├── lib
 │   ├── meta_tool
 │   ├── palo_be
 │   ├── small_file
 │   ├── udf
 │   └── udf-runtime

示例：

我这里只使用了Mysql

[root@hadoop102 conf]# vim /opt/module/apache-doris-0.15.0/be/conf/odbcinst.ini
# 添加下面内容
[MySQL Driver]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc5a.so
FileUsage       = 1

配置完以后重启BE即可，所有BE节点操作一样

5. Doris 使用ODBC访问外部数据源

5.1 不使用Resource方式访问

CREATE EXTERNAL TABLE wb_student (
  id int(11)  NULL COMMENT '学号',
  classid int(11)  NULL COMMENT '班级号',
  name varchar(10) NULL COMMENT '姓名',
  sex varchar(10) NULL COMMENT '性别',
  subjectid int(11)  NULL COMMENT '学科号',
  score int(11)  NULL COMMENT '成绩'
) ENGINE=ODBC
 COMMENT "ODBC"
 PROPERTIES (
 "host" = "172.26.203.210",
 "port" = "3306",
 "user" = "root",
 "password" = "root",
 "database" = "test",
 "table" = "student",
 "driver" = "MySQL Driver",  -- 注意这里的名称和be下的odbcinst.ini里的mysql[]里的名称一致
 "odbc_type" = "mysql"
 );

-- 查询外部表数据
SELECT * FROM wb_student;

5.2 通过ODBC_Resource来创建ODBC外表 (推荐使用的方式)

首先创建Resource

CREATE EXTERNAL RESOURCE mysql_odbc
PROPERTIES (
    "type" = "odbc_catalog",
    "host" = "172.26.203.210",
    "port" = "3306",
    "user" = "root",
    "password" = "root",
    "database" = "test",
    "driver" = "MySQL Driver",  -- -- 注意这里的名称和be下的odbcinst.ini里的mysql[]里的名称一致
    "odbc_type" = "mysql"
);

然后创建 ODBC 外表

CREATE EXTERNAL TABLE wb_student (
  id int(11)  NULL COMMENT '学号',
  classid int(11)  NULL COMMENT '班级号',
  name varchar(10) NULL COMMENT '姓名',
  sex varchar(10) NULL COMMENT '性别',
  subjectid int(11)  NULL COMMENT '学科号',
  score int(11)  NULL COMMENT '成绩'
 ) ENGINE=ODBC
 COMMENT "ODBC"
 PROPERTIES (
 "odbc_catalog_resource" = "mysql_odbc",
 "database" = "test",
 "table" = "student"
 );
 
SELECT * FROM wb_student;

6. Doris ODBC操作支持方式

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

2.通过insert into将Doris执行的查询结果写入外部的数据源

通过insert导入外部表数据

-- 创建doris表
CREATE TABLE student (
  id int(11) NULL COMMENT '学号',
  classid int(11) NULL COMMENT '班级号',
  name varchar(10) NULL COMMENT '姓名',
  sex varchar(10) NULL COMMENT '性别',
  subjectid int(11) NULL COMMENT '学科号',
  score int(11) NULL COMMENT '成绩'
)
DISTRIBUTED BY HASH(id) BUCKETS 10
PROPERTIES (
    "replication_num" = "1"
);
-- 导入外部表数据到doris表
INSERT INTO student SELECT * FROM wb_student;

7. mysql外表注意事项

1.mysql数据库及表的字符集一定要是用UTF8，不要使用UTF8mb4，目前doris ODBC外表只支持UTF8编码

2.在doris BE节点配置conf/odbcinst.ini，这里配置

[MySQL Driver]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc8w.so
FileUsage       = 1

这里一定不要使用libmyodbc8a.so 要使用 libmyodbc8w.so 否则会出现中文查询不到结果的问题，这里是因为：

当前ODBC支持ANSI 与 Unicode 两种Driver形式，当前Doris只支持Unicode Driver。
如果强行使用ANSI Driver可能会导致查询结果出错。

如果使用了libmyodbc8a.so ,BE转发过去的sql语句会出现乱码，这个可以通过Mysql的general log 看到。

参考资料

Apache doris ODBC外表使用方式

Apache Doris ODBC mysql外表注意事项

doris ODBC 连接 mysql 外部表