Calcite developers,
I am a university researcher trying to use Calcite to develop a distributed
query capability. I have read the Calcite tutorial and some example code,
but am still unclear on how to create a calcite SQL parser, parse a simple
SQL statement and examine its associated relational expression (tree).
I start by building a JSON object that represents operands consumed by a
JdbcSchema.Factory(), which builds a JdbcSchema supplied by a SQLite
database. Once I have the schema, I assign it to the Calcite framework
using the FrameworkConfig builder, then configure the SQL parser to ignore
case in a query (case insensitive). I verify the schema has been build and
print out its tables successfully, e.g. "Students", "Grades". I then obtain
the PLANNER and parser the following statement:
"SELECT * FROM Students WHERE age > 15.0"
Next, when I attempt to VALIDATE the syntax correctness of the statement,
an error reports:
org.apache.calcite.sql.validate.SqlValidatorException: Table 'STUDENTS' not
found
The framework appears to convert schema table names to UPPERCASE, even
though I try to configure the SQL parser to be case insensitive. It
therefore fails to find the "Students" table imported from the SQLite
database and reports the error.
Finally, If and when I get the SQL parser working, I want to examine the
relational tree produced by the Calcite planner, and perhaps display it
graphically? Later I intend to add rules to the planner that support
distributing a query across multiple nodes.
I see value in the Calcite project and would like to learn more to
contribute. My simple Eclipse program ParseSql.java is attached along with
the trivial SQLite database used to experiment with the parser. Your
suggestion and guidance is very much appreciated.
Is there a developer chat room?
Regards
David Holland
--
David Holland
512-300-8396
package parser;
import java.util.HashMap;
import java.util.Map;
import javax.json.Json;
import javax.json.JsonObjectBuilder;
import javax.json.JsonString;
import javax.json.JsonValue;
import org.apache.calcite.adapter.jdbc.JdbcSchema;
import org.apache.calcite.rel.RelRoot;
import org.apache.calcite.schema.Schema;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.schema.Table;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.parser.SqlParseException;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.parser.SqlParser.ConfigBuilder;
import org.apache.calcite.tools.FrameworkConfig;
import org.apache.calcite.tools.Frameworks;
import org.apache.calcite.tools.Planner;
import org.apache.calcite.tools.RelConversionException;
import org.apache.calcite.tools.ValidationException;
public class ParseSQL {
public static void main(String[] args) {
try {
// register the JDBC driver
String sDriverName = "org.sqlite.JDBC";
Class.forName(sDriverName);
JsonObjectBuilder builder = Json.createObjectBuilder();
builder.add("jdbcDriver", "org.sqlite.JDBC")
.add("jdbcUrl", "jdbc:sqlite://home/david/Dropbox/DBaaS_Docker_Notes/calcite/students.db")
.add("jdbcUser", "root")
.add("jdbcPassword", "root");
Map<String, JsonValue> JsonObject = builder.build();
//operand: map argument for JdbcSchema.Factory().create(rootSchema, "students", operand)
Map<String, Object> operand = new HashMap<String, Object>();
//have to explicitly extract JsonString(s) from builder object and load into operand map
for(String key : JsonObject.keySet()) {
JsonString value = (JsonString) JsonObject.get(key);
operand.put(key, value.getString());
}
final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
Schema schema = new JdbcSchema.Factory().create(rootSchema, "students", operand);
//output tables to validate database schema has been read in correctly
for(String tableName : schema.getTableNames()){
System.out.println(tableName);
Table table = schema.getTable(tableName);
System.out.println(table.toString());
}
//build a FrameworkConfig using defaults where values aren't required
Frameworks.ConfigBuilder configBuilder = Frameworks.newConfigBuilder();
//set defaultSchema
configBuilder.defaultSchema(rootSchema);
//build configuration
FrameworkConfig frameworkdConfig = configBuilder.build();
//get parser config builder and modify set SQL case sensitive to false
ConfigBuilder parserConfig = SqlParser.configBuilder(frameworkdConfig.getParserConfig());
parserConfig.setCaseSensitive(false);
parserConfig.setConfig(parserConfig.build());
//get planner
Planner planner = Frameworks.getPlanner(frameworkdConfig);
//parse SQL statement
SqlNode sql_node = planner.parse("SELECT * FROM Students WHERE age > 15.0");
System.out.println("\n" + sql_node.toString());
//validate SQL
SqlNode sql_validated = planner.validate(sql_node);
//get associated relational expression
RelRoot relationalExpression = planner.rel(sql_validated);
relationalExpression.toString();
} catch (SqlParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (RelConversionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ValidationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} // end main
} // end class
