flink批量写入clickhouse，频繁请求导致内存异常(非ck内置设置问题)

潘永青

已于 2022-03-23 11:03:20 修改

阅读量5.8k

点赞数 1

分类专栏：大数据开发文章标签： flink kafka clickhouse

于 2022-03-23 11:01:51 首次发布

本文链接：https://blog.csdn.net/u014539465/article/details/123680044

版权

大数据开发专栏收录该内容

23 篇文章 0 订阅

订阅专栏

一、问题背景

在综合决策平台客流预测实时计算过程中，flink30s步长窗口，需要实时大批量数据实时写入clikhouse，频繁请求导致内存异常。

User class threw exception: ru.yandex.clickhouse.except.ClickHouseException: ClickHouse exception, code: 241, host: xxx.xxx.xxx.xxx, port: 8123; Code: 241, e.displayText() = DB::Exception: Memory limit (for query) exceeded: would use 9.31 GiB (attempt to allocate chunk of 1048591 bytes), maximum: 9.31 GiB (version 19.9.5.36)

二、解决方案

1、修改users.xml

<max_memory_usage>15032385536</max_memory_usage>

没有解决根本问题

2.使用批量插入，可使用JDBCAppendTableSink

val sink2 = JDBCAppendTableSink
  .builder()
  .setDrivername("ru.yandex.clickhouse.ClickHouseDriver")
  .setDBUrl(Constants.CLICKHOUSE_URL)
  .setUsername(Constants.CLICKHOUSE_USERNAME)
  .setPassword(Constants.CLICKHOUSE_PASSWORD)
  .setQuery(sqlInsertIntoCapacity02)
  .setBatchSize(50) //批量值不够不会执行插入
  .setParameterTypes(Types.STRING, Types.STRING, Types.STRING, Types.STRING, Types.STRING, Types.STRING, Types.STRING, Types.STRING, Types.STRING, Types.STRING, Types.INT, Types.STRING, Types.STRING, Types.STRING, Types.STRING, Types.STRING, Types.INT)
  .build()

存在问题：批量值不够不会执行插入

3、通过keyBy分区，批量插入

.keyBy(ads => ads.time_id.toString)
.window(TumblingEventTimeWindows.of(Time.seconds(30)))
.process(new ProcessWindowFunction[XXX, List[XXX], String, TimeWindow] {
  override def process(key: String, context: Context, input: Iterable[XXX], out:    Collector[List[XXX]]): Unit = {
    val list = input.toList
    if (list.size > 0) {
      println("内部" + list.size)
      out.collect(list)
    }
  }
}).addSink(new JDBCSinkXXX)

class JDBCSinkXXX extends RichSinkFunction[List[XXX]] {

  // 定义连接、预编译语句
  var conn: Connection = _

  override def open(parameters: Configuration): Unit = {
    conn = DriverManager.getConnection("jdbc:clickhouse://xxxx:8123/db", "default", "123@123")
    conn.setAutoCommit(false); //关键，参考源码看注解
  }

  @Override
  override def invoke(list: List[xxx]): Unit = {
    var insertStmt: PreparedStatement = null
    try {
      insertStmt = conn.prepareStatement("insert into ads_index(xxx,xxx...) values (?,?,....)")
      for (pedNum <- list) {
        insertStmt.setString(1, "index_0011")
         ..........
        insertStmt.addBatch()
      }
      val count = insertStmt.executeBatch //批量执行
      conn.commit()
      insertStmt.close()
      System.out.println("成功了插入了" + count.length + "行数据")
    } catch {
      case e: SQLException => println(e)
    }
  }

  override def close(): Unit = {
    conn.close()
  }
}

以上是个人处理方式。