SSC-EWI-BQ0011

Session variable assignment of complex datatype is not supported in Snowflake

Severity

Medium

Description

In BigQuery, declaring a variable at script level allows it to be used in the entire script, to replicate this behavior in Snowflake SQL variables are used.

However, declaring variables of datatypes that are complex like ARRAY, GEOGRAPHY, STRUCT or JSON will fail in Snowflake when trying to set the value to the SQL variable. When SnowConvert detects one of such cases then this is EWI will be added to the SQL variable declaration.

Variables of these types can be declared without problems inside block statements and other procedural statements, this EWI applies only for variables declared at script level.

Example code

Input Code:

BigQuery

IN -> BigQuery_01.sql

CREATE TABLE test.JsonTable
(
    col1 JSON
);

DECLARE myVar1 JSON DEFAULT JSON'{"name": "John", "age": 30}';

INSERT INTO test.JsonTable VALUES (myVar1);

BEGIN
    DECLARE myVar2 JSON DEFAULT JSON'{"name": "Mike", "age": 27}';
    INSERT INTO test.JsonTable VALUES (myVar2);
END;

SELECT col1 FROM test.JsonTable;

Output Code:

Snowflake

OUT -> BigQuery_01.sql

CREATE TABLE test.JsonTable
(
    col1 VARIANT
)

!!!RESOLVE EWI!!! /*** SSC-EWI-BQ0011 - SESSION VARIABLE ASSIGNMENT OF COMPLEX DATATYPE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
SET myVar1 = (
    SELECT
    PARSE_JSON('{"name": "John", "age": 30}')
);

INSERT INTO test.JsonTable
VALUES ($myVar1);

BEGIN
    LET myVar2 VARIANT DEFAULT PARSE_JSON('{"name": "Mike", "age": 27}');
    INSERT INTO test.JsonTable
    VALUES (:myVar2);
END;

SELECT col1 FROM
    test.JsonTable;

Recommendations

• If the uses of the variable are limited to a single scope or its value is never modified, consider declaring the variable locally in the scopes that use it, that will solve the issue.

• If you need more support, you can email us at [email protected]