Apache Doris ELT 数据集成

javaX
162 阅读10分钟

Apache Doris ELT 数据集成

  1. 官方文档

    1. 跨源 数据处理 ： Doris 支持多种数据源连接器，可使用 SQL 对这些数据源进行联邦查询

      1. 数据源连接器：无论是 Hive、Iceberg、Hudi 、Paimon，还是支持 JDBC 协议的数据库系统，Doris 均能轻松连接并高效提取数据
      2. 支持数据源： Apache Hive、Apache Iceberg、Apache Hudi、Apache Paimon(Incubating)、Elasticsearch、MySQL、Oracle、SQL Server 等主流数据湖

    2. 数据导入方式：doris.apache.org/zh-CN/docs/…

    3. 外部数据源集成：通过与外部数据源的集成，实现对外部数据的查询和部分数据导入到 Doris 表中。

      1. 可以创建 Catalog 读取外部数据源中的数据，使用 INSERT INTO SELECT 将外部数据源中的数据同步写入到 Doris 中，配合 JOB 可以异步写入。
      2. 可以使用 X2Doris 将其他 AP 系统的数据迁移到 Doris 中。

  1. 数据集成ELT：支持外部数据源:

    1.   使用场景

    1. 用户希望将已经在 Doris 表中的数据进行 ETL 转换并导入到一个新的 Doris 表中，此时适合使用 INSERT INTO SELECT 语法。

      1. INSERT INTO 支持将 Doris 查询的结果导入到另一个表中。INSERT INTO 是一个同步导入方式，执行导入后返回导入结果。可以通过请求的返回判断导入是否成功。INSERT INTO 可以保证导入任务的原子性，要么全部导入成功，要么全部导入失败。

    2. 与 Multi-Catalog 外部表机制进行配合，如通过 Multi-Catalog 映射 MySQL 或者 Hive 系统中的表，然后通过 INSERT INTO SELECT 语法将外部表中的数据导入到 Doris 表中存储。

    3. 通过 Table Value Function（TVF）功能，可以直接将对象存储或 HDFS 上的文件作为 Table 进行查询，并且支持自动的列类型推断。然后，通过 INSERT INTO SELECT 语法将外部表中的数据导入到 Doris 表中存储。

    1.   基本原理

      在使用 INSERT INTO 时，需要通过 MySQL 协议发起导入作业给 FE 节点，FE 会生成执行计划，执行计划中前部是查询相关的算子，最后一个是 OlapTableSink 算子，用于将查询结果写到目标表中。执行计划会被发送给 BE 节点执行，Doris 会选定一个节点做为 Coordinator 节点，Coordinator 节点负责接受数据并分发数据到其他节点上。

    1.   通过外部表 Multi-Catalog 导入数据

    1. Doris 可以创建外部表。创建完成后，可以通过 INSERT INTO SELECT 的方式导入外部表的数据，当然也可以通过 SELECT 语句直接查询外部表的数据
    2. Doris 通过多源数据目录（Multi-Catalog）功能，支持了包括 Apache Hive、Apache Iceberg、Apache Hudi、Apache Paimon(Incubating)、Elasticsearch、MySQL、Oracle、SQL Server 等主流数据湖、数据库的连接访问。

    1.   创建 MySQL Catalog

    • MySQL 5.7, 8.0 或更高版本
    CREATE CATALOG mysql PROPERTIES ( "type" = "jdbc", "user" = "root", "password" = "secret", "jdbc_url" = "jdbc:mysql://example.net:3306", "driver_url" = "mysql-connector-j-8.3.0.jar", "driver_class" = "com.mysql.cj.jdbc.Driver" )

    1.   创建导入目标表

    1. 创建一张 Doris 的导入目标表
    CREATE TABLE `target_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"
);
    1. 关于创建 Doris 表的详细说明，请参阅 CREATE-TABLE 语法帮助。

    1.   导入数据 (从 mysql.db1.source_tbl 表导入到 target_tbl 表)

    INSERT INTO target_tbl SELECT k1,k2,k3 FROM  mysql.db1.source_tbl limit 100;

      INSERT 命令是同步命令，返回成功，即表示导入成功。

    1.    定时调度

    1. 官方文档：doris.apache.org/zh-CN/docs/…

    2. Job Scheduler：

      1. 为解决上述问题，Apache Doris 在 2.1 版本中引入了 Job Scheduler 功能，实现了自主任务调度能力，调度的精准度可达到秒级。该功能的推出不仅保障了数据导入的完整性和一致性，更让用户能够灵活、便捷调整调度策略。同时，因减少了对外部系统的依赖，也降低了系统故障的风险和运维成本，为社区用户带来更加统一、可靠的使用体验。

      2. Doris Job Scheduler 是一种基于预设计划运行的任务管理系统，能够在特定时间点或按照指定时间间隔触发预定义操作，实现任务的自动化执行。Job Scheduler 具备以下特点：

        1. 高效调度：Job Scheduler 可以在指定的时间间隔内安排任务和事件，确保数据处理的高效性。采用时间轮算法保证事件能够精准做到秒级触发。
        2. 灵活调度：Job Scheduler 提供了多种调度选项，如按 分、小时、天或周的间隔进行调度，同时支持一次性调度以及循环（周期）事件调度，并且周期调度也可以指定开始时间、结束时间。
        3. 事件池和高性能处理队列：Job Scheduler 采用 Disruptor 实现高性能的生产消费者模型，最大可能的避免任务执行过载。
        4. 调度记录可追溯：Job Scheduler 会存储最新的 Task 执行记录（可配置），通过简单的命令即可查看任务执行记录，确保过程可追溯。
        5. 高可用：依托于 Doris 自身的高可用机制，Job Schedule 可以很轻松的做到自恢复、高可用。

    3. 语法说明：

      1. 关键字 CREATE JOB 需加作业名称，它在数据库中标识唯一事件。

      2. ON SCHEDULE 子句用于指定 Job 作业的类型、触发时间和频率。

        1. AT timestamp 用于一次性事件。它指定 JOB 仅在给定的日期和时间执行一次，AT current_timestamp 指定当前日期和时间。因 JOB 一旦创建则会立即运行，也可用于异步任务创建。

        2. EVERY：用于周期性作业，可指定作业的执行频率，关键字后需指定时间间隔（周、天、小时、分钟）。

          1. Interval：用于指定作业执行频率。1 DAY 表示每天执行一次，1 HOUR 表示每小时执行一次，1 MINUTE 表示每分钟执行一次，1 WEEK 表示每周执行一次。
          2. 子句 EVERY 包含可选 STARTS 子句。STARTS 后面为 timestamp 值，该值用于定义开始重复的时间，CURRENT_TIMESTAMP 用于指定当前日期和时间。JOB 一旦创建则会立即运行。
          3. 子句 EVERY 包含可选 ENDS 子句。ENDS 关键字后面为 timestamp 值，该值定义 JOB 事件停止运行的时间。

      3. DO 子句用于指定 Job 作业触发时所需执行的操作，目前仅支持 Insert 语句。

      4. CREATE
JOB
  job_name
  ON SCHEDULE schedule
  [COMMENT 'string']
  DO execute_sql;

schedule: {
    AT timestamp
    | EVERY interval
    [STARTS timestamp ]
    [ENDS timestamp ]
}
interval:
    quantity { WEEK |DAY | HOUR | MINUTE}

    4. 使用示例：基于 Catalog 与 Job Scheduler 的数据自动同步

      1. 以某电商场景为例，用户常常需要从 MySQL 中提取业务数据，并将这些数据同步到 Doris 中进行数据分析，从而支持精准的营销活动。而 Job Scheduler 可与数据湖能力 Multi Catalog 配合，高效完成跨数据源的定期数据同步。
    CREATE TABLE IF NOT EXISTS user.activity (
    `user_id` INT NOT NULL,
    `date` DATE NOT NULL,
    `city` VARCHAR(20),
    `age` SMALLINT,
    `sex` TINYINT,
    `last_visit_date` DATETIME DEFAULT '1970-01-01 00:00:00',
    `cost` BIGINT DEFAULT '0',
    `max_dwell_time` INT DEFAULT '0',
    `min_dwell_time` INT DEFAULT '99999'
);
INSERT INTO user.activity VALUES
    (10000, '2017-10-01', '北京', 20, 0, '2017-10-01 06:00:00', 20, 10, 10),
    (10000, '2017-10-01', '北京', 20, 0, '2017-10-01 07:00:00', 15, 2, 2),
    (10001, '2017-10-01', '北京', 30, 1, '2017-10-01 17:05:00', 2, 22, 22),
    (10002, '2017-10-02', '上海', 20, 1, '2017-10-02 12:59:00', 200, 5, 5),
    (10003, '2017-10-02', '广州', 32, 0, '2017-10-02 11:20:00', 30, 11, 11),
    (10004, '2017-10-01', '深圳', 35, 0, '2017-10-01 10:00:00', 100, 3, 3),
    (10004, '2017-10-03', '深圳', 35, 0, '2017-10-03 10:20:00', 11, 6, 6);
    user_iddatecityagesexlast_visit_datecostmax_dwell_timemin_dwell_time
    100002017/10/1北京2002017/10/1 6:00201010
    100002017/10/1北京2002017/10/1 7:001522
    100012017/10/1北京3012017/10/1 17:0522222
    100022017/10/2上海2012017/10/2 12:5920055
    100032017/10/2广州3202017/10/2 11:20301111
    100042017/10/1深圳3502017/10/1 10:0010033
    100042017/10/3深圳3502017/10/3 10:201166
    1. 以上表为例，用户希望查询符合总消费金额、最后一次访问时间、性别、所在城市这几个数值条件的用户，并将满足条件的用户信息导入到 Doris 中，以便后续的定向推送。
    2. 首先，创建一张 Doris 表
    CREATE TABLE IF NOT EXISTS user_activity
  (
  `user_id` LARGEINT NOT NULL COMMENT "用户 id",
  `date` DATE NOT NULL COMMENT "数据灌入日期时间",
  `city` VARCHAR(20) COMMENT "用户所在城市",
  `age` SMALLINT COMMENT "用户年龄",
  `sex` TINYINT COMMENT "用户性别",
  `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
  `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
  `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
  `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
  )
  AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
  DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
  PROPERTIES (
  "replication_allocation" = "tag.location.default: 1"
  );
    1. 其次，创建对应 MySQL 库的 Catalog
    CREATE CATALOG mysql PROPERTIES (
  "type"="jdbc",
  "user"="root",
  "password"="123456",
  "jdbc_url" = "jdbc:mysql://127.0.0.1:3306/user?useSSL=false",
  "driver_url" = "mysql-connector-java-5.1.49.jar",
  "driver_class" = "com.mysql.jdbc.Driver"
  );

    1. 最后，将 MySQL 数据导入到 Doris 中。采用 Catalog + Insert Into 的方式来导入全量数据，由于全量导入操作可能会引发系统服务波动，通常选择在业务闲暇时进行操作。

    • 一次性调度：如下方代码所示，使用一次性任务来定时触发全量导入任务，触发时间为凌晨 3:00。
    CREATE JOB one_time_load_job
  ON SCHEDULE
  AT '2024-8-10 03:00:00'
  DO
  INSERT INTO user_activity SELECT * FROM mysql .user.activity
    • 周期调度：用户也可以创建一个周期性的调度任务，定期更新最新的数据。
    CREATE JOB schedule_load
  ON SCHEDULE EVERY 1 DAY
  DO
  INSERT INTO user_activity SELECT * FROM mysql .user.activity 
  where last_visit_date >=  days_add(now(),-1)

    1.   注意事项

    • 必须保证外部数据源与 Doris 集群是可以互通，包括 BE 节点和外部数据源的网络是互通的。

  1. 参考文件

    1. doris.apache.org/zh-CN/docs/…
    2. 数据无界、湖仓无界，Apache Doris 湖仓一体解决方案全面解读
avatar
文章
阅读
粉丝