Doris_Mysql 外部表配置流程

1.在每台 be节点安装对应mysql版本的Mysql Driver。

2.修改安装路径的 Driver 配置信息，进入Doris的安装路径 be/conf/odbcinst.ini 。
添加如下配置，Driver 为对应的驱动路径。

[MySQL ODBC 5.3.13]
Description     = ODBC for MySQL
Driver          = /data/doris/mysql-odbc-5.3.13/lib/libmyodbc5w.so
FileUsage       = 1

3. 创建统一管理外部表的连接配置

CREATE EXTERNAL RESOURCE `mysql_test_odbc`
PROPERTIES (
    "type" = "odbc_catalog",
    "host" = "192.168.0.10",
    "port" = "8086",
    "user" = "name",
    "password" = "pwd",
    "database" = "test_db",
    "odbc_type" = "mysql",
    "driver" = "MySQL ODBC 5.3.13"
);

删除资源：
DROP RESOURCE 'mysql_test_odbc'

4. 创建外部表，并引用连接配置

CREATE EXTERNAL TABLE `ext_mysql_demo` (
  `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_test_odbc",
    "database" = "test_db",
    "table" = "test_demo"
);

5. 创建Doris表

CREATE TABLE `doris_mysql_tbl` (
  `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 ""
)
COMMENT "Doris Table"
DISTRIBUTED BY HASH(k1) BUCKETS 2;
PROPERTIES (
    "replication_num" = "1"
);

6. 从外部表导入数据到Doris表

INSERT INTO doris_mysql_tbl SELECT k1,k2,k3 FROM ext_mysql_demo limit 100;