使用SQL hints配置不同状态的TTL

本章节适用于MRS 3.6.0-LTS及以后版本。

Table API和SQL用户可以使用STATE_TTL提示，以更灵活的方式直接在查询中为常规连接和分组聚合指定自定义TTL值。

• 为join算子设置ttl SQL示例：

  CREATE TABLE source1 (
    `user_id` VARCHAR,
    `user_name` VARCHAR,
    `age` INT
  ) WITH (
    'connector' = 'kafka',
    'topic' = 'source1',
    'properties.bootstrap.servers' = 'Kafka的Broker实例业务IP:Kafka端口号',
    'scan.startup.mode' = 'latest-offset',
    'format' = 'csv'
  );
  CREATE TABLE source2 (
    `user_id` VARCHAR,
    `user_name` VARCHAR,
    `age` INT
  ) WITH (
    'connector' = 'kafka',
    'topic' = 'source2',
    'properties.bootstrap.servers' = 'Kafka的Broker实例业务IP:Kafka端口号',
    'scan.startup.mode' = 'latest-offset',
    'format' = 'csv'
  );
  CREATE TABLE print (
    `user_id` VARCHAR,
    `user_name` VARCHAR,
    `age` INT
  ) WITH ('connector' = 'print');
  INSERT INTO
    print
  SELECT
    /*+ STATE_TTL('s1'= '1d', 's2' = '20d') */
    s1.user_id,
    s1.user_name,
    s2.age
  FROM
    source1 s1
    left join source2 s2 ON s1.user_id = s2.user_id;

• 为aggregation算子设置ttl SQL示例：

  CREATE TABLE Orders (o_orderkey VARCHAR, o_totalprice INT) WITH (
    'connector' = 'kafka',
    'topic' = 'Orders',
    'properties.bootstrap.servers' = '192.168.67.13:21005',
    'scan.startup.mode' = 'latest-offset',
    'format' = 'csv'
  );
  CREATE TABLE print (o_orderkey VARCHAR, sum_pri INT) WITH ('connector' = 'print');
  INSERT INTO
    print
  SELECT
    /*+ STATE_TTL('o' = '1d') */
    o_orderkey,
    SUM(o_totalprice) AS revenue
  FROM
    Orders AS o
  GROUP BY
    o_orderkey;