史上最全干货！Flink SQL 成神之路（一）

import org.apache.flink.table.api.*;
import static org.apache.flink.table.api.Expressions.*;
EnvironmentSettings settings = EnvironmentSettings
    .newInstance()
    .inStreamingMode()
    .build();
TableEnvironment tEnv = TableEnvironment.create(settings);
// 下面就是 Table API 的案例，其语义等同于
// select a, count(b) as cnt 
// from Orders
// group by a
DataSet<Row> result = tEnv
        .from("Orders")
        .groupBy($("a"))
        .select($("a"), $("b").count().as("cnt"))
        .toDataSet(counts, Row.class);
result.print();
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

insert into target
select a, count(b) as cnt
from Orders
group by a
1.
2.
3.
4.

<dependency>
  <groupId>org.apache.flink</groupId>
  <artifactId>flink-table-api-java-bridge_2.11</artifactId>
  <version>1.13.5</version>
</dependency>
<dependency>
  <groupId>org.apache.flink</groupId>
  <artifactId>flink-table-planner-blink_2.11</artifactId>
  <version>1.13.5</version>
</dependency>
<dependency>
  <groupId>org.apache.flink</groupId>
  <artifactId>flink-streaming-java_2.11</artifactId>
  <version>1.13.5</version>
</dependency>
<dependency>
  <groupId>org.apache.flink</groupId>
  <artifactId>flink-table-common</artifactId>
  <version>1.13.5</version>
</dependency>
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

<dependency>
  <groupId>org.apache.flink</groupId>
  <artifactId>flink-table-api-scala-bridge_2.11</artifactId>
  <version>1.13.5</version>
</dependency>
<dependency>
  <groupId>org.apache.flink</groupId>
  <artifactId>flink-table-planner-blink_2.11</artifactId>
  <version>1.13.5</version>
  <scope>provided</scope>
</dependency>
<dependency>
  <groupId>org.apache.flink</groupId>
  <artifactId>flink-streaming-scala_2.11</artifactId>
  <version>1.13.5</version>
  <scope>provided</scope>
</dependency>
<dependency>
  <groupId>org.apache.flink</groupId>
  <artifactId>flink-table-common</artifactId>
  <version>1.13.5</version>
</dependency>
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

// 创建一个 TableEnvironment，为后续使用 SQL 或者 Table API 提供上线
EnvironmentSettings settings = EnvironmentSettings
    .newInstance()
    .inStreamingMode() // 声明为流任务
    //.inBatchMode() // 声明为批任务
    .build();
TableEnvironment tEnv = TableEnvironment.create(settings);
// 创建一个输入表
tableEnv.executeSql("CREATE TEMPORARY TABLE table1 ... WITH ( 'connector' = ... )");
// 创建一个输出表
tableEnv.executeSql("CREATE TEMPORARY TABLE outputTable ... WITH ( 'connector' = ... )");
// 1. 使用 Table API 做一个查询并返回 Table
Table table2 = tableEnv.from("table1").select(...);
// 2. 使用 SQl API 做一个查询并返回 Table
Table table3 = tableEnv.sqlQuery("SELECT ... FROM table1 ... ");
// 将 table2 的结果使用 Table API 写入 outputTable 中，并返回结果
TableResult tableResult = table2.executeInsert("outputTable");
tableResult...
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

import org.apache.flink.table.api.EnvironmentSettings;
import org.apache.flink.table.api.TableEnvironment;
// 1. 就是设置一些环境信息
EnvironmentSettings settings = EnvironmentSettings
    .newInstance()
    .inStreamingMode() // 声明为流任务
    //.inBatchMode() // 声明为批任务
    .build();
// 2. 创建 TableEnvironment
TableEnvironment tEnv = TableEnvironment.create(settings);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.EnvironmentSettings;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;
StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
StreamTableEnvironment tEnv = StreamTableEnvironment.create(env);
1.
2.
3.
4.
5.

tableEnv.executeSql("CREATE TEMPORARY TABLE table1 ... WITH ( 'connector' = ... )");
1.

tableEnv.executeSql("CREATE TEMPORARY TABLE mydatabase.table1 ... WITH ( 'connector' = ... )");
1.

