Calcite Sql解析

Calcite自定义SQL解析(经常使用的方法和配置)(mvn版本：1.21.0)

1、config.fmpp(calcite模板配置)

data: {
  parser: {
    # Generated parser implementation package and class name.
    # 生成解析器实现类包和名称   包名   实体类名
    package: "com.meton.flink.sql.parser",
    class: "MetonSqlParserImpl",

    # List of additional classes and packages to import.
    # Example. "org.apache.calcite.sql.*", "java.util.List".
    # 导入处理的语句
    imports: [
        "org.apache.calcite.sql.SqlDrop",
        "org.apache.calcite.sql.SqlCreate",
        "java.util.List",
        "java.util.ArrayList"
        ......
    ]
    
    # List of new keywords. Example: "DATABASES", "TABLES". If the keyword is not a reserved
    # keyword add it to 'nonReservedKeywords' section.
    # 新关键字列表。示例:“DATABASES”、“TABLES”。若是关键字不是一个保留关键字，将其添加到“无保留关键字”部分。
    keywords: [
        "COMMENT",
        "PARTITIONED",
        "IF",
        "WATERMARK",
        "OVERWRITE",
        "STRING",
        ......
    ]
    
    # List of keywords from "keywords" section that are not reserved.
    # “keywords”部分中未保留的关键字列表。
    nonReservedKeywords: [
        "A"
        "ABSENT"
        "ABSOLUTE"
        "ACTION"
        "ADA"
        "ADD"
        "ADMIN"
        ......
        # not in core, added in Flink
        "PARTITIONED",
        "IF",
        "OVERWRITE"
    ]
    
    # List of methods for parsing custom SQL statements.
    # Return type of method implementation should be 'SqlNode'.
    # Example: SqlShowDatabases(), SqlShowTables().
    # 用于解析自定义SQL语句的方法列表。
    statementParserMethods: [
    ]
    
    # List of methods for parsing custom literals.
    # Return type of method implementation should be "SqlNode".
    # Example: ParseJsonLiteral().
    # 解析自定义文本的方法列表
    literalParserMethods: [
    ]
    
    # List of methods for parsing custom data types.
    # Return type of method implementation should be "SqlIdentifier".
    # 用于解析自定义数据类型的方法列表。
    dataTypeParserMethods: [
    ]
    
    # List of methods for parsing builtin function calls.
    # Return type of method implementation should be "SqlNode".
    # Example: DateFunctionCall().
    builtinFunctionCallMethods: [
    ]

    # List of methods for parsing extensions to "ALTER <scope>" calls.
    # Each must accept arguments "(SqlParserPos pos, String scope)".
    # Example: "SqlUploadJarNode"
    # 每一个都必须接受参数 "(SqlParserPos pos, String scope)".
    # 解析扩展到“ALTER ”调用的方法。
    alterStatementParserMethods: [
    ]
    
    # List of methods for parsing extensions to "CREATE [OR REPLACE]" calls.
    # Each must accept arguments "(SqlParserPos pos, boolean replace)".
    # 解析扩展以"CREATE [OR REPLACE]"调用的方法列表。
    # 每一个都必须接受参数 "(SqlParserPos pos, String scope)".
    createStatementParserMethods: [
    ]
    
    # List of methods for parsing extensions to "DROP" calls.
    # Each must accept arguments "(SqlParserPos pos)".
    # 解析扩展到“DROP”调用的方法列表。
    # 每一个都必须接受参数 "(SqlParserPos pos)".
    dropStatementParserMethods: [
    ]
    
    # Binary operators tokens
    binaryOperatorsTokens: [
    ]
    
    # Binary operators initialization
    extraBinaryExpressions: [
    ]
         
    # List of files in @includes directory that have parser method
    # implementations for parsing custom SQL statements, literals or types
    # given as part of "statementParserMethods", "literalParserMethods" or
    # "dataTypeParserMethods".
    # @includes目录中具备解析器方法的文件列表
    # 解析自定义SQL语句、文本或类型的实现
    # 做为“statementParserMethods”、“literalParserMethods”或“dataTypeParserMethods”的一部分给出。
    implementationFiles: [
        "parserImpls.ftl"
    ]
    
    # List of additional join types. Each is a method with no arguments.
    # Example: LeftSemiJoin()
    # 其余链接类型的列表。每一个方法都是没有参数的方法。
    joinTypes: [
    ]
    
    includePosixOperators: false
    includeCompoundIdentifier: true
    includeBraces: true
    includeAdditionalDeclarations: false
  }
}

# freemarker模板的位置
freemarkerLinks: {
    includes: includes/
}

