The technologies for SQL migration
The function syntaxes of various types of databases are more or less different from each other. In order to make the SQL query statements of the same functionality be executed successfully in different types of databases, we need to translate these differentiated function syntaxes that are available in each database, and that is SQL migration. This article will explore several technologies for SQL migration and compare them in detail.
ORM technology
The queries written by programmers can be converted to SQLs of different databases with ORM, which equals certain migratability. But ORM is only suitable for simple SQL in OLTP scenario. As for OLAP scenario, it can hardly migrates the complex SQL.
Hibernate and Mybatis are two of the most common ORM technology. Mybatis is also known as semi-automatic ORM, which is mainly because the programmers need to write native SQL by themselves. That makes Mybatis hardly for migration, even with third-party expansion packages like Mybatis-plus and Mybatis-ext, Mybatis’ input method can be improved but its migratability is still far less than Hibernate. While Hibernate can use HQL to automatically generate corresponding SQL based on the database dialect in the configuration file, so it can perform migration for some simple situations (which are directly supported in Hibernate), for example:
HQL:
SELECT
client,
YEAR(orderDate),
sum(amount),
count(orderId)
FROM
OrdersEntity
GROUP BY
client,
YEAR(orderDate)
HAVING
sum(amount)>2000
MySQL:
SELECT
ordersenti0_.Client AS col_0_0_,
YEAR(ordersenti0_.OrderDate) AS col_1_0_,
sum(ordersenti0_.Amount) AS col_2_0_,
count(ordersenti0_.OrderID) AS col_3_0_
FROM
orders ordersenti0_
GROUP BY
ordersenti0_.Client,
YEAR(ordersenti0_.OrderDate)
HAVING
sum(ordersenti0_.Amount)>2000
Oracle:
SELECT
ordersenti0_.Client AS col_0_0_,
EXTRACT(YEAR FROM ordersenti0_.OrderDate) AS col_1_0_,
sum(ordersenti0_.Amount) AS col_2_0_,
count(ordersenti0_.OrderID) AS col_3_0_
FROM
system.orders ordersenti0_
GROUP BY
ordersenti0_.Client,
EXTRACT(YEAR FROM ordersenti0_.OrderDate)
HAVING
sum(ordersenti0_.Amount)>2000
In the SQL generated in HQL, the YEAR(d) function is used to correspond to MySQL and the EXTRACT(YEAR FROM d) function is used to Oracle, which can achieve SQL migration.
However, Hibernate may not perform migration successfully in some more complicated circumstances.
For example, we need to register custom functions according to the current database before generating SQL from HQL when using functions not directly supported in HQL itself, using MySQL as an example:
The key code to register custom functions:
registerFunction("udf_dateadd", new SQLFunctionTemplate(DateType.INSTANCE,"date_add(?1,INTERVAL ?2 DAY)"));
HQL:
SELECT
udf_dateadd (orderDate,3)
FROM
OrdersEntity
MySQL:
SELECT
date_add(ordersenti0_.OrderDate,INTERVAL 3 DAY) AS col_0_0_
FROM
orders ordersenti0_
But there is no date_add function in Oracle. If the database turns to Oracle, then we need to register other new custom functions based on the function syntax of Oracle, which can not be migrated automatically.
There are also some operations that cannot be described in HQL, such as the subquery in FROM:
SELECT
orderId,
m
FROM
(
SELECT
orderId,
MONTH(orderDate) m
FROM
OrdersEntity) t1
In order to solve such a problem, we usually execute native SQL to use SQLQuery interfaces, which will also lose the ability of migration.
SQL conversion tools
With the help of some tools to migrate SQL, we can directly translate the current original SQL (like Oracle SQL) to target SQL (like MySQL SQL). Even in some complex situations like nested subqueries, the conversion can still be achieved.
For example, in https://www.sqlines.com/online:
Select the database type of the original SQL as MySQL, and the SQL to be translated is:
SELECT
O_YEAR ,
SUM(CASE WHEN NATION = 'CHINA' THEN VOLUME ELSE 0 END) / SUM(VOLUME) ASMKT_SHARE
FROM
(
SELECT
YEAR (O_ORDERDATE) AS O_YEAR,
L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS VOLUME,
N2.N_NAME AS NATION
FROM
PART,
SUPPLIER,
…
Select the database type of the target SQL as Oracle, and click the “convert” button, then the original SQL is converted to SQL that can be executed by the target database:
SELECT
O_YEAR ,
SUM(CASE WHEN NATION = 'CHINA' THEN VOLUME ELSE 0 END) / SUM(VOLUME) ASMKT_SHARE
FROM
(
SELECT
EXTRACT(YEAR FROM O_ORDERDATE) AS O_YEAR,
L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS VOLUME,
N2.N_NAME AS NATION
FROM
PART,
SUPPLIER,
…
Still, the method has its defects. Such tools are mostly independent applets which only support output of the target SQL on the command line or in the result file, and lack a programmatic interface for integration in various development tools.
esProc SPL
SPL has designed a set of standard SQL query syntax which has many built-in functions (and more are being added) to describe more common operations. And there is a sqltranslate function in SPL, which can translate the standard SQL to SQLs for different databases for the purpose of database migration.
Take this standard SQL for example:
SELECT CLIENT, YEAR(ORDERDATE), SUM(AMOUNT)
, COUNT(ORDERID)
FROM ORDERS
GROUP BY CLIENT, YEAR(ORDERDATE)
HAVING SUM(AMOUNT) > 2000
Translate it with .sqltranlate("MYSQL") and the result will be:
SELECT CLIENT, YEAR(ORDERDATE), SUM(AMOUNT)
, COUNT(ORDERID)
FROM ORDERS
GROUP BY CLIENT, YEAR(ORDERDATE)
HAVING SUM(AMOUNT) > 2000
Whereas the returned result of using .sqltranslate("ORACLE") will be:
SELECT CLIENT, EXTRACT(YEAR FROM ORDERDATE), SUM(AMOUNT)
, COUNT(ORDERID)
FROM ORDERS
GROUP BY CLIENT, EXTRACT(YEAR FROM ORDERDATE)
HAVING SUM(AMOUNT) > 2000
As we can see, the standard function is able to correctly select the appropriate functions according to the databases.
In addition, SPL can also handle with those operations not supported in HQL itself like INTERVAL n DAY:
The standard SQL is:
SELECT
ADDDAYS(ORDERDATE,3)
FROM
ORDERS
Translate it to MySQL SQL as:
SELECT
ORDERDATE + INTERVAL 3 DAY
FROM
ORDERS
Translate it to Oracle SQL as:
SELECT
ORDERDATE + NUMTODSINTERVAL(3,'DAY')
FROM
ORDERS
In order to achieve SQL migration, SPL just translates the functions rather than the statements (copied as they are) in standard SQL so that the standard SQL is able to describe more operations. For example, the following subquery will not change and can be executed normally no matter which database SQL it is translated to.
SELECT
ORDERID,
M
FROM
(
SELECT
ORDERID,
MONTH(ORDERDATE) M
FROM
ORDERS) T1
SPL can be easily integrated with JAVA to enable migration in applications. For further information, please refer to How to Call an SPL Script in Java.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
Chinese version