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=============
ParameterDescription
--type or -tThe 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:

CommandDescription
!chtype value;change the source sql type of current session
exit or quitto exit the interactive mode
historyto get the previous input statements
helpdisplace 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:

ParameterDescription
--file or -fA file that contains SQL statements, separated by ";". All of the SQLs in the file can be converted in batch process.
--type or -tThe type of input SQL statement, such as hive, impala. It's optional parameter and the default value is hive.
--output or -othe 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 -cthe config file of SQL Migration Tool.
--debug or -dif 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:

SQLDescriptionopenLooKeng Syntax Reference
CREATE DATABASE/SCHEMAstatement with “COMMENT”, “WITH DBPROPERTIES” is not supportedCREATE SCHEMA
DROP DATABASE/SCHEMAstatement with “CASCADE” is not supportedDROP SCHEMA
SHOW DATABASE/SCHEMAstatement with “like” is not supportedSHOW SCHEMA
CREATE TABLEstatement with “SKEWED BY”,“ROW FORMAT” is not supportedCREATE TABLE
DROP TABLEstatement with “PURGE” is not supportedDROP TABLE
ALTER TABLEonly “Rename table” and “Add a single column “are supportedALTER TABLE
SHOW CREATE TABLETo hive show table can works on both table and view. But in openLooKeng, this can only be applied to table.SHOW CREATE TABLE
DESCRIBEstatement with column name is supportedDESCRIBE
CREATE VIEWstatement with “COMMENT”, “WITH DBPROPERTIES” is not supportedCREATE VIEW
SHOW COLUMNSstatement with “like” is not supportedSHOW COLUMNS
SHOW GRANTStatement with Specified user or role is not supportedSHOW GRANT
INSERTstatement with “partition” is not supportedINSERT
SELECTstatement with “cluster by”, “offset” is not supportedSELECT

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:

SQLDescriptionopenLooKeng Syntax Reference
DROP SCHEMAstatement with “CASCADE” is not supportedDROP SCHEMA
CREATE TABLEstatement with “ROW FORMAT”, “WITH SERDEPROPERTIES”, “CACHED IN” is not supportedCREATE TABLE
CREATE TABLE LIKEstatement with “PARQUET” is not supportedCREATE TABLE
DROP TABLEstatement with “PURGE” is not supportedDROP TABLE
CREATE VIEWstatement with “IF NOT EXISTS”, “ALIAS” is not supportedCREATE VIEW
ALTER VIEWAlias is not supported, and it will be converted to “CREATE OR REPLACE VIEW”ALTER TABLE
DESCRIBEOnly table is supported to use describeDESCRIBE
GRANT ROLEGranting role to Group is not supportedGRANT ROLES
GRANTOnly “SELECT”,“INSERT” privileges are supported, and only ROLE can be granted toGRANT
REVOKE ROLERevoking role from Group is not supportedREVOKE ROLES
REVOKEOnly “SELECT”,“INSERT” privileges are supported, and only ROLE can be revoked fromREVOKE
INSERT INTOstatement with “WITH”, “HINT”, “PARTITION” is not supportedINSERT INTO
DELETEstatement with “JOIN” is not supportedDELETE
SHOW SCHEMASstatement with more than one wildcard is not supportedDELETE
SHOW TABLESstatement with more than one wildcard is not supportedDELETE
ADD COMMENTSAdding comments to databases or columns is not supportedCOMMENT
SET SESSIONOnly support “SET” and “SET ALL”SET SESSION
ADD COLUMNSADD multiple columns within single statement is not supported, kudu properties are not supported.ALTER TABLE
SHOW FUNCTIONSOnly 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.