Tutorial

This is a step-by-step tutorial that shows how to build and connect toCalcite. It uses a simple adapter that makes a directory of CSV filesappear to be a schema containing tables. Calcite does the rest, andprovides a full SQL interface.

Calcite-example-CSV is a fully functional adapter forCalcite that readstext files inCSV(comma-separated values) format. It is remarkable that a couple ofhundred lines of Java code are sufficient to provide full SQL querycapability.

CSV also serves as a template for building adapters to otherdata formats. Even though there are not many lines of code, it coversseveral important concepts:

  • user-defined schema using SchemaFactory and Schema interfaces;
  • declaring schemas in a model JSON file;
  • declaring views in a model JSON file;
  • user-defined table using the Table interface;
  • determining the record type of a table;
  • a simple implementation of Table, using the ScannableTable interface, thatenumerates all rows directly;
  • a more advanced implementation that implements FilterableTable, and canfilter out rows according to simple predicates;
  • advanced implementation of Table, using TranslatableTable, that translatesto relational operators using planner rules.

Download and build

You need Java (version 8, 9 or 10) and git.

  1. $ git clone https://github.com/apache/calcite.git
  2. $ cd calcite
  3. $ ./mvnw install -DskipTests -Dcheckstyle.skip=true
  4. $ cd example/csv

First queries

Now let’s connect to Calcite usingsqlline, a SQL shellthat is included in this project.

  1. $ ./sqlline
  2. sqlline> !connect jdbc:calcite:model=target/test-classes/model.json admin admin

(If you are running Windows, the command is sqlline.bat.)

Execute a metadata query:

  1. sqlline> !tables
  2. +------------+--------------+-------------+---------------+----------+------+
  3. | TABLE_CAT  | TABLE_SCHEM  | TABLE_NAME  |  TABLE_TYPE   | REMARKS  | TYPE |
  4. +------------+--------------+-------------+---------------+----------+------+
  5. | null       | SALES        | DEPTS       | TABLE         | null     | null |
  6. | null       | SALES        | EMPS        | TABLE         | null     | null |
  7. | null       | SALES        | HOBBIES     | TABLE         | null     | null |
  8. | null       | metadata     | COLUMNS     | SYSTEM_TABLE  | null     | null |
  9. | null       | metadata     | TABLES      | SYSTEM_TABLE  | null     | null |
  10. +------------+--------------+-------------+---------------+----------+------+

(JDBC experts, note: sqlline’s !tables command is just executingDatabaseMetaData.getTables())behind the scenes.It has other commands to query JDBC metadata, such as !columns and !describe.)

As you can see there are 5 tables in the system: tablesEMPS, DEPTS and HOBBIES in the currentSALES schema, and COLUMNS andTABLES in the system metadata schema. Thesystem tables are always present in Calcite, but the other tables areprovided by the specific implementation of the schema; in this case,the EMPS and DEPTS tables are based on theEMPS.csv and DEPTS.csv files in thetarget/test-classes directory.

Let’s execute some queries on those tables, to show that Calcite is providinga full implementation of SQL. First, a table scan:

  1. sqlline> SELECT * FROM emps;
  2. +--------+--------+---------+---------+----------------+--------+-------+---+
  3. | EMPNO  |  NAME  | DEPTNO  | GENDER  |      CITY      | EMPID  |  AGE  | S |
  4. +--------+--------+---------+---------+----------------+--------+-------+---+
  5. | 100    | Fred   | 10      |         |                | 30     | 25    | t |
  6. | 110    | Eric   | 20      | M       | San Francisco  | 3      | 80    | n |
  7. | 110    | John   | 40      | M       | Vancouver      | 2      | null  | f |
  8. | 120    | Wilma  | 20      | F       |                | 1      | 5     | n |
  9. | 130    | Alice  | 40      | F       | Vancouver      | 2      | null  | f |
  10. +--------+--------+---------+---------+----------------+--------+-------+---+

