How to combine Flink Table and SQL with Apache Calcite

How to combine Flink Table and SQL with Apache Calcite
What is Apache Calcite?

Apache Calcite is designed for Hadoop's new SQL engine. It provides standard SQL languages, multiple query optimizations, and the ability to connect to various data sources. In addition, Calcite also provides a query engine for OLAP and stream processing. Since it became an Apache incubator project in 2013, it has become increasingly eye-catching in Hadoop and has been integrated by many projects. For example, Flink, Storm, Drill, and Phoenix depend on it for SQL parsing and optimization.

Flink combined with Calcite

Flink Table API & SQL retains a unified interface for querying the relationship between streaming data and static data, and uses the Calcite Query Optimization Framework and SQL parser. This design is based on the APIS built by Flink. DataStream API provides low-latency and high-throughput stream processing capabilities, exactly-once semantics, and can be processed based on event-time. DataSet also has stable and efficient memory operators and streamlined data exchange. All improvements to Flink's core APIs and engines are automatically applied to Table APIs and SQL.
A stream SQL statement is generally divided into the following stages:

1. 1. SQL Parser: parses an SQL statement into an AST (syntax tree) through java cc, and uses SqlNode to represent the AST in calcite;
   
2. 2. SQL Validator: used in combination with a digital dictionary to verify the SQL syntax;
   
3. 3. Generate a Logical Plan: Convert the AST represented by sqlNode into a LogicalPlan, represented by relNode;
   
4. 4. Generate optimized LogicalPlan: optimize the logical Plan based on calcite rules,
   
5. Optimize the logical Plan based on the optimized rules customized by flink;
   
6. 5. Generate Flink PhysicalPlan: This is also based on the rules in flink. optimized LogicalPlan is converted into a physical execution plan of Flink;
   
7. 6. convert a physical execution plan to Flink ExecutionPlan: Call the corresponding tanslateToPlan method to convert it and program it into Flink operators using CodeGen.

If you submit a task through the table api, it will also go through the calcite optimization and other stages. The basic process is similar to running SQL directly:

1. 1. table api parser: flink also expresses the computing logic expressed by table api as a tree and uses treeNode to detable the table;
   
2. The computing logic of each node on this tree is expressed by Expression.
   
3. 2. Validate: bind the Unresolved Expression of each node of the tree in combination with the digital dictionary to generate a Resolved Expression;
   
4. 3. Generate a Logical Plan: traverse each node of the number in sequence, and call the construct Method to convert the node originally expressed in treeNode into a relNode expressed in the internal data structure of calcite. LogicalPlan is generated and expressed by relNode;
   
5. 4. Generate optimized LogicalPlan: optimize the logical Plan based on calcite rules,
   
6. Optimize the logical Plan based on the optimized rules customized by flink;
   
7. 5. Generate Flink PhysicalPlan: This is also based on the rules in flink. optimized LogicalPlan is converted into a physical execution plan of Flink;
   
8. 6. convert a physical execution plan to Flink ExecutionPlan: Call the corresponding tanslateToPlan method to convert it and program it into Flink operators using CodeGen.

Therefore, flink provides two APIs for relational queries, Table API and SQL. Both APIs are verified using the catalog that contains the registered Table. Except for the differences in the conversion from the computing logic to the logical plan in the initial stage, they are similar in the future. At the same time, stream and batch queries look exactly the same. However, flink uses different rules for optimization based on the nature of the data source (streaming and static), and finally the optimized plan is transferred to the conventional Flink DataSet or DataStream program. Therefore, we will use the table api as an example to explain how flink uses calcite for resolution optimization and then convert it back to DataStream.

Parsing and executing Table api tasks

1. // Set up execution environment
   
2. Val env = StreamExecutionEnvironment. getExecutionEnvironment
   
3. Val tEnv = TableEnvironment. getTableEnvironment (env)
   
4. // Define the data source
   
5. Val dataStream = env. fromCollection (Seq (Order (1L, "beer", 3), Order (1L, "diaper", 4), Order (3L, "rubber", 2 )))
   
6. // Convert DataStream to table, that is, register the data source into a table in TableEnvironment.
   
7. Val orderA = dataStream. toTable (tEnv)
   
8. // Use the table api to execute the business logic. The generated tab contains the flink's own logicalPlan, which is represented by LogicalNode.
   
