Tencent Cloud

Recent Pages

ODBC 外表

最后更新时间：2024-07-04 15:39:03

说明：
本文档展示的内容仅适用于腾讯云数据仓库 TCHouse-D 1.1及以下版本，后续版本建议使用 Multi-Catalog 功能对接外部数据目录。
ODBC External Table 提供了腾讯云数据仓库 TCHouse-D 通过数据库访问的标准接口（ODBC）来访问外部表，外部表省去了繁琐的数据导入工作，让腾讯云数据仓库 TCHouse-D 可以具有了访问各式数据库的能力，并借助腾讯云数据仓库 TCHouse-D 本身的 OLAP 的能力来解决外部表的数据分析问题：
支持各种数据源接入腾讯云数据仓库 TCHouse-D。
支持腾讯云数据仓库 TCHouse-D 与各种数据源中的表联合查询，进行更加复杂的分析操作。
通过 insert into 将腾讯云数据仓库 TCHouse-D 执行的查询结果写入外部的数据源。
本文档主要介绍该功能的实现原理、使用方式等。
使用方法
腾讯云数据仓库 TCHouse-D 中创建 ODBC 的外表
具体建表语法参照：CREATE TABLE。
不使用 Resource 创建 ODBC 的外表
CREATE EXTERNAL TABLE `baseall_oracle` (
  `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" = "8086",
"user" = "test",
"password" = "test",
"database" = "test",
"table" = "baseall",
"driver" = "Oracle 19 ODBC driver",
"odbc_type" = "oracle"
);
通过 ODBC_Resource 来创建 ODBC 外表 (推荐使用的方式)
CREATE EXTERNAL RESOURCE `oracle_odbc`
PROPERTIES (
"type" = "odbc_catalog",
"host" = "192.168.0.1",
"port" = "8086",
"user" = "test",
"password" = "test",
"database" = "test",
"odbc_type" = "oracle",
"driver" = "Oracle 19 ODBC driver"
);
﻿
CREATE EXTERNAL TABLE `baseall_oracle` (
  `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" = "oracle_odbc",
"database" = "test",
"table" = "baseall"
);
参数说明：
参数
说明
hosts
外表数据库的 IP 地址
driver
ODBC 外表的 Driver名，该名字需要和 be/conf/odbcinst.ini 中的 Driver 名一致。
odbc_type
外表数据库的类型，当前支持 Oracle，Mysql，Postgresql
user
外表数据库的用户名
password
对应用户的密码信息
charset
数据库连接使用的字符集
说明
PROPERTIES 中除了可以添加上述参数之外，还支持每个数据库的 ODBC Driver 实现的专用参数，例如 Mysql 的sslverify 等。
ODBC Driver 的安装和配置
各大主流数据库都会提供 ODBC 的访问 Driver，用户可以参照各数据库官方推荐的方式安装对应的 ODBC Driver lib 库。
安装完成之后，查找对应的数据库的Driver Lib库的路径，并且修改 be/conf/odbcinst.ini 的配置：
[MySQL Driver]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc8w.so
FileUsage       = 1 
上述配置[] 里的对应的是 Driver 名，在建立外部表时需要保持外部表的 Driver 名和配置文件之中的一致。
Driver=  这个要根据实际 BE 安装 Driver 的路径来填写，本质上就是一个动态库的路径，这里需要保证该动态库的前置依赖都被满足。
注意
 切记，这里要求所有的 BE 节点都安装上相同的 Driver，并且安装路径相同，同时有相同的 be/conf/odbcinst.ini 的配置。
