Snowpark Migration Accelerator: SQL-Anweisungen¶

Tagged elements¶

SQL-Anweisungen werden markiert, um die Nutzung und den Verbrauch zu überwachen.


Anweisungen	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

Bemerkung

Wenn ein Kommentar mit „FUNCTIONAL EQUIVALENT“ gekennzeichnet ist, bedeutet dies, dass nur die Transformation des Kommentars in Snowflake validiert wurde. Alle anderen Anweisungen innerhalb des Kommentars sind nicht in dieser Statusbewertung enthalten.

Usages¶

Das Tool identifiziert und markiert die folgenden Anweisungen:

CREATE STATEMENTS¶

CREATE-Anweisungen werden Tags in zwei Szenarien enthalten:

In der SQL-Anweisung fehlt die COMMENT-Eigenschaft.
The SQL statement includes a COMMENT property, but no value has been assigned to it.

Wenn eine SQL-Anweisung einen Kommentar enthält, wird dieser bei der Konvertierung beibehalten.

Example¶

Eingabe (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';

Ausgabe (Snowfake 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';

Die Formatierung des generierten Codes kann aufgrund von Formatierungsunterschieden in der Originaldatei anders aussehen als der Quellcode.

Create Table¶

Eingabecode (SparkSQL)

CREATE TABLE SOME_TABLE
(COL1 VARCHAR(5));

Ausgabecode (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¶

Quellcode (HiveSQL)

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

Ausgabecode (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¶

Eingabecode (SparkSQL)

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

Ausgabe (Snowfake 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¶

ALTER-Anweisungen enthalten ein Tag, wenn die Kommentareigenschaft leer ist. Dies geschieht in SparkSQL in zwei Szenarien:

When using SET TBLPROPERTIES with an empty comment
When using UNSET TBLPROPERTIES

Beispiele¶

SET TBLPROPERTIES (ALTER VIEW und ALTER TABLE)

Eingabe (Apache Spark SQL)

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

Ausgabe (Snowfake 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'= ' ');

Ausgabe (Snowfake 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 und ALTER TABLE)

Eingabe (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"}}'