STRUCT
Translation specification for the STRUCT datatype from BigQuery to Snowflake.
Description
In BigQuery, a container of ordered fields each with a type (required) and field name (optional). See Struct Type.
In Snowflake, OBJECT_CONSTRUCT
can be used to emulate the STRUCT
behavior, and SnowConvert handles most implementation differences.
Sample Source Patterns
Setup data
CREATE OR REPLACE TABLE test.structTypes
(
COL1 STRUCT<sc1 INT64>,
COL2 STRUCT<sc2 STRING(10)>,
COL3 STRUCT<sc3 STRUCT<sc31 INT64, sc32 INT64>>,
COL4 STRUCT<sc4 ARRAY<INT64>>,
COL5 STRUCT<sc5 INT64, sc51 INT64>,
COL7 STRUCT<sc7 INT64 OPTIONS(description = "A repeated STRING field"), sc71 BOOL>,
COL8 STRUCT<sc8 INT64 NOT NULL, sc81 BOOL NOT NULL OPTIONS(description = "A repeated STRING field")>
);
CREATE OR REPLACE TABLE test.tuple_sample (
COL1 STRUCT<Key1 INT64, Key2 INT64>
);
Insert INT Data Type to STRUCT column
Input Code:
INSERT INTO test.structTypes (COL1) VALUES
(STRUCT(1)),
(STRUCT<INT64>(2)),
(STRUCT<a INT64>(3)),
(STRUCT<sc1 INT64>(4)),
(STRUCT<sc1 INT64>(5));
Output Code:
INSERT INTO test.structTypes (COL1)
SELECT
OBJECT_CONSTRUCT('sc1', 1 :: INT)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc1', 2 :: INT)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc1', 3 :: INT)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc1', 4 :: INT)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc1', 5 :: INT);
Insert STRING Data Type to STRUCT column
Input Code:
INSERT INTO test.structTypes (COL2) VALUES
(STRUCT('t1')),
(STRUCT<STRING>('t2')),
(STRUCT<sc2 STRING>('t3'));
Output Code:
INSERT INTO test.structTypes (COL2)
SELECT
OBJECT_CONSTRUCT('sc2', 't1' :: STRING)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc2', 't2' :: STRING)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc2', 't3' :: STRING);
Insert STRUCT Data Type to STRUCT column
Input Code:
INSERT INTO test.structTypes (COL3) VALUES
(STRUCT(STRUCT(1,2))),
(STRUCT<sc3 STRUCT<sc31 INT64, sc32 INT64>>(STRUCT<INT64, INT64>(3, 4))),
(STRUCT<sc3 STRUCT<sc31 INT64, sc32 INT64>>(STRUCT<sc31 INT64, sc32 INT64>(5, 6))),
(STRUCT<STRUCT<INT64,INT64>>(STRUCT<INT64, INT64>(7, 8))),
(STRUCT<STRUCT<INT64,INT64>>(STRUCT(9, 10)));
Output Code:
INSERT INTO test.structTypes (COL3)
SELECT
OBJECT_CONSTRUCT('sc3', OBJECT_CONSTRUCT('sc31', 1 :: INT, 'sc32', 2 :: INT))
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc3', OBJECT_CONSTRUCT('sc31', 3 :: INT, 'sc32', 4 :: INT))
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc3', OBJECT_CONSTRUCT('sc31', 5 :: INT, 'sc32', 6 :: INT))
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc3', OBJECT_CONSTRUCT('sc31', 7 :: INT, 'sc32', 8 :: INT))
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc3', OBJECT_CONSTRUCT('sc31', 9 :: INT, 'sc32', 10 :: INT));
Insert ARRAY Data Type to STRUCT column
Input Code:
INSERT INTO test.structTypes (COL4) VALUES
(STRUCT([1,2,3,4])),
(STRUCT<sc4 ARRAY<INT64>>(ARRAY[5,6,7])),
(STRUCT<ARRAY<INT64>>([8,9,10,11]));
Output Code:
INSERT INTO test.structTypes (COL4)
SELECT
OBJECT_CONSTRUCT('sc4', [1,2,3,4] :: ARRAY)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc4', [5,6,7] :: ARRAY)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc4', [8,9,10,11] :: ARRAY);
Insert to selected STRUCT columns
Input Code:
INSERT INTO test.structTypes (COL7, COL8) VALUES
(STRUCT(1,true), STRUCT(2,false)),
(STRUCT<INT64, BOOL>(3, false), STRUCT<INT64, BOOL>(4, false)),
(STRUCT<a INT64, b BOOL>(5, true), STRUCT<a INT64, b BOOL>(6, true));
Output Code:
INSERT INTO test.structTypes (COL7, COL8)
SELECT
OBJECT_CONSTRUCT('sc7', 1 :: INT, 'sc71', true),
OBJECT_CONSTRUCT('sc8', 2 :: INT, 'sc81', false)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc7', 3 :: INT, 'sc71', false),
OBJECT_CONSTRUCT('sc8', 4 :: INT, 'sc81', false)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc7', 5 :: INT, 'sc71', true),
OBJECT_CONSTRUCT('sc8', 6 :: INT, 'sc81', true);
Insert to STRUCT column tuple syntax
Translation of tuple syntax values is currently not supported.
Input Code:
INSERT INTO test.tuple_sample
VALUES
((12, 34)),
((56, 78)),
((9, 99)),
((12, 35));
Output Code:
INSERT INTO test.tuple_sample
!!!RESOLVE EWI!!! /*** SSC-EWI-BQ0012 - UNABLE TO GENERATE CORRECT OBJECT_CONSTRUCT PARAMETER. SYMBOL INFORMATION COULD NOT BE COLLECTED. ***/!!!
VALUES
((12, 34)),
((56, 78)),
((9, 99)),
((12, 35));
Update STRUCT column
Input Code:
UPDATE test.structTypes
SET col1 = STRUCT(100 AS number)
WHERE col1.sc1 = 4;
Output Code:
UPDATE test.structTypes
SET col1 = OBJECT_CONSTRUCT('sc1', 100 :: INT)
WHERE col1:sc1 = 4;
Update STRUCT column field
Input Code:
UPDATE test.structTypes
SET col3 = STRUCT(STRUCT(80,90))
WHERE col3.sc3.sc31 = 20;
Output Code:
UPDATE test.structTypes
SET col3 = OBJECT_CONSTRUCT('sc3', OBJECT_CONSTRUCT('sc31', 80 :: INT, 'sc32', 90 :: INT))
WHERE col3:sc3:sc31 = 20;
Select from STRUCT column
Input Code:
SELECT COL3.sc3 FROM test.structTypes;
SELECT COL3.sc3.sc32 FROM test.structTypes;
SELECT COL4.sc4 FROM test.structTypes WHERE COL4.sc4 IS NOT NULL;
Output Code:
SELECT COL3:sc3
FROM
test.structTypes;
SELECT COL3:sc3:sc32
FROM
test.structTypes;
SELECT COL4:sc4
FROM
test.structTypes
WHERE COL4:sc4 IS NOT NULL;
Select from STRUCT column tuple syntax
Input Code:
SELECT *
FROM test.tuple_sample
WHERE (COL1.Key1, COL1.Key2) IN ((12, 34), (56, 78));
SELECT STRUCT<x ARRAY<INT64>, y INT64>(COL4.sc4, COL1.sc1)
FROM test.structTypes
WHERE COL1.sc1 IS NOT NULL;
Output Code:
SELECT *
FROM
test.tuple_sample
WHERE (COL1:Key1, COL1:Key2) IN ((12, 34), (56, 78));
SELECT
OBJECT_CONSTRUCT('x', COL4:sc4 :: ARRAY, 'y', COL1:sc1 :: INT)
FROM
test.structTypes
WHERE COL1:sc1 IS NOT NULL;
Create a view using an anonymous STRUCT definition
Input Code:
CREATE OR REPLACE TABLE project-test.mydataset.sourcetable (
id STRING,
payload JSON
);
CREATE VIEW project-test.mydataset.myview AS
SELECT
id,
STRUCT(
payload.user_id AS user_id,
STRUCT(
JSON_VALUE(payload, '$.details.ip_address') AS ip_address,
JSON_VALUE(payload, '$.details.item_id') AS item_id,
SAFE_CAST(JSON_VALUE(payload, '$.details.quantity') AS INT64) AS quantity,
SAFE_CAST(JSON_VALUE(payload, '$.details.price') AS FLOAT64) AS price,
JSON_VALUE(payload, '$.details.text') AS text
) AS details
) AS structured_payload
FROM project-test.mydataset.sourcetable;
Output Code:
CREATE OR REPLACE TABLE "project-test".mydataset.sourcetable (
id STRING,
payload VARIANT
));
CREATE VIEW "project-test".mydataset.myview
AS
SELECT
id,
OBJECT_CONSTRUCT('user_id',
payload:user_id, 'details', OBJECT_CONSTRUCT('ip_address', JSON_EXTRACT_PATH_TEXT(payload, 'details.ip_address'), 'item_id', JSON_EXTRACT_PATH_TEXT(payload, 'details.item_id'), 'quantity', TRY_CAST(JSON_EXTRACT_PATH_TEXT(payload, 'details.quantity') AS INT), 'price', TRY_CAST(JSON_EXTRACT_PATH_TEXT(payload, 'details.price') AS FLOAT), 'text', JSON_EXTRACT_PATH_TEXT(payload, 'details.text'))) AS structured_payload
FROM
"project-test".mydataset.sourcetable;
STRUCT column comparison expressions
BigQuery comparison operations for Structs compare value to value, ignoring the key if it exists, while Snowflake comparison operations for Objects compare both, value and key. This may cause that some comparisons return a different result.
Input Code:
SELECT * FROM test.structTypes WHERE COL1 NOT IN (COL2);
SELECT * FROM test.structTypes WHERE COL1 <> (COL2);
SELECT * FROM test.structTypes WHERE COL1 != (COL2);
Output Code:
SELECT * FROM
test.structTypes
--** SSC-FDM-BQ0008 - WHERE CLAUSE REFERENCES A COLUMN OF STRUCT TYPE. COMPARISON OPERATIONS MAY PRODUCE DIFFERENT RESULTS IN SNOWFLAKE. **
WHERE COL1 NOT IN (COL2);
SELECT * FROM
test.structTypes
--** SSC-FDM-BQ0008 - WHERE CLAUSE REFERENCES A COLUMN OF STRUCT TYPE. COMPARISON OPERATIONS MAY PRODUCE DIFFERENT RESULTS IN SNOWFLAKE. **
WHERE COL1 <> (COL2);
SELECT * FROM
test.structTypes
--** SSC-FDM-BQ0008 - WHERE CLAUSE REFERENCES A COLUMN OF STRUCT TYPE. COMPARISON OPERATIONS MAY PRODUCE DIFFERENT RESULTS IN SNOWFLAKE. **
WHERE COL1 != (COL2);
Related EWIs
SSC-FDM-BQ0010: Struct converted to VARIANT. Some of its usages might have functional differences.
SSC-EWI-BQ0012: Unable to generate correct OBJECT_CONSTRUCT parameter.
SSC-FDM-BQ0008: Where clause references a column of STRUCT type.
Last updated