Translation specification for the ARRAY<T> datatype from BigQuery to Snowflake
Description
In BigQuery, an array is an ordered list of zero or more elements of non-array values. Elements in an array must share the same type. (Array Type. BigQuery)
Sample Source Patterns
Setup data
Input Code:
IN -> BigQuery_01.sql
CREATE TABLE test.arrayTable
(
col1 ARRAY<INT64>
);
CREATE TABLE test.anotherArrayTable
(
col2 ARRAY<INT64>
);
INSERT INTO test.arrayTable VALUES ([4, 10, 55]);
INSERT INTO test.arrayTable VALUES ([6, 7, 33]);
INSERT INTO test.arrayTable VALUES ([50, 12, 22]);
INSERT INTO test.anotherArrayTable VALUES ([9, 11, 52]);
INSERT INTO test.anotherArrayTable VALUES ([3, 18, 11]);
INSERT INTO test.anotherArrayTable VALUES ([33, 27, 43]);
Output Code:
OUT -> BigQuery_01.sql
CREATE TABLE test.arrayTable
(
col1 ARRAY DEFAULT []
);
CREATE TABLE test.anotherArrayTable
(
col2 ARRAY DEFAULT []
);
INSERT INTO test.arrayTable SELECT [4, 10, 55];
INSERT INTO test.arrayTable SELECT [6, 7, 33];
INSERT INTO test.arrayTable SELECT [50, 12, 22];
INSERT INTO test.anotherArrayTable SELECT [9, 11, 52];
INSERT INTO test.anotherArrayTable SELECT [3, 18, 11];
INSERT INTO test.anotherArrayTable SELECT [33, 27, 43];
ARRAY access by index
Input Code:
IN -> BigQuery_02.sql
SELECT
col1[0] + 4 AS byIndex,
col1[OFFSET(0)] + 4 AS byOffset,
col1[ORDINAL(1)] + 4 AS byOrdinal
FROM test.arrayTable ORDER BY col1[0];
Output Code:
OUT -> BigQuery_02.sql
SELECT
--** SSC-FDM-BQ0001 - ACCESSING ARRAYS PRODUCES NULL INSTEAD OF AN ERROR FOR POSITIVE OUT OF BOUNDS INDEXES IN SNOWFLAKE **
col1[0] + 4 AS byIndex,
--** SSC-FDM-BQ0001 - ACCESSING ARRAYS PRODUCES NULL INSTEAD OF AN ERROR FOR POSITIVE OUT OF BOUNDS INDEXES IN SNOWFLAKE **
col1[0] + 4 AS byOffset,
--** SSC-FDM-BQ0001 - ACCESSING ARRAYS PRODUCES NULL INSTEAD OF AN ERROR FOR POSITIVE OUT OF BOUNDS INDEXES IN SNOWFLAKE **
col1[1 - 1] + 4 AS byOrdinal
FROM
test.arrayTable
ORDER BY
--** SSC-FDM-BQ0001 - ACCESSING ARRAYS PRODUCES NULL INSTEAD OF AN ERROR FOR POSITIVE OUT OF BOUNDS INDEXES IN SNOWFLAKE **
col1[0];
Safe ARRAY access by index
Input Code:
IN -> BigQuery_03.sql
SELECT
col1[SAFE_OFFSET(0)] AS byOffsset,
col1[SAFE_OFFSET(-4)] AS byOffsetUnderflow,
col1[SAFE_OFFSET(500)] AS byOffsetOverflow,
col1[SAFE_ORDINAL(1)] AS byOrdinal,
col1[SAFE_ORDINAL(-4)] AS byOrdinalUnderflow,
col1[SAFE_ORDINAL(500)] AS byOrdinalOverflow
FROM test.arrayTable ORDER BY col1[0];
Output Code:
OUT -> BigQuery_02.sql
SELECT
PUBLIC.SAFE_OFFSET_UDF(col1, 0) AS byOffsset,
PUBLIC.SAFE_OFFSET_UDF(col1, -4) AS byOffsetUnderflow,
PUBLIC.SAFE_OFFSET_UDF(col1, 500) AS byOffsetOverflow,
PUBLIC.SAFE_OFFSET_UDF(col1, 1 - 1) AS byOrdinal,
PUBLIC.SAFE_OFFSET_UDF(col1, -4 - 1) AS byOrdinalUnderflow,
PUBLIC.SAFE_OFFSET_UDF(col1, 500 - 1) AS byOrdinalOverflow
FROM test.arrayTable ORDER BY
--** SSC-FDM-BQ0001 - ACCESSING ARRAYS PRODUCES NULL INSTEAD OF AN ERROR FOR POSITIVE OUT OF BOUNDS INDEXES IN SNOWFLAKE **
col1[0];
INSERT with ARRAY in the VALUES clause
Input Code:
IN -> BigQuery_04.sql
INSERT INTO test.arrayTable VALUES ([4, 10]);
INSERT INTO test.arrayTable (COL1)
VALUES ([1, 2, 3]), ([4, 5, 6]);
SELECT col1 FROM test.arrayTable ORDER BY col1[0], col1[1];
Output Code:
OUT -> BigQuery_04.sql
INSERT INTO test.arrayTable SELECT [4, 10];
INSERT INTO test.arrayTable (COL1)
SELECT [1, 2, 3]
UNION ALL
SELECT [4, 5, 6];
SELECT col1 FROM
test.arrayTable
ORDER BY
--** SSC-FDM-BQ0001 - ACCESSING ARRAYS PRODUCES NULL INSTEAD OF AN ERROR FOR POSITIVE OUT OF BOUNDS INDEXES IN SNOWFLAKE **
col1[0],
--** SSC-FDM-BQ0001 - ACCESSING ARRAYS PRODUCES NULL INSTEAD OF AN ERROR FOR POSITIVE OUT OF BOUNDS INDEXES IN SNOWFLAKE **
col1[1];
MERGE statement
Input Code:
IN -> BigQuery_05.sql
MERGE INTO test.anotherArrayTable
USING test.arrayTable
ON col1[0] = col2[0]
WHEN MATCHED THEN UPDATE SET col2 = col1
WHEN NOT MATCHED THEN INSERT VALUES ([100, 100, 100]);
SELECT col2 FROM test.anotherArrayTable ORDER BY col2[0];
Output Code:
OUT -> BigQuery_05.sql
MERGE INTO test.anotherArrayTable
USING test.arrayTable
ON col1[0] = col2[0]
WHEN MATCHED THEN UPDATE SET col2 = col1
WHEN NOT MATCHED THEN INSERT VALUES ([100, 100, 100]) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'MergeStatement' NODE ***/!!!;
SELECT col2 FROM
test.anotherArrayTable
ORDER BY
--** SSC-FDM-BQ0001 - ACCESSING ARRAYS PRODUCES NULL INSTEAD OF AN ERROR FOR POSITIVE OUT OF BOUNDS INDEXES IN SNOWFLAKE **
col2[0];
ARRAY DEFAULT column value insertion/update
Input Code:
IN -> BigQuery_06.sql
INSERT INTO test.arrayTable VALUES (DEFAULT);
UPDATE test.arrayTable
SET col1 = DEFAULT
WHERE TRUE;
SELECT col1 FROM test.arrayTable;
Output Code:
OUT -> BigQuery_06.sql
INSERT INTO test.arrayTable SELECT [];
UPDATE test.arrayTable
SET col1 = DEFAULT
WHERE TRUE;
SELECT col1 FROM test.arrayTable;
INSERT/UPDATE with NULL value
Input Code:
IN -> BigQuery_07.sql
INSERT INTO test.arrayTable
SELECT
numbers
FROM
(SELECT [6] AS numbers
UNION ALL
SELECT CAST(NULL AS ARRAY<INT64>));
UPDATE test.arrayTable
SET col1 = NULL
WHERE ARRAY_LENGTH(col1) > 1;
SELECT col1 FROM test.arrayTable ORDER BY ARRAY_LENGTH(col1);
Output Code:
OUT -> BigQuery_07.sql
INSERT INTO test.arrayTable
SELECT
numbers
FROM
(SELECT [6] AS numbers
UNION ALL
SELECT IFNULL(CAST(NULL AS ARRAY), []));
UPDATE test.arrayTable
SET col1 = IFNULL(NULL, [])
WHERE ARRAY_SIZE(col1) > 1;
SELECT col1 FROM test.arrayTable ORDER BY ARRAY_SIZE(col1);
ARRAY concatenation
Input Code:
IN -> BigQuery_08.sql
SELECT [50, 30, 12] || [22, 33, 44] AS result;
Output Code:
OUT -> BigQuery_08.sql
SELECT ARRAY_CAT([50, 30, 12], [22, 33, 44]) AS result;
ARRAY used as parameter/return type
Input Code:
IN -> BigQuery_09.sql
CREATE FUNCTION test.myArrayFunction (valuesArray ARRAY<INT64>, otherValue INTEGER)
RETURNS ARRAY<INT64>
AS
(
valuesArray || [otherValue]
);
SELECT test.myArrayFunction([5, 20, 10], 55) AS result;
Output Code:
OUT -> BigQuery_09.sql
CREATE FUNCTION test.myArrayFunction (valuesArray ARRAY, otherValue INTEGER)
RETURNS ARRAY
AS
$$
ARRAY_CAT(valuesArray, [otherValue])
$$;
SELECT test.myArrayFunction([5, 20, 10], 55) AS result;
Known Issues
1. Non-safe ARRAY access will not fail for positive out of bounds indexes
In BigQuery, accessing an array element by index will fail for any index value that is too low (underflow) or too high (overflow) when not using SAFE_OFFSET or SAFE_ORDINAL. However, in Snowflake errors are thrown only for underflow cases, any index that would case an overflow error will generate a NULL value instead.
When non-safe access to elements in an array is detected SnowConvert will generate SSC-FDM-BQ0001 to warn the user about this.
Related EWIs
SSC-FDM-BQ0001: Accessing arrays produces NULL instead of an error for positive out of bounds indexes in Snowflake.
