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
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
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
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;
+--------+--------+--------+--------+--------+--------+--------+--------+
| MYVAR1 | MYVAR2 | MYVAR3 | MYVAR4 | MYVAR5 | MYVAR6 | MYVAR7 | MYVAR8 |
+--------+--------+--------+--------+--------+--------+--------+--------+
| | | | | Hello | 3 | 3 | 3 |
+--------+--------+--------+--------+--------+--------+--------+--------+
Snowflake
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;
+--------+--------+--------+--------+--------+--------+--------+--------+
| MYVAR1 | MYVAR2 | MYVAR3 | MYVAR4 | MYVAR5 | MYVAR6 | MYVAR7 | MYVAR8 |
+--------+--------+--------+--------+--------+--------+--------+--------+
| | | | | Hello | 3 | 3 | 3 |
+--------+--------+--------+--------+--------+--------+--------+--------+
Declare inside BEGIN...END block
BigQuery
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;
+------+------------+---------------+---------------+---------------+
| COL1 | COL2 | COL3 | COL4 | COL5 |
+------+------------+---------------+---------------+---------------+
| | 2023-12-11 | Default value | Default value | Default value |
+------+------------+---------------+---------------+---------------+
Snowflake
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;
+------+------------+---------------+---------------+---------------+
| COL1 | COL2 | COL3 | COL4 | COL5 |
+------+------------+---------------+---------------+---------------+
| | 2023-12-11 | Default value | Default value | Default value |
+------+------------+---------------+---------------+---------------+
Declare of complex datatype inside BEGIN...END block
BigQuery
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;
+-------------------+-------------------------+-------------+-----------------------------------+
| COL1 | COL2 | COL3 | COL4 |
+-------------------+-------------------------+-------------+-----------------------------------+
| [ 4, 5, 6 ] | POINT(-122.0838 37.386) | 1-0 0 0:0:0 | { "age": 30, "name": "John" } |
+-------------------+-------------------------+-------------+-----------------------------------+
| [ 4, 5, 6 ] | POINT(-122.0838 37.386) | 1-0 0 0:0:0 | { "age": 30, "name": "John" } |
+-------------------+-------------------------+-------------+-----------------------------------+
Snowflake
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;
+-------------------+-------------------------+--------+-----------------------------------+
| COL1 | COL2 | COL3 | COL4 |
+-------------------+-------------------------+--------+-----------------------------------+
| [ 4, 5, 6 ] | POINT(-122.0838 37.386) | 1 year | { "age": 30, "name": "John" } |
+-------------------+-------------------------+--------+-----------------------------------+
| [ 4, 5, 6 ] | POINT(-122.0838 37.386) | 1 year | { "age": 30, "name": "John" } |
+-------------------+-------------------------+--------+-----------------------------------+
Declare of non-literal value at script level
BigQuery
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;
+------------+-----------------------------+---------------+
| MYVAR37 | TO_CHAR($MYVAR38, 'BASE64') | MYVAR39 |
+------------+-----------------------------+---------------+
| 2010-07-12 | /+A= | Default value |
+------------+-----------------------------+---------------+
Snowflake
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;
+------------+-----------------------------+---------------+
| MYVAR37 | TO_CHAR($MYVAR38, 'BASE64') | MYVAR39 |
+------------+-----------------------------+---------------+
| 2010-07-12 | /+A= | Default value |
+------------+-----------------------------+---------------+
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
MSC-BQ0016: Session variable assignment of complex datatype is not supported in Snowflake.
Last updated