Flink系列--使用Datagen，print测试Flink sql（使用 Table API 进行实时报告为基础改造）

使用 Table API 进行实时报告

官网示例链接：https://nightlies.apache.org/flink/flink-docs-release-1.15/docs/try-flink/table_api/

Apache Flink 提供了一个 Table API 作为用于批处理和流处理的统一的关系 API，即查询在无界的实时流或有界的批处理数据集上以相同的语义执行并产生相同的结果。Flink 中的 Table API 通常用于简化数据分析、数据管道和 ETL 应用程序的定义。

ackage com.test.flink.table;

import org.apache.flink.table.api.EnvironmentSettings;
import org.apache.flink.table.api.Table;
import org.apache.flink.table.api.TableEnvironment;
import org.apache.flink.table.expressions.TimeIntervalUnit;

import static org.apache.flink.table.api.Expressions.$;

/**
 * 原生官网示例
 */
public class FlinkTableTest {

    public static void main(String[] args) {

        EnvironmentSettings settings = EnvironmentSettings.inStreamingMode();
        TableEnvironment tEnv = TableEnvironment.create(settings);

        tEnv.executeSql("CREATE TABLE transactions (\n" +
                "    account_id  BIGINT,\n" +
                "    amount      BIGINT,\n" +
                "    transaction_time TIMESTAMP(3),\n" +
                "    WATERMARK FOR transaction_time AS transaction_time - INTERVAL '5' SECOND\n" +
                ") WITH (\n" +
                "    'connector' = 'kafka',\n" +
                "    'topic'     = 'transactions',\n" +
                "    'properties.bootstrap.servers' = 'kafka:9092',\n" +
                "    'format'    = 'csv'\n" +
                ")");

        tEnv.executeSql("CREATE TABLE spend_report (\n" +
                "    account_id BIGINT,\n" +
                "    log_ts     TIMESTAMP(3),\n" +
                "    amount     BIGINT\n," +
                "    PRIMARY KEY (account_id, log_ts) NOT ENFORCED" +
                ") WITH (\n" +
                "   'connector'  = 'jdbc',\n" +
                "   'url'        = 'jdbc:mysql://mysql:3306/sql-demo',\n" +
                "   'table-name' = 'spend_report',\n" +
                "   'driver'     = 'com.mysql.jdbc.Driver',\n" +
                "   'username'   = 'sql-demo',\n" +
                "   'password'   = 'demo-sql'\n" +
                ")");

        Table transactions = tEnv.from("transactions");
        report(transactions).executeInsert("spend_report");

    }

    public static Table report(Table transactions) {
        return transactions.select(
                        $("account_id"),
                        $("transaction_time").floor(TimeIntervalUnit.HOUR).as("log_ts"),
                        $("amount"))
                .groupBy($("account_id"), $("log_ts"))
                .select(
                        $("account_id"),
                        $("log_ts"),
                        $("amount").sum().as("amount"));
    }
}

此调试需要Kafka服务和mysql数据库，简单调试的话可以借助datagen产生模拟数据，使用print查看运行效果

1、datagen模拟数据，print打印

maven依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>FlinkTableApiTest</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.apache.flink</groupId>
        <artifactId>flink-parent</artifactId>
        <version>1.15.1</version>
    </parent>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>


    <dependencies>
        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-clients</artifactId>
            <version>1.15.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-connector-kafka</artifactId>
            <version>1.15.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-table-api-java</artifactId>
            <version>1.15.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-connector-hbase-2.2_2.11</artifactId>
            <version>1.13.6</version>
        </dependency>


        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-connector-base</artifactId>
            <version>1.15.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-table-planner_2.12</artifactId>
            <version>1.15.1</version>
        </dependency>

    </dependencies>

