可能是史上覆盖flinksql功能最全的demo--part2

接上一篇文章可能是史上覆盖flinksql功能最全的demo–part1

Flink SQL join Table的5种方式

静态表常规join

静态表常规join指的是：静态表join静态表

例：按地区和优先级显示特定日期的客户及其订单

-- 订单表dev_orders(基于S3的静态表) join MySQL表
SET execution.type=batch;
USE CATALOG hive;
SELECT
  r_name AS `region`,
  o_orderpriority AS `priority`,
  COUNT(DISTINCT c_custkey) AS `number_of_customers`,
  COUNT(o_orderkey) AS `number_of_orders`
FROM dev_orders
JOIN prod_customer ON o_custkey = c_custkey
JOIN prod_nation ON c_nationkey = n_nationkey
JOIN prod_region ON n_regionkey = r_regionkey
WHERE
  FLOOR(o_ordertime TO DAY) = TIMESTAMP '2020-04-01 0:00:00.000'
  AND NOT o_orderpriority = '4-NOT SPECIFIED'
GROUP BY r_name, o_orderpriority
ORDER BY r_name, o_orderpriority;

动态表常规join

动态表常规join指的是：动态表join静态表

例：将上例中的静态订单表改为动态表，查询相同也的业务逻辑

-- 将静态订单表dev_orders改为动态订单表prod_orders，移除ORDER BY子句（流处理引擎不支持）
SET execution.type=streaming;
USE CATALOG hive;