SQL statements

Tagged elements

The following statements on SQL are tagged to track consumption:

Statements	HiveSQL	SparkSQL	SnowSQL
CREATE TABLE	SUPPORTED	SUPPORTED	FUNCTIONAL EQUIVALENT
CREATE VIEW	SUPPORTED	SUPPORTED	FUNCTIONAL EQUIVALENT
CREATE FUNCTION	NOT SUPPORTED	SUPPORTED	FUNCTIONAL EQUIVALENT
ALTER TABLE	SUPPORTED	SUPPORTED	FUNCTIONAL EQUIVALENT
ALTER VIEW	SUPPORTED	SUPPORTED	FUNCTIONAL EQUIVALENT

The FUNCTIONAL EQUIVALENT status only covers scenarios where the comment is transformed to Snowflake. Other statements contained are not considered on this status.

Usages

The following statements are identified and tagged by the tool:

CREATE STATEMENTS

The CREATE statements will have the tag in two possible scenarios:

1. The statement does not contain the COMMENT property.

2. The statement contains the COMMENT property but its value is empty.

If the statement already contains a comment, this one will be preserved.

Example

Input (SparkSQL)

CREATE OR REPLACE VIEW some_view
AS
SELECT id, name FROM some_table WHERE some_column > 5;

CREATE OR REPLACE FUNCTION blue()
RETURNS STRING
LANGUAGE SQL
COMMENT ''
RETURN '0000FF'; 

CREATE TABLE my_varchar (
    COL1 VARCHAR(5)
) COMMENT 'The Table';

Output (Snowflake SQL)

CREATE OR REPLACE VIEW some_view
COMMENT = '{"origin":"sf_sit","name":"sma","version":{"major":1,"minor":2,"patch":3},"attributes":{"language":"HiveSql"}}'
AS
SELECT
   id,
   name
FROM
   some_table
WHERE
   some_column > 5;
   
CREATE OR REPLACE FUNCTION blue()
RETURNS STRING LANGUAGE SQL
COMMENT = '{"origin":"sf_sit","name":"sma","version":{"major":0,"minor":0,"patch":0},"attributes":{"language":"SparkSql"}}'
RETURN '0000FF';

CREATE TABLE my_varchar
(COL1 VARCHAR(5))
COMMENT = 'The Table';

The indentation of the output code may change due to the original format of the source code.

---

Create Table

Input code (SparkSQL)

CREATE TABLE SOME_TABLE
(COL1 VARCHAR(5));

Output code (Snowflake SQL)

CREATE TABLE SOME_TABLEA
(COL1 VARCHAR(5))
COMMENT = '{"origin":"sf_sit","name":"sma","version":{"major":0,"minor":0,"patch":0},"attributes":{"language":"SparkSql"}}';

---

Create View

Input code (HiveSQL)

CREATE OR REPLACE VIEW experienced_employee
AS
SELECT id, name FROM all_employee
WHERE working_years > 5;

Output code (Snowflake SQL)

CREATE OR REPLACE VIEW experienced_employee
COMMENT = '{"origin":"sf_sit","name":"sma","version":{"major":1,"minor":2,"patch":3},"attributes":{"language":"HiveSql"}}'
AS
SELECT
   id,
   name
FROM
   all_employee
WHERE
   working_years > 5;

---

Create Function

Input code (SparkSQL)

CREATE OR REPLACE FUNCTION blue()
RETURNS STRING
LANGUAGE SQL RETURN '0000FF';

Output (Snowflake SQL)

CREATE OR REPLACE FUNCTION blue()
RETURNS STRING
LANGUAGE SQL
COMMENT = '{"origin":"sf_sit","name":"sma","version":{"major":0,"minor":0,"patch":0},"attributes":{"language":"SparkSql"}}'
RETURN '0000FF';

ALTER STATEMENTS

The ALTER statements will have the tag when the comment property comes empty. There are two ways that comment clause comes empty, one is in SET TBLPROPERTIES and the other UNSET TBLPROPERTIES (in case of SparkSql) .

Examples

SET TBLPROPERTIES (Alter View and Alter Table)

Input (SparkSql)

-- ALTER TABLE
ALTER TABLE SOME_TABLE SET TBLPROPERTIES ('comment'= ' ');

-- ALTER VIEW
ALTER VIEW SOME_VIEW SET TBLPROPERTIES ('comment'= ' ');

Output (Snowflake SQL)

-- ALTER TABLE
ALTER TABLE SOME_TABLE
SET TBLPROPERTIES ('comment' = '{"origin":"sf_sit","name":"sma","version":{"major":0,"minor":0,"patch":0},"attributes":{"language":"SparkSql"}}');

-- ALTER VIEW
ALTER VIEW SOME_VIEW
SET TBLPROPERTIES ('comment' = '{"origin":"sf_sit","name":"sma","version":{"major":0,"minor":0,"patch":0},"attributes":{"language":"SparkSql"}}');

Input (HiveSql)

-- ALTER TABLE
ALTER TABLE SOME_TABLE SET TBLPROPERTIES ('comment'= ' ');

-- ALTER VIEW
ALTER VIEW SOME_VIEW SET TBLPROPERTIES ('comment'= ' ');

Output (Snowflake SQL)

-- ALTER TABLE
ALTER TABLE SOME_TABLE
SET TBLPROPERTIES ('comment' = '{"origin":"sf_sit","name":"sma","version":{"major":0,"minor":0,"patch":0},"attributes":{"language":"HiveSql"}}');

-- ALTER VIEW
ALTER VIEW SOME_VIEW
SET TBLPROPERTIES ('comment' = '{"origin":"sf_sit","name":"sma","version":{"major":0,"minor":0,"patch":0},"attributes":{"language":"HiveSql"}}');

UNSET TBLPROPERTIES (Alter View and Alter Table)

Input (SparkSql)

-- ALTER TABLE
ALTER TABLE SOME_TABLE UNSET TBLPROPERTIES ('comment');

-- ALTER VIEW
ALTER VIEW SOME_VIEW UNSET TBLPROPERTIES ('comment');

Output (Snowflake SQL)

-- ALTER TABLE
ALTER TABLE SOME_TABLE
UNSET TBLPROPERTIES ('comment')
ALTER TABLE SOME_TABLE
SET COMMENT = '{"origin":"sf_sit","name":"sma","version":{"major":0,"minor":0,"patch":0},"attributes":{"language":"SparkSql"}}'

-- ALTER VIEW
ALTER VIEW SOME_VIEW
UNSET TBLPROPERTIES ('comment')
ALTER VIEW SOME_VIEW
SET COMMENT = '{"origin":"sf_sit","name":"sma","version":{"major":0,"minor":0,"patch":0},"attributes":{"language":"SparkSql"}}'