<!--    <build>-->
<!--        <plugins>-->
<!--            <plugin>-->
<!--                <groupId>org.apache.maven.plugins</groupId>-->
<!--                <artifactId>maven-shade-plugin</artifactId>-->
<!--                <version>3.1.1</version>-->
<!--                <executions>-->
<!--                    <execution>-->
<!--                        <phase>package</phase>-->
<!--                        <goals>-->
<!--                            <goal>shade</goal>-->
<!--                        </goals>-->
<!--                        <configuration>-->
<!--                            <artifactSet>-->
<!--                                <excludes>-->
<!--                                    <exclude>com.google.code.findbugs:jsr305</exclude>-->
<!--                                </excludes>-->
<!--                            </artifactSet>-->
<!--                            <filters>-->
<!--                                <filter>-->
<!--                                    &lt;!&ndash; Do not copy the signatures in the META-INF folder.-->
<!--                                    Otherwise, this might cause SecurityExceptions when using the JAR. &ndash;&gt;-->
<!--                                    <artifact>*:*</artifact>-->
<!--                                    <excludes>-->
<!--                                        <exclude>META-INF/*.SF</exclude>-->
<!--                                        <exclude>META-INF/*.DSA</exclude>-->
<!--                                        <exclude>META-INF/*.RSA</exclude>-->
<!--                                    </excludes>-->
<!--                                </filter>-->
<!--                            </filters>-->
<!--                            <transformers>-->
<!--                                <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">-->
<!--                                    &lt;!&ndash; Replace this with the main class of your job &ndash;&gt;-->
<!--                                    <mainClass>my.programs.main.clazz</mainClass>-->
<!--                                </transformer>-->
<!--                                <transformer implementation="org.apache.maven.plugins.shade.resource.ServicesResourceTransformer"/>-->
<!--                            </transformers>-->
<!--                        </configuration>-->
<!--                    </execution>-->
<!--                </executions>-->
<!--            </plugin>-->
<!--        </plugins>-->
<!--    </build>-->

</project>

maven依赖

改造代码:

package com.test.flink.table;

import org.apache.flink.table.api.EnvironmentSettings;
import org.apache.flink.table.api.Table;
import org.apache.flink.table.api.TableEnvironment;
import org.apache.flink.table.expressions.TimeIntervalUnit;

import static org.apache.flink.table.api.Expressions.$;

/**
 * 修改官方原生示例来源端为data_gen 目标端为：print 验证逻辑
 * datagen https://nightlies.apache.org/flink/flink-docs-release-1.15/docs/connectors/table/datagen/
 * print  https://nightlies.apache.org/flink/flink-docs-release-1.15/docs/connectors/table/print/
 * Talbe api操作
 */
public class FlinkTableTest_table_api {

    public static void main(String[] args) {
        EnvironmentSettings settings = EnvironmentSettings.inStreamingMode();
        TableEnvironment tEnv = TableEnvironment.create(settings);
        tEnv.getConfig().set("parallelism","1");

//        DataTypes.TIMESTAMP(3)
        tEnv.executeSql("CREATE TABLE data_gen (\n" +
                "    account_id  BIGINT,\n" +
                "    amount      BIGINT,\n" +
                "    transaction_time TIMESTAMP(3),\n" +
                "    WATERMARK FOR transaction_time AS transaction_time - INTERVAL '3' SECOND\n" +
                ") WITH (\n" +
                "    'connector' = 'datagen'," +
                "    'rows-per-second' = '1'," +
                "    'number-of-rows' = '3'," +
                "    'fields.amount.kind' = 'random'," +
                "    'fields.amount.min' = '10'," +
                "    'fields.amount.max' = '11'," +
                "    'fields.account_id.kind' = 'random'," +
                "    'fields.account_id.min' = '1'," +
                "    'fields.account_id.max' = '2'" +
                ")");


        tEnv.executeSql("CREATE TABLE print (\n" +
                "    account_id BIGINT,\n" +
                "    log_ts     TIMESTAMP(3),\n" +
                "    amount     BIGINT\n," +
                "    PRIMARY KEY (account_id, log_ts) NOT ENFORCED" +
                ") WITH (\n" +
                "   'connector'  = 'print'\n" +
                ")");

        Table data_gen = tEnv.from("data_gen");
        Table report = report(data_gen);
        report.executeInsert("print");

    }

    public static Table report(Table transactions) {
        return transactions.select(
                        $("account_id"),
                        $("transaction_time").floor(TimeIntervalUnit.HOUR).as("log_ts"),
                        $("amount"))
                .groupBy($("account_id"),$("log_ts"))
                .select(
                        $("account_id"),
                        $("log_ts"),
                        $("amount").sum().as("amount"));
    }
}

Group聚合-流模式

package com.test.flink.table;

import org.apache.flink.table.api.EnvironmentSettings;
import org.apache.flink.table.api.Table;
import org.apache.flink.table.api.TableEnvironment;
import org.apache.flink.table.expressions.TimeIntervalUnit;

import static org.apache.flink.table.api.Expressions.$;

/**
 * 修改官方原生示例来源端为data_gen 目标端为：print 验证逻辑
 * datagen https://nightlies.apache.org/flink/flink-docs-release-1.15/docs/connectors/table/datagen/
 * print  https://nightlies.apache.org/flink/flink-docs-release-1.15/docs/connectors/table/print/
 * Talbe api操作
 */
