Flink Table Sql(六)

基础代码

环境信息

    sql-client.sh  启动 SQL 客户端

    CREATE TABLE employee_information (emp_id INT,name VARCHAR,dept_id INT)
    WITH ('connector' = 'filesystem','path' = '/path/to/something.csv','format' = 'csv');
    SELECT * from employee_information WHERE dept_id = 1;  --查询语句

    SELECT * FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES));

窗口

    group window  sql支持的group 函数
    SELECT window_start, window_end, SUM(price) FROM TABLE( TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES))
    GROUP BY window_start, window_end;//滚动窗口

    SELECT window_start, window_end, SUM(price) FROM TABLE( HOP(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '5' MINUTES, INTERVAL '10' MINUTES))
    GROUP BY window_start, window_end;//滑动窗口

    SELECT window_start, window_end, SUM(price) FROM TABLE( CUMULATE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '2' MINUTES, INTERVAL '10' MINUTES))
    GROUP BY window_start, window_end;//会话窗口

    还有其他的window函数 可以获取开始和结束时间戳，以及时间
    这里只写 TUMBLE_*，滑动和会话窗口是类似的（HOP_*，SESSION_*）
    TUMBLE_START(time_attr, interval)
    TUMBLE_END(time_attr, interval)
    TUMBLE_ROWTIME(time_attr, interval)
    TUMBLE_PROCTIME(time_attr, interval)

窗口聚合

    SELECT window_start, window_end, supplier_id, SUM(price) as price FROM TABLE( TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES))
    GROUP BY window_start, window_end, GROUPING SETS ((supplier_id), ());

    SELECT window_start, window_end, supplier_id, SUM(price) as price FROM TABLE( TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES))
    GROUP BY window_start, window_end, ROLLUP (supplier_id);

    SELECT window_start, window_end, item, supplier_id, SUM(price) as price FROM TABLE( TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES))
    GROUP BY window_start, window_end, CUBE (supplier_id, item);

级联窗口聚合

    CREATE VIEW window1 AS SELECT window_start, window_end, window_time as rowtime, SUM(price) as partial_price
    FROM TABLE( TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '5' MINUTES))
    GROUP BY supplier_id, window_start, window_end, window_time;

    SELECT window_start, window_end, SUM(partial_price) as total_price
    FROM TABLE(TUMBLE(TABLE window1, DESCRIPTOR(rowtime), INTERVAL '10' MINUTES))
    GROUP BY window_start, window_end;

组聚合

    SELECT COUNT(DISTINCT order_id) FROM Orders GROUP BY order_id HAVING aa > 50

over 两种写法

    RANGE BETWEEN INTERVAL '30' MINUTE PRECEDING AND CURRENT ROW
    ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
    "select count(amount) over(PARTITION by user order by proctime rows between 2 preceding and current row) from orders "
    "select id,count(id) over ow from data window ow as (partation by id order by st rows between 2 preceding and current row)  "
    datatable.window(Tumble.over(10.second()).on($"ts").as("ts")).groupBy($"id",$"ts").select($"id",$"ts",$"id".count())
    "select id,count(id),Tumble(ts,interval '10' second) from data group by id,TUMBLE(ts,interval '10' second)"

join

    SELECT * FROM Orders INNER JOIN Product ON Orders.product_id = Product.id
    SELECT * FROM Orders LEFT  JOIN Product ON Orders.product_id = Product.id
    SELECT * FROM Orders RIGHT JOIN Product ON Orders.product_id = Product.id
    SELECT * FROM Orders FULL OUTER JOIN Product ON Orders.product_id = Product.id
    SELECT * FROM Orders o, Shipments s WHERE o.id = s.order_id AND o.order_time BETWEEN s.ship_time - INTERVAL '4' HOUR AND s.ship_time

时间连接

    CREATE TABLE orders (order_id STRING,price DECIMAL(32,2),currency STRING,order_time TIMESTAMP(3),
    WATERMARK FOR order_time AS order_time ) WITH (/* ... */);
    CREATE TABLE currency_rates (currency STRING,conversion_rate DECIMAL(32, 2),
      update_time TIMESTAMP(3) METADATA FROM `values.source.timestamp` VIRTUAL,
      WATERMARK FOR update_time AS update_time,
      PRIMARY KEY(currency) NOT ENFORCED
    ) WITH ('connector' = 'kafka','value.format' = 'debezium-json');