2、配置和Parser.jj文件结合说明

package、class、imports：主要是负责导入包,设置编译package目录,编译的类名

keywords：定义关键字

nonReservedKeywords：keywords定义关键字中,保留的关键字

joinTypes：join类型

statementParserMethods：解析自定义SQL语句的方法列表，必须实现SqlNode

literalParserMethods：解析自定义文字的方法列表，必须实现SqlNode

dataTypeParserMethods：解析自定义数据类型的方法列表，必须实现SqlIdentifier

alterStatementParserMethods：解析自定义alter语句，必须有构造方法(SqlParserPos pos, String scope)

createStatementParserMethods：解析自定义create语句，必须有构造方法(SqlParserPos pos, boolean replace)

dropStatementParserMethods：解析自定义drop语句,必须有构造方法(SqlParserPos pos)

implementationFiles：模板文件

includeCompoundIdentifier：是否包含CompoundIdentifier解析

includeBraces

includeAdditionalDeclarations

3、Parser.jj经常使用方法

getPos()：获取当前token的配置，自定义解析的时候使用

StringLiteral：主要解析是语句中的string类型的字段
select * from table where a='string'

Identifier：解析Identifier字段, 返回string类型
select SqlIdentifier from table;

SimpleIdentifier：解析Identifier字段, 返回Identifier
select SqlIdentifier from table;

CompoundIdentifier：解析Identifier字段, 返回Identifier
select Compound.SqlIdentifier from table;
select SqlIdentifier from table;

示例：自定义SQL

1、自定义SQL语法

create function function_name as class_name
[method]
[with] [(key=value)]

create function                             # 建立函数关键字
  hr.custom_function                        # 函数名
as                                          # as关键字
  'com.meton.calcite.func.CustomFunction'   # 类名称
method 'eval'                               # 可选 方法名称 
comment 'comment'                          # 可选 备注信息
property ('a'='b','c'='1')                 # 可选 附件变量

2、语法模板parserImpls.ftl

// 建立函数
SqlNode SqlCreateFunction() :
{
    // 声明变量
    SqlParserPos createPos;
    SqlParserPos propertyPos;
    SqlNode functionName = null;
    String className = null;
    String methodName = null;
    String comment = null;
    SqlNodeList properties = null;
}
{
    <CREATE>   // create 关键字
    {
        createPos = getPos();   // 获取当前token的行列位置
     }
    <FUNCTION>   // function 关键字
         functionName = CompoundIdentifier()   // 函数名
    <AS>    // as关键字
    { className = StringLiteralValue(); }   // 类名
    // if语句
    [
        <METHOD>    // method关键字
        {
            methodName = StringLiteralValue();    // 方法名称
        }
    ]
    // if
    [
        <PROPERTY>   // property 关键字, 设置初始化变量
        {
             propertyPos = getPos();  // 获取关键字位置
             SqlNode property;
             properties = new SqlNodeList(propertyPos);
        }
        <LPAREN>
        [
            property = PropertyValue()
            {
                properties.add(property);
            }
            (
                <COMMA>
                {
                    property = PropertyValue();
                    properties.add(property);
                }
            )*
        ]
        <RPAREN>
    ]
    // if
    [
        <COMMENT> {
            comment = StringLiteralValue();    // 备注
        }
    ]
    {
        return new SqlCreateFunction(createPos, functionName, className, methodName, comment, properties);
    }
}

JAVACODE String StringLiteralValue() {
    SqlNode sqlNode = StringLiteral();
    return ((NlsString) SqlLiteral.value(sqlNode)).getValue();
}


/**
 * 解析SQL中的key=value形式的属性值
 */
SqlNode PropertyValue() :
{
    SqlNode key;
    SqlNode value;
    SqlParserPos pos;
}
{
    key = StringLiteral()
    { pos = getPos(); }
    <EQ> value = StringLiteral()
    {
        return new SqlProperty(getPos(), key, value);
    }
}

3、定义解析结果类

SqlCreateFunction.java

import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.parser.SqlParserPos;

import java.util.List;

public class SqlCreateFunction extends SqlCall {
    private SqlNode functionName;
    private String className;
    private SqlNodeList properties;
    private String methodName;
    private String comment;

    public SqlCreateFunction(SqlParserPos pos,
                             SqlNode functionName, 
                             String className, 
                             String methodName, 
                             String comment,
                             SqlNodeList properties) {
        super(pos);
        this.functionName = functionName;
        this.className = className;
        this.properties = properties;
        this.methodName = methodName;
    }

    public SqlNode getFunctionName() {
        return functionName;
    }

    public String getClassName() {
        return className;
    }

