DECLARE

Translation specification for the DECLARE statement from BigQuery to Snowflake

Description

Declares one or more variables of the specified datatype and assigns them the given default value. (BigQuery documentation)

Transformation information

DECLARE variable_name[, ...] [variable_type] [DEFAULT expression];

Sample Source Patterns

Setup data

BigQuery

Query

CREATE TABLE test.declareTable
(
    col1 INT64,
    col2 DATE,
    col3 STRING,
    col4 STRING,
    col5 STRING
);

CREATE TABLE test.declareTable2
(
    col1 ARRAY<INT64>,
    col2 GEOGRAPHY,
    col3 INTERVAL,
    col4 JSON
);

Snowflake

Query

CREATE TABLE test.declareTable
(
    col1 INT,
    col2 DATE,
    col3 STRING,
    col4 STRING,
    col5 STRING
);

CREATE TABLE test.declareTable2
(
    col1 ARRAY DEFAULT [],
    col2 GEOGRAPHY,
    col3 VARCHAR(30) /*** MSC-WARNING - MSCEWI1036 - INTERVAL DATA TYPE CONVERTED TO VARCHAR ***/,
    col4 VARIANT
);

Declare at script level

BigQuery

Query

DECLARE myVar1 STRING;

DECLARE myVar2, myVar3, myVar4 INT64;

DECLARE myVar5 STRING DEFAULT 'Hello';

DECLARE myVar6, myVar7, myVar8 DEFAULT 3;

SELECT myVar1, myVar2, myVar3, myVar4, myVar5, myVar6, myVar7, myVar8;

Result

Snowflake

Query

SET myVar1 = NULL;

SET (myVar2, myVar3, myVar4) = (NULL, NULL, NULL);

SET myVar5 = 'Hello';

SET (myVar6, myVar7, myVar8) = (3, 3, 3);

SELECT $myVar1, $myVar2, $myVar3, $myVar4, $myVar5, $myVar6, $myVar7, $myVar8;

Result

Declare inside BEGIN...END block

BigQuery

Query

BEGIN
    DECLARE myVar9 INT64;
    DECLARE myVar10 DEFAULT CURRENT_DATE();
    DECLARE myVar11, myVar12, myVar13 STRING DEFAULT 'Default value';
    INSERT INTO test.declareTable VALUES (myVar9, myVar10, myVar11, myVar12, myVar13);
END;

SELECT * FROM test.declareTable;

Result

Snowflake

Query

BEGIN
    LET myVar9 INT DEFAULT NULL;
    LET myVar10 DEFAULT CURRENT_DATE();
    LET myVar11 STRING DEFAULT 'Default value';
    LET myVar12 STRING DEFAULT 'Default value';
    LET myVar13 STRING DEFAULT 'Default value';
    INSERT INTO test.declareTable VALUES(:myVar9, :myVar10, :myVar11, :myVar12, :myVar13);
END;

SELECT * FROM test.declareTable;

Result

Declare of complex datatype inside BEGIN...END block

BigQuery

Query

BEGIN
    DECLARE myVar24 ARRAY<INT64> DEFAULT [4, 5, 6];
    DECLARE myVar25 DEFAULT [4, 5, 6];

    DECLARE myVar26 GEOGRAPHY DEFAULT ST_GEOGPOINT(-122.0838, 37.3860);
    DECLARE myVar27 DEFAULT ST_GEOGPOINT(-122.0838, 37.3860);

    DECLARE myvar28 INTERVAL DEFAULT INTERVAL 1 YEAR;
    DECLARE myvar29 DEFAULT INTERVAL 1 YEAR;

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

    INSERT INTO test.declareTable2 VALUES (myVar24, myVar26, myVar28, myVar30);
    INSERT INTO test.declareTable2 VALUES (myVar25, myVar27, myVar29, myVar31);
END;

SELECT * FROM test.declareTable2;

Result