查询用法
完成建立 ODBC 外表后，除了无法使用 Doris 中的数据模型（rollup、预聚合、物化视图等）外，与普通的 Doris 表并无区别。
select * from oracle_table where k1 > 1000 and k3 ='term' or k4 like '%doris';
数据写入
在 Doris 中建立 ODBC 外表后，可以通过 insert into 语句直接写入数据，也可以将 Doris 执行完查询之后的结果写入 ODBC 外表，或者是从一个 ODBC 外表将数据导入另一个 ODBC 外表。
insert into oracle_table values(1, "doris");
insert into oracle_table select * from postgre_table;
事务
Doris 的数据是由一组 batch 的方式写入外部表的，如果中途导入中断，之前写入数据可能需要回滚。所以 ODBC 外表支持数据写入时的事务，事务的支持需要通过 session variable：enable_odbc_transcation 设置。
set enable_odbc_transcation = true; 
事务保证了 ODBC 外表数据写入的原子性，但是一定程度上会降低数据写入的性能，可以考虑酌情开启该功能。
数据库 ODBC 版本对应关系
Centos 操作系统
使用的 unixODBC 版本是：2.3.1，Doris 0.15，centos 7.9，全部使用 yum 方式安装。
Mysql
Mysql 版本
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
PostgreSQL
PostgreSQL 的 yum 源 rpm 包地址：
https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
这里面包含 PostgreSQL 从9.x 到 14.x的全部版本，包括对应的 ODBC 版本，可以根据需要选择安装。
PostgreSQL 版本
PostgreSQL ODBC 版本
12.9
postgresql12-odbc-13.02.0000
13.5
postgresql13-odbc-13.02.0000
14.1
postgresql14-odbc-13.02.0000
9.6.24
postgresql96-odbc-13.02.0000
10.6
postgresql10-odbc-13.02.0000
11.6
postgresql11-odbc-13.02.0000
Oracle
Oracle 版本
Oracle ODBC 版本
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
oracle-instantclient19.13-odbc-19.13.0.0.0
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
oracle-instantclient19.13-odbc-19.13.0.0.0
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
oracle-instantclient19.13-odbc-19.13.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
oracle-instantclient19.13-odbc-19.13.0.0.0
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
oracle-instantclient19.13-odbc-19.13.0.0.0
Oracle ODBC 驱动版本下载地址：
https://download.oracle.com/otn_software/linux/instantclient/1913000/oracle-instantclient19.13-sqlplus-19.13.0.0.0-2.x86_64.rpm
https://download.oracle.com/otn_software/linux/instantclient/1913000/oracle-instantclient19.13-devel-19.13.0.0.0-2.x86_64.rpm
https://download.oracle.com/otn_software/linux/instantclient/1913000/oracle-instantclient19.13-odbc-19.13.0.0.0-2.x86_64.rpm
https://download.oracle.com/otn_software/linux/instantclient/1913000/oracle-instantclient19.13-basic-19.13.0.0.0-2.x86_64.rpm
Ubuntu 操作系统
使用的 unixODBC 版本是：2.3.4，Doris 0.15，Ubuntu 20.04。
Mysql
Mysql 版本
Mysql ODBC 版本
8.0.27
8.0.11, 5.3.13
目前只测试了这一个版本其他版本测试后补充。
PostgreSQL
PostgreSQL版本
PostgreSQL ODBC版本
12.9
psqlodbc-12.02.0000
其他版本只要下载和数据库大版本相符合的 ODBC 驱动版本，这块后续会持续补充其他版本在 Ubuntu 系统下的测试结果。
Oracle
同上 Centos 操作系统的 Oracle 数据库及 ODBC 对应关系，在 ubuntu 下安装 rpm 软件包使用下面方式。
为了在 ubuntu 下可以进行安装 rpm 包，我们还需要安装一个 alien，这是一个可以将 rpm 包转换成 deb 安装包的工具。
sudo apt-get install alien
然后执行安装上面四个包：
sudo alien -i  oracle-instantclient19.13-basic-19.13.0.0.0-2.x86_64.rpm
sudo alien -i  oracle-instantclient19.13-devel-19.13.0.0.0-2.x86_64.rpm
sudo alien -i  oracle-instantclient19.13-odbc-19.13.0.0.0-2.x86_64.rpm
sudo alien -i  oracle-instantclient19.13-sqlplus-19.13.0.0.0-2.x86_64.rpm
类型匹配
各个数据库之间数据类型存在不同，这里列出了各个数据库中的类型和 Doris 之中数据类型匹配的情况。
MySQL
MySQL
Doris
替换方案
BOOLEAN
BOOLEAN
-
CHAR
CHAR
当前仅支持 UTF8编码
VARCHAR
VARCHAR
当前仅支持 UTF8编码
DATE
DATE
-
FLOAT
FLOAT
-
TINYINT
TINYINT
-
SMALLINT
SMALLINT
-
INT
INT
-
BIGINT
BIGINT
-
DOUBLE
DOUBLE
-
DATETIME
DATETIME
-
DECIMAL
DECIMAL
-
PostgreSQL
PostgreSQL
Doris
替换方案
BOOLEAN
BOOLEAN
-
CHAR
CHAR
当前仅支持 UTF8编码
VARCHAR
VARCHAR
当前仅支持 UTF8编码
DATE
DATE
-
REAL
FLOAT
-
SMALLINT
SMALLINT
-
INT
INT
-
BIGINT
BIGINT
-
DOUBLE
DOUBLE
-
TIMESTAMP
DATETIME
-
DECIMAL
DECIMAL
-
Oracle
Oracle
Doris
替换方案
不支持
BOOLEAN
Oracle 可用 number(1) 替换 boolean
CHAR
CHAR
-
VARCHAR
VARCHAR
-
DATE
DATE
-
FLOAT
FLOAT
-
无
TINYINT
Oracle 可由 NUMMBER 替换
SMALLINT
SMALLINT
-
INT
INT
-
无
BIGINT
Oracle 可由 NUMMBER 替换
无
DOUBLE
Oracle 可由 NUMMBER 替换
DATETIME
DATETIME
-
NUMBER
DECIMAL
-
SQLServer
SQLServer
Doris
替换方案
BOOLEAN
BOOLEAN
-
CHAR
CHAR
当前仅支持 UTF8编码
VARCHAR
VARCHAR
当前仅支持 UTF8编码
DATE
DATE
-
REAL
FLOAT
-
TINYINT
TINYINT
-
SMALLINT
SMALLINT
-
INT
INT
-
BIGINT
BIGINT
-
FLOAT
DOUBLE
-
DATETIME/DATETIME2
DATETIME
-
DECIMAL/NUMERIC
DECIMAL
-
最佳实践
适用于少数据量的同步。
例如 Mysql 中一张表有100万数据，想同步到 Doris，就可以采用 ODBC 的方式将数据映射过来，在使用 insert into 方式将数据同步到 Doris 中，如果想同步大批量数据，可以分批次使用 insert into 同步（不建议使用）。
常见问题
1. 与原先的 MySQL 外表的关系。
在接入 ODBC 外表之后，原先的访问 MySQL 外表的方式将被逐渐弃用。如果之前没有使用过 MySQL 外表，建议新接入的 MySQL 表直接使用 ODBC 的 MySQL 外表。
2. 除了MySQL，Oracle，PostgreSQL，SQLServer是否能够支持更多的数据库？
目前 Doris 只适配了 MySQL，Oracle，PostgreSQL，SQLServer，关于其他的数据库的适配工作正在规划之中，原则上来说任何支持 ODBC 访问的数据库都能通过 ODBC 外表来访问。如果您有访问其他外表的需求，欢迎修改代码并贡献给 Doris。
3. 什么场合适合通过外表访问？
通常在外表数据量较小，少于100W条时，可以通过外部表的方式访问。由于外表无法发挥 Doris 在存储引擎部分的能力和会带来额外的网络开销，所以建议根据实际对查询的访问时延要求来确定是否通过外部表访问还是将数据导入 Doris 之中。
4. 通过 Oracle 访问出现乱码？
尝试在 BE 启动脚本之中添加如下参数：export NLS_LANG=AMERICAN_AMERICA.AL32UTF8， 并重新启动所有 BE。
5. ANSI Driver or Unicode Driver？
当前 ODBC 支持 ANSI 与 Unicode 两种 Driver 形式，当前 Doris 只支持 Unicode Driver。如果强行使用 ANSI Driver 可能会导致查询结果出错。
6. 报错 driver connect Err: 01000 [unixODBC][Driver Manager]Can't open lib 'Xxx' : file not found (0) 。
没有在每一个BE上安装好对应数据的 Driver，或者是没有在 be/conf/odbcinst.ini 配置正确的路径，抑或是建表是 Driver 名与 be/conf/odbcinst.ini 不同。
7. 报错 Fail to convert odbc value 'PALO ' TO INT on column:'A'？
ODBC 外表的 A 列类型转换出错，说明外表的实际列与 ODBC 的映射列的数据类型不同，需要修改列的类型映射。
8. 同时使用旧的 MySQL 表与 ODBC 外表的 Driver 时出现程序 Crash？
这个是 MySQL 数据库的 Driver 与现有 Doris 依赖 MySQL 外表的兼容问题。推荐解决的方式如下：
方式1：通过 ODBC 外表替换旧的 MySQL 外表，并重新编译 BE，关闭 WITH_MYSQL 的选项。
方式2：不使用最新8.X 的 MySQL 的 ODBC Driver，而是使用5.X 的 MySQL 的 ODBC Driver。
9. 过滤条件下推？
当前 ODBC 外表支持过滤条件下推，目前 MySQL 的外表是能够支持所有条件下推的。其他的数据库的函数与 Doris 不同会导致下推查询失败。目前除 MySQL 外表之外，其他的数据库不支持函数调用的条件下推。Doris 是否将所需过滤条件下推，可以通过explain 查询语句进行确认。
10. 报错driver connect Err: xxx？
通常是连接数据库失败，Err 部分代表了不同的数据库连接失败的报错。这种情况通常是配置存在问题。可以检查是否错配了 IP 地址，端口或账号密码。
11. 读写 mysql 外表的 emoji 表情出现乱码？
Doris 进行 odbc 外表连接时，默认采用的编码为 utf8，由于 mysql 之中默认的 utf8编码为 utf8mb3，无法表示需要4字节编码的emoji表情。这里需要在建立 mysql 外表时设置charset=utf8mb4，便可以正常读写 emoji 表情。