事件时间连接

    SELECT order_id,price,currency,conversion_rate,order_time,
    FROM orders LEFT JOIN currency_rates FOR SYSTEM_TIME AS OF orders.order_time
    ON orders.currency = currency_rates.currency;

处理时间连接

    SELECT o.amount, o.currency, r.rate, o.amount * r.rate
    FROM Orders AS o JOIN LatestRates FOR SYSTEM_TIME AS OF o.proctime AS r
    ON r.currency = o.currency

查找join

    SELECT o.order_id, o.total, c.country, c.zip FROM Orders AS o
    JOIN Customers FOR SYSTEM_TIME AS OF o.proc_time AS c
    ON o.customer_id = c.id;

数组展开

    SELECT order_id, tag FROM Orders CROSS JOIN UNNEST(tags) AS t (tag)

内连接、左外连接

    SELECT order_id, res FROM Orders, LATERAL TABLE(table_func(order_id)) t(res)
    SELECT order_id, res FROM Orders LEFT OUTER JOIN LATERAL TABLE(table_func(order_id)) t(res) ON TRUE

两表join

    create view t1(s) as values ('c'), ('a'), ('b'), ('b'), ('c');
    create view t2(s) as values ('d'), ('e'), ('a'), ('b'), ('b');
    (SELECT s FROM t1) UNION (SELECT s FROM t2);
    (SELECT s FROM t1) UNION ALL (SELECT s FROM t2);
    (SELECT s FROM t1) INTERSECT (SELECT s FROM t2);--返回两个表都存在的记录  删除重复项
    (SELECT s FROM t1) INTERSECT ALL (SELECT s FROM t2);--返回两个表都存在的记录  不删除重复项
    (SELECT s FROM t1) EXCEPT (SELECT s FROM t2);  --类似not in 删除重复项
    (SELECT s FROM t1) EXCEPT ALL (SELECT s FROM t2);--类似not in 不删除重复项
    SELECT user, amount FROM Orders WHERE product IN (SELECT product FROM NewProducts) --in
    SELECT user, amount FROM Orders WHERE product EXISTS (SELECT product FROM NewProducts)  --EXISTS

top N

    SELECT product_id, category, product_name, sales FROM (
        SELECT *,ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS row_num
        FROM ShopSales
    )WHERE row_num <= 5

窗口 top N

    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY window_start, window_end ORDER BY price DESC) as rownum
        FROM (
            SELECT window_start, window_end, supplier_id, SUM(price) as price, COUNT(*) as cnt
            FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES))
            GROUP BY window_start, window_end, supplier_id
        )
    ) WHERE rownum <= 3;

重复数据删除

    SELECT order_id, user, product, num FROM (
        SELECT *,ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY proctime ASC) AS row_num FROM Orders
    ) WHERE row_num = 1

模式识别(CEP SQL版本)

    SELECT T.aid, T.bid, T.cid FROM MyTable MATCH_RECOGNIZE (
      PARTITION BY userid ORDER BY proctime MEASURES A.id AS aid,
      B.id AS bid,C.id AS cid PATTERN (A B C)
      DEFINE A AS name = 'a', B AS name = 'b', C AS name = 'c'
    ) AS T
    PARTITION BY - 定义表的逻辑分区；类似于 GROUP BY 操作。
    ORDER BY - 指定传入行的排序方式；这是必须的，因为模式依赖于顺序。
    MEASURES - 定义子句的输出；类似于 SELECT 子句。
    ONE ROW PER MATCH - 输出方式，定义每个匹配项应产生多少行。
    AFTER MATCH SKIP - 指定下一个匹配的开始位置；这也是控制单个事件可以属于多少个不同匹配项的方法。
    PATTERN - 允许使用类似于 正则表达式 的语法构造搜索的模式。
    DEFINE - 本部分定义了模式变量必须满足的条件。

