Apache Doris 代碼倉(cāng)庫(kù)地址：apache/incubator-doris 歡迎大家關(guān)注加星
1.概述
ODBC External Table Of Doris 提供了Doris通過(guò)數(shù)據(jù)庫(kù)訪問(wèn)的標(biāo)準(zhǔn)接口(ODBC)來(lái)訪問(wèn)外部表普筹，外部表省去了繁瑣的數(shù)據(jù)導(dǎo)入工作改化，讓Doris可以具有了訪問(wèn)各式數(shù)據(jù)庫(kù)的能力湘换，并借助Doris本身的OLAP的能力來(lái)解決外部表的數(shù)據(jù)分析問(wèn)題：
- 支持各種數(shù)據(jù)源接入Doris
- 支持Doris與各種數(shù)據(jù)源中的表聯(lián)合查詢禁荸，進(jìn)行更加復(fù)雜的分析操作
- 通過(guò)insert into將Doris執(zhí)行的查詢結(jié)果寫(xiě)入外部的數(shù)據(jù)源
本文主要介紹Doris ODBC的安裝使用方式
這里以最常用的Mysql為例酸钦。
2.ODBC驅(qū)動(dòng)安裝
2.1 安裝Mysql ODBC驅(qū)動(dòng)
Mysql ODBC驅(qū)動(dòng)下載地址：https://dev.mysql.com/downloads/connector/odbc/
這里我們下載的是RPM安裝包：mysql-connector-odbc-8.0.11-1.el7.x86_64.rpm
然后執(zhí)行
yum localinstall -y mysql-connector-odbc-8.0.11-1.el7.x86_64.rpm
2.2 配置Mysql ODBC驅(qū)動(dòng)
編輯 /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 8.0 Unicode Driver]
Driver=/usr/lib64/libmyodbc8w.so
UsageCount=1
[MySQL ODBC 8.0 ANSI Driver]
Driver=/usr/lib64/libmyodbc8a.so
UsageCount=1
2.3 測(cè)試驅(qū)動(dòng)
配置 /etc/odbc.ini
[mysql]
Description = Data source MySQL
Driver = MySQL ODBC 8.0 Driver
Server = 192.168.1.120
Host = 192.168.1.120
Database = dbname
Port = 3306
User = root
Password = 123456
一般是通過(guò)uncode 方式連接拆内，Driver 必須是MySQL ODBC 8.0 Driver 顽爹，
其他參數(shù)按mysql server 的設(shè)置到逊。Database 選擇已經(jīng)建立好的數(shù)據(jù)庫(kù)泉手。
2.4 測(cè)試ODBC連接
執(zhí)行:
$ isql mysql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from test limit 2;
+---------------------------------------------------+-----------+
| name | age |
+---------------------------------------------------+-----------+
| zhangfeng | 20 |
| zhang | 22 |
+---------------------------------------------------+-----------+
SQLRowCount returns 2
2 rows fetched
一切正常
3. 配置Doris be ODBC
Doris 使用ODBC連接mysql或者其他數(shù)據(jù)庫(kù)黔寇，只需要在BE節(jié)點(diǎn)安裝和配置ODBC即可
修改BE節(jié)點(diǎn)odbc配置信息
├── bin
│ ├── be.pid
│ ├── start_be.sh
│ └── stop_be.sh
├── conf
│ ├── be.conf
│ └── odbcinst.ini //修改這個(gè)文件的配置信息
├── lib
│ ├── meta_tool
│ ├── palo_be
│ ├── small_file
│ ├── udf
│ └── udf-runtime
示例：
我這里只使用了Mysql
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/psqlodbc.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
#[MySQL ODBC 8.0 Unicode Driver]
#Description = ODBC for MySQL
#Driver = /usr/lib64/libmyodbc8w.so
#FileUsage = 1
[MySQL Driver]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc8a.so
FileUsage = 1
# Driver from the oracle-connector-odbc package
# Setup from the unixODBC package
[Oracle 19 ODBC driver]
Description=Oracle ODBC driver for Oracle 19
Driver=/usr/lib/libsqora.so.19.1
配置完以后重啟BE即可，所有BE節(jié)點(diǎn)操作一樣
4. Doris 使用ODBC訪問(wèn)外部數(shù)據(jù)源
4.1 不使用Resource方式訪問(wèn)
CREATE EXTERNAL TABLE `test_mysql` (
`k1` decimal(9, 3) NOT NULL COMMENT "",
`k2` char(10) NOT NULL COMMENT "",
`k3` datetime NOT NULL COMMENT "",
`k5` varchar(20) NOT NULL COMMENT "",
`k6` double NOT NULL COMMENT ""
) ENGINE=ODBC
COMMENT "ODBC"
PROPERTIES (
"host" = "192.168.0.1",
"port" = "3306",
"user" = "root",
"password" = "123456",
"database" = "test",
"table" = "test",
"driver" = "MySQL Driver", --注意這里的名稱和odbcinst.ini里的mysql[]里的名稱一致
"odbc_type" = "mysql"
);
然后在doris里執(zhí)行查詢驗(yàn)證
4.2 通過(guò)ODBC_Resource來(lái)創(chuàng)建ODBC外表 (推薦使用的方式)
首先創(chuàng)建Resource
CREATE EXTERNAL RESOURCE `mysql_odbc`
PROPERTIES (
"type" = "odbc_catalog",
"host" = "192.168.0.1",
"port" = "3306",
"user" = "root",
"password" = "123456",
"driver" = "MySQL Driver", --注意這里的名稱和odbcinst.ini里的mysql[]里的名稱一致
"odbc_type" = "mysql"
);
然后創(chuàng)建 ODBC 外表
CREATE EXTERNAL TABLE `test_mysql` (
`k1` decimal(9, 3) NOT NULL COMMENT "",
`k2` char(10) NOT NULL COMMENT "",
`k3` datetime NOT NULL COMMENT "",
`k5` varchar(20) NOT NULL COMMENT "",
`k6` double NOT NULL COMMENT ""
) ENGINE=ODBC
COMMENT "ODBC"
PROPERTIES (
"odbc_catalog_resource" = "mysql_odbc",
"database" = "test",
"table" = "baseall"
);