public class FlinkTableTest_table_api {

    public static void main(String[] args) {
        EnvironmentSettings settings = EnvironmentSettings.inStreamingMode();
        TableEnvironment tEnv = TableEnvironment.create(settings);
        tEnv.getConfig().set("parallelism","1");

//        DataTypes.TIMESTAMP(3)
        tEnv.executeSql("CREATE TABLE data_gen (\n" +
                "    account_id  BIGINT,\n" +
                "    amount      BIGINT,\n" +
                "    transaction_time TIMESTAMP(3),\n" +
                "    WATERMARK FOR transaction_time AS transaction_time - INTERVAL '3' SECOND\n" +
                ") WITH (\n" +
                "    'connector' = 'datagen'," +
                "    'rows-per-second' = '1'," +
                "    'number-of-rows' = '3'," +
                "    'fields.amount.kind' = 'random'," +
                "    'fields.amount.min' = '10'," +
                "    'fields.amount.max' = '11'," +
                "    'fields.account_id.kind' = 'random'," +
                "    'fields.account_id.min' = '1'," +
                "    'fields.account_id.max' = '2'" +
                ")");


        tEnv.executeSql("CREATE TABLE print (\n" +
                "    account_id BIGINT,\n" +
                "    log_ts     TIMESTAMP(3),\n" +
                "    amount     BIGINT\n," +
                "    PRIMARY KEY (account_id, log_ts) NOT ENFORCED" +
                ") WITH (\n" +
                "   'connector'  = 'print'\n" +
                ")");

        Table data_gen = tEnv.from("data_gen");
        Table report = report(data_gen);
        report.executeInsert("print");

    }

    public static Table report(Table transactions) {
        return transactions.select(
                        $("account_id"),
                        $("transaction_time").floor(TimeIntervalUnit.HOUR).as("log_ts"),
                        $("amount"))
                .groupBy($("account_id"),$("log_ts"))
                .select(
                        $("account_id"),
                        $("log_ts"),
                        $("amount").sum().as("amount"));
    }
}

Group聚合-流模式

Group聚合-批模式

package com.test.flink.table;

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

/**
 * 修改官方原生示例来源端为data_gen 目标端为：print 验证逻辑
 * sql操作
 * watermark 实际效果在此种 group aggregate貌似没有实际意义
 * group aggregate 是更新流，相同key的结果会不断更新输出， 没有窗口的说法，会累积更新结果
 * window aggredate 是窗口聚合，每个窗口结束时输出结果
 * 换成batch mode 查看效果，此模式要求 source要支持有界流配置
 */
public class FlinkTableTest_sql_batch_group_aggreate {

    public static void main(String[] args) {
//        Factory
        EnvironmentSettings settings = EnvironmentSettings.inBatchMode();
        TableEnvironment tEnv = TableEnvironment.create(settings);
        tEnv.getConfig().set("parallelism.default","1");

//        DataTypes.TIMESTAMP(3)
        tEnv.executeSql("CREATE TABLE data_gen (\n" +
                "    account_id  BIGINT,\n" +
                "    amount      BIGINT\n" +
//                "    transaction_time TIMESTAMP(3)\n" +
//                "    WATERMARK FOR transaction_time AS transaction_time - INTERVAL '10' SECOND\n" +
                ") WITH (\n" +
                "    'connector' = 'datagen'," +
                "    'rows-per-second' = '1'," +
//                "    'number-of-rows' = '5'," +
                "    'fields.amount.kind' = 'random'," +
                "    'fields.amount.min' = '10'," +
                "    'fields.amount.max' = '11'," +
                "    'fields.account_id.kind' = 'random'," +
                "    'fields.account_id.min' = '1'," +
                "    'fields.account_id.max' = '2'" +
                ")");


        tEnv.executeSql("CREATE TABLE print (\n" +
                "    account_id  BIGINT,\n" +
                "    count1      BIGINT,\n" +
                "    PRIMARY KEY (account_id) NOT ENFORCED" +
                ") WITH (\n" +
                "   'connector'  = 'print',\n" +
                "   'sink.parallelism'='1'\n" +
                ")");

        tEnv.executeSql("insert into print select account_id ,count(amount) as count1 from data_gen group by account_id");
        //        public enum RowKind {
//            INSERT("+I", (byte)0),
//            UPDATE_BEFORE("-U", (byte)1),
//            UPDATE_AFTER("+U", (byte)2),
//            DELETE("-D", (byte)3);

    }


}

Group聚合-批模式

window聚合

