Background

Apache Calcite is a dynamic data management framework.

It contains many of the pieces that comprise a typical databasemanagement system, but omits some key functions: storage of data,algorithms to process data, and a repository for storing metadata.

Calcite intentionally stays out of the business of storing andprocessing data. As we shall see, this makes it an excellent choicefor mediating between applications and one or more data storagelocations and data processing engines. It is also a perfect foundationfor building a database: just add data.

To illustrate, let’s create an empty instance of Calcite and thenpoint it at some data.

  1. public static class HrSchema {
  2.   public final Employee[] emps = 0;
  3.   public final Department[] depts = 0;
  4. }
  5. Class.forName("org.apache.calcite.jdbc.Driver");
  6. Properties info = new Properties();
  7. info.setProperty("lex", "JAVA");
  8. Connection connection =
  9.     DriverManager.getConnection("jdbc:calcite:", info);
  10. CalciteConnection calciteConnection =
  11.     connection.unwrap(CalciteConnection.class);
  12. SchemaPlus rootSchema = calciteConnection.getRootSchema();
  13. Schema schema = new ReflectiveSchema(new HrSchema());
  14. rootSchema.add("hr", schema);
  15. Statement statement = calciteConnection.createStatement();
  16. ResultSet resultSet = statement.executeQuery(
  17.     "select d.deptno, min(e.empid)\n"
  18.     + "from hr.emps as e\n"
  19.     + "join hr.depts as d\n"
  20.     + "  on e.deptno = d.deptno\n"
  21.     + "group by d.deptno\n"
  22.     + "having count(*) > 1");
  23. print(resultSet);
  24. resultSet.close();
  25. statement.close();
  26. connection.close();

Where is the database? There is no database. The connection iscompletely empty until new ReflectiveSchema registers a Javaobject as a schema and its collection fields emps and depts astables.

Calcite does not want to own data; it does not even have a favorite dataformat. This example used in-memory data sets, and processed themusing operators such as groupBy and join from the linq4jlibrary. But Calcite can also process data in other data formats, suchas JDBC. In the first example, replace

  1. Schema schema = new ReflectiveSchema(new HrSchema());

with

  1. Class.forName("com.mysql.jdbc.Driver");
  2. BasicDataSource dataSource = new BasicDataSource();
  3. dataSource.setUrl("jdbc:mysql://localhost");
  4. dataSource.setUsername("username");
  5. dataSource.setPassword("password");
  6. Schema schema = JdbcSchema.create(rootSchema, "hr", dataSource,
  7.     null, "name");

and Calcite will execute the same query in JDBC. To the application,the data and API are the same, but behind the scenes theimplementation is very different. Calcite uses optimizer rules to pushthe JOIN and GROUP BY operations to the source database.

In-memory and JDBC are just two familiar examples. Calcite can handleany data source and data format. To add a data source, you need towrite an adapter that tells Calcite what collections in the datasource it should consider “tables”.

For more advanced integration, you can write optimizerrules. Optimizer rules allow Calcite to access data of a new format,allow you to register new operators (such as a better join algorithm),and allow Calcite to optimize how queries are translated tooperators. Calcite will combine your rules and operators with built-inrules and operators, apply cost-based optimization, and generate anefficient plan.

Writing an adapter

The subproject under example/csv provides a CSV adapter, which isfully functional for use in applications but is also simple enough toserve as a good template if you are writing your own adapter.

See the tutorial for information on usingthe CSV adapter and writing other adapters.

See the HOWTO for more information aboutusing other adapters, and about using Calcite in general.

Status

The following features are complete.

  • Query parser, validator and optimizer
  • Support for reading models in JSON format
  • Many standard functions and aggregate functions
  • JDBC queries against Linq4j and JDBC back-ends
  • Linq4j front-end
  • SQL features: SELECT, FROM (including JOIN syntax), WHERE, GROUP BY(including GROUPING SETS), aggregate functions (includingCOUNT(DISTINCT …) and FILTER), HAVING, ORDER BY (including NULLSFIRST/LAST), set operations (UNION, INTERSECT, MINUS), sub-queries(including correlated sub-queries), windowed aggregates, LIMIT(syntax as Postgres);more details in the SQL reference
  • Local and remote JDBC drivers; see Avatica
  • Several adapters