SQL over anything with an Optiq / Calcite Adapter

This post shows how to create an Optiq (now renamed to Calcite) Adapter. There are examples out there with optiq-csv and couple of other projects but I found them a little hard to comprehend. Moreover, query push down feature isn’t well documented and I would do a followup post on that soon.

What is Optiq / Calcite?
Optiq (https://github.com/julianhyde/optiq) is a query planning engine that can help you execute and plan SQL over your data sources. For example, there is a project optiq-web using which you can point to wiki pages with tables and query those html tables through SQL. Similar projects are there to run SQL on CSV, JSON files, MongoDB etc. Lets say you built a custom data store and you want to provide SQL access to it, then Optiq is a good choice. You just need to write an Optiq adapter to your data source.

A Sample Use Case
Anyways, the intention of this post is a step by step guide to write a custom adapter. Let’s see how I wrote an adapter to run SQL on top of JavaBean objects. Lets say you have a List of Users (JavaBean Objects) and would like to run queries like: “select max(age) from users” etc. Optiq has an inbuilt ReflectiveSchema that can be used here, but lets do our own implementation to see how it’s done, also ReflectiveSchema does not have query push down which I am planning to add.

Creating the Adapter
Tutorial source code link: https://github.com/cyberabis/optiq-javabean

Step 1: Create a Schema Class
The Schema class is the equivalent of a Database and can contain multiple tables.
Extend AbstractSchema and override getTableMap method. This method should return table names and Tables. How to create table class is next.
https://github.com/cyberabis/optiq-javabean/blob/master/src/main/java/io/thedal/optiq/javabean/JavaBeanSchema.java

Step 2: Create Table Class
Create table class extending AbstractQueryableTable and implementing TranslatableTable.
The table has 2 important methods:
getRowType – This method should return the table row headers and their types in two arrays like [Name, Age, Country] and [String, Integer, String].
asQueryable – This method should return an Enumerator. The Enumerator will have methods to iterate the actual rows in the table. See below on how to create an Enumerator.
https://github.com/cyberabis/optiq-javabean/blob/master/src/main/java/io/thedal/optiq/javabean/JavaBeanTable.java

Step 3: Create Enumerator Class
The Enumerator needs to have an Iterator for the rows in a table. The rows in a Table are modelled as an Object[]. So we have to convert our custom storage component into an Iterator. In my Case my storage component is a JavaBean List – I converted this into an Iterator in my Enumerator constructor. The implemented methods are self descriptive.
https://github.com/cyberabis/optiq-javabean/blob/master/src/main/java/io/thedal/optiq/javabean/JavaBeanEnumerator.java

Step 4: A Query Executor Class
The Query Executor can take a Schema object and prepare a connection and statement for executing SQL. This execute method accepts a SQL string and returns a java ResultSet object. Note the query execution code in Optiq github home page is outdated, you can see the correct way in my example.
https://github.com/cyberabis/optiq-javabean/blob/master/src/main/java/io/thedal/optiq/javabean/JdbcQueryExecutpr.java

Putting it all together:
Checkout the Test Program I have created!
https://github.com/cyberabis/optiq-javabean/blob/master/src/test/java/io/thedal/optiq/javabean/SampleTestProgram.java

The next difficult step is pushing down queries to the storage by creating rules and matching SQL expressions. I hope to write a post on that soon.

Happy querying!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s