Apache Drill之 SQLParser Apache Calcite

Apache Drill

为了理解Calcite，下面的PPT可以学习下

http://www.slideshare.net/julianhyde/apache-calcite-overview?related=2

http://www.slideshare.net/julianhyde/calcite-stratany2014

http://www.slideshare.net/julianhyde/drill-sql-optiq

https://datapsyche.wordpress.com/2014/08/06/optiq-query-push-down-concepts/

Drill的逻辑结构如下图所示。其中SQL Parser部分采用的是Apache Calcite。简单的来说这里在Drill里面Calcite实现的功能是提供了JDBC interface，接收用户的查询请求，然后将SQL Query语句转换成为SQL语法树，也就是逻辑计划。

比如查询语句：SELECT device, cust_id, order_id FROM clicks.json t, hive.orders o WHERE t.cust_id=o.cust_id

在SQL Parser之后Drill中会得到如下的逻辑计划

那么Apache Calcite究竟是何方神圣，其实就是之前的Optiq

Calcite

架构如下图所示。（Calcite最初被命名为Optiq，由Julian Hyde所编写，但如今已经成为Apache孵化器项目之一。

http://calcite.incubator.apache.org/

对Calcite进行一个简单的入门学习

http://www.tuicool.com/articles/u2qqIr  SQL over anything with an Optiq Adapter

this post shows how to create an Optiq 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?

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.

package io.thedal.optiq.javabean;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4