联系我们

联系我们，为您的业务提供专属服务。

技术支持

如果你想寻求进一步的帮助，通过工单与我们进行联络。我们提供7x24的工单服务。

7x24 电话支持

tencent cloud

Recent Pages

ODBC 外表

使用方法

腾讯云数据仓库 TCHouse-D 中创建 ODBC 的外表

不使用 Resource 创建 ODBC 的外表

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

ODBC Driver 的安装和配置

查询用法

数据写入

事务

数据库 ODBC 版本对应关系

Centos 操作系统

Mysql

PostgreSQL

Oracle

Ubuntu 操作系统

Mysql

PostgreSQL

Oracle

类型匹配

MySQL

PostgreSQL

Oracle

SQLServer

最佳实践

常见问题

本页内容是否解决了您的问题？

本页内容是否解决了您的问题？

参数	说明
hosts	外表数据库的 IP 地址
driver	ODBC 外表的 Driver名，该名字需要和 be/conf/odbcinst.ini 中的 Driver 名一致。
odbc_type	外表数据库的类型，当前支持 Oracle，Mysql，Postgresql
user	外表数据库的用户名
password	对应用户的密码信息
charset	数据库连接使用的字符集

Mysql 版本	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

PostgreSQL 版本	PostgreSQL ODBC 版本
12.9	postgresql12-odbc-13.02.0000
13.5	postgresql13-odbc-13.02.0000
14.1	postgresql14-odbc-13.02.0000
9.6.24	postgresql96-odbc-13.02.0000
10.6	postgresql10-odbc-13.02.0000
11.6	postgresql11-odbc-13.02.0000

