Apache Doris ODBC 外表配置和使用, 访问MySQL外表

2021-09-28

Apache Doris是一个支持对海量大数据进行快速分析的MPP数据库。MPP即(Massively Parallel Processing)大规模并行处理，简单的理解就是将任务并行的分散到多个服务器节点上，在每个节点上计算完成后，再将各部分结果汇总到一起得到最终的结果。 Apache Doris就是一个大规模并行分析(Analytical Massively Parallel Processing ) MPP数据库。本文将介绍Apache Doris ODBC External Table的配置和使用。

Doris ODBC External Table介绍 #

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

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

Doris从0.13开始，默认的编译参数移除第三方依赖库mysql-5.7.18，不再支持旧版的MySQL外表功能，后续访问MySQL外表的功能通过UnixODBC实现，就是本文介绍的ODBC外表功能。当前Doris的ODBC外表功能适配了MySQL,Oracle,PostgreSQL,SQLServer数据库，后续可能会支持其他数据库的适配。

在使用外表的时候，需要注意地方，通常在外表数据量较小，少于100W条时，可以通过外部表的方式访问。由于外表无法发挥Doris在存储引擎部分的能力和会带来额外的网络开销，所以建议根据实际对查询的访问时延要求来确定是否通过外部表访问还是将数据导入Doris之中。

MySQL ODBC驱动安装 #

首先需要在doris集群的各个BE节点上安装MySQL ODBC驱动，这里的以CentOS 7为例。

先在各个BE服务器节点上安装mysql connector odbc所需的依赖:

1yum install -y libtool-ltdl-devel libtool-ltdl unixODBC-devel unixODBC

接下来下载mysql connector odbc安装包，mysql官方提供了linux generic和rpm等多种格式的安装包。这里一开始参考文档https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-installation-binary-unix.html中的内容，安装的是linux generic的，结果在测试时出现[unixODBC][Driver Manager]Can't open lib '/usr/lib64/libmyodbc8w.so' : file not found的错误，确认依据根据文档中步骤正确安装。于是又尝试使用rpm安装，从https://dev.mysql.com/downloads/connector/odbc/下载的是mysql-connector-odbc-8.0.26-1.el7.x86_64.rpm。

1yum localinstall mysql-connector-odbc-8.0.26-1.el7.x86_64.rpm

在所有的Doris BE节点上安装完成后，随便找一台节点作如下测试，确认ODBC安装成功。

查看/etc/odbcinst.ini文件确认有如下内容:

1[MySQL ODBC 8.0 Unicode Driver]
2Driver=/usr/lib64/libmyodbc8w.so
3UsageCount=1
4
5[MySQL ODBC 8.0 ANSI Driver]
6Driver=/usr/lib64/libmyodbc8a.so
7UsageCount=1

使用rpm安装的mysql connector odbc默认安装了以上两个Driver。

下面创建/etc/odbc.ini并添加一个数据源:

1[mydb]
2Driver=MySQL ODBC 8.0 Unicode Driver
3Description=
4SERVER = 192.168.1.100
5CHARSET = UTF8
6Port = 3306
7Database = databasename
8User = user
9Password = password

这里选择使用MySQL ODBC 8.0 Unicode Driver连接，下面测试ODBC连接:

 1isql mydb
 2+---------------------------------------+
 3| Connected!                            |
 4|                                       |
 5| sql-statement                         |
 6| help [tablename]                      |
 7| quit                                  |
 8|                                       |
 9+---------------------------------------+
10SQL>

确认连接可以成功连上数据源即可。

Doris BE的ODBC配置 #

Doris要使用ODBC访问MySQL数据库，需要在各个BE节点的配置文件中的odbcinst.ini中完成配置。Doris BE的odbcinst.ini配置文件一般在BE的安装目录的conf目录下。修改并确保该配置文件中关于[MySQL ODBC 8.0 Unicode Driver]的内容与前面安装ODBC时/etc/odbcinst.ini中[MySQL ODBC 8.0 Unicode Driver]一样。

