This is a library for SQL optimizing/rewriting. Current version (0.4.0) we have already supports:
- Materialized View rewrite.
- Data Lineage analysis
This project is under active development and *** NOT READY FOR PRODUCTION ***.
We have provided free API http://sql-booster.mlsql.tech. You can visit http://sql-booster.mlsql.tech/api to
get all available APIs. We recommend you using PostMan to play with these APIs. Notice that most of the APIs only
support POST method.
please check: HTTP API Tutorial
You can link against this library in your program at the following coordinates:
groupId: tech.mlsql
artifactId: sql-booster_2.11
version: 0.4.0
We recommend people wrap sql-booster with springboot(or other web framework) as http service.
In order to do view-based query rewriting, you should register schema of your concrete tables and views manually. Notice that we only need following information to make sql-booster work:
- table create statement
- view create statement
- view schema (infer from view create statement automatically)
sql-booster supports three kinds of create statement:
- MySQL/Oracle
- Hive
- SimpleSchema
- Spark StructType json
Steps:
- Initial sql-booster, do only one time.
ViewCatalyst.createViewCatalyst()
val schemaReg = new SchemaRegistry(spark)- register tables:
schemaReg.createTableFromDBSQL(
"""
|CREATE TABLE depts(
| deptno INT NOT NULL,
| deptname VARCHAR(20),
| PRIMARY KEY (deptno)
|);
""".stripMargin)
schemaReg.createTableFromDBSQL(
"""
|CREATE TABLE locations(
| locationid INT NOT NULL,
| state CHAR(2),
| PRIMARY KEY (locationid)
|);
""".stripMargin)
schemaReg.createTableFromDBSQL(
"""
|CREATE TABLE emps(
| empid INT NOT NULL,
| deptno INT NOT NULL,
| locationid INT NOT NULL,
| empname VARCHAR(20) NOT NULL,
| salary DECIMAL (18, 2),
| PRIMARY KEY (empid),
| FOREIGN KEY (deptno) REFERENCES depts(deptno),
| FOREIGN KEY (locationid) REFERENCES locations(locationid)
|);
""".stripMargin)
schemaReg.createTableFromHiveSQL("src",
"""
|CREATE TABLE IF NOT EXISTS src (key INT, value STRING) USING hive
""".stripMargin)
schemaReg.createTableFromSimpleSchema("table1","""st(field(a,string),field(b,string))""")
schemaReg.createTableFromJson("table2",
"""
|{"type":"struct","fields":[{"name":"a","type":"string","nullable":true,"metadata":{}},{"name":"b","type":"string","nullable":true,"metadata":{}}]}
""".stripMargin) - register MV:
schemaReg.createMV("emps_mv",
"""
|SELECT empid
|FROM emps
|JOIN depts ON depts.deptno = emps.deptno
""".stripMargin)
- Using MaterializedViewOptimizeRewrite to execute rewrite:
val rewrite3 = MaterializedViewOptimizeRewrite.execute(schemaReg.toLogicalPlan(
"""
|select * from (SELECT e.empid
|FROM emps e
|JOIN depts d
|ON e.deptno = d.deptno
|where e.empid=1) as a where a.empid=2
""".stripMargin))- Generate rewrite SQL
assert(schemaReg.genSQL(rewrite3)
== "SELECT a.`empid` FROM (SELECT `empid` FROM emps_mv WHERE `empid` = CAST(1 AS BIGINT)) a WHERE a.`empid` = CAST(2 AS BIGINT)")Given a SQL, sql-booster can help you analysis:
- tables and their corresponding columns which this sql dependents includes the columns used in where,select,join condition.
- every output column of this sql is composed by which columns in the original tables
NOTICE: sql-booster needs you to register table schema firstly like described in View-based query rewriting usage.
Here is the example code:
val result = DataLineageExtractor.execute(schemaReg.toLogicalPlan(
"""
|select * from (SELECT e.empid
|FROM emps e
|JOIN depts d
|ON e.deptno = d.deptno
|where e.empid=1) as a where a.empid=2
""".stripMargin))
println(JSONTool.pretty(result))then output is like this:
{
"outputMapToSourceTable":[{
"name":"empid",
"sources":[{
"tableName":"emps",
"columns":["empid"],
"locates":[["PROJECT","FILTER"]]
},{
"tableName":"depts",
"columns":[],
"locates":[]
}]
}],
"dependences":[{
"tableName":"emps",
"columns":["empid","deptno"],
"locates":[["PROJECT","FILTER"],["JOIN"]]
},{
"tableName":"depts",
"columns":["deptno"],
"locates":[["JOIN"]]
}]
}this means the new table only have one column named empid, and it depends empid in table emps. the new table depends emps and depts, and empid,deptno are required.
Also, sql-booster tell you the column appears in which part of the sql. There are 4 parts:
FILTER
GROUP_BY
JOIN
PROJECT
As to the example, depts.depno exists in Join, and emps.empid exists in PROJECT and FILTER(where condition).
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent