Snowpark Migration Accelerator：SQL 语句¶

带标签的元素 ¶

SQL 语句会被打上标签，以监控其使用情况和消耗情况。

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

备注

当注释被标记为“FUNCTIONAL EQUIVALENT”时，这仅表示该注释已成功通过验证，转换为适用于 Snowflake 的格式。注释中的任何其他语句不包含在此状态评估中。

使用情况 ¶

该工具识别并标记以下语句：

CREATE STATEMENTS¶

CREATE 语句在以下两种情况下包含标签：

SQL 语句缺少 COMMENT 属性。
SQL 语句包含 COMMENT 属性，但尚未为其分配任何值。

如果 SQL 语句包含注释，则该注释将在转换过程中保留下来。

示例 ¶

输入 (Apache 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

输出 (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

由于原始文件中的格式差异，生成的代码的格式可能与源代码有所不同。

创建表 ¶

输入代码 (SparkSQL)

CREATE TABLE SOME_TABLE
(COL1 VARCHAR(5));

Copy

输出代码 (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 ¶

源代码 (HiveSQL)

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

Copy

输出代码 (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 ¶

输入代码 (SparkSQL)

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

Copy

输出 (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¶

当注释属性为空时，ALTER 语句将包含一个标签。在 SparkSQL 中，这种情况出现在以下两种场景：

使用含空注释的 SETTBLPROPERTIES 时
使用 UNSET TBLPROPERTIES 时

示例¶

SET TBLPROPERTIES (ALTER VIEW 和 ALTER TABLE)

输入 (Apache Spark SQL)

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

Copy

输出 (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 (Apache HiveSQL)**

```{code} sql
:force:
-- ALTER TABLE
ALTER TABLE SOME_TABLE SET TBLPROPERTIES ('comment'= ' ');

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

Copy

输出 (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 和 ALTER TABLE)

输入 (Apache Spark SQL)

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

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

**Output (Snowflake SQL)**

```{code} sql
:force:
-- 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