SQL Migration Tool

SQL Migration tool helps user to transform SQL Syntax to ANSI 2003 SQL syntax. Currently, only Hive and Impala SQL syntax are supported.

Usage of SQL Migration Tool

Download hetu-sql-migration-cli-{version number}-executable.jar, rename it to openlk-sql-migration-cli, make it executable with chmod +x, then run it.

Interactive mode

This tool can be run in interactive mode. The example is like:

./openlk-sql-migration-cli --type hive

lk:HIVE>
lk:HIVE> INSERT INTO TABLE table1 VALUES(10, "NAME");
==========converted result==========
INSERT INTO table1
 VALUES
  ROW (10, 'NAME')

=================Success=============

Parameter	Description
`--type` or `-t`	The type of input SQL statement, such as `hive`, `impala`. It's optional parameter and the default value is `hive`.

Here are some frequently used command:

Command	Description
`!chtype value;`	change the source sql type of current session
`exit` or `quit`	to exit the interactive mode
`history`	to get the previous input statements
`help`	displace the help information

Batch mode

This tool also can take parameters and running in batch mode. It has five parameters, "file", "sourceType", "execute", "output" and "config". The meaning of each parameters lists as below:

Parameter	Description
`--file` or `-f`	A file that contains SQL statements, separated by ";". All of the SQLs in the file can be converted in batch process.
`--type` or `-t`	The type of input SQL statement, such as `hive`, `impala`. It's optional parameter and the default value is `hive`.
`--output` or `-o`	the directory to save the converted SQL results. The result file’s naming convention will be the input file’s name + timestamp + .html suffix.
`--config` or `-c`	the config file of SQL Migration Tool.
`--debug` or `-d`	if set value to ’true’, then print the debug information in console.

Tip:

If user has large number of sql statements to convert, the suggested way is to consolidate all the statements into a single file, and use the batch mode.

Here is an example of batch mode usage:

    ./openlk-sql-migration-cli --file /home/Query01.sql --output ./
    May 26, 2020 5:27:10 PM io.airlift.log.Logger info
    INFO: Migration Completed.
    May 26, 2020 5:27:10 PM io.airlift.log.Logger info
    INFO: Result is saved to .//Query01_1590485230193.html

When file is specified, parameter output must be provided. The converted result will be a html file in output directory. You can open that html file via any web browser, and then review the conversion details.

Execute mode

It is possible to execute a query directly with the command and have the tool exit after transformation completion. Here is the example of using execute:

./openlk-sql-migration-cli --execute "INSERT INTO TABLE T1 VALUES(10, 'openLooKeng')" --type hive


==========converted result==========
INSERT INTO t1
 VALUES
  ROW (10, 'openLooKeng')

=================Success=============

If user specify the parameter execute only, the converted result will be printed onto the screen. Optionally, user can specify output parameter, the result will be saved into target file.

User can also provide config parameter to control the conversion behavior. Below is an example for config:

file name “config.properties” with content as below:

convertDecimalLiteralsAsDouble=true


./openlk-sql-migration-cli --execute "INSERT INTO TABLE T1 select 2.0 * 3" --config config.properties


==========converted result==========
INSERT INTO t1
SELECT (DECIMAL '2.0' * 3)

=================Success=============

Currently, the config file only supports one property convertDecimalLiteralsAsDouble. It means whether to convert decimal literals as double or not. The default value is false, which means converting decimal literals to type "decimal" .

Check list of Hive Statements:

Below Hive statements are fully supported:

SQL
USE DATABASE/SCHEMA
SHOW TABLES
DROP VIEW
DESCRIBE view_name
CREATE ROLE
GRANT ROLE
REVOKE ROLE
DROP ROLE
SHOW ROLES
SHOW CURRENT ROLES
SET ROLE
GRANT
REVOKE
DELETE
EXPLAIN ANALYZE
SHOW
SHOW FUCNTIONS

Below Hive statements are partially supported, which mean some keywords or attributes are not supported:

SQL	Description	openLooKeng Syntax Reference
CREATE DATABASE/SCHEMA	statement with “COMMENT”, “WITH DBPROPERTIES” is not supported	CREATE SCHEMA
DROP DATABASE/SCHEMA	statement with “CASCADE” is not supported	DROP SCHEMA
SHOW DATABASE/SCHEMA	statement with “like” is not supported	SHOW SCHEMA
CREATE TABLE	statement with “SKEWED BY”,“ROW FORMAT” is not supported	CREATE TABLE
DROP TABLE	statement with “PURGE” is not supported	DROP TABLE
ALTER TABLE	only “Rename table” and “Add a single column “are supported	ALTER TABLE
SHOW CREATE TABLE	To hive show table can works on both table and view. But in openLooKeng, this can only be applied to table.	SHOW CREATE TABLE
DESCRIBE	statement with column name is supported	DESCRIBE
CREATE VIEW	statement with “COMMENT”, “WITH DBPROPERTIES” is not supported	CREATE VIEW
SHOW COLUMNS	statement with “like” is not supported	SHOW COLUMNS
SHOW GRANT	Statement with Specified user or role is not supported	SHOW GRANT
INSERT	statement with “partition” is not supported	INSERT
SELECT	statement with “cluster by”, “offset” is not supported	SELECT

