Snowpark Migration Accelerator : Instructions SQL¶

Éléments balisés ¶

Les instructions SQL sont balisées pour surveiller l’utilisation et la consommation.

Instructions	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

Lorsqu’un commentaire est marqué comme « FUNCTIONAL EQUIVALENT », cela signifie que seule la transformation du commentaire en Snowflake a été validée. Toute autre instruction contenue dans le commentaire n’est pas incluse dans cette évaluation du statut.

Utilisations ¶

L’outil identifie et étiquette les instructions suivantes :

CREATE STATEMENTS¶

Les instructions CREATE incluront des balises dans deux scénarios :

L’instruction SQL ne contient pas la propriété COMMENT.
L’instruction SQL comprend une propriété COMMENT, mais aucune valeur ne lui a été attribuée.

Si une instruction SQL comporte un commentaire, celui-ci sera conservé lors de la conversion.

Exemple : ¶

Entrée (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

Sortie (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

Le formatage du code généré peut apparaître différent du code source en raison de différences de formatage dans le fichier d’origine.

Créer une table ¶

Code d’entrée (SparkSQL)

CREATE TABLE SOME_TABLE
(COL1 VARCHAR(5));

Copy

Code de sortie (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 ¶

Code source (HiveSQL)

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

Copy

Code de sortie (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 ¶

Code d’entrée (SparkSQL)

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

Copy

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

Les instructions ALTER incluent une balise lorsque la propriété commentaire est vide. Cela se produit dans deux scénarios dans SparkSQL :

Lors de l’utilisation de SET TBLPROPERTIES avec un commentaire vide
Lors de l’utilisation de UNSET TBLPROPERTIES

Exemples¶

SET TBLPROPERTIES (ALTER VIEW et ALTER TABLE)

Entrée (Apache Spark SQL)

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

Copy

Sortie (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

Sortie (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 et ALTER TABLE)

Entrée (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