package com.test.flink.table;

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

/**
 * 修改官方原生示例来源端为data_gen 目标端为：print 验证逻辑
 * sql操作
 * group aggregate 是更新流，相同key的结果会不断更新输出， 没有窗口的说法，会累积更新结果
 * window aggredate 是窗口聚合，每个窗口结束时输出结果
 */
public class FlinkTableTest_sql_streaming_window_aggreate {

    public static void main(String[] args) {
//        Factory
        EnvironmentSettings settings = EnvironmentSettings.inStreamingMode();
        TableEnvironment tEnv = TableEnvironment.create(settings);
        tEnv.getConfig().set("parallelism.default","1");

//        DataTypes.TIMESTAMP(3)
        tEnv.executeSql("CREATE TABLE data_gen (\n" +
                "    account_id  BIGINT,\n" +
                "    amount      BIGINT,\n" +
                "    transaction_time TIMESTAMP(3)" +
                ",\n" +
                "    WATERMARK FOR transaction_time AS transaction_time - INTERVAL '0' SECOND\n" +
                ") WITH (\n" +
                "    'connector' = 'datagen'," +
                "    'rows-per-second' = '10'," +
//                "    'number-of-rows' = '5'," +
                "    'fields.amount.kind' = 'random'," +
                "    'fields.amount.min' = '10'," +
                "    'fields.amount.max' = '11'," +
                "    'fields.account_id.kind' = 'random'," +
                "    'fields.account_id.min' = '1'," +
                "    'fields.account_id.max' = '2'" +
                ")");

        tEnv.executeSql("CREATE TABLE print (\n" +
                "    account_id  BIGINT,\n" +
                "    count1      BIGINT,\n" +
                "    PRIMARY KEY (account_id) NOT ENFORCED" +
                ") WITH (\n" +
                "   'connector'  = 'print',\n" +
                "   'sink.parallelism'='1'\n" +
                ")");

        tEnv.executeSql("insert into print select account_id ,count(amount) as count1 " +
                "from data_gen group by account_id, " +
                "TUMBLE(transaction_time,INTERVAL '10' SECOND)");
    }


}

window聚合

window聚合-使用处理实践ProcTime

package com.test.flink.table;

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

/**
 * 修改官方原生示例来源端为data_gen 目标端为：print 验证逻辑
 * sql操作
 * group aggregate 是更新流，相同key的结果会不断更新输出， 没有窗口的说法，会累积更新结果
 * window aggredate 是窗口聚合，每个窗口结束时输出结果
 *
 * 使用系统时间
 * 输出window 结束时间
 */
public class FlinkTableTest_sql_streaming_window_aggreate_PROCTIME {

    public static void main(String[] args) {
//        Factory
        EnvironmentSettings settings = EnvironmentSettings.inStreamingMode();
        TableEnvironment tEnv = TableEnvironment.create(settings);
        tEnv.getConfig().set("parallelism.default","1");

//        DataTypes.TIMESTAMP(3)
        tEnv.executeSql("CREATE TABLE data_gen (\n" +
                "    account_id  BIGINT,\n" +
                "    amount      BIGINT,\n" +
                "    transaction_time AS proctime()" +
                ") WITH (\n" +
                "    'connector' = 'datagen'," +
                "    'rows-per-second' = '10'," +
//                "    'number-of-rows' = '5'," +
                "    'fields.amount.kind' = 'random'," +
                "    'fields.amount.min' = '10'," +
                "    'fields.amount.max' = '11'," +
                "    'fields.account_id.kind' = 'random'," +
                "    'fields.account_id.min' = '1'," +
                "    'fields.account_id.max' = '2'" +
                ")");

        tEnv.executeSql("CREATE TABLE print (\n" +
                "    account_id  BIGINT,\n" +
                "    window_end  TIMESTAMP(3),\n" +
                "    count1      BIGINT,\n" +
                "    PRIMARY KEY (account_id) NOT ENFORCED" +
                ") WITH (\n" +
                "   'connector'  = 'print',\n" +
                "   'sink.parallelism'='1'\n" +
                ")");

        tEnv.executeSql("insert into print " +
                "select account_id ," +
                "TUMBLE_END(transaction_time,INTERVAL '10' SECOND) as window_end, " +
                "count(amount) as count1 " +
                "from data_gen group by account_id, " +
                "TUMBLE(transaction_time,INTERVAL '10' SECOND)");
    }
}

window聚合使用数据处理时间procTime

posted on 2022-08-09 15:28 life_start 阅读(1978) 评论(0) 收藏举报