Doris MySQL Load

发表于 分类于

Doris MySQL Load 的操作说明。

Doris 支持创建 Mysql 引擎的表，操作此表相当于操作远程 Mysql 表。可以用来导入Mysql 数据使用或测试。

Tips: Doris 中默认字段都是不能为空的，如果字段可能为空需要加上 null 标记。

例如, grade int(11) NULL DEFAULT NULL

MySql load DEMO 案例

创建 Mysql 测试用表及测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# Mysql 测试库 test_udp, 测试表 test_score
CREATE DATABASE IF NOT EXISTS test_udp;
ROP TABLE IF EXISTS `test_score`;
CREATE TABLE `test_score`  (
  `sno` varchar(7) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',
  `cno` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程号',
  `grade` int(11) NULL DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`sno`, `cno`) USING BTREE,
  INDEX `Cno`(`cno`) USING BTREE
)
ENGINE = InnoDB
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ROW_FORMAT = Dynamic;
 
# 测试表 test_score 导入数据
INSERT INTO `test_score` VALUES ('0811101', 'C001', 96);
INSERT INTO `test_score` VALUES ('0811101', 'C002', 80);
INSERT INTO `test_score` VALUES ('0811102', 'C002', 90);
INSERT INTO `test_score` VALUES ('0811102', 'C005', 73);
INSERT INTO `test_score` VALUES ('0811102', 'C007', NULL);
INSERT INTO `test_score` VALUES ('0811103', 'C001', 50);
INSERT INTO `test_score` VALUES ('0811103', 'C004', 80);

创建 Doris 表映射 Mysql 表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# 在 Doris test 库下创建映射 Mysql 的测试表 score
USE test;
CREATE TABLE `score`(
  `sno` varchar(7) NOT NULL,
  `cno` varchar(10) NOT NULL,
  `grade` int(11) NULL DEFAULT NULL
)
ENGINE = mysql
PROPERTIES
(
  "host" = "10.0.15.134",
  "port" = "3306",
  "user" = "root",
  "password" = "hmd@_#admin",
  "database" = "test_udp",
  "table" = "test_score"
);
 
 
# 查询 Doris 测试表 score
mysql> select * from score;
+---------+------+-------+
| sno     | cno  | grade |
+---------+------+-------+
| 0811101 | C001 |    96 |
| 0811101 | C002 |    80 |
| 0811102 | C002 |    90 |
| 0811102 | C005 |    73 |
| 0811102 | C007 |  NULL |
| 0811103 | C001 |    50 |
| 0811103 | C004 |    80 |
+---------+------+-------+
7 rows in set (0.03 sec)