Customize SQL parser based on calculate

Posted by keziah on Mon, 07 Feb 2022 10:05:32 +0100

Recently, the project team asked to write an SQL script, and the relevant parsing tools can customize the verification rules. They began to study the learning of Irony framework based on C#; Due to my weak foundation, there are not many resources for this framework on the Internet. Finally, the Apache compute framework was abandoned. I borrowed some resources and blogs on the Internet and finally finished the demo of custom rules. Now the learning process and problems are sorted out as follows. I hope it can help new partners who also want to learn from calcium.

  • 1> Download IDE and configure maven environment

    My demo is developed based on eclipse, and other IDE environments can study by themselves
    1. Download maven
    http://maven.apache.org/download.cgi

    1.2. Unzip the downloaded compressed package and configure the settings in maven xml

    Partial configuration description

    D:\JAVA\MavenJar
    Set the local warehouse address and Alibaba cloud warehouse address


    1.3 configuring maven environment in eclipse
    Window–>Preferences–>Maven–>User Settings

  • 2> Download the calcite source package
    Download address: https://gitee.com/Bomy/calcite
    2.1: copy all files under code\src\main\codegen to your own code path

  • 3> Open eclipse to create Maven project
    3.1 project creation

    3.2. Copy all codes in code \ SRC \ main \ codegrand in the cacite source code to the main folder, and refresh the project after copying

    3.3. Build maven project. Note here that you need to configure the compilation plug-ins, mainly including freemaker and javacc. Here is my POM xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>cn.wupeng</groupId>
  <artifactId>FirstCalciteProject</artifactId>
  <version>0.0.1-SNAPSHOT</version>
   <dependencies>
        <dependency>
            <groupId>org.apache.calcite</groupId>
            <artifactId>calcite-core</artifactId>
            <version>1.19.0</version>
            <!--<version>1.11.0</version>-->
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>
    </dependencies>
  
    <build>
    <plugins>
       <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>javacc-maven-plugin</artifactId>
                <executions>
                    <execution>
                        <id>javacc</id>
                        <goals>
                            <goal>javacc</goal>
                        </goals>
                        <configuration>
                            <sourceDirectory>${project.build.directory}/generated-sources/fmpp</sourceDirectory>
                            <includes>
                                <include>**/Parser.jj</include>
                            </includes>
                            <lookAhead>2</lookAhead>
                            <isStatic>false</isStatic>
                        </configuration>
                    </execution>
                    <execution>
                        <id>javacc-test</id>
                        <phase>generate-test-sources</phase>
                        <goals>
                            <goal>javacc</goal>
                        </goals>
                        <configuration>
                            <sourceDirectory>${project.build.directory}/generated-test-sources/fmpp</sourceDirectory>
                            <outputDirectory>${project.build.directory}/generated-test-sources/javacc</outputDirectory>
                            <includes>
                                <include>**/Parser.jj</include>
                            </includes>
                            <lookAhead>2</lookAhead>
                            <isStatic>false</isStatic>
                        </configuration>
                    </execution>
                </executions>
            </plugin>

            <plugin>
                <groupId>org.apache.drill.tools</groupId>
                <artifactId>drill-fmpp-maven-plugin</artifactId>
                <executions>
                    <execution>
                        <configuration>
                            <config>src/main/codegen/config.fmpp</config>
                            <output>${project.build.directory}/generated-sources/fmpp</output>
                            <templates>src/main/codegen/templates</templates>
                        </configuration>
                        <id>generate-fmpp-sources</id>
                        <phase>validate</phase>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
       
       </plugins>
</build>
</project>

pom. If there is an error in execution in XML, please follow the following method

Window–>Preferences–>Maven–>Errors/Warnings

3.4. Create SQL parsing class SqlWuPeng, and the package path is org apache. calcite. sql

