doris ODBC 连接 mysql 外部表

Centos 数据库 ODBC 版本对应

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

###

# 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

https://downloads.mysql.com/archives/get/p/10/file/mysql-connector-odbc-5.3.11-1.el7.x86_64.rpm

# rpm -ivh /opt/module/mysql-connector-odbc-5.3.11-1.el7.x86_64.rpm
Preparing...                          ########################################
Updating / installing...
mysql-connector-odbc-5.3.11-1.el7     ########################################
Success: Usage count is 1
Success: Usage count is 1

###
]# myodbc-installer -d -l

PostgreSQL
MySQL
MySQL ODBC 5.3 Unicode Driver
MySQL ODBC 5.3 ANSI Driver

# cat /etc/odbc.ini
[mysql]
Description     = Data source MySQL
Driver          = MySQL ODBC 5.3 Unicode Driver
Server          = 10.0.2.37
Host            = 10.0.2.37
Database        = v2xRTData
Port            = 32316
User            = root
Password        = 123456

# isql -v mysql
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

doris-be odbcinst.int 添加 ，无需重启 be 服务

添加 odbc resource | table

CREATE EXTERNAL RESOURCE `mysql_5_3_11`
PROPERTIES (
"host" = "10.0.2.37",
"port" = "32316",
"user" = "root",
"password" = "xxx",
"database" = "v2xRTData",
"table" = "v2xrtdata_test_table",
"driver" = "MySQL ODBC 5.3.11",  -- 注意这里的名称要和我们上面红框标识部分的[]里的名称一致
"odbc_type" = "mysql",
"type" = "odbc_catalog"
);

--- odbc table

CREATE EXTERNAL TABLE `test_odbc_5_3_11` (
vehicleId` varchar(50) ,
`timestamp` varchar(50) ,
...
`epbFlag` varchar(50) ,
`wiperState` varchar(50)
) ENGINE=ODBC
COMMENT "ODBC"
PROPERTIES (
"odbc_catalog_resource" = "mysql_5_3_11", --这里的名称就是我们上面定义的resource的名称
"database" = "v2xRTData",
"table" = "v2xrtdata_test_table"
);

---
mysql> select count(1) from v2xrtdata_test_table;
+----------+
| count(1) |
+----------+
|  2081366 |
+----------+
1 row in set (1.57 sec)

参考：https://zhuanlan.zhihu.com/p/452182695