9. Val tab = orderA. groupBy ('user). select ('user, 'amount. sum)
   
10. . Filter ('user <2L)
    
11. // Convert the table into DataStream. the header here involves the generation of the calcite logical plan.
    
12. // Optimize and convert to the flink operator executed by cocoa
    
13. Val result = tab. toDataStream [Order]

Register a data source as a table

The process of converting DataStream into a table is actually the process of registering DataStream into a table in TableEnvironment, mainly by calling tableEnv. fromDataStream.

1. // Generate a unique table Named val name = createUniqueTableName ()
   
2. // Generate the table's scheme val (fieldNames, fieldIndexes) = getFieldInfo [T] (dataStream. getType)
   
3. // Input dataStream to create a table that can be recognized by calcite
   
4. Val dataStreamTable = new DataStreamTable [T] (
   
5. DataStream,
   
6. FieldIndexes,
   
7. FieldNames, None, None)
   
8. // Register the registerTableInternal (name, dataStreamTable) table in the digital dictionary)

Scan will be called at the end of the above function implementation. Here, a CatalogNode object will be created in the header, carrying the table path that can be found in the data source. In fact, it is a leaf node in the Flink logic tree.

Generate Flink's own logical plan

 val tab = orderA.groupBy('user).select('user, 'amount.sum)      .filter('user < 2L) 

Each time the table api is called above, a node of the Flink logical plan is generated. For example, the call of grouBy and select generates node projects, Aggregate, and Project, while the call of filter generates node filters. The logical relationships between these nodes constitute a logical tree expressed by the Flink data structure:

Because this example is very simple, there are no two subnodes. Some people may wonder about the implementation here. The form parameter type of the filter function is Expression, and we pass in "'user <2L", isn't it? In fact, this is scala's awesome feature: implicit conversion, these passed expressions will be automatically converted to expressions first. The definitions of these implicit conversions are basically in the interface class ImplicitExpressionOperations. Scala converts the user string to the Symbol type. By implicit conversion of the "'user <2L" representation, an LessThan object is generated, which has two child expressions: UnresolvedFieldReference ("user") and Liter ("2 "). This LessThan object serves as the condition of the Filter object.

Flink's logical plan is converted into a calcite identifiable logical plan

According to the above analysis, we only generated the Flink logical Plan. We must convert it into the calcite logical Plan so that we can use the powerful optimization rules of calcite. In Flink, the construct Method of each node is called the next time to convert the Flink node into the RelNode node of calcite.

1. // ----- Filter construct create the LogicalFilter node of Calcite ----
   
2. // Traverse subnodes first
   
3. Child. construct (relBuilder)
   
4. // Create a LogicalFilter
   
5. RelBuilder. filter (condition. toRexNode (relBuilder ))
   
6. 
   
7. // ----- Construct of the Project creates the LogicalProject node of Calcite ----
   
8. // Traverse subnodes first
   
9. Child. construct (relBuilder)
   
10. // Create a LogicalProject
    
11. RelBuilder. project (
    
12. ProjectList. map (_. toRexNode (relBuilder). asJava,
    
13. ProjectList. map (_. name). asJava,
    
14. True)
    
15. 
    
16. // ----- Construct of Aggregate create the LogicalAggregate node of Calcite ----
    
17. Child. construct (relBuilder)
    
18. RelBuilder. aggregate (
    
19. RelBuilder. groupKey (groupingExpressions. map (_. toRexNode (relBuilder). asJava ),
    
20. Aggresponexpressions. map {
    
21. Case Alias (dependencies: Aggregation, name, _) => dependencies. toAggCall (name) (relBuilder)
    
22. Case _ => throw new RuntimeException ("This shoshould never happen .")
    
23. }. AsJava)
    
24. 
    
25. // ----- Construct of CatalogNode create the LogicalTableScan node of Calcite ----
    
26. RelBuilder. scan (tablePath. asJava)

After the above conversion, the Calcite logical plan is generated:

Optimize logical plans and convert them to Flink physical plans

Flink is encapsulated in the optimize method. The specific implementation of this method is as follows:

1. // Remove associated subqueries
   
2. Val decorPlan = RelDecorrelator. decorrelateQuery (relNode)
   
3. // Convert the time identifier. For example, if the rowtime identifier exists, we will introduce TimeMaterializationSqlFunction operator,
   
4. // This operator will be used in codeGen
   
5. Val convPlan = RelTimeIndicatorConverter. convert (decorPlan, getRelBuilder. getRexBuilder)
   
6. // Normalize the logica plan. For example, if the filtering condition of a Filter is true, we can remove the filter directly.
   
7. Val normRuleSet = getNormRuleSet
   
8. Val normalizedPlan = if (normRuleSet. iterator (). hasNext ){
   
9. RunHepPlanner (HepMatchOrder. BOTTOM_UP, normRuleSet, convPlan, convPlan. getTraitSet)
   
10. } Else {
    
11. ConvPlan
    
12. }
    
13. // Optimize the logical plan, adjust the upstream and downstream nodes between nodes to reach the optimized computing logic, and set
    
14. // The node is converted to a node born in FlinkLogicalRel.
    
15. Val logicalOptRuleSet = getLogicalOptRuleSet
    
16. // Replace the traitSet with FlinkConventions. LOGICAL, indicating that the transformed Tree node requires the derivation and Interface
    
17. // FlinkLogicalRel
    
18. Val logicalOutputProps = relNode. getTraitSet. replace (FlinkConventions. LOGICAL). simplify ()
    
19. Val logicalPlan = if (logicalOptRuleSet. iterator (). hasNext ){
    
20. RunVolcanoPlanner (logicalOptRuleSet, normalizedPlan, logicalOutputProps)
    
21. } Else {
    
22. NormalizedPlan
    
23. }
    
24. // Convert the optimized logical plan to the physical plan of Flink.
    
25. // Convert a node to a node generated in DataStreamRel
    
26. Val physicalOptRuleSet = getPhysicalOptRuleSet
    
27. Val physicalOutputProps = relNode. getTraitSet. replace (FlinkConventions. DATASTREAM). simplify ()
    
28. Val physicalPlan = if (physicalOptRuleSet. iterator (). hasNext ){
    
29. RunVolcanoPlanner (physicalOptRuleSet, logicalPlan, physicalOutputProps)
    
30. } Else {
    
31. LogicalPlan
    
32. }

This section involves multiple stages, and each stage is nothing more than using Rule to optimize and improve the logical plan. Let's look at the logic of each Rule. What should I do if I want to customize a Rule myself? It is declared to be a class that derives from RelOptRule, and then required to pass in the RelOptRuleOperand object in the constructor. The object needs to pass in the node type that your Rule will match. If your custom Rule is only used for LogicalTableScan nodes, your operand object should be operand (LogicalTableScan. class, any ()). Just like this

1. Public class TableScanRule extends RelOptRule {
   
2. //~ Static fields/initializers ---------------------------------------------
   
3. Public static final TableScanRule INSTANCE = new TableScanRule ();
   
4. //~ Constructors -----------------------------------------------------------
   
5. Private TableScanRule (){
   
6. Super (operand (LogicalTableScan. class, any ()));
   
7. }
   
8. // By default, True is returned, and matches can be inherited. The implementation logic in it is to determine whether to convert and call onMatch.
   
9. @ Override
   
10. Public boolean matches (RelOptRuleCall call ){
    
11. Return super. matches (call );
    
12. }
    
13. //~ Methods ----------------------------------------------------------------
    
14. // Convert the current node
    
15. Public void onMatch (RelOptRuleCall call ){
    
16. Final LogicalTableScan oldRel = call. rel (0 );
    
17. RelNode newRel =
    
18. OldRel. getTable (). toRel (
    
19. RelOptUtil. getContext (oldRel. getCluster ()));
    
20. Call. transformTo (newRel );
    
21. }
    
22. }

The above code optimizes and transforms the logical plan, and finally converts each Node of the logical plan into a Flink Node, which can be a physical plan. The final result of the entire conversion process is as follows:

 
 

  
  
== Optimized pyhical Plan == DataStreamGroupAggregate(groupBy=[user], select=[user, SUM(amount) AS TMP_0]) 
  
  
DataStreamCalc(select=[user, amount], where=[<(user, 2)]) 
  
  
DataStreamScan(table=[[_DataStreamTable_0]]) 
 
 

We found that the Filter node moved down in the tree structure, so that the data is filtered and aggregated to reduce the calculation workload.

Generate plans that can be executed by Flink

This part only needs to recursively call the translateToPlan method of DataStreamRel on each node. This method is converted to various operators that use CodeGen to program to Flink. Now it is equivalent to a program developed using Flink DataSet or DataStream API. The entire process is basically like this:

1. = Physical Execution Plan =
   
2. Stage 1: Data Source
   
3. Content: collect elements with CollectionInputFormat
   
4. Stage 2: Operator content: from: (user, product, amount)
   
5. Ship_strategy: REBALANCE Stage 3: Operator content: where: (<(user, 2), select: (user, amount)
   
6. Ship_strategy: FORWARD Stage 5: Operator content: groupBy: (user), select: (user, SUM (amount) AS TMP_0)
   
7. Ship_strategy: HASH

Summary

However, this example ignores the most interesting part of stream processing: window aggregate and join. How can these operations be expressed in SQL? The Apache Calcite Community proposes a proposal to discuss the syntax and semantics of SQL on streams. The Community describes stream SQL of Calcite as a standard SQL extension rather than another SQL-like language. This has many benefits. First, those familiar with SQL standards can analyze streaming data without learning New syntaxes. The queries for static tables and stream tables are almost the same and can be easily transplanted. In addition, you can query static tables and stream tables at the same time. This is the same as flink's vision. batch processing is considered as a special stream processing (batch processing is considered as a finite stream ). Finally, using standard SQL for stream processing means many mature tools.