InnoDB adapter
MySQL is the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation. InnoDB is a general-purpose storage engine that balances high reliability and high performance in MySQL, since 5.6 InnoDB has become the default MySQL storage engine.
Calcite’s InnoDB adapter allows you to query the data based on InnoDB
data files directly as illustrated below, data files are also known as
.ibd files. It leverages the
innodb-java-reader. This
adapter is different from JDBC adapter which maps a schema in a JDBC
data source and requires a MySQL server to serve response.
With .ibd files and the corresponding DDLs, the InnoDB adapter acts
as a simple “MySQL server”: it accepts SQL queries and attempts to
compile each query based on InnoDB file access APIs provided by
innodb-java-reader.
It projects, filters and sorts directly in the InnoDB data files where
possible.
SQL query
| |
/ \
--------- ---------
| |
v v
+-------------------------+ +------------------------+
| MySQL Server | | Calcite InnoDB Adapter |
| | +------------------------+
| +---------------------+ | +--------------------+
| |InnoDB Storage Engine| | | innodb-java-reader |
| +---------------------+ | +--------------------+
+-------------------------+
-------------------- File System --------------------
+------------+ +-----+
| .ibd files | ... | | InnoDB Data files
+------------+ +-----+What’s more, with DDL statements, the adapter is “index aware”. It leverages rules to choose the appropriate index to scan, for example, using primary key or secondary keys to look up data, then it tries to push down some conditions into storage engine. The adapter also supports hints, so that users can tell the optimizer to use a particular index.
A basic example of a model file is given below, this schema reads from a MySQL “scott” database:
{
"version": "1.0",
"defaultSchema": "scott",
"schemas": [
{
"name": "scott",
"type": "custom",
"factory": "org.apache.calcite.adapter.innodb.InnodbSchemaFactory",
"operand": {
"sqlFilePath": [ "/path/scott.sql" ],
"ibdDataFileBasePath": "/usr/local/mysql/data/scott"
}
}
]
}sqlFilePath is a list of DDL files, you can generate table
definitions by executing `mysqldump -d -u
CREATE TABLE `DEPT`(
`DEPTNO` TINYINT NOT NULL,
`DNAME` VARCHAR(50) NOT NULL,
`LOC` VARCHAR(20),
UNIQUE KEY `DEPT_PK` (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `EMP`(
`EMPNO` INT(11) NOT NULL,
`ENAME` VARCHAR(100) NOT NULL,
`JOB` VARCHAR(15) NOT NULL,
`AGE` SMALLINT,
`MGR` BIGINT,
`HIREDATE` DATE,
`SAL` DECIMAL(8,2) NOT NULL,
`COMM` DECIMAL(6,2),
`DEPTNO` TINYINT,
`EMAIL` VARCHAR(100) DEFAULT NULL,
`CREATE_DATETIME` DATETIME,
`CREATE_TIME` TIME,
`UPSERT_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`EMPNO`),
KEY `ENAME_KEY` (`ENAME`),
KEY `HIREDATE_KEY` (`HIREDATE`),
KEY `CREATE_DATETIME_JOB_KEY` (`CREATE_DATETIME`, `JOB`),
KEY `CREATE_TIME_KEY` (`CREATE_TIME`),
KEY `UPSERT_TIME_KEY` (`UPSERT_TIME`),
KEY `DEPTNO_JOB_KEY` (`DEPTNO`, `JOB`),
KEY `DEPTNO_SAL_COMM_KEY` (`DEPTNO`, `SAL`, `COMM`),
KEY `DEPTNO_MGR_KEY` (`DEPTNO`, `MGR`),
KEY `AGE_KEY` (`AGE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;sqlline> !connect jdbc:calcite:model=model.json admin adminsqlline> select empno, ename, job, age, mgr from "EMP";
+-------+--------+-----------+-----+------+
| EMPNO | ENAME | JOB | AGE | MGR |
+-------+--------+-----------+-----+------+
| 7369 | SMITH | CLERK | 30 | 7902 |
| 7499 | ALLEN | SALESMAN | 24 | 7698 |
| 7521 | WARD | SALESMAN | 41 | 7698 |
| 7566 | JONES | MANAGER | 28 | 7839 |
| 7654 | MARTIN | SALESMAN | 27 | 7698 |
| 7698 | BLAKE | MANAGER | 38 | 7839 |
| 7782 | CLARK | MANAGER | 32 | 7839 |
| 7788 | SCOTT | ANALYST | 45 | 7566 |
| 7839 | KING | PRESIDENT | 22 | null |
| 7844 | TURNER | SALESMAN | 54 | 7698 |
| 7876 | ADAMS | CLERK | 35 | 7788 |
| 7900 | JAMES | CLERK | 40 | 7698 |
| 7902 | FORD | ANALYST | 28 | 7566 |
| 7934 | MILLER | CLERK | 32 | 7782 |
+-------+--------+-----------+-----+------+sqlline> select empno, ename, job, age, mgr from "EMP" where empno = 7782;
+-------+-------+---------+-----+------+
| EMPNO | ENAME | JOB | AGE | MGR |
+-------+-------+---------+-----+------+
| 7782 | CLARK | MANAGER | 32 | 7839 |
+-------+-------+---------+-----+------+sqlline> select empno, ename, job, age, mgr from "EMP" where empno > 7782 and empno < 7900;sqlline> select empno, ename, job, age, mgr from "EMP" where ename = 'smith';
+-------+-------+-------+-----+------+
| EMPNO | ENAME | JOB | AGE | MGR |
+-------+-------+-------+-----+------+
| 7369 | SMITH | CLERK | 30 | 7902 |
+-------+-------+-------+-----+------+sqlline> select empno, ename, job, age, mgr from "EMP" where deptno = 20 and mgr = 7566;
+-------+-------+---------+-----+------+
| EMPNO | ENAME | JOB | AGE | MGR |
+-------+-------+---------+-----+------+
| 7788 | SCOTT | ANALYST | 45 | 7566 |
| 7902 | FORD | ANALYST | 28 | 7566 |
+-------+-------+---------+-----+------+select empno, ename, job, age, mgr from "EMP" where deptno = 20 and upsert_time > '2018-01-01 00:00:00';sqlline> select empno, deptno, sal from "EMP" where deptno = 20 and sal > 2000;
+-------+--------+---------+
| EMPNO | DEPTNO | SAL |
+-------+--------+---------+
| 7788 | 20 | 3000.00 |
| 7902 | 20 | 3000.00 |
| 7566 | 20 | 2975.00 |
+-------+--------+---------+sqlline> select empno, deptno, sal from "EMP"/*+ index(DEPTNO_SAL_COMM_KEY) */ where deptno = 20 and sal > 2000;sqlline> select empno,mgr from "EMP"/*+ index(DEPTNO_MGR_KEY) */ where mgr = 7839;sqlline> select deptno,ename,hiredate from "EMP" where hiredate < '2020-01-01' order by hiredate desc;
+--------+--------+------------+
| DEPTNO | ENAME | HIREDATE |
+--------+--------+------------+
| 20 | ADAMS | 1987-05-23 |
| 20 | SCOTT | 1987-04-19 |
| 10 | MILLER | 1982-01-23 |
| 20 | FORD | 1981-12-03 |
| 30 | JAMES | 1981-12-03 |
| 10 | KING | 1981-11-17 |
| 30 | MARTIN | 1981-09-28 |
| 30 | TURNER | 1981-09-08 |
| 10 | CLARK | 1981-06-09 |
| 30 | WARD | 1981-02-22 |
| 30 | ALLEN | 1981-02-20 |
| 20 | JONES | 1981-02-04 |
| 30 | BLAKE | 1981-01-05 |
| 20 | SMITH | 1980-12-17 |
+--------+--------+------------+{
"version": "1.0",
"defaultSchema": "test",
"schemas": [
{
"name": "test",
"type": "custom",
"factory": "org.apache.calcite.adapter.innodb.InnodbSchemaFactory",
"operand": {
"sqlFilePath": ["src/test/resources/data_types.sql"],
"ibdDataFileBasePath": "src/test/resources/data",
"timeZone": "America/Los_Angeles"
}
}
]
}