[大数据]Doris导入数据的方式

1. 通过命令导入csv数据

1.0 建表语句

mysql>  CREATE TABLE `wudl_doris` (
    ->    `id` int NULL COMMENT "",
    ->    `name` int NULL COMMENT "",
    ->    `address` string NULL COMMENT "",
    ->    `city` varchar(2000) NULL COMMENT "",
    ->    `phone` varchar(200) NULL COMMENT ""
    ->  ) ENGINE=OLAP
    ->  DUPLICATE KEY(`id`)
    ->  COMMENT "flink sink  测试表"
    ->  DISTRIBUTED BY HASH(`id`) BUCKETS 1
    ->  PROPERTIES (
    ->  "replication_num" = "3",
    ->  "in_memory" = "false",
    ->  "storage_format" = "V2"
    ->  );
Query OK, 0 rows affected (0.01 sec)

mysql> select * from wudl_doris;
Empty set (0.02 sec)

mysql> show create table wudl_doris;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                              |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wudl_doris | CREATE TABLE `wudl_doris` (
  `id` int(11) NULL COMMENT "",
  `name` int(11) NULL COMMENT "",
  `address` text NULL COMMENT "",
  `city` varchar(2000) NULL COMMENT "",
  `phone` varchar(200) NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`id`)
COMMENT "flink sink  测试表"
DISTRIBUTED BY HASH(`id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"in_memory" = "false",
"storage_format" = "V2"
);    |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from wudl_doris;
+------+------+---------------+------------+---------------+
| id   | name | address       | city       | phone         |
+------+------+---------------+------------+---------------+
|    1 |    2 | '广东省'      | '深圳'     | '18575697660' |
|    2 |    2 | '广东省01'    | '深圳01'   | '18575697660' |
+------+------+---------------+------------+---------------+
2 rows in set (0.01 sec)

mysql> 

1.1 文件内容：

-rw-r--r-- 1 root root        25 1月   2 22:11 test.csv 
[root@node01 datas]# vi test.csv 

2,3,'广东省03','深圳03','18575697660'

参数说明: 1. 分隔符 ： column_separator:,"— 表示就用逗号(,) 分割。

1.2 导入命令

curl --location-trusted -u root -T test.csv -H "label:testdoris111" -H "column_separator:," -XPUT http://192.168.1.161:8090/api/wudldb/wudl_doris/_stream_load

[root@node01 datas]# curl --location-trusted -u root -T test.csv -H "label:testdoris111" -H "column_separator:," -XPUT http://192.168.1.161:8090/api/wudldb/wudl_doris/_stream_load
Enter host password for user 'root':
{
    "TxnId": 12025,
    "Label": "testdoris111",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 1,
    "NumberLoadedRows": 1,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 44,
    "LoadTimeMs": 27,
    "BeginTxnTimeMs": 0,
    "StreamLoadPutTimeMs": 1,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 9,
    "CommitAndPublishTimeMs": 15
}

2. json 格式的文件内容导入

2.1 文件内容

[root@node01 datas]# vi doris.json

{"address":"广东省","city":"陕西","id":5,"name":5,"phone":"13004214933"}

2.2 命令：

命令：
curl --location-trusted -u root -T doris.json -H “label:testdoris1sdfa” -H “format:json” -H “column_separator:,” -XPUT http://192.168.1.161:8090/api/wudldb/wudl_doris/_stream_load

[root@node01 datas]# curl --location-trusted -u root -T doris.json -H "label:testdoris1sdfa"  -H "format:json" -H "column_separator:," -XPUT http://192.168.1.161:8090/api/wudldb/wudl_doris/_stream_load
Enter host password for user 'root':
{
    "TxnId": 12028,
    "Label": "testdoris1sdfa",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 1,
    "NumberLoadedRows": 1,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 79,
    "LoadTimeMs": 33,
    "BeginTxnTimeMs": 0,
    "StreamLoadPutTimeMs": 1,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 5,
    "CommitAndPublishTimeMs": 25
}
[root@node01 datas]# 

2.3 查询结果

mysql> select * from wudl_doris;
+------+------+---------------+------------+---------------+
| id   | name | address       | city       | phone         |
+------+------+---------------+------------+---------------+
|    1 |    2 | '广东省'      | '深圳'     | '18575697660' |
|    2 |    2 | '广东省01'    | '深圳01'   | '18575697660' |
|    5 |    5 | 广东省        | 陕西       | 13004214933   |
+------+------+---------------+------------+---------------+
3 rows in set (0.01 sec)

mysql> 

代码操作********************************

[root@node01 datas]# curl --location-trusted -u root -H "format: json" -H "strip_outer_array: true" -H "jsonpaths: [\"$.address\",\"$.city\",\"$.id\",\"$.name\",\"$.phone\"]" -T data.json http://192.168.1.161:8090/api/wudldb/wudl_doris01/_stream_load
Enter host password for user 'root':
{
    "TxnId": 14040,
    "Label": "420d13d2-533c-4498-94f1-8c4d966c1335",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 3,
    "NumberLoadedRows": 3,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 304,
    "LoadTimeMs": 27,
    "BeginTxnTimeMs": 0,
    "StreamLoadPutTimeMs": 1,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 6,
    "CommitAndPublishTimeMs": 18
}
[root@node01 datas]#