匹配模式

    https://nightlies.apache.org/flink/flink-docs-release-1.13/zh/docs/dev/table/sql/queries/match_recognize/
    demo
    SELECT * FROM Ticker MATCH_RECOGNIZE (
        PARTITION BY symbol ORDER BY rowtime MEASURES
           START_ROW.rowtime AS start_tstamp,
           LAST(PRICE_DOWN.rowtime) AS bottom_tstamp,
           LAST(PRICE_UP.rowtime) AS end_tstamp,
           AVG(PRICE_UP) AS avgPrice
        ONE ROW PER MATCH
        AFTER MATCH SKIP TO LAST PRICE_UP
        PATTERN (START_ROW PRICE_DOWN+ PRICE_UP)
        DEFINE PRICE_DOWN AS
        (LAST(PRICE_DOWN.price, 1) IS NULL AND PRICE_DOWN.price < START_ROW.price) OR
        PRICE_DOWN.price < LAST(PRICE_DOWN.price, 1),
        PRICE_UP AS PRICE_UP.price > LAST(PRICE_DOWN.price, 1)
    ) MR;
    LAST:映射到变量最后n个元素的事件中的字段值   FIRST映射到变量的第 n 个元素的事件中的字段值
    SKIP PAST LAST ROW - 在当前匹配的最后一行之后的下一行继续模式匹配。
    SKIP TO NEXT ROW - 继续从匹配项开始行后的下一行开始搜索新匹配项。
    SKIP TO LAST variable - 恢复映射到指定模式变量的最后一行的模式匹配。
    SKIP TO FIRST variable - 在映射到指定模式变量的第一行继续模式匹配。

常用样例

   CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...)
   DROP TABLE IF EXISTS Orders;DROP DATABASE IF EXISTS DB;DROP [TEMPORARY] VIEW IF EXISTS;DROP FUNCTION  IF EXISTS
   ALTER TABLE Orders RENAME TO NewOrders;ALTER TABLE Orders SET (key1=val1, key2=val2);
   ALTER DATABASE Orders SET (key1=val1, key2=val2);
   INSERT INTO RubberOrders PARTITION (date='2019-8-30') SELECT product from data;
   INSERT OVERWRITE RubberOrders PARTITION (date='2019-8-30') SELECT product from data;
   DESCRIBE Orders;DESC Orders
   EXPLAIN PLAN FOR select * from data;
   SHOW CATALOGS;USE CATALOG;SHOW DATABASES;USE DB;USE MODULES hive;SHOW FULL MODULES;
   SHOW CATALOGS;SHOW CURRENT CATALOG;SHOW DATABASES;SHOW CURRENT DATABASE;
   SHOW TABLES;SHOW VIEWS;SHOW FUNCTIONS;SHOW MODULES;SHOW FULL MODULES;
   LOAD MODULE hive WITH ('hive-version' = '3.1.2');SHOW MODULES;
   UNLOAD MODULE core;SHOW MODULES;
   SET table.planner = blink;SET;
   RESET table.planner;RESET(所有设置回复默认);

km暂存

    系统函数
    https://nightlies.apache.org/flink/flink-docs-release-1.13/zh/docs/dev/table/functions/systemfunctions/
    SQL 配置页面
    https://nightlies.apache.org/flink/flink-docs-release-1.13/zh/docs/dev/table/config/
    代码 配置页面
    https://nightlies.apache.org/flink/flink-docs-release-1.13/zh/docs/dev/execution/execution_configuration/

模块

   SHOW MODULES;SHOW FULL MODULES;LOAD MODULE hive WITH ('hive-version' = '...');
   USE MODULES hive,core;UNLOAD MODULE hive;

可视化模式

    在内存中实体化结果，并将结果用规则的分页表格可视化展示出来
    SET sql-client.execution.result-mode=table;
    不会实体化和可视化结果，而是由插入（+）和撤销（-）组成的持续查询产生结果流
    SET sql-client.execution.result-mode=changelog;
    更接近传统的数据库，会将执行的结果以制表的形式直接打在屏幕之上
    SET sql-client.execution.result-mode=tableau;

可设置参数

    SET table.planner = blink;
    SET execution.runtime-mode = streaming;
    SET sql-client.execution.result-mode = table;
    SET sql-client.execution.max-table-result.rows = 10000;
    SET parallelism.default = 1;
    SET pipeline.auto-watermark-interval = 200;
    SET pipeline.max-parallelism = 10;
    SET table.exec.state.ttl=1000;
    SET table.optimizer.join-reorder-enabled = true;
    SET table.exec.spill-compression.enabled = true;
    SET table.exec.spill-compression.block-size = 128kb;
    SET execution.savepoint.path=/tmp/flink-savepoints/savepoint-cca7bc-bb1e257f0dab;
    SET pipeline.name= 'kafka-to-hive' ;--定义自定义作业名称