Below Hive statements are not supported, because of feature differences:

SQL
ALTER DATABASE/SCHEMA
DESCRIBE DATABASE/SCHEMA
SHOW TABLE EXTENDED
SHOW TBLPROPERTIES
TRUNCATE TABLE
MSCK REPAIR TABLE
ALTER PARTITION
ALTER COLUMN
ALTER VIEW
SHOW VIEWS
CREATE MATERIALIZED VIEW
DROP MATERIALIZED VIEW
ALTER MATERIALIZED VIEW
SHOW MATERIALIZED VIEWS
CREATE FUNCTION
DROP FUNCTION
RELOAD FUNCTION
CREATE INDEX
DROP INDEX
ALTER INDEX
SHOW INDEX(ES)
SHOW PARTITIONS
Describe partition
CREATE MACRO
DROP MACRO
SHOW ROLE GRANT
SHOW PRINCIPALS
SHOW LOCKS
SHOW CONF
SHOW TRANSACTIONS
SHOW COMPACTIONS
ABORT TRANSACTIONS
LOAD
UPDATE
MERGE
EXPORT
IMPORT
EXPLAIN
SET
RESET

Check list of Impala Statements:

Below Impala statements are fully supported:

SQL
CREATE SCHEMA
RENAME TABLE
DROP VIEW
CREATE ROLE
SHOW CREATE TABLE
SHOW CREATE VIEW
SHOW ROLES
EXPLAIN

Below Impala statements are partially supported, which mean some keywords or attributes are not supported:

SQL	Description	openLooKeng Syntax Reference
DROP SCHEMA	statement with “CASCADE” is not supported	DROP SCHEMA
CREATE TABLE	statement with “ROW FORMAT”, “WITH SERDEPROPERTIES”, “CACHED IN” is not supported	CREATE TABLE
CREATE TABLE LIKE	statement with “PARQUET” is not supported	CREATE TABLE
DROP TABLE	statement with “PURGE” is not supported	DROP TABLE
CREATE VIEW	statement with “IF NOT EXISTS”, “ALIAS” is not supported	CREATE VIEW
ALTER VIEW	Alias is not supported, and it will be converted to “CREATE OR REPLACE VIEW”	ALTER TABLE
DESCRIBE	Only table is supported to use describe	DESCRIBE
GRANT ROLE	Granting role to Group is not supported	GRANT ROLES
GRANT	Only “SELECT”,“INSERT” privileges are supported, and only ROLE can be granted to	GRANT
REVOKE ROLE	Revoking role from Group is not supported	REVOKE ROLES
REVOKE	Only “SELECT”,“INSERT” privileges are supported, and only ROLE can be revoked from	REVOKE
INSERT INTO	statement with “WITH”, “HINT”, “PARTITION” is not supported	INSERT INTO
DELETE	statement with “JOIN” is not supported	DELETE
SHOW SCHEMAS	statement with more than one wildcard is not supported	DELETE
SHOW TABLES	statement with more than one wildcard is not supported	DELETE
ADD COMMENTS	Adding comments to databases or columns is not supported	COMMENT
SET SESSION	Only support “SET” and “SET ALL”	SET SESSION
ADD COLUMNS	ADD multiple columns within single statement is not supported, kudu properties are not supported.	ALTER TABLE
SHOW FUNCTIONS	Only support show all functions or statement with “LIKE”.	SHOW FUNCTIONS

Below Impala statements are not supported, because of feature differences:

SQL
ALTER SCHEMA
CREATE KUDU TABLE
REPLACE COLUMNS
DROP SINGLE COLUMN
ALTER TABLE OWNER
ALTER KUDU TABLE
TRUNCATE TABLE
RENAME VIEW
ALTER VIEW OWNER
COMPUTE STATS
DROP STATS
CREATE FUNCTION
REFRESH FUNCTION
UPDATE TABLE
UPSERT
SHOW TABLE/COLUMN STATS
SHOW PARTITIONS
SHOW FILES
SHOW ROLE GRANT
DROP SINGLE COLUMN
SHUTDOWN
INVALIDATE META
LOAD DATA
REFRESH META
REFRESH AUTH

Limitations

Converting the UDFs and functions in SQL statements are not supported.

Version : 1.10.0

SQL Migration Tool

Usage of SQL Migration Tool

Check list of Hive Statements:

Check list of Impala Statements:

Limitations