CentOS下 Apache Doris Oracle ODBC外表使用指南-阿里云开发者社区

1.软件环境

操作系统：CentOS 7.8
Apache Doris ：0.15
Postgresql数据库：oracle 19c
UnixODBC：2.3.1
Oracle ODBC ：instantclient-odbc-linux.x64-19.13.0.0.0dbru

2.Oracle安装部署

因为是测试没有Oracle的环境，由于安装比较繁琐耗时，所以通过Docker进行快速安装部署,下面给出各个版本的Docker镜像地址

2.1 Oracle 21C

# 从Docker hub下载，网络不好时，一般比较慢 
docker pull lhrbest/oracle21c_ee_db_21.3.0.0 
# 可以选择从阿里云下载 
docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/oracle21c_ee_db_21.3.0.0 
# 从阿里云下载后可以tag成如下形式 
docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/oracle21c_ee_db_21.3.0.0 lhrbest/oracle21c_ee_db_21.3.0.0

启动容器

# 1、创建容器 
docker run -d --name lhroracle21c -h lhroracle21c \
-p 5510:5500 -p 55100:5501 -p 1530:1521  -p 3400:3389 \
-v /sys/fs/cgroup:/sys/fs/cgroup \
--privileged=true lhrbest/oracle21c_ee_db_21.3.0.0 \
/usr/sbin/init 
# 2、进入容器 
docker exec -it lhroracle21c bash 
# 3、启动监听和数据库 
su - oracle 
sas 
startup 
lsnrctl start

2.2 Oracle 19C

2.3 Oracle 18C

## 拉取镜像
docker pull registry.cn-hangzhou.aliyuncs.com/zhengqing/oracle18c
## 运行容器
docker run -d --name oracle18c -p 1521:1521 registry.cn-hangzhou.aliyuncs.com/zhengqing/oracle18c

2.4 Oracle 12C

##拉取镜像
docker pull quay.io/maksymbilenko/oracle-12c
##运行容器
docker run -d -p 8080:8080 -p 1521:1521 -v /my/oracle/data:/u01/app/oracle quay.io/maksymbilenko/oracle-12c

2.5 Oracle 11g

##拉取镜像
docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
##运行容器
docker run -d -p 1521:1521 --name oracle11g registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g

3.安装unixODBC驱动

首先我们安装unixODBC驱动、这里直接给出驱动的安装命令

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

安装完成之后执行odbcinst -j,看到下面信息说明安装成功

#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

4.安装Oracle ODBC驱动及测试

4.1 安装驱动

我们需要下载下面这几个安装包：

oracle-instantclient19.13-basic-19.13.0.0.0-2.x86_64.rpm
oracle-instantclient19.13-devel-19.13.0.0.0-2.x86_64.rpm
oracle-instantclient19.13-odbc-19.13.0.0.0-2.x86_64.rpm
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-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

然后执行安装上面四个包

rpm -ivh  oracle-instantclient19.13-basic-19.13.0.0.0-2.x86_64.rpm
rpm -ivh  oracle-instantclient19.13-devel-19.13.0.0.0-2.x86_64.rpm
rpm -ivh  oracle-instantclient19.13-odbc-19.13.0.0.0-2.x86_64.rpm
rpm -ivh  oracle-instantclient19.13-sqlplus-19.13.0.0.0-2.x86_64.rpm

验证我们安装的ODBC驱动动态链接库是否正确

root@iZbp1cq4g9n8chsy7hcafhZ:~/doris/be/conf# ldd /usr/lib/oracle/19.13/client64/lib/libsqora.so.19.1
 linux-vdso.so.1 (0x00007ffefef27000)
 libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f43e80b0000)
 libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f43e7f61000)
 libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f43e7f3e000)
 libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007f43e7f21000)
 librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f43e7f16000)
 libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007f43e7f11000)
 libresolv.so.2 => /lib/x86_64-linux-gnu/libresolv.so.2 (0x00007f43e7ef3000)
 libclntsh.so.19.1 => /usr/lib/oracle/19.13/client64/lib/libclntsh.so.19.1 (0x00007f43e3d6f000)
 libclntshcore.so.19.1 => /usr/lib/oracle/19.13/client64/lib/libclntshcore.so.19.1 (0x00007f43e37cb000)
 libodbcinst.so.2 => /usr/local/lib/libodbcinst.so.2 (0x00007f43e37b3000)
 libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f43e35c1000)
 /lib64/ld-linux-x86-64.so.2 (0x00007f43e8379000)
 libnnz19.so => /usr/lib/oracle/19.13/client64/lib/libnnz19.so (0x00007f43e2f4a000)
 libltdl.so.7 => /lib/x86_64-linux-gnu/libltdl.so.7 (0x00007f43e2f3d000)

