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

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 FUCNTIONS statement with “like” is not supported SHOW FUCNTIONS
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. 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.