flink sql parallelism mysql source - Flink菜鸟

flink sql parallelism mysql source

最近遇到个场景，需要对大表进行 Table Scan，使用官方的 jdbc connect, 发现在执行的时候，如果表的数据量很大（百万级别），在 select 阶段会消耗大量的时间，如下：

任务执行了 9 分钟多，数据才上来，数据上来后，差不多一批就全部上来了

差不多 10 分钟读完，还不能通过增加并行度的方式提高读取速度

并行 mysql source 实现

在自定义的 mysql lookup source 基础上添加 TableScan 的 Source

Source 继承 RichParallelSourceFunction

构造方法中接收配置参数
open 方法中创建 jdbc 连接，基于主键查询表中的最大值，最小值
run 方法中，基于任务的并行度，将数据按主键，均分给每个并行度
每个并行度分批次读取分给自己的数据

MysqlOption

并行 source，最重要的是基于键，将数据均分到每个并行度；同时在读取的时候，加入了批次概念，避免一次性读取太多数据（其实是抄的 flink cdc 的概念）
MysqlOption.java

public static final ConfigOption<Integer> TIME_OUT = ConfigOptions.key("timeout")
        .intType()
        .defaultValue(10);
public static final ConfigOption<Long> BATCH_SIZE = ConfigOptions.key("batch.size")
        .longType()
        .defaultValue(10000l)
        .withDescription("if batch.size max than (totalsize / subtask num), set batch.size as (totalsize / subtask num)");

TableFactory

在 factory 中添加为非必需参数，因为是和 lookup source 共用的

MysqlDynamicTableFactory.optionalOptions


@Override
public Set<ConfigOption<?>> optionalOptions() {
    final Set<ConfigOption<?>> options = new HashSet<>();
    // no optional option
    options.add(MysqlOption.CACHE_MAX_SIZE);
    options.add(MysqlOption.CACHE_EXPIRE_MS);
    options.add(MysqlOption.MAX_RETRY_TIMES);
    options.add(MysqlOption.TIME_OUT);
    // add for table scan source
    options.add(MysqlOption.KEY);
    options.add(MysqlOption.BATCH_SIZE);
    return options;
}

DynamicTableSource

在 DynamicTableSource 中添加一个 TableScan 的 source

@Override
public ScanRuntimeProvider getScanRuntimeProvider(ScanContext runtimeProviderContext) {

    final SourceFunction<RowData> sourceFunction
            = new MysqlSource(producedDataType, options);

    return SourceFunctionProvider.of(sourceFunction, false);
}

MysqlSource

主要的逻辑都在 MysqlSource

open 方法

创建 jdbc 连接，获取基于主键的最大、最小值

counter = new SimpleCounter();
this.counter = getRuntimeContext()
        .getMetricGroup()
        .counter("myCounter");
// jdbc connection
conn = DriverManager.getConnection(this.options.getUrl(), this.options.getUsername(), this.options.getPassword());
// 获取主键
String key = this.options.getKey();
// 如果没有配置主键
if (key != null) {
    // find min/max
    String sql = "select min(" + key + ") as min, max(" + key + ") as max from " + this.options.getTable();
    PreparedStatement ps = conn.prepareStatement(sql);
    ResultSet resultSet = ps.executeQuery();
    resultSet.next();
    // todo cdc data split algorithm ChunkSplitter.splitTableIntoChunks
    // flink cdc key only support number type
    min = resultSet.getLong(1);
    max = resultSet.getLong(2);
} else {
    hasKey = false;
}
LOG.info("load table min {}, max {}", min, max);

run 方法

核心方法：计算当前并行度的数据范围，并执行查询

@Override
public void run(SourceContext<RowData> ctx) throws Exception {
    // splicing query sql
    String sql = makeupSql();
    PreparedStatement ps = conn.prepareStatement(sql);
    // Split by primary key for subtask
    int index = getRuntimeContext().getIndexOfThisSubtask();
    int total = getRuntimeContext().getNumberOfParallelSubtasks();
    // cal current subtask data range, if not config key, data ranger (-1,0)
    long size = (max - min);
    long avg = (long) Math.floor(size / total);
    if (this.options.getBatchSize() > size) {
        this.batchSize = avg;
    }
    // subtask start index : min + index * 平均数量
    long indexStart = min + index * avg;
    long indexEnd = min + (index + 1) * avg;
    // if index 是最大那个，把结尾哪些一块包含进去
    if (index == total - 1) {
        // include max id
        indexEnd = max + 1;
    }

    LOG.info("subtask index : {}, data range : {} to {}， -1 means no split", index, indexStart, indexEnd);
    // query data in batch
    for (; indexStart < indexEnd; indexStart += batchSize) {
        long currentEnd = indexStart + this.options.getBatchSize() > indexEnd ? indexEnd : indexStart + this.options.getBatchSize();
        LOG.info("subtask index {} start process : {} to {}", index, indexStart, currentEnd);
        if (hasKey) {
            ps.setLong(1, indexStart);
            ps.setLong(2, currentEnd);
        }
        ResultSet resultSet = ps.executeQuery();
        // loop result set
        while (isRunning && resultSet.next()) {
            queryAndEmitData(ctx, resultSet);
        }
        // if no key, only exec once
        if (!hasKey) {
            break;
        }
    }
    LOG.info("subtask {} finish scan", index);
}