Snowflake

Query

BEGIN
    LET myVar24 ARRAY DEFAULT [4, 5, 6];
    LET myVar25 DEFAULT [4, 5, 6];

    LET myVar26 GEOGRAPHY DEFAULT ST_POINT(-122.0838, 37.3860);
    LET myVar27 DEFAULT ST_POINT(-122.0838, 37.3860);

    LET myvar28 DEFAULT '1 year';
    LET myvar29 DEFAULT '1 year';

    LET myVar30 VARIANT DEFAULT PARSE_JSON('{"name": "John", "age": 30}');
    LET myVar31 VARIANT DEFAULT PARSE_JSON('{"name": "John", "age": 30}');

    INSERT INTO test.declareTable2 SELECT :myVar24, :myVar26, :myVar28, :myVar30;
    INSERT INTO test.declareTable2 SELECT :myVar25, :myVar27, :myVar29, :myVar31;
END;

ALTER SESSION SET GEOGRAPHY_OUTPUT_FORMAT = 'WKT';

SELECT * FROM test.declareTable2;

Result

Declare of non-literal value at script level

BigQuery

Query

DECLARE myVar37 DATETIME DEFAULT DATE '2010-07-07' + INTERVAL 5 DAY;
DECLARE myVar38 BYTES DEFAULT FROM_BASE64('/+A=');
DECLARE myVar39 STRING DEFAULT (SELECT col3 FROM test.declareTable LIMIT 1);

SELECT myVar37, myVar38, myVar39;

Result

Snowflake

Query

SET myVar37 = (SELECT DATE '2010-07-07' + INTERVAL '5 day');
SET myVar38 = (SELECT TRY_BASE64_DECODE_BINARY('/+A='));
SET myVar39 = (SELECT col3 FROM test.declareTable LIMIT 1);

SELECT $myVar37, BASE64_ENCODE($myVar38), $myVar39;

Result

Known Issues

1. Complex datatypes at script level may fail in Snowflake

Declaring variables at script level (outside BEGIN...END blocks) with complex datatypes like ARRAY, GEOGRAPHY or JSON can cause the exception Unsupported feature 'assignment from non-constant source expression' in Snowflake when trying to assign values to the session variable.

The user will be warned of this with an EWI:

Input code:

DECLARE myVar14 ARRAY<INT64> DEFAULT [4, 5, 6];
DECLARE myVar15 DEFAULT [4, 5, 6];

DECLARE myVar16 GEOGRAPHY DEFAULT ST_GEOGPOINT(-122.0838, 37.3860);
DECLARE myVar17 DEFAULT ST_GEOGPOINT(-122.0838, 37.3860);

SELECT myVar14, myVar16;
SELECT myVar15, myVar17;

Expected code:

-- MSCE-BQ0016 - SESSION VARIABLE ASSIGNMENT OF COMPLEX DATATYPE IS NOT SUPPORTED IN SNOWFLAKE
SET myVar14 = [4, 5, 6];
-- MSCE-BQ0016 - SESSION VARIABLE ASSIGNMENT OF COMPLEX DATATYPE IS NOT SUPPORTED IN SNOWFLAKE
SET myVar15 = [4, 5, 6];

-- MSCE-BQ0016 - SESSION VARIABLE ASSIGNMENT OF COMPLEX DATATYPE IS NOT SUPPORTED IN SNOWFLAKE
SET myVar16 = ST_POINT(-122.0838, 37.3860);
-- MSCE-BQ0016 - SESSION VARIABLE ASSIGNMENT OF COMPLEX DATATYPE IS NOT SUPPORTED IN SNOWFLAKE
SET myVar17 = ST_POINT(-122.0838, 37.3860);

SELECT $myVar14, $myVar16;
SELECT $myVar15, $myVar17;

Related EWIs

1. MSC-BQ0016: Session variable assignment of complex datatype is not supported in Snowflake.