4.2 配置环境变量

sudo vi ~/.bashrc

加入下面的内容

export ORACLE_HOME=/usr/lib/oracle/19.13/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=LEI
export PATH=$ORACLE_HOME/bin:$PATH

4.3 配置tnsnames.ora文件

cd /usr/lib/oracle/19.13/client64
mkdir -p network/admin
vi tnsnames.ora

加入下面的内容（注意修改成自己的，这里是示例）

demo =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.7.211)(PORT = 1521))
 )
 (CONNECT_DATA =
 (  SERVICE_NAME = LEI)
 )
 )

注意

将HOST、PORT换成你自己的

SERVICE_NAME :这个是我们启动Oracle docker的时候设置的ORACLE_SID

4.4 配置odbcinst.ini

这里使用RPM包安装的额ODBC驱动动态链接库在

/usr/lib/oracle/19.13/client64/lib/目录下

root@iZbp1cq4g9n8chsy7hcafhZ:~/instantclient_21_4# ll /usr/lib/oracle/19.13/client64/lib/
total 236232
drwxr-xr-x 3 root root      4096 Jan  2 11:04 ./
drwxr-xr-x 5 root root      4096 Jan  1 19:47 ../
-rw-r--r-- 1 root root       342 Nov 27 02:41 glogin.sql
lrwxrwxrwx 1 root root        21 Jan  1 19:43 libclntshcore.so -> libclntshcore.so.19.1*
-rwxr-xr-x 1 root root   8057664 Nov 27 02:39 libclntshcore.so.19.1*
lrwxrwxrwx 1 root root        17 Jan  1 19:43 libclntsh.so -> libclntsh.so.19.1*
lrwxrwxrwx 1 root root        17 Jan  1 19:43 libclntsh.so.10.1 -> libclntsh.so.19.1*
lrwxrwxrwx 1 root root        17 Jan  1 19:43 libclntsh.so.11.1 -> libclntsh.so.19.1*
lrwxrwxrwx 1 root root        17 Jan  1 19:43 libclntsh.so.12.1 -> libclntsh.so.19.1*
lrwxrwxrwx 1 root root        17 Jan  1 19:43 libclntsh.so.18.1 -> libclntsh.so.19.1*
-rwxr-xr-x 1 root root  81679160 Nov 27 02:39 libclntsh.so.19.1*
-rwxr-xr-x 1 root root   3642520 Nov 27 02:39 libipc1.so*
-rwxr-xr-x 1 root root    478728 Nov 27 02:39 libmql1.so*
-rwxr-xr-x 1 root root   5831752 Nov 27 02:39 libnnz19.so*
-rwxr-xr-x 1 root root   2342024 Nov 27 02:39 libocci.so.19.1*
-rwxr-xr-x 1 root root 130543568 Nov 27 02:39 libociei.so*
-rwxr-xr-x 1 root root    153464 Nov 27 02:39 libocijdbc19.so*
-rwxr-xr-x 1 root root    116376 Nov 27 02:39 liboramysql19.so*
-rwxr-xr-x 1 root root   1660776 Nov 27 02:41 libsqlplusic.so*
-rwxr-xr-x 1 root root   1572432 Nov 27 02:41 libsqlplus.so*
-rwxr-xr-x 1 root root   1070192 Nov 27 02:41 libsqora.so.19.1* ---这里是要使用的ODBC动态链接库
drwxr-xr-x 3 root root      4096 Jan  1 19:43 network/
-rw-r--r-- 1 root root   4355723 Nov 27 02:39 ojdbc8.jar
-rw-r--r-- 1 root root    313026 Nov 27 02:41 ottclasses.zip
-rw-r--r-- 1 root root     37519 Nov 27 02:39 xstreams.jar

编辑/etc/odbcinst.ini，在最后加上下面的内容

[Oracle 19 ODBC driver]
Description     = Oracle ODBC driver for Oracle 19
Driver          = /usr/lib/oracle/19.13/client64/lib/libsqora.so.19.1
Setup           =
FileUsage       =
CPTimeout       =
CPReuse         =

配置odbc.ini，在最后加上下面的内容

[oracle]
Driver = Oracle 19 ODBC driver ---这里的名称是上面odbcinst.ini里oracle部分用[]括起来的内容
ServerName =172.16.192.81:1521/LEI --这里是你的oracle数据ip地址，端口及SID
UserID = C##dbuser  --这里是我们上面创建的用户名
Password = zhangfeng   --密码