Now JOIN and GROUP BY:

  1. sqlline> SELECT d.name, COUNT(*)
  2. . . . .> FROM emps AS e JOIN depts AS d ON e.deptno = d.deptno
  3. . . . .> GROUP BY d.name;
  4. +------------+---------+
  5. |    NAME    | EXPR$1  |
  6. +------------+---------+
  7. | Sales      | 1       |
  8. | Marketing  | 2       |
  9. +------------+---------+

Last, the VALUES operator generates a single row, and is a convenientway to test expressions and SQL built-in functions:

  1. sqlline> VALUES CHAR_LENGTH('Hello, ' || 'world!');
  2. +---------+
  3. | EXPR$0  |
  4. +---------+
  5. | 13      |
  6. +---------+

Calcite has many other SQL features. We don’t have time to cover themhere. Write some more queries to experiment.

Schema discovery

Now, how did Calcite find these tables? Remember, core Calcite does notknow anything about CSV files. (As a “database without a storagelayer”, Calcite doesn’t know about any file formats.) Calcite knows aboutthose tables because we told it to run code in the calcite-example-csvproject.

There are a couple of steps in that chain. First, we define a schemabased on a schema factory class in a model file. Then the schemafactory creates a schema, and the schema creates several tables, eachof which knows how to get data by scanning a CSV file. Last, afterCalcite has parsed the query and planned it to use those tables, Calciteinvokes the tables to read the data as the query is beingexecuted. Now let’s look at those steps in more detail.

On the JDBC connect string we gave the path of a model in JSONformat. Here is the model:

  1. {
  2.   version: '1.0',
  3.   defaultSchema: 'SALES',
  4.   schemas: [
  5.     {
  6.       name: 'SALES',
  7.       type: 'custom',
  8.       factory: 'org.apache.calcite.adapter.csv.CsvSchemaFactory',
  9.       operand: {
  10.         directory: 'target/test-classes/sales'
  11.       }
  12.     }
  13.   ]
  14. }

The model defines a single schema called ‘SALES’. The schema ispowered by a plugin class,org.apache.calcite.adapter.csv.CsvSchemaFactory,which is part of thecalcite-example-csv project and implements the Calcite interfaceSchemaFactory.Its create method instantiates aschema, passing in the directory argument from the model file:

  1. public Schema create(SchemaPlus parentSchema, String name,
  2.     Map<String, Object> operand) {
  3.   String directory = (String) operand.get("directory");
  4.   String flavorName = (String) operand.get("flavor");
  5.   CsvTable.Flavor flavor;
  6.   if (flavorName == null) {
  7.     flavor = CsvTable.Flavor.SCANNABLE;
  8.   } else {
  9.     flavor = CsvTable.Flavor.valueOf(flavorName.toUpperCase());
  10.   }
  11.   return new CsvSchema(
  12.       new File(directory),
  13.       flavor);
  14. }

Driven by the model, the schema factory instantiates a single schemacalled ‘SALES’. The schema is an instance oforg.apache.calcite.adapter.csv.CsvSchemaand implements the Calcite interfaceSchema.

A schema’s job is to produce a list of tables. (It can also list sub-schemas andtable-functions, but these are advanced features and calcite-example-csv doesnot support them.) The tables implement Calcite’sTableinterface. CsvSchema produces tables that are instances ofCsvTableand its sub-classes.