查询数据库，发出结果

// 查询 数据库，发出返回结果
private void queryAndEmitData(SourceContext<RowData> ctx, ResultSet resultSet) throws SQLException {
    GenericRowData result = new GenericRowData(fieldCount);
    result.setRowKind(RowKind.INSERT);
    for (int i = 0; i < fieldCount; i++) {
        LogicalType type = rowType.getTypeAt(i);
        String value = resultSet.getString(i + 1);
        Object fieldValue = RowDataConverterBase.createConverter(type, value);

        result.setField(i, fieldValue);
        // out result
    }
    ctx.collect(result);
    this.counter.inc();
}

构造 sql

private String makeupSql() {
    StringBuilder builder = new StringBuilder();

    builder.append("select ");
    for (int i = 0; i < fieldCount; i++) {
        if (i == fieldCount - 1) {
            builder.append(fieldNames[i]).append(" ");
        } else {
            builder.append(fieldNames[i]).append(",");
        }
    }
    builder.append("from ");
    builder.append(this.options.getDatabase()).append(".");
    builder.append(this.options.getTable());
    if (hasKey) {
        builder.append(" where " + this.options.getKey() + " >= ?");
        builder.append(" and " + this.options.getKey() + " < ?");
    }

    return builder.toString();
}

测试

基于自定义的 TableScan source，查询一个 99 万数据的表

sql 如下：

create table cust_mysql_user_log
(
    user_id STRING,
    sex                      STRING,
    age                      INTEGER,
    degree                   STRING,
    address                  STRING,
    work_address             STRING,
    income_range             STRING,
    default_shipping_address STRING,
    register_date            TIMESTAMP(3)
) WITH (
      'connector' = 'cust-mysql'
      ,'url' = 'jdbc:mysql://10.201.0.166:3306/shell1?useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true'
      ,'username' = 'root'
      ,'password' = 'daas2020'
      ,'database' = 'shell1'
      ,'table' = 'user_info'
--       ,'key' = 'id'
      ,'batch.size' = '10000'
      )
;

create table cust_mysql_user_log_sink
(
    user_id STRING,
    sex                      STRING,
    age                      INTEGER,
    degree                   STRING,
    address                  STRING,
    work_address             STRING,
    income_range             STRING,
    default_shipping_address STRING,
    register_date            TIMESTAMP(3)
) WITH (
      'connector' = 'kafka'
      ,'topic' = 'user_log_sink'
      ,'properties.bootstrap.servers' = 'localhost:9092'
      ,'properties.group.id' = 'user_log'
      ,'scan.startup.mode' = 'latest-offset'
      ,'format' = 'json'
)
;

insert into cust_mysql_user_log_sink
select user_id, sex, age, degree, address, work_address, income_range,default_shipping_address, register_date
from cust_mysql_user_log;

6并行度执行并查看结果（单并行度的执行没有意义）

数据分片

查看任务执行日志可以看到 1分半，数据就全部就读取完成了

2022-07-29 18:55:30,965 INFO  - subtask index 3 start process : 504998 to 514998
2022-07-29 18:55:30,975 INFO  - subtask index 4 start process : 669997 to 679997
2022-07-29 18:55:30,990 INFO  - subtask index 1 start process : 175000 to 185000
2022-07-29 18:55:30,990 INFO  - subtask index 0 start process : 10001 to 20001
2022-07-29 18:55:30,996 INFO  - subtask index 5 start process : 834996 to 844996
2022-07-29 18:55:31,084 INFO  - subtask index 2 start process : 339999 to 349999
2022-07-29 18:55:34,567 INFO  - subtask index 1 start process : 185000 to 195000
.....

2022-07-29 18:56:48,251 INFO  - subtask index 1 start process : 325000 to 329999

2022-07-29 18:56:50,902 INFO  - subtask 1 finish scan

2022-07-29 18:56:52,590 INFO  - subtask index 4 start process : 819997 to 824996
2022-07-29 18:56:53,657 INFO  - subtask index 5 start process : 984996 to 990001
2022-07-29 18:56:55,418 INFO  - subtask 4 finish scan
2022-07-29 18:56:56,356 INFO  - subtask 5 finish scan

2022-07-29 18:57:02,572 INFO  - subtask index 0 start process : 160001 to 165000
2022-07-29 18:57:02,861 INFO  - subtask index 2 start process : 489999 to 494998
2022-07-29 18:57:03,451 INFO  - subtask index 3 start process : 654998 to 659997
2022-07-29 18:57:04,079 INFO  - subtask 0 finish scan
2022-07-29 18:57:04,325 INFO  - subtask 2 finish scan
2022-07-29 18:57:04,836 INFO  - subtask 3 finish scan

完整代码参数：github sqlSubmit

欢迎关注Flink菜鸟公众号，会不定期更新Flink（开发技术）相关的推文

posted on 2022-08-10 16:47 Flink菜鸟阅读(1365) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部