-- 临时外部表
CREATE TEMPORARY TABLE source_table (
    user_id BIGINT,
    `name` STRING
) WITH (
  'connector' = 'user_defined',
  'format' = 'json',
  'class.name' = 'flink.examples.sql._03.source_sink.table.user_defined.UserDefinedSource'
);
-- 临时视图
CREATE TEMPORARY VIEW query_view as
SELECT
    *
FROM source_table
;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

-- 永久外部表。需要外部 Catalog 持久化！！！
CREATE TABLE source_table (
    user_id BIGINT,
    `name` STRING
) WITH (
  'connector' = 'user_defined',
  'format' = 'json',
  'class.name' = 'flink.examples.sql._03.source_sink.table.user_defined.UserDefinedSource'
);
-- 永久视图。需要外部 Catalog 持久化！！！
CREATE VIEW query_view as
SELECT
    *
FROM source_table
;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

public static void main(String[] args) throws Exception {
    StreamExecutionEnvironment env =
            StreamExecutionEnvironment.createLocalEnvironmentWithWebUI(new Configuration()); 
    EnvironmentSettings settings = EnvironmentSettings
            .newInstance()
            .useBlinkPlanner()
            .inStreamingMode()
            .build();
    StreamTableEnvironment tEnv = StreamTableEnvironment.create(env, settings);
    // kafka 数据源
    DataStream<Row> r = env.addSource(new FlinkKafkaConsumer<Row>(xxx));
    // 将 DataStream 转为一个 Table API 中的 Table 对象进行使用
    Table sourceTable = tEnv.fromDataStream(r
            , Schema
                    .newBuilder()
                    .column("f0", "string")
                    .column("f1", "string")
                    .column("f2", "bigint")
                    .columnByExpression("proctime", "PROCTIME()")
                    .build());
    tEnv.createTemporaryView("source_table", sourceTable);
    String selectWhereSql = "select f0 from source_table where f1 = 'b'";
    Table resultTable = tEnv.sqlQuery(selectWhereSql);
    tEnv.toRetractStream(resultTable, Row.class).print();
    env.execute();
}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

EnvironmentSettings settings = EnvironmentSettings
        .newInstance()
        .useBlinkPlanner()
        .inStreamingMode()
        .build();
StreamTableEnvironment tEnv = StreamTableEnvironment.create(env, settings);
// SQL API 执行 create table 创建表
tEnv.executeSql(
        "CREATE TABLE KafkaSourceTable (\n"
                + "  `f0` STRING,\n"
                + "  `f1` STRING\n"
                + ") WITH (\n"
                + "  'connector' = 'kafka',\n"
                + "  'topic' = 'topic',\n"
                + "  'properties.bootstrap.servers' = 'localhost:9092',\n"
                + "  'properties.group.id' = 'testGroup',\n"
                + "  'format' = 'json'\n"
                + ")"
);
Table t = tEnv.sqlQuery("SELECT * FROM KafkaSourceTable");
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

import org.apache.flink.table.api.EnvironmentSettings;
import org.apache.flink.table.api.TableEnvironment;
EnvironmentSettings settings = EnvironmentSettings
    .newInstance()
    .inStreamingMode() // 声明为流任务
    .build();
TableEnvironment tEnv = TableEnvironment.create(settings);
// Table API 中的一个 Table 对象
Table projTable = tEnv.from("X").select(...);
// 将 projTable 创建为一个叫做 projectedTable 的 VIEW
tEnv.createTemporaryView("projectedTable", projTable);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

import org.apache.flink.table.api.EnvironmentSettings;
import org.apache.flink.table.api.TableEnvironment;

EnvironmentSettings settings = EnvironmentSettings
    .newInstance()
    .inStreamingMode() // 声明为流任务
    .build();

TableEnvironment tEnv = TableEnvironment.create(settings);