验证ODBC

isql oracle
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

显示一切正常

5.Apache Doris Oracle外表验证

5.1 修改配置

修改BE节点conf/odbcinst.ini文件,加入刚才/etc/odbcinst.ini添加的一样内容，并删除原先的Oracle配置,加上你刚才安装的，如下：

[Oracle 19 ODBC driver]
Description     = Oracle ODBC driver for Oracle 19
Driver          = /usr/lib/oracle/19.13/client64/lib/libsqora.so.19.1

5.2 验证

创建oracle的ODBC Resource

CREATE EXTERNAL RESOURCE `oracle_19`
 PROPERTIES (
 "host" = "172.16.192.81",
 "port" = "1521",
 "user" = "C##dbuser",
 "password" = "zhangfeng",
 "database" = "LEI", --这里是你的数据库示例名称，也就是我们在docker启动时的ORACLE_SID
 "driver" = "Oracle 19 ODBC driver",   ---这里的名称一定和你在be odbcinst.ini里的oracle部分的[]里的内容一样，重要
 "odbc_type" = "oracle",
 "type" = "odbc_catalog"
 );

创建ODBC外表

CREATE EXTERNAL TABLE `oracle_odbc` (
 person_id int,
 first_name VARCHAR(50) NOT NULL,
 last_name VARCHAR(50) NOT NULL
) ENGINE=ODBC
COMMENT "ODBC"
PROPERTIES (
 "odbc_catalog_resource" = "oracle_19", 
 "database" = "LEI",
 "table" = "persons"
);

下面我们看执行结果

mysql>  CREATE EXTERNAL RESOURCE `oracle_19`
 ->  PROPERTIES (
 ->  "host" = "172.16.192.81",
 ->  "port" = "1521",
 ->  "user" = "C##dbuser",
 ->  "password" = "zhangfeng",
 ->  "database" = "LEI",
 ->  "driver" = "Oracle 19 ODBC driver",
 ->  "odbc_type" = "oracle",
 ->  "type" = "odbc_catalog"
 ->  );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>  CREATE EXTERNAL TABLE `oracle_odbc` (
 ->     person_id int,
 ->     first_name VARCHAR(50) NOT NULL,
 ->     last_name VARCHAR(50) NOT NULL
 -> ) ENGINE=ODBC
 -> COMMENT "ODBC"
 -> PROPERTIES (
 -> "odbc_catalog_resource" = "oracle_19",
 -> "database" = "LEI",
 -> "table" = "persons"
 -> );
Query OK, 0 rows affected (0.01 sec)
mysql> select * from oracle_odbc;
+-----------+------------+-----------+
| person_id | first_name | last_name |
+-----------+------------+-----------+
|         1 | zhang      | feng      |
|         2 | 张峰       | 峰        |
|         3 | zhangfeng  | 峰值      |
+-----------+------------+-----------+
3 rows in set (0.06 sec)

OK一切正常，正常情况下，Oracle ODBC驱动只要你的数据库版本和你的ODBC驱动版本（大版本号对上就行）一致，就基本不会出问题。

6.Oracle 各个版本和ODBC驱动程序的对应关系

这个同样适用于Ubuntu操作系统下

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

CentOS下 Apache Doris Oracle ODBC外表使用指南

1.软件环境

2.Oracle安装部署

2.1 Oracle 21C

2.2 Oracle 19C

2.3 Oracle 18C

2.4 Oracle 12C

2.5 Oracle 11g

3.安装unixODBC驱动

4.安装Oracle ODBC驱动及测试

4.1 安装驱动

4.2 配置环境变量

4.3 配置tnsnames.ora文件

4.4 配置odbcinst.ini

5.Apache Doris Oracle外表验证

5.1 修改配置

5.2 验证

6.Oracle 各个版本和ODBC驱动程序的对应关系

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

CentOS下 Apache Doris Oracle ODBC外表使用指南

1.软件环境

2.Oracle安装部署

2.1 Oracle 21C

2.2 Oracle 19C

2.3 Oracle 18C

2.4 Oracle 12C

2.5 Oracle 11g

3.安装unixODBC驱动

4.安装Oracle ODBC驱动及测试

4.1 安装驱动

4.2 配置环境变量

4.3 配置tnsnames.ora文件

4.4 配置odbcinst.ini

5.Apache Doris Oracle外表验证

5.1 修改配置

5.2 验证

6.Oracle 各个版本和ODBC驱动程序的对应关系

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像