Here is the relevant code from CsvSchema, overriding thegetTableMap()method in the AbstractSchema base class.

  1. protected Map<String, Table> getTableMap() {
  2.   // Look for files in the directory ending in ".csv", ".csv.gz", ".json",
  4.   // ".json.gz".
  6.   File[] files = directoryFile.listFiles(
  7.       new FilenameFilter() {
  8.         public boolean accept(File dir, String name) {
  9.           final String nameSansGz = trim(name, ".gz");
  10.           return nameSansGz.endsWith(".csv")
  11.               || nameSansGz.endsWith(".json");
  12.         }
  13.       });
  14.   if (files == null) {
  15.     System.out.println("directory " + directoryFile + " not found");
  16.     files = new File[0];
  17.   }
  18.   // Build a map from table name to table; each file becomes a table.
  20.   final ImmutableMap.Builder<String, Table> builder = ImmutableMap.builder();
  21.   for (File file : files) {
  22.     String tableName = trim(file.getName(), ".gz");
  23.     final String tableNameSansJson = trimOrNull(tableName, ".json");
  24.     if (tableNameSansJson != null) {
  25.       JsonTable table = new JsonTable(file);
  26.       builder.put(tableNameSansJson, table);
  27.       continue;
  28.     }
  29.     tableName = trim(tableName, ".csv");
  30.     final Table table = createTable(file);
  31.     builder.put(tableName, table);
  32.   }
  33.   return builder.build();
  34. }
  36. /** Creates different sub-type of table based on the "flavor" attribute. */
  37. private Table createTable(File file) {
  38.   switch (flavor) {
  39.   case TRANSLATABLE:
  40.     return new CsvTranslatableTable(file, null);
  41.   case SCANNABLE:
  42.     return new CsvScannableTable(file, null);
  43.   case FILTERABLE:
  44.     return new CsvFilterableTable(file, null);
  45.   default:
  46.     throw new AssertionError("Unknown flavor " + flavor);
  47.   }
  48. }

The schema scans the directory and finds all files whose name endswith “.csv” and creates tables for them. In this case, the directoryis target/test-classes/sales and contains filesEMPS.csv and DEPTS.csv, which these becomethe tables EMPS and DEPTS.

Tables and views in schemas

Note how we did not need to define any tables in the model; the schemagenerated the tables automatically.

You can define extra tables,beyond those that are created automatically,using the tables property of a schema.

Let’s see how to createan important and useful type of table, namely a view.

A view looks like a table when you are writing a query, but it doesn’t store data.It derives its result by executing a query.The view is expanded while the query is being planned, so the query plannercan often perform optimizations like removing expressions from the SELECTclause that are not used in the final result.

Here is a schema that defines a view:

  1. {
  2.   version: '1.0',
  3.   defaultSchema: 'SALES',
  4.   schemas: [
  5.     {
  6.       name: 'SALES',
  7.       type: 'custom',
  8.       factory: 'org.apache.calcite.adapter.csv.CsvSchemaFactory',
  9.       operand: {
  10.         directory: 'target/test-classes/sales'
  11.       },
  12.       tables: [
  13.         {
  14.           name: 'FEMALE_EMPS',
  15.           type: 'view',
  16.           sql: 'SELECT * FROM emps WHERE gender = \'F\''
  17.         }
  18.       ]
  19.     }
  20.   ]
  21. }

The line type: 'view' tags FEMALE_EMPS as a view,as opposed to a regular table or a custom table.Note that single-quotes within the view definition are escaped using aback-slash, in the normal way for JSON.

JSON doesn’t make it easy to author long strings, so Calcite supports analternative syntax. If your view has a long SQL statement, you can insteadsupply a list of lines rather than a single string:

  1. {
  2.   name: 'FEMALE_EMPS',
  3.   type: 'view',
  4.   sql: [
  5.     'SELECT * FROM emps',
  6.     'WHERE gender = \'F\''
  7.   ]
  8. }

Now we have defined a view, we can use it in queries just as if it were a table:

  1. sqlline> SELECT e.name, d.name FROM female_emps AS e JOIN depts AS d on e.deptno = d.deptno;
  2. +--------+------------+
  4. |  NAME  |    NAME    |
  5. +--------+------------+
  7. | Wilma  | Marketing  |
  8. +--------+------------+

Custom tables

Custom tables are tables whose implementation is driven by user-defined code.They don’t need to live in a custom schema.

