文档首页/ 表格存储服务 CloudTable/ 开发指南/ Doris应用开发指导/ 开发Doris冷热分离应用/ 样例代码
更新时间：2024-08-15 GMT+08:00
查看PDF
分享

样例代码

此章节主要介绍CloudTable Doris冷热分离的使用命令，以及冷数据如何自动存储到obs桶中。

样例代码

  1. 自动存储冷数据。
    创建冷数据自动归档到冷存储的数据迁移策略testPolicy。 
    CREATE STORAGE POLICY testPolicy
PROPERTIES(
"storage_resource" = "hot_to_cold",
"cooldown_ttl" = "1d"
);
  2. 创建test_tbl1数据表并关联数据迁移策略testPolicy。
    创建数据库。 
    CREATE DATABASE IF NOT EXISTS test_db;
    创建test_tbl1表并关联数据迁移策略。 
    CREATE TABLE IF NOT EXISTS test_db.test_tbl1
(
    `timestamp` DATETIME NOT NULL COMMENT "日志时间",
    `type` INT NOT NULL COMMENT "日志类型",
    `error_code` INT COMMENT "错误码",
    `error_msg` VARCHAR(1024) COMMENT "错误详细信息",
    `op_id` BIGINT COMMENT "负责人id",
    `op_time` DATETIME COMMENT "处理时间"
)
DISTRIBUTED BY HASH(`type`) BUCKETS 10
PROPERTIES (
"storage_policy" = "testPolicy",
"replication_num" = "3"
);
  3. 每天整点使用Broker Load导入批量数据到test_tbl1表，请参考Broker Load。

    创建数据库test_db。

    CREATE DATABASE test_db;

    使用数据库。

    use test_db;

    批量导入数据。

    LOAD LABEL brokerload_test_csv_label00001
(
DATA INFILE("obs://xxxxx/doris-data/data1/part-m-00002")
INTO TABLE `test_tbl1`
COLUMNS TERMINATED BY ','
FORMAT AS 'csv'
)
WITH BROKER "broker1"
(
"fs.obs.access.key" = "xxxxx",
"fs.obs.secret.key" = "xxxxx",
"fs.obs.endpoint" = "obs.xxx.xxx.com"
);

    查看数据插入进度。

    mysql> show load order by createtime desc limit 1\G;  
*************************** 1. row ***************************
         JobId: 18355
         Label: brokerload_test_csv_label00001
         State: FINISHED
      Progress: 100.00% (1/1)
          Type: BROKER
       EtlInfo: unselected.rows=0; dpp.abnorm.ALL=0; dpp.norm.ALL=9
      TaskInfo: cluster:broker1; timeout(s):14400; max_filter_ratio:0.0; priority:NORMAL
      ErrorMsg: NULL
    CreateTime: 2024-06-03 09:08:40
  EtlStartTime: 2024-06-03 09:08:44
 EtlFinishTime: 2024-06-03 09:08:44
 LoadStartTime: 2024-06-03 09:08:44
LoadFinishTime: 2024-06-03 09:08:46
           URL: NULL
    JobDetails: {"Unfinished backends":{"67bf44bed68d4d3a-9539b655e550e960":[]},"ScannedRows":9,"TaskNumber":1,"LoadBytes":506,"All backends":{"67bf44bed68d4d3a-9539b655e550e960":[10007]},"FileNumber":1,"FileSize":639}
 TransactionId: 143
  ErrorTablets: {}
          User: admin
       Comment: 
1 row in set (0.00 sec)

    fs.obs.access.key, fs.obs.secret.key,fs.obs.endpoint获取方式如下所示：

  4. 查询插入的数据。 
    mysql> select * from test_tbl1 limit 10;
+---------------------+------+------------+--------------------+--------+---------------------+
| timestamp           | type | error_code | error_msg          | op_id  | op_time             |
+---------------------+------+------------+--------------------+--------+---------------------+
| 2024-03-12 20:32:00 |    1 |        404 | Resource Not Found | 998756 | 2024-03-12 21:32:00 |
| 2024-03-12 22:35:00 |    1 |        404 | Resource Not Found | 998756 | 2024-03-12 23:35:00 |
| 2024-03-20 19:35:00 |    1 |        404 | Resource Not Found | 998756 | 2024-03-20 20:35:00 |
| 2024-03-21 14:39:00 |    1 |        404 | Resource Not Found | 998756 | 2024-03-21 15:39:00 |
| 2024-03-25 11:08:00 |    1 |        404 | Resource Not Found | 998756 | 2024-03-25 12:08:00 |
| 2024-03-26 10:33:00 |    1 |        404 | Resource Not Found | 998756 | 2024-03-26 11:33:00 |
| 2024-03-26 10:35:00 |    1 |        404 | Resource Not Found | 998756 | 2024-03-26 11:35:00 |
| 2024-03-26 10:36:00 |    1 |        404 | Resource Not Found | 998756 | 2024-03-26 11:36:00 |
| 2024-03-27 09:10:00 |    1 |        200 | ok                 | 998756 | 2024-03-27 10:10:00 |
+---------------------+------+------------+--------------------+--------+---------------------+
9 rows in set (0.16 sec)

相关文档