以上完成配置后，需要把Doris集群的各个BE服务都重启一下。注意，要求在所有的BE节点上都安装相同版本的ODBC Driver，同时要求conf/odbcinst.ini的配置也要一样。

Doris对外部资源的抽象: Resource #

接下来，将开始使用Doris的ODBC External Table，在开始之前先学习一下Doris中的Resource对象。为了节省Doris集群内的计算、存储资源，Doris需要引入一些其他外部资源来完成相关的工作，如Spark/GPU用于查询，HDFS/S3用于外部存储，Spark/MapReduce用于ETL, 通过ODBC连接外部存储等。 Doris提供了Resource对象的抽象用来管理和使用这些外部资源。

为了使用通过ODBC连接外部的MySQL，需要先创建一个类型为odbc_catalog的外部资源:

 1CREATE EXTERNAL RESOURCE `mysql_odbc_test`
 2PROPERTIES (
 3"type" = "odbc_catalog",
 4"host" = "192.168.1.100",
 5"port" = "3306",
 6"user" = "test",
 7"password" = "test",
 8"database" = "test",
 9"odbc_type" = "mysql",
10"driver" = "MySQL ODBC 8.0 Unicode Driverr"
11);

上面的SQL语句创建了名称为mysql_odbc_test的Doris外部资源，type是odbc_catalog表示该外部资源是一个通过ODBC连接的数据库，host是外部数据库的IP地址，port是数据库的端口，database是具体的数据库名字，odbc_type是具体的数据库类型这里是mysql，driver十分关键，要求和be安装目录/conf/odbcinst.ini中的Driver名称一致。

注意Resource对象创建完成后，与数据库对象一样，如果普通用户需要使用这个Resource，还需要对普通用户进行授权:

1GRANT USAGE_PRIV ON RESOURCE mysql_odbc_test to someuser@'%';

Doris ODBC外表的创建 #

创建了odbc数据库外部资源之后，就可以使用该外部资源在Doris中创建外表。下面一个创建外表t_user_mysql的例子:

 1CREATE EXTERNAL  TABLE `t_user_mysql` (
 2  `id` bigint NOT NULL COMMENT '',
 3  `name` varchar(200) NOT NULL COMMENT '',
 4  `province_name` varchar(20) NOT NULL COMMENT '',
 5  `city_name` varchar(20) NOT NULL DEFAULT '' COMMENT ''
 6) ENGINE=ODBC 
 7COMMENT '' 
 8PROPERTIES (
 9"odbc_catalog_resource" = "mysql_odbc_test",
10"database" = "test",
11"table" = "t_user"
12);

上面的sql在doris的数据库中创建一个外表t_user_mysql，指向外部资源mysql_odbc_test的mysql数据库test库中的t_user表。

ODBC外表创建成功后，除了无法使用Doris数据模型中的(rollup、预聚合、物化视图)外，预普通的doris表没有什么区别，可以进行普通的查询，如下:

1SELECT * FROM t_user_mysql limit 10;

同样可以使用insert into语句直接写入数据，也可以将doris执行完查询后的结果写入到ODBC外表，或者是从一个ODBC外表将数据导入另一个ODBC外表，或从一个ODBC外表将数据导入另一个doris表。

1insert into t_user_mysql values ...;
2insert into t_user_mysql select * from t_user_oracle;
3insert into t_table select * from t_user_mysql;

下面简单试验一下将数据从ODBC外表导入doris普通表，创建一个doris表如下:

1CREATE TABLE t_user_region_dist
2(
3    `province_name` varchar(20) NOT NULL COMMENT '',
4    `city_name` varchar(20) NOT NULL DEFAULT '' COMMENT '',
5    num BIGINT SUM DEFAULT '0'
6)
7AGGREGATE KEY(province_name, city_name)
8DISTRIBUTED BY HASH(province_name) BUCKETS 10
9PROPERTIES("replication_num" = "3");

1insert into t_user_region_dist select province_name, city_name, 1 from  t_user_mysql where id < 10000;
2
3SELECT * from t_user_region_dist;

可以看到上面的SQL实现了从一个ODBC外表t_user_mysql导入数据到doris表中的功能。