I need some help on parsing Bigquery string using calcite and I Am new to Apache calcite, so please bear with me for any dumb things.
My goal is to parse the Bigquery SQL string (that is either Bigquery expression or Bigquery SQL). If something wrong with syntax, it should through error. But the following expression is valid and string I am getting error.
So far, I did following:
I have added following dependency in my pom after I create maven project:
<dependency>
<groupId>org.apache.calcite</groupId>
<artifactId>calcite-core</artifactId>
<version>1.32.0</version>
</dependency>
Here is my code:
public static void main(String[] args){
String expr = "ROUND(((DATE_DIFF(cust360_customer_demographics.acct_eff_dt,todays_dt,DAY))/30),1)";
String sql = "select avg(a) as \"avg_a\",b from csv.test group by b";
System.out.println(validateExprCalcite(expr));
//System.out.println(validateQueryCalcite(sql));
}
/* public static String validateQueryCalcite(String sql) {
try {
SqlParser expParser = getCalciteConfig(sql);
SqlNode sqlNode = expParser.parseQuery();
log.info("Validating Query - {}. After Query {}",sql,sqlNode.toSqlString(BigQuerySqlDialect.DEFAULT));
} catch (Exception e) {
return e.getCause().getMessage();
}
return "Query Successfully validated.";
}
*/
public static String validateExprCalcite(String sql) {
try {
SqlParser expParser = getCalciteConfig(sql);
SqlNode sqlNode = expParser.parseExpression();
log.info("Validating Query - {}. After Query {}",sql,sqlNode.toSqlString(BigQuerySqlDialect.DEFAULT));
} catch (Exception e) {
return e.getCause().getMessage();
}
return "Query Successfully validated.";
}
public static SqlParser getCalciteConfig(String sql){
SqlParser.Config config = SqlParser.Config.DEFAULT
.withLex(Lex.BIG_QUERY)
.withConformance(SqlConformanceEnum.BIG_QUERY)
.withParserFactory(SqlParserImpl.FACTORY)
.withQuoting(Quoting.DOUBLE_QUOTE)
.withUnquotedCasing(Casing.TO_UPPER)
.withLex(Lex.BIG_QUERY);
return SqlParser.create(sql, config);
}
I Am an getting error Incorrect syntax near the keyword 'DAY' at line 1, column 71. though this is valid Big Query expression.
I have also check with few expressions like this and its failing with same expression though they are valid BQ expressions.
Am I missing something? What am I doing wrong here?
Any help would greatly appreciate. Thank you!