String sql = "CREATE TABLE source_table (\n"
    + "    user_id BIGINT,\n"
    + "    `name` STRING\n"
    + ") WITH (\n"
    + "  'connector' = 'user_defined',\n"
    + "  'format' = 'json',\n"
    + "  'class.name' = 'flink.examples.sql._03.source_sink.table.user_defined.UserDefinedSource'\n"
    + ");\n"
    + "\n"
    + "CREATE TABLE sink_table (\n"
    + "    user_id BIGINT,\n"
    + "    name STRING\n"
    + ") WITH (\n"
    + "  'connector' = 'print'\n"
    + ");\n"
    + "CREATE VIEW query_view as\n" // 创建 VIEW
    + "SELECT\n"
    + "    *\n"
    + "FROM source_table\n"
    + ";\n"
    + "INSERT INTO sink_table\n"
    + "SELECT\n"
    + "    *\n"
    + "FROM query_view;";
Arrays.stream(sql.split(";"))
      .forEach(tEnv::executeSql);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.

EnvironmentSettings settings = EnvironmentSettings
    .newInstance()
    .inStreamingMode() // 声明为流任务
    //.inBatchMode() // 声明为批任务
    .build();

TableEnvironment tEnv = TableEnvironment.create(settings);

// 1. 创建一个数据源（输入）表，这里的数据源是 flink 自带的一个随机 mock 数据的数据源。
String sourceSql = "CREATE TABLE source_table (\n"
        + "    sku_id STRING,\n"
        + "    price BIGINT\n"
        + ") WITH (\n"
        + "  'connector' = 'datagen',\n"
        + "  'rows-per-second' = '1',\n"
        + "  'fields.sku_id.length' = '1',\n"
        + "  'fields.price.min' = '1',\n"
        + "  'fields.price.max' = '1000000'\n"
        + ")";

// 2. 创建一个数据汇（输出）表，输出到 kafka 中
String sinkSql = "CREATE TABLE sink_table (\n"
        + "    sku_id STRING,\n"
        + "    count_result BIGINT,\n"
        + "    sum_result BIGINT,\n"
        + "    avg_result DOUBLE,\n"
        + "    min_result BIGINT,\n"
        + "    max_result BIGINT,\n"
        + "    PRIMARY KEY (`sku_id`) NOT ENFORCED\n"
        + ") WITH (\n"
        + "  'connector' = 'upsert-kafka',\n"
        + "  'topic' = 'tuzisir',\n"
        + "  'properties.bootstrap.servers' = 'localhost:9092',\n"
        + "  'key.format' = 'json',\n"
        + "  'value.format' = 'json'\n"
        + ")";
// 3. 执行一段 group by 的聚合 SQL 查询
String selectWhereSql = "insert into sink_table\n"
        + "select sku_id,\n"
        + "       count(*) as count_result,\n"
        + "       sum(price) as sum_result,\n"
        + "       avg(price) as avg_result,\n"
        + "       min(price) as min_result,\n"
        + "       max(price) as max_result\n"
        + "from source_table\n"
        + "group by sku_id";
tEnv.executeSql(sourceSql);
tEnv.executeSql(sinkSql);
tEnv.executeSql(selectWhereSql);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.