    public String getMethodName() {
        return methodName;
    }

    public SqlNodeList getProperties() {
        return properties;
    }

    public String getComment() {
        return comment;
    }

    @Override
    public SqlOperator getOperator() {
        return null;
    }

    @Override
    public List<SqlNode> getOperandList() {
        return null;
    }

    @Override
    public SqlKind getKind() {
        return SqlKind.OTHER_DDL;
    }

    @Override
    public void unparse(SqlWriter writer, int leftPrec, int rightPrec) {
        writer.keyword("CREATE");
        writer.keyword("FUNCTION");
        functionName.unparse(writer, leftPrec, rightPrec);
        writer.keyword("AS");
        writer.print("'" + className + "'");
        if (methodName != null) {
            writer.newlineAndIndent();
            writer.keyword("METHOD");
            writer.print("'" + methodName + "'");
        }
        if (properties != null) {
            writer.newlineAndIndent();
            writer.keyword("PROPERTY");
            SqlWriter.Frame propertyFrame = writer.startList("(", ")");
            for (SqlNode property : properties) {
                writer.sep(",", false);
                writer.newlineAndIndent();
                writer.print("  ");
                property.unparse(writer, leftPrec, rightPrec);
            }
            writer.newlineAndIndent();
            writer.endList(propertyFrame);
        }
        if (comment != null) {
            writer.newlineAndIndent();
            writer.keyword("COMMENT");
            writer.print("'" + COMMENT + "'");
        }
    }
}

SqlProperty.java：解析key=value语句

import com.google.common.collect.ImmutableList;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlIdentifier;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlSpecialOperator;
import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.util.NlsString;

import java.util.List;

import static java.util.Objects.requireNonNull;

public class SqlProperty extends SqlCall {
    /**
     * 定义特殊操作符
     */
    protected static final SqlOperator OPERATOR = new SqlSpecialOperator("Property", SqlKind.OTHER);
    private SqlNode key;
    private SqlNode value;

    public SqlProperty(SqlParserPos pos, SqlNode key, SqlNode value) {
        super(pos);
        this.key = requireNonNull(key, "Property key is missing");
        this.value = requireNonNull(value, "Property value is missing");
    }

    @Override
    public SqlOperator getOperator() {
        return OPERATOR;
    }

    @Override
    public List<SqlNode> getOperandList() {
        return ImmutableList.of(key, value);
    }

    @Override
    public SqlKind getKind() {
        return SqlKind.OTHER;
    }

    @Override
    public void unparse(SqlWriter writer, int leftPrec, int rightPrec) {
        key.unparse(writer, leftPrec, rightPrec);
        writer.keyword("=");
        value.unparse(writer, leftPrec, rightPrec);
    }

    public SqlNode getKey() {
        return key;
    }

    public SqlNode getValue() {
        return value;
    }

    public String getKeyString() {
        return key.toString();
    }

    public String getValueString() {
        return ((NlsString) SqlLiteral.value(value)).getValue();
    }
}

4、定义config.fmpp(calcite模板配置)

package: "com.meton.flink.calcite.parser"
class: "MetonSqlParserImpl",

imports: [
    "com.meton.flink.calcite.parser.SqlCreateFunction"
    "com.meton.flink.calcite.parser.sample.model.*"
]

定义关键字 config.fmpp
keywords: [
    "PARAMS"
    "COMMENT"
    "PROPERTY" 
]

定义自定义解析statementParserMethods
statementParserMethods: [
    "SqlCreateFunction()"
]

测试类

import org.apache.calcite.config.Lex;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.dialect.OracleSqlDialect;
import org.apache.calcite.sql.parser.SqlParseException;
import org.apache.calcite.sql.parser.SqlParser;

public class SqlCreateFunctionMain {
    public static void main(String[] args) throws SqlParseException {
        // 解析配置 - mysql设置(Lex.MYSQL  Lex.Java  .etc.)
        SqlParser.Config mysqlConfig = SqlParser.configBuilder()
                .setParserFactory(MetonSqlParserImpl.FACTORY)   // 定义解析工厂
                .setLex(Lex.MYSQL)
                .build();
                
        SqlParser parser = SqlParser.create("", mysqlConfig);   // 建立解析器
        String sql = "create function " +
                "hr.custom_function as 'com.meton.flink.calcite.func.CustomFunction' " +
                "method 'eval'  " +
                "property ('a'='b','c'='1') ";   // Sql语句
        SqlNode sqlNode = parser.parseQuery(sql);   // 解析sql
        System.out.println(sqlNode.toSqlString(OracleSqlDialect.DEFAULT));   // 还原某个方言的SQL
    }
}