There is an example in model-with-custom-table.json:

  1. {
  2.   version: '1.0',
  3.   defaultSchema: 'CUSTOM_TABLE',
  4.   schemas: [
  5.     {
  6.       name: 'CUSTOM_TABLE',
  7.       tables: [
  8.         {
  9.           name: 'EMPS',
  10.           type: 'custom',
  11.           factory: 'org.apache.calcite.adapter.csv.CsvTableFactory',
  12.           operand: {
  13.             file: 'target/test-classes/sales/EMPS.csv.gz',
  14.             flavor: "scannable"
  15.           }
  16.         }
  17.       ]
  18.     }
  19.   ]
  20. }

We can query the table in the usual way:

  1. sqlline> !connect jdbc:calcite:model=target/test-classes/model-with-custom-table.json admin admin
  2. sqlline> SELECT empno, name FROM custom_table.emps;
  3. +--------+--------+
  5. | EMPNO  |  NAME  |
  6. +--------+--------+
  8. | 100    | Fred   |
  9. | 110    | Eric   |
  10. | 110    | John   |
  11. | 120    | Wilma  |
  12. | 130    | Alice  |
  13. +--------+--------+

The schema is a regular one, and contains a custom table powered byorg.apache.calcite.adapter.csv.CsvTableFactory,which implements the Calcite interfaceTableFactory.Its create method instantiates a CsvScannableTable,passing in the file argument from the model file:

  1. public CsvTable create(SchemaPlus schema, String name,
  2.     Map<String, Object> map, RelDataType rowType) {
  3.   String fileName = (String) map.get("file");
  4.   final File file = new File(fileName);
  5.   final RelProtoDataType protoRowType =
  6.       rowType != null ? RelDataTypeImpl.proto(rowType) : null;
  7.   return new CsvScannableTable(file, protoRowType);
  8. }

Implementing a custom table is often a simpler alternative to implementinga custom schema. Both approaches might end up creating a similar implementationof the Table interface, but for the custom table you don’tneed to implement metadata discovery. (CsvTableFactorycreates a CsvScannableTable, just as CsvSchema does,but the table implementation does not scan the filesystem for .csv files.)

Custom tables require more work for the author of the model (the authorneeds to specify each table and its file explicitly) but also give the authormore control (say, providing different parameters for each table).

Comments in models

Models can include comments using / … / and // syntax:

  1. {
  2.   version: '1.0',
  3.   /* Multi-line
  4.      comment. */
  5.   defaultSchema: 'CUSTOM_TABLE',
  6.   // Single-line comment.
  7.   schemas: [
  8.     ..
  9.   ]
  10. }

(Comments are not standard JSON, but are a harmless extension.)

Optimizing queries using planner rules

The table implementations we have seen so far are fine as long as the tablesdon’t contain a great deal of data. But if your customer table has, say, ahundred columns and a million rows, you would rather that the system did notretrieve all of the data for every query. You would like Calcite to negotiatewith the adapter and find a more efficient way of accessing the data.

This negotiation is a simple form of query optimization. Calcite supports queryoptimization by adding planner rules. Planner rules operate bylooking for patterns in the query parse tree (for instance a project on topof a certain kind of table), and replacing the matched nodes in the tree bya new set of nodes which implement the optimization.

Planner rules are also extensible, like schemas and tables. So, if you have adata store that you want to access via SQL, you first define a custom table orschema, and then you define some rules to make the access efficient.