public static void main(String[] args) throws Exception {
    FlinkEnv flinkEnv = FlinkEnvUtils.getStreamTableEnv(args);
    // 1. pdd 发补贴券流水数据
    String createTableSql = "CREATE TABLE source_table (\n"
            + "    id BIGINT,\n" -- 补贴券的流水 id
            + "    money BIGINT,\n" -- 补贴券的金额
            + "    row_time AS cast(CURRENT_TIMESTAMP as timestamp_LTZ(3)),\n"
            + "    WATERMARK FOR row_time AS row_time - INTERVAL '5' SECOND\n"
            + ") WITH (\n"
            + "  'connector' = 'datagen',\n"
            + "  'rows-per-second' = '1',\n"
            + "  'fields.id.min' = '1',\n"
            + "  'fields.id.max' = '100000',\n"
            + "  'fields.money.min' = '1',\n"
            + "  'fields.money.max' = '100000'\n"
            + ")\n";
    // 2. 计算总计发放补贴券的金额
    String querySql = "SELECT UNIX_TIMESTAMP(CAST(window_end AS STRING)) * 1000 as window_end, \n"
            + "      window_start, \n"
            + "      sum(money) as sum_money,\n" -- 补贴券的发放总金额
            + "      count(distinct id) as count_distinct_id\n"
            + "FROM TABLE(CUMULATE(\n"
            + "         TABLE source_table\n"
            + "         , DESCRIPTOR(row_time)\n"
            + "         , INTERVAL '5' SECOND\n"
            + "         , INTERVAL '1' DAY))\n"
            + "GROUP BY window_start, \n"
            + "        window_end";
    flinkEnv.streamTEnv().executeSql(createTableSql);
    Table resultTable = flinkEnv.streamTEnv().sqlQuery(querySql);
    // 3. 将金额结果转为 DataStream，然后自定义超过 1w 的报警逻辑
    flinkEnv.streamTEnv()
            .toDataStream(resultTable, Row.class)
            .flatMap(new FlatMapFunction<Row, Object>() {
                @Override
                public void flatMap(Row value, Collector<Object> out) throws Exception {
                    long l = Long.parseLong(String.valueOf(value.getField("sum_money")));

                    if (l > 10000L) {
                        log.info("报警，超过 1w");
                    }
                }
            });
    flinkEnv.env().execute();
}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.

CREATE TABLE sink_table (
    result_interval_year TIMESTAMP(3),
    result_interval_year_p TIMESTAMP(3),
    result_interval_year_p_to_month TIMESTAMP(3),
    result_interval_month TIMESTAMP(3),
    result_interval_day TIMESTAMP(3),
    result_interval_day_p1 TIMESTAMP(3),
    result_interval_day_p1_to_hour TIMESTAMP(3),
    result_interval_day_p1_to_minute TIMESTAMP(3),
    result_interval_day_p1_to_second_p2 TIMESTAMP(3),
    result_interval_hour TIMESTAMP(3),
    result_interval_hour_to_minute TIMESTAMP(3),
    result_interval_hour_to_second TIMESTAMP(3),
    result_interval_minute TIMESTAMP(3),
    result_interval_minute_to_second_p2 TIMESTAMP(3),
    result_interval_second TIMESTAMP(3),
    result_interval_second_p2 TIMESTAMP(3)
) WITH (
  'connector' = 'print'
);
INSERT INTO sink_table
SELECT
    -- Flink SQL 支持的所有 INTERVAL 子句如下，总体可以分为 `年-月`、`日-小时-秒` 两种

    -- 1. 年-月。取值范围为 [-9999-11, +9999-11]，其中 p 是指有效位数，取值范围 [1, 4]，默认值为 2。比如如果值为 1000，但是 p = 2，则会直接报错。
    -- INTERVAL YEAR
    f1 + INTERVAL '10' YEAR as result_interval_year
    -- INTERVAL YEAR(p)
    , f1 + INTERVAL '100' YEAR(3) as result_interval_year_p
    -- INTERVAL YEAR(p) TO MONTH
    , f1 + INTERVAL '10-03' YEAR(3) TO MONTH as result_interval_year_p_to_month
    -- INTERVAL MONTH
    , f1 + INTERVAL '13' MONTH as result_interval_month

    -- 2. 日-小时-秒。取值范围为 [-999999 23:59:59.999999999, +999999 23:59:59.999999999]，其中 p1\p2 都是有效位数，p1 取值范围 [1, 6]，默认值为 2；p2 取值范围 [0, 9]，默认值为 6
    -- INTERVAL DAY
    , f1 + INTERVAL '10' DAY as result_interval_day
    -- INTERVAL DAY(p1)
    , f1 + INTERVAL '100' DAY(3) as result_interval_day_p1
    -- INTERVAL DAY(p1) TO HOUR
    , f1 + INTERVAL '10 03' DAY(3) TO HOUR as result_interval_day_p1_to_hour
    -- INTERVAL DAY(p1) TO MINUTE
    , f1 + INTERVAL '10 03:12' DAY(3) TO MINUTE as result_interval_day_p1_to_minute
    -- INTERVAL DAY(p1) TO SECOND(p2)
    , f1 + INTERVAL '10 00:00:00.004' DAY TO SECOND(3) as result_interval_day_p1_to_second_p2
    -- INTERVAL HOUR
    , f1 + INTERVAL '10' HOUR as result_interval_hour
    -- INTERVAL HOUR TO MINUTE
    , f1 + INTERVAL '10:03' HOUR TO MINUTE as result_interval_hour_to_minute
    -- INTERVAL HOUR TO SECOND(p2)
    , f1 + INTERVAL '00:00:00.004' HOUR TO SECOND(3) as result_interval_hour_to_second
    -- INTERVAL MINUTE
    , f1 + INTERVAL '10' MINUTE as result_interval_minute
    -- INTERVAL MINUTE TO SECOND(p2)
    , f1 + INTERVAL '05:05.006' MINUTE TO SECOND(3) as result_interval_minute_to_second_p2
    -- INTERVAL SECOND
    , f1 + INTERVAL '3' SECOND as result_interval_second
    -- INTERVAL SECOND(p2)
    , f1 + INTERVAL '300' SECOND(3) as result_interval_second_p2
