32.flink sql 语法大全

摘要

写在前面：坐标上海，房补1500 三餐管饭， 有意向的请私聊简历砸过来，注意：不要无经验，不要无经验，要3-5年经验的开发者。🐧1466927252，游戏公司，游戏公司，此话有效期：2023/03/01-2023/04/30
在阅读下面给文章之前你需要了解：flink table api 之 time Attributes
Flink parses SQL using Apache Calcite, which supports standard ANSI SQL(ANSI SQL是sql语言标准).
Calcite 是什么？
如果用一句话形容 Calcite，Calcite 是一个用于优化异构数据源的查询处理的基础框架。
最近十几年来，出现了很多专门的数据处理引擎。例如列式存储 (HBase)、流处理引擎 (Flink)、文档搜索引擎 (Elasticsearch) 等等。这些引擎在各自针对的领域都有独特的优势，在现有复杂的业务场景下，我们很难只采用当中的某一个而舍弃其他的数据引擎。当引擎发展到一定成熟阶段，为了减少用户的学习成本，大多引擎都会考虑引入 SQL 支持，但如何避免重复造轮子又成了一个大问题。基于这个背景，Calcite 横空出世，它提供了标准的 SQL 语言、多种查询优化和连接各种数据源的能力，将数据存储以及数据管理的能力留给引擎自身实现。更多

SELECT语句是通过TableEnvironment的sqlQuery()方法指定的。该方法将SELECT语句的结果作为Table对象返回。Table对象可以在后续的SQL和Table API查询中使用，可以转换为DataStream，也可以写入到tableink。SQL和Table API查询可以无缝混合，可以整体优化并转换为单个程序。
Table对象是flink Table Api/Sql 的核心， 不仅可以直接用sql查询，也可以用table对象对sql封装的方法查询，比如下面的两种方式一样：

Table  table =...;
//调用table封装的方法
table.select($("*"))
//调用table  sql
tableEnv.sqlQuery("select * from " + table)

//上面两种查询方法是一样的，但是本篇文章不讲见table封装的原生调用方式，本文章的重点是flink  sql.
//这两种用法本质上都是一样的，近乎能实现所有同样的功能。 
//后面有时间的话我会单独出一篇文章讲解tabel原生的调用方式。

先来看一个例子

StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);

// 定义source
DataStream<Tuple3<Long, String, Integer>> ds = env.addSource(...);

// datastream-to-table转换并给字段按照顺序命名
Table table = tableEnv.fromDataStream(ds, $("user"), $("product"), $("amount"));
//执行sql查询,计算结果会作为一个新的Table  对象返回。
Table result = tableEnv.sqlQuery(
  "SELECT SUM(amount) FROM " + table + " WHERE product LIKE '%Rubber%'");

//datastream-to-table视图， 并给字段按照顺序命名
tableEnv.createTemporaryView("Orders", ds, $("user"), $("product"), $("amount"));
// 对视图执行sql查询，  计算结果会作为一个新的Table  对象返回。
Table result2 = tableEnv.sqlQuery(
  "SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%'");

// schema 定义列的信息
final Schema schema = new Schema()
    .field("product", DataTypes.STRING())
    .field("amount", DataTypes.INT());
//定义table  sink(注意这种方式一般不用了，可直接用ddl建表的形式代替)
tableEnv.connect(new FileSystem().path("/path/to/file"))
    .withFormat(...)
    .withSchema(schema)
    .createTemporaryTable("RubberOrders");

//用 INSERT INTO将计算结果输出
tableEnv.executeSql(
  "INSERT INTO RubberOrders SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%'");

核心注意的点：sqlQuery 返回一个新的Table 对象

1.executeSql(…)

通过TableEnvironment.executeSql()可以执行SELECT语句，该方法可以将执行结果作为TableResult返回。与sqlQuery 语句类似，sqlQuery 可以使用Table.execute()方法执行Table对象，将查询的内容收集到本地客户端。
TableResult中的collect()方法返回一个可关闭的行迭代器。除非收集了所有结果数据，否则选择作业不会完成。我们应该通过CloseableIterator#close()方法主动关闭作业，以避免资源泄漏。我们还可以通过TableResult.print()方法将选择结果打印到客户端控制台。TableResult中的结果数据只能被访问一次。因此，collect()和print()只能选择一个去调用。

StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env, settings);

tableEnv.executeSql("CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...)");

// execute SELECT statement
TableResult tableResult1 = tableEnv.executeSql("SELECT * FROM Orders");
// use try-with-resources statement to make sure the iterator will be closed automatically
try (CloseableIterator<Row> it = tableResult1.collect()) {
   
    while(it.hasNext()) {
   
        Row row = it.next();
        // handle row
    }
}

// execute Table
TableResult tableResult2 = tableEnv.sqlQuery("SELECT * FROM Orders").execute();
tableResult2.print();

2. hints

2.1 sql hits