To see this in action, let’s use a planner rule to accessa subset of columns from a CSV file. Let’s run the same query against two verysimilar schemas:

  1. sqlline> !connect jdbc:calcite:model=target/test-classes/model.json admin admin
  2. sqlline> explain plan for select name from emps;
  3. +-----------------------------------------------------+
  5. | PLAN                                                |
  6. +-----------------------------------------------------+
  8. | EnumerableCalcRel(expr#0..9=[{inputs}], NAME=[$t1]) |
  9. |   EnumerableTableScan(table=[[SALES, EMPS]])        |
  10. +-----------------------------------------------------+
  12. sqlline> !connect jdbc:calcite:model=target/test-classes/smart.json admin admin
  13. sqlline> explain plan for select name from emps;
  14. +-----------------------------------------------------+
  16. | PLAN                                                |
  17. +-----------------------------------------------------+
  19. | EnumerableCalcRel(expr#0..9=[{inputs}], NAME=[$t1]) |
  20. |   CsvTableScan(table=[[SALES, EMPS]])               |
  21. +-----------------------------------------------------+

What causes the difference in plan? Let’s follow the trail of evidence. In thesmart.json model file, there is just one extra line:

  1. flavor: "translatable"

This causes a CsvSchema to be created withflavor = TRANSLATABLE,and its createTable method creates instances ofCsvTranslatableTablerather than a CsvScannableTable.

CsvTranslatableTable implements theTranslatableTable.toRel()method to createCsvTableScan.Table scans are the leaves of a query operator tree.The usual implementation isEnumerableTableScan,but we have created a distinctive sub-type that will cause rules to fire.

Here is the rule in its entirety:

  1. public class CsvProjectTableScanRule extends RelOptRule {
  2.   public static final CsvProjectTableScanRule INSTANCE =
  3.       new CsvProjectTableScanRule();
  5.   private CsvProjectTableScanRule() {
  6.     super(
  7.         operand(Project.class,
  8.             operand(CsvTableScan.class, none())),
  9.         "CsvProjectTableScanRule");
  10.   }
  12.   @Override
  13.   public void onMatch(RelOptRuleCall call) {
  14.     final Project project = call.rel(0);
  15.     final CsvTableScan scan = call.rel(1);
  16.     int[] fields = getProjectFields(project.getProjects());
  17.     if (fields == null) {
  18.       // Project contains expressions more complex than just field references.
  20.       return;
  21.     }
  22.     call.transformTo(
  23.         new CsvTableScan(
  24.             scan.getCluster(),
  25.             scan.getTable(),
  26.             scan.csvTable,
  27.             fields));
  28.   }
  30.   private int[] getProjectFields(List<RexNode> exps) {
  31.     final int[] fields = new int[exps.size()];
  32.     for (int i = 0; i < exps.size(); i++) {
  33.       final RexNode exp = exps.get(i);
  34.       if (exp instanceof RexInputRef) {
  35.         fields[i] = ((RexInputRef) exp).getIndex();
  36.       } else {
  37.         return null; // not a simple projection
  39.       }
  40.     }
  41.     return fields;
  42.   }
  43. }

The constructor declares the pattern of relational expressions that will causethe rule to fire.

The onMatch method generates a new relational expression and callsRelOptRuleCall.transformTo()to indicate that the rule has fired successfully.

The query optimization process

There’s a lot to say about how clever Calcite’s query planner is, but we won’tsay it here. The cleverness is designed to take the burden off you, the writerof planner rules.

First, Calcite doesn’t fire rules in a prescribed order. The query optimizationprocess follows many branches of a branching tree, just like a chess playingprogram examines many possible sequences of moves. If rules A and B both match agiven section of the query operator tree, then Calcite can fire both.

Second, Calcite uses cost in choosing between plans, but the cost model doesn’tprevent rules from firing which may seem to be more expensive in the short term.

Many optimizers have a linear optimization scheme. Faced with a choice betweenrule A and rule B, as above, such an optimizer needs to choose immediately. Itmight have a policy such as “apply rule A to the whole tree, then apply rule Bto the whole tree”, or apply a cost-based policy, applying the rule thatproduces the cheaper result.

Calcite doesn’t require such compromises.This makes it simple to combine various sets of rules.If, say you want to combine rules to recognize materialized views with rules toread from CSV and JDBC source systems, you just give Calcite the set of allrules and tell it to go at it.

Calcite does use a cost model. The cost model decides which plan to ultimatelyuse, and sometimes to prune the search tree to prevent the search space fromexploding, but it never forces you to choose between rule A and rule B. This isimportant, because it avoids falling into local minima in the search space thatare not actually optimal.

Also (you guessed it) the cost model is pluggable, as are the table and queryoperator statistics it is based upon. But that can be a subject for later.

JDBC adapter

The JDBC adapter maps a schema in a JDBC data source as a Calcite schema.

For example, this schema reads from a MySQL “foodmart” database:

  1. {
  2.   version: '1.0',
  3.   defaultSchema: 'FOODMART',
  4.   schemas: [
  5.     {
  6.       name: 'FOODMART',
  7.       type: 'custom',
  8.       factory: 'org.apache.calcite.adapter.jdbc.JdbcSchema$Factory',
  9.       operand: {
  10.         jdbcDriver: 'com.mysql.jdbc.Driver',
  11.         jdbcUrl: 'jdbc:mysql://localhost/foodmart',
  12.         jdbcUser: 'foodmart',
  13.         jdbcPassword: 'foodmart'
  14.       }
  15.     }
  16.   ]
  17. }

(The FoodMart database will be familiar to those of you who have usedthe Mondrian OLAP engine, because it is Mondrian’s main test dataset. To load the data set, follow Mondrian’sinstallation instructions.)

Current limitations: The JDBC adapter currently only pushesdown table scan operations; all other processing (filtering, joins,aggregations and so forth) occurs within Calcite. Our goal is to pushdown as much processing as possible to the source system, translatingsyntax, data types and built-in functions as we go. If a Calcite queryis based on tables from a single JDBC database, in principle the wholequery should go to that database. If tables are from multiple JDBCsources, or a mixture of JDBC and non-JDBC, Calcite will use the mostefficient distributed query approach that it can.

The cloning JDBC adapter

The cloning JDBC adapter creates a hybrid database. The data issourced from a JDBC database but is read into in-memory tables thefirst time each table is accessed. Calcite evaluates queries based onthose in-memory tables, effectively a cache of the database.

For example, the following model reads tables from a MySQL“foodmart” database:

  1. {
  2.   version: '1.0',
  3.   defaultSchema: 'FOODMART_CLONE',
  4.   schemas: [
  5.     {
  6.       name: 'FOODMART_CLONE',
  7.       type: 'custom',
  8.       factory: 'org.apache.calcite.adapter.clone.CloneSchema$Factory',
  9.       operand: {
  10.         jdbcDriver: 'com.mysql.jdbc.Driver',
  11.         jdbcUrl: 'jdbc:mysql://localhost/foodmart',
  12.         jdbcUser: 'foodmart',
  13.         jdbcPassword: 'foodmart'
  14.       }
  15.     }
  16.   ]
  17. }

Another technique is to build a clone schema on top of an existingschema. You use the source property to reference a schemadefined earlier in the model, like this:

  1. {
  2.   version: '1.0',
  3.   defaultSchema: 'FOODMART_CLONE',
  4.   schemas: [
  5.     {
  6.       name: 'FOODMART',
  7.       type: 'custom',
  8.       factory: 'org.apache.calcite.adapter.jdbc.JdbcSchema$Factory',
  9.       operand: {
  10.         jdbcDriver: 'com.mysql.jdbc.Driver',
  11.         jdbcUrl: 'jdbc:mysql://localhost/foodmart',
  12.         jdbcUser: 'foodmart',
  13.         jdbcPassword: 'foodmart'
  14.       }
  15.     },
  16.     {
  17.       name: 'FOODMART_CLONE',
  18.       type: 'custom',
  19.       factory: 'org.apache.calcite.adapter.clone.CloneSchema$Factory',
  20.       operand: {
  21.         source: 'FOODMART'
  22.       }
  23.     }
  24.   ]
  25. }

You can use this approach to create a clone schema on any type ofschema, not just JDBC.

The cloning adapter isn’t the be-all and end-all. We plan to developmore sophisticated caching strategies, and a more complete andefficient implementation of in-memory tables, but for now the cloningJDBC adapter shows what is possible and allows us to try out ourinitial implementations.

Further topics

There are many other ways to extend Calcite not yet described in this tutorial.The adapter specification describes the APIs involved.