最近项目组要求写个SQL脚本相关的解析工具能自定义校验规则,开始一直研究基于C#的Irony框架学习;由于本人基础薄弱,网上这个框架的资源不是很多。最终放弃改使用Apache Calcite框架。借用了网上的一些资源和博客,终于把自定义规则的demo弄好了。现把学习过程和其中出现的问题整理如下,希望能帮到也想学习Calcite新伙伴。
-
1>下载IDE并配置maven环境
我的demo基于eclipse开发的,其它IDE环境可自行钻研学习
1.1、maven下载
http://maven.apache.org/download.cgi
1.2、解压下载的压缩包并配置maven中的settings.xml
部分配置说明D:\JAVA\MavenJar
设置本地仓库地址和阿里云仓库地址
1.3、eclipse中配置maven环境
Window–》Preferences–》Maven–》User Settings
-
2>下载calcite源码包
下载地址:https://gitee.com/Bomy/calcite
2.1:将code\src\main\codegen下所有文件复制到自己的代码路径下
-
3>打开eclipse创建Maven项目
3.1、项目创建
3.2、复制calcite源码中code\src\main\codegend 中的所有代码到main文件夹下,复制完成之后刷新下项目
3.3、构建maven工程,这里注意,需要将编译插件配置好,主要包括freemarker和javacc,下面是我的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.xml中如果execution报错请按照下面方法处理
Window–》Preferences–》Maven–》Errors/Warnings
3.4、创建sql解析类SqlWuPeng ,包路径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、修改config.fmpp文件
找到 package: “org.apache.calcite.sql.parser.impl” 修改下面的class为:WPSqlParserImpl
3.6、修改Parser.jj文件
3.6.1-> 添加解析类引用
3.6.2> 添加解析逻辑
/**
*自定义解析解析逻辑
**/
SqlNode SqlJacky() :
{
SqlNode stringNode;
}
{
<hello> <calcite>
stringNode = StringLiteral()
{
return new SqlJacky(getPos(), token.image);
}
}
3.6.3> 找到SqlNode SqlStmt() 添加自定义解析逻辑
3.6.4>设置关键字
3.6.5>编译maven项目
编译后项目目录
3.6.6>创建测试类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();
}
}
}
引用自定义解析类报错问题处理
右键点击工程 – Properties – Java Build Path --Source – Link Source然后从target下找到javacc目录添加即可
3.6.7>执行测试类WupengParser
鸣谢:这个demo主要参考了 余启大神 的代码,受益匪浅。
参考连接:
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