执行一组SQL

    BEGIN STATEMENT SET;
        INSERT INTO|OVERWRITE <select_statement>;
    END;

版本表

    CREATE TABLE product_changelog (product_id STRING,product_name STRING,product_price DECIMAL(10, 4),
      update_time TIMESTAMP(3) METADATA FROM 'value.source.timestamp' VIRTUAL,
    PRIMARY KEY(product_id) NOT ENFORCED,      -- (1) 定义主键约束
      WATERMARK FOR update_time AS update_time   -- (2) 通过 watermark 定义事件时间
    ) WITH ('connector' = 'kafka','topic' = 'products',
    'scan.startup.mode' = 'earliest-offset',
    'properties.bootstrap.servers' = 'localhost:9092',
    'value.format' = 'debezium-json');

版本视图

    CREATE TABLE RatesHistory (currency_time TIMESTAMP(3),currency STRING,rate DECIMAL(38, 10),
      WATERMARK FOR currency_time AS currency_time   -- 定义事件时间)
    WITH ('connector' = 'kafka','topic' = 'rates','scan.startup.mode' = 'earliest-offset',
    'properties.bootstrap.servers' = 'localhost:9092','format' = 'json'  -- 普通的 append-only 流)

普通表

    CREATE TABLE LatestRates (currency STRING,fam1 ROW<rate DOUBLE>)
    WITH ('connector' = 'hbase-1.4','table-name' = 'rates','zookeeper.quorum' = 'localhost:2181');

CEP

//CEP 允许再事件流中组合事件模式，一个或者多个由简单事件构成的事件流  Pattern模式
//个体模式：组成每一个单独的模式定义    组合模式：很多个体模式组合起来(严格 宽松)    模式组：将一个模式序列作为条件嵌套再个体模式里面
//个体模式：单例(循环一次) 循环模式(多个)  量词(循环次数  greedy贪心)  条件(.where .or .until)
//oneOrMore   必须指定until结束    迭代条件：  .where((value,ctx))//上下文状态
//.next()下一个必须是    followedBy后面跟着     followedByAny之前匹配过的也可以用
//notNext()  不让某个事件紧邻前一个时间      notFollowedBy不想让每个事件再两个事件之间发生
//所有模式必须以begin()   不能以notFollowedBy结束    not类型不能被optional修饰   还可以指定时间约束
    import org.apache.flink.cep.pattern.Pattern
    import org.apache.flink.cep.scala.CEP
    import org.apache.flink.streaming.api.windowing.time.Time
    import org.apache.flink.streaming.api.scala.createTypeInformation
    import java.util
//  https://nightlies.apache.org/flink/flink-docs-release-1.15/docs/libs/cep/
    case class UserBehavior(userId:Long,itemId:Long,timestamp:Long)
    val cepdata = sourceTestEnv.fromElements[UserBehavior](
      new UserBehavior(1l,1l,1663828406000l),
      new UserBehavior(2l,2l,1663828416000l),
      new UserBehavior(3l,3l,1663828426000l))
    val pattern1 = Pattern.begin[UserBehavior]("first")
      .next("second").followedBy("end")

    CEP.pattern(cepdata,pattern1)


    import org.apache.flink.cep.PatternSelectFunction
    class CepSelectFunctiob extends PatternSelectFunction[Long,Long]{//乱序数据也搞得定
      override def select(pattern: util.Map[String, util.List[Long]]): Long = {
        val firstFail = pattern.get("begin").iterator().next()
        val failFail  = pattern.get("next").iterator().next()
        1L
      }
    }

    val pattern2 = Pattern.begin("first")//.where(_.login="false")
      .followedBy("second")//.where(_.login="false")
      .within(Time.seconds(30))//创建订单后支付  30min超时

    import org.apache.flink.cep.PatternTimeoutFunction
    class OrderTimeOutSelect extends PatternTimeoutFunction [Long,Long] {
      override def timeout(pattern: util.Map[String, util.List[Long]], timeoutTimestamp: Long): Long = {
        timeoutTimestamp //超时的时间
        val timeout = pattern.get("begin").iterator().next()
        1l
      }
    }