package org.apache.calcite.sql;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.util.SqlVisitor;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.sql.validate.SqlValidatorScope;
import org.apache.calcite.util.Litmus;
public class SqlWuPeng extends SqlNode {
    private String jackyString;
    private SqlParserPos pos;
    public  SqlWuPeng(SqlParserPos pos, String jackyString){
        super(pos);
        this.pos = pos;
        this.jackyString = jackyString;
    }

    public String getJackyString(){
        System.out.println("getJackyString");
        return this.jackyString;
    }

    @Override
    public SqlNode clone(SqlParserPos sqlParserPos) {
        System.out.println("clone");
        return null;
    }

    @Override
    public void unparse(SqlWriter sqlWriter, int i, int i1) {
        sqlWriter.keyword("hello");
        sqlWriter.keyword("calcite");
        sqlWriter.print("\n");
        sqlWriter.keyword("" + jackyString + "");
    }

    @Override
    public void validate(SqlValidator sqlValidator, SqlValidatorScope sqlValidatorScope) {
        System.out.println("validate");
    }

    @Override
    public <R> R accept(SqlVisitor<R> sqlVisitor) {
        System.out.println("accept");
        return null;
    }

    @Override
    public boolean equalsDeep(SqlNode sqlNode, Litmus litmus) {
        System.out.println("equalsDeep");
        return false;
    }
}

3.5. Modify config FMPP file
Find the package: "org. Apache. Cite. SQL. Parser. Impl" and modify the following class to WPSqlParserImpl

3.6. Modify parser JJ file
3.6.1 - > Add resolution class reference

3.6.2 > Add parsing logic

/**
*Custom parsing logic
**/
SqlNode SqlJacky() :
{
     SqlNode stringNode;
}
{
    <hello> <calcite>
    stringNode = StringLiteral()
    {
        return new SqlJacky(getPos(), token.image);
    }
}

3.6.3 > find SqlNode SqlStmt() and add custom parsing logic

3.6.4 > set keyword

3.6.5 > compiling maven project

Compiled project directory

3.6.6 > create a test class WupengParser

package wupeng.test;
import org.apache.calcite.avatica.util.Casing;
import org.apache.calcite.avatica.util.Quoting;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.tools.FrameworkConfig;
import org.apache.calcite.tools.Frameworks;
public class WupengParser {
    public static void main(String[] args) {
        SchemaPlus rootSchema = Frameworks.createRootSchema(true);
        final FrameworkConfig config = Frameworks.newConfigBuilder()
                .parserConfig(SqlParser.configBuilder()
                        //.setLex(Lex.ORACLE)
                        .setParserFactory(WPSqlParserImpl.FACTORY)
                        .setCaseSensitive(false)
                        .setQuoting(Quoting.BACK_TICK)
                        .setQuotedCasing(Casing.TO_UPPER)
                        .setUnquotedCasing(Casing.TO_UPPER)
                        //.setConformance(SqlConformanceEnum.ORACLE_12)
                        .build())
                .build();
//        "jacky 'select ids, name from test where id < 5'";
        String sql = "hello  calcite  'select ids, name from test where id < 5'";
        SqlParser parser = SqlParser.create(sql, config.getParserConfig());
        try {
            SqlNode sqlNode = parser.parseStmt();
            System.out.println(sqlNode.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

Handling of error reporting by referencing user-defined parsing classes

Right click Project – Properties – Java Build Path --Source – Link Source, and then find javacc directory under target to add


3.6.7 > execute test class WupengParser

Acknowledgment: This demo mainly refers to the code of Yu Qi Great God, which has benefited a lot.

Reference connection:

https://blog.csdn.net/ccllcaochong1/article/details/93367343

https://github.com/yuqi1129/calcite-test

https://github.com/quxiucheng/apache-calcite-tutorial/tree/a7d63273d0c7585fc65ad250c99a67a201bcb8b5

https://dafei1288.blog.csdn.net/article/details/102735371?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-1.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-1.control

Topics: Java Maven