Oracle 版本	Oracle ODBC 版本
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production	oracle-instantclient19.13-odbc-19.13.0.0.0
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production	oracle-instantclient19.13-odbc-19.13.0.0.0
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production	oracle-instantclient19.13-odbc-19.13.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production	oracle-instantclient19.13-odbc-19.13.0.0.0
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production	oracle-instantclient19.13-odbc-19.13.0.0.0

PostgreSQL版本	PostgreSQL ODBC版本
12.9	psqlodbc-12.02.0000

MySQL	Doris	替换方案
BOOLEAN	BOOLEAN	-
CHAR	CHAR	当前仅支持 UTF8编码
VARCHAR	VARCHAR	当前仅支持 UTF8编码
DATE	DATE	-
FLOAT	FLOAT	-
TINYINT	TINYINT	-
SMALLINT	SMALLINT	-
INT	INT	-
BIGINT	BIGINT	-
DOUBLE	DOUBLE	-
DATETIME	DATETIME	-
DECIMAL	DECIMAL	-

tencent cloud

注册

登录

Recent Pages

ODBC 外表

使用方法

腾讯云数据仓库 TCHouse-D 中创建 ODBC 的外表

不使用 Resource 创建 ODBC 的外表

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

ODBC Driver 的安装和配置

查询用法

数据写入

事务

数据库 ODBC 版本对应关系

Centos 操作系统

Mysql

PostgreSQL

Oracle

Ubuntu 操作系统

Mysql

PostgreSQL

Oracle

类型匹配

MySQL

PostgreSQL

Oracle

SQLServer

最佳实践

常见问题

本页内容是否解决了您的问题？

本页内容是否解决了您的问题？