Snowpark Migration Accelerator: 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

Note

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';
Copy

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';
Copy

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));
Copy

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"}}';
Copy
CREATE VIEW ¶

Input code (HiveSQL)

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

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;
Copy
CREATE FUNCTION ¶

Input code (SparkSQL)

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

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';
Copy

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 SOME_TABLE SET TBLPROPERTIES ('comment'= ' ');

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

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"}}');
Copy

Input (HiveSQL)

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

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

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"}}');
Copy
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');
Copy

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"}}'
Copy
Language: English