Doris通过ODBC驱动导入外部表数据

基础环境

Doris版本1.1

MySQL 5.7.32

CentOS 7 

//通过外部表导入数据
ODBC驱动创建外部表（前置条件）
安装Mysql ODBC驱动
这里我默认你是知道Mysql的安装方法，或者你已经有了Mysql数据库，对Mysql的安装配置就不在讲了，如果这块不清楚，请去百度。
①先安装odbc工具
yum install -y unixODBC.x86_64
从mysql 站点下载对应的驱动
②下载odbc驱动
https://dev.mysql.com/downloads/connector/odbc/

解压

tar -zxvf mysql-connector-odbc-5.3.14-linux-glibc2.12-x86-64bit.tar.gz
mv mysql-connector-odbc-5.3.14-linux-glibc2.12-x86-64bit mysql-connector-odbc

安装ODBC

./myodbc-installer -a -d -n "MySQL ODBC 5.3.14 Unicode Driver" -t "Driver=/root/doris/mysql-connector-odbc/lib/libmyodbc5w.so"
./myodbc-installer -a -d -n "MySQL ODBC 5.3.14 ANSI Driver" -t "Driver=/root/doris/mysql-connector-odbc/lib/libmyodbc5a.so"

③配置doris驱动(位置/root/doris/apache-doris-be-1.1.3-bin-x86_64/conf/odbcinst.ini)

# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[ODBC for MySQL]
Description     = ODBC for MySQL
Driver          = /root/doris/mysql-connector-odbc/lib/libmyodbc5w.so
FileUsage       = 1

///
1.创建 ODBC Resource

JDBC Resource 的目的是用于统一管理外部表的连接信息。
Drop RESOURCE `TableName_test_odbc`

CREATE EXTERNAL RESOURCE `TableName_test_odbc`
PROPERTIES (
    "type" = "odbc_catalog",
    "host" = "ip",
    "port" = "3306",
    "user" = "root",
    "password" = "",
    "database" = "mysqlDatabase",
    "table" = "TableName",
    "odbc_type" = "mysql",
     "jdbc_url"="jdbc:mysql://ip:3306/mysqlDatabase",
    "driver"="ODBC for MySQL",
    "driver_class"="com.mysql.jdbc.Driver"
)

2.创建外部表
(数据类型注意修改成Doris版本的)

CREATE EXTERNAL TABLE `ext_mysql_TableName` (
  `id` int(11) NOT NULL COMMENT '自增ID',
 /*此处省略所有字段*/
) ENGINE=ODBC
COMMENT "ODBC"
PROPERTIES (
    "odbc_catalog_resource" = "TableName_test_odbc",
    "database" = "mysqlDatabase",
    "table" = "TableName"
);

3.在Doris中创建表

CREATE  TABLE `doris_mysql_TableName` (
  `id` int(11) NOT NULL COMMENT '自增ID',
 
)
COMMENT "Doris Table"
DISTRIBUTED BY HASH(id) BUCKETS 2
PROPERTIES (
    "replication_num" = "1"
);

4. 导入数据 (从 ext_mysql_TableName 导入到 doris_mysql_TableName 表)

INSERT INTO doris_mysql_TableName SELECT * FROM ext_mysql_TableName;