FROM (SELECT TO_TIMESTAMP_LTZ(1640966476500, 3) as f1)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.

public class User {
    // 1. 基础类型，Flink 可以通过反射类型信息自动把数据类型获取到
    // 关于 SQL 类型和 Java 类型之间的映射见：https://nightlies.apache.org/flink/flink-docs-release-1.13/docs/dev/table/types/#data-type-extraction
    public int age;
    public String name;
    // 2. 复杂类型，用户可以通过 @DataTypeHint("DECIMAL(10, 2)") 注解标注此字段的数据类型
    public @DataTypeHint("DECIMAL(10, 2)") BigDecimal totalBalance;
}
1.
2.
3.
4.
5.
6.
7.
8.

public class UserScalarFunction extends ScalarFunction {

    // 1. 自定义数据类型作为输出参数
    public User eval(long i) {
        if (i > 0 && i <= 5) {
            User u = new User();
            u.age = (int) i;
            u.name = "name1";
            u.totalBalance = new BigDecimal(1.1d);
            return u;
        } else {
            User u = new User();
            u.age = (int) i;
            u.name = "name2";
            u.totalBalance = new BigDecimal(2.2d);
            return u;
        }
    }
    // 2. 自定义数据类型作为输入参数
    public String eval(User i) {
        if (i.age > 0 && i.age <= 5) {
            User u = new User();
            u.age = 1;
            u.name = "name1";
            u.totalBalance = new BigDecimal(1.1d);
            return u.name;
        } else {
            User u = new User();
            u.age = 2;
            u.name = "name2";
            u.totalBalance = new BigDecimal(2.2d);
            return u.name;
        }
    }
}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.

-- 1. 创建 UDF
CREATE FUNCTION user_scalar_func AS 'flink.examples.sql._12_data_type._02_user_defined.UserScalarFunction';
-- 2. 创建数据源表
CREATE TABLE source_table (
    user_id BIGINT NOT NULL COMMENT '用户 id'
) WITH (
  'connector' = 'datagen',
  'rows-per-second' = '1',
  'fields.user_id.min' = '1',
  'fields.user_id.max' = '10'
);
-- 3. 创建数据汇表
CREATE TABLE sink_table (
    result_row_1 ROW<age INT, name STRING, totalBalance DECIMAL(10, 2)>,
    result_row_2 STRING
) WITH (
  'connector' = 'print'
);
-- 4. SQL 查询语句
INSERT INTO sink_table
select
    -- 4.a. 用户自定义类型作为输出
    user_scalar_func(user_id) as result_row_1,
    -- 4.b. 用户自定义类型作为输出及输入
    user_scalar_func(user_scalar_func(user_id)) as result_row_2
from source_table;
-- 5. 查询结果
+I[+I[9, name2, 2.20], name2]
+I[+I[1, name1, 1.10], name1]
+I[+I[5, name1, 1.10], name1]
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.

[
  user:  VARCHAR,   // 用户名
  cTime: TIMESTAMP, // 访问 URL 的时间
  url:   VARCHAR    // 用户访问的 URL
]
1.
2.
3.
4.
5.