sql hints 可以和sql语句混杂在一起，其可以修改sql的执行计划( execution plans).

Generally a hint can be used to:

1. Enforce planner: there’s no perfect planner, so it makes sense to implement hints to allow user better control the execution; (没有完美的planner, 故而更好地控制执行计划有重大意义。)

2. Append meta data(or statistics): some statistics like “table index for scan” and “skew info of some shuffle keys” are somewhat dynamic for the query, it would be very convenient to config them with hints because our planning metadata from the planner is very often not that accurate;(为查询返回元数据和某些统计数据，比如返回 扫描的索引信息又或者发生了倾斜的key信息等， 这很方便，因为我们从planner中获得的源数据信息不够精确。通过sql hints我们可以控制这个行为。)

3. Operator resource constraints: for many cases, we would give a default resource configuration for the execution operators, i.e. min parallelism or managed memory (resource consuming UDF) or special resource requirement (GPU or SSD disk) and so on, it would be very flexible to profile the resource with hints per query(instead of the Job). (资源约束：在大多数情况下，我们可以通过configration配置执行参数，比如并行度，内存等。这些配置作用在job作用域，如果我们想控制某个sql query则可以通过sql hints )

2.2.Dynamic Table Options

Dynamic table options allows to specify or override table options dynamically, different with static table options defined with SQL DDL or connect API, these options can be specified flexibly in per-table scope within each query.(Dynamic table options允许动态的指定或者覆盖tabe的参数，和静态参数不同的是，动态表参数可以将作用域和当前table对象绑定。 而静态参数作用在全局)

Note: Dynamic table options default is forbidden to use because it may change the semantics of the query. You need to set the config option table.dynamic-table-options.enabled to be true explicitly (default is false), See the Configuration for details on how to set up the config options.
（此行为默认禁用， table.dynamic-table-options.enabled设置为true方可启用，请参考：table api configration查询如何设置启用）

2.3 语法demo

CREATE TABLE kafka_table1 (id BIGINT, name STRING, age INT) WITH (...);
CREATE TABLE kafka_table2 (id BIGINT, name STRING, age INT) WITH (...);

-- override table options in query source
select id, name from kafka_table1 /*+ OPTIONS('scan.startup.mode'='earliest-offset') */;

-- override table options in join
select * from
    kafka_table1 /*+ OPTIONS('scan.startup.mode'='earliest-offset') */ t1
    join
    kafka_table2 /*+ OPTIONS('scan.startup.mode'='earliest-offset') */ t2
    on t1.id = t2.id;

-- override table options for INSERT target table
insert into kafka_table1 /*+ OPTIONS('sink.partitioner'='round-robin') */ select * from kafka_table2;

3.数据准备

学习flink sql之前先准备数据， mysql我用的8+版本。

CREATE TABLE `person` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(260) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int NOT NULL COMMENT '年龄',
  `sex` tinyint NOT NULL COMMENT '0:女, 1男',
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '邮箱',
  `event_time` varchar(255) DEFAULT NULL,
  `num` int DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;


INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (1, 'pg01', 12, 1, 'www.johngo689.com', '2022-09-19 23:01:01', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (2, 'pg01', 13, 0, 'www.johngo689.com', '2022-09-19 23:01:02', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (3, 'pg01', 14, 0, 'www.johngo689.com', '2022-09-19 23:01:03', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (4, 'pg01', 15, 0, 'www.johngo689.com', '2022-09-19 23:01:04', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (5, 'pg01', 16, 1, 'www.johngo689.com', '2022-09-19 23:01:05', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (6, 'pg01', 17, 1, 'www.johngo689.com', '2022-09-19 23:01:06', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (7, 'pg01', 18, 0, 'www.johngo689.com', '2022-09-19 23:01:07', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (8, 'pg01', 19, 0, 'www.johngo689.com', '2022-09-19 23:01:08', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (9, 'pg01', 20, 1, 'www.johngo689.com', '2022-09-19 23:01:09', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (10, 'pg01', 21, 0, 'www.johngo689.com', '2022-09-19 23:01:10', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (16, 'pg01', 21, 0, 'www.johngo689.com', '2022-09-19 23:01:11', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (17, 'pg01', 21, 0, 'www.johngo689.com', '2022-09-19 23:01:12', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (18, 'pg01', 21, 0, 'www.johngo689.com', '2022-09-19 23:01:13', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (19, 'pg02', 13, 0, 'www.johngo689.com', '2022-09-19 23:01:01', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (20, 'pg02', 14, 0, 'www.johngo689.com', '2022-09-19 23:01:02', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (21, 'pg02', 15, 0, 'www.johngo689.com', '2022-09-19 23:01:03', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (22, 'pg02', 20, 1, 'www.johngo689.com', '2022-09-19 23:01:04', 1);
INSERT INTO `TestDB`.`person`(`id`, `name`, `age`, `sex`, `email`, `event_time`, `num`) VALUES (23, 'pg02', 21