一、doris的外部表
略
二、创建resource
1. 安装ODBC插件
略
2. 创建ODBC
CREATE EXTERNAL RESOURCE `oracle_test_odbc`
PROPERTIES (
"type" = "odbc_catalog",
"host" = "192.168.0.10",
"port" = "8086",
"user" = "oracle",
"password" = "oracle",
"database" = "oracle",
"odbc_type" = "oracle",
"driver" = "Oracle"
);
3. 查看ODBC配置
mysql> show RESOURCES;
+------------+--------------+-----------+---------------+
| Name | ResourceType | Item | Value |
+------------+--------------+-----------+---------------+
| mysql_odbc | odbc_catalog | password | |
| mysql_odbc | odbc_catalog | driver | MySQL |
| mysql_odbc | odbc_catalog | port | 3606 |
| mysql_odbc | odbc_catalog | odbc_type | mysql |
| mysql_odbc | odbc_catalog | host | 172.24.51.218 |
| mysql_odbc | odbc_catalog | type | odbc_catalog |
| mysql_odbc | odbc_catalog | user | saas_crm |
+------------+--------------+-----------+---------------+
7 rows in set (0.02 sec)
说明：
· 数据横着看，password字段空，表示要么加密隐藏了，要么免密。
· name表示这是个mysql odbc
4. 修改
有2种方式：
1、使用sql方式实现
ALTER RESOURCE "mysql_odbc"
SET PROPERTIES (
"host" = "new_host_ip",
"port" = "new_port",
"user" = "new_user",
"password" = "new_password"
);
将 new_host_ip、new_port、new_user 和 new_password 替换为新的配置值。
修改完成后记得重新加载配置，无需重启服务。
ADMIN RELOAD CONFIG;
2、在配置文件中修改
略
三、不依赖resource创建外表
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"
);
四、使用resource创建外表
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"
);
数据库ODBE版本对应关系
五、
略
