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.

Note: Arguments that represent keys within the OBJECT_CONSTRUCT must be the original names of the target STRUCT. Any name specified within a STRUCT expression body will be replaced with the name found in the target STRUCT. Most of the data pattern examples below contain an example of a name that is replaced by the target name.

Sample Source Patterns

Setup data

IN -> BigQuery_01.sql
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:

IN -> BigQuery_02.sql
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:

OUT -> BigQuery_02.sql
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:

IN -> BigQuery_03.sql
INSERT INTO test.structTypes (COL2) VALUES
(STRUCT('t1')), 
(STRUCT<STRING>('t2')), 
(STRUCT<sc2 STRING>('t3'));

Output Code:

OUT -> BigQuery_03.sql
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:

IN -> BigQuery_04.sql
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:

OUT -> BigQuery_04.sql
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:

IN -> BigQuery_05.sql
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:

OUT -> BigQuery_05.sql
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:

IN -> BigQuery_06.sql
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:

OUT -> BigQuery_06.sql
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

Input Code:

IN -> BigQuery_07.sql
INSERT INTO test.tuple_sample
VALUES
  ((12, 34)),
  ((56, 78)),
  ((9, 99)),
  ((12, 35));

Output Code:

OUT -> BigQuery_07.sql
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:

IN -> BigQuery_08.sql
UPDATE test.structTypes
SET col1 = STRUCT(100 AS number)
WHERE col1.sc1 = 4;

Output Code:

OUT -> BigQuery_08.sql
UPDATE test.structTypes
    SET col1 = OBJECT_CONSTRUCT('sc1', 100 :: INT)
WHERE col1:sc1 = 4;

Update STRUCT column field

Input Code:

IN -> BigQuery_09.sql
UPDATE test.structTypes
SET col3 = STRUCT(STRUCT(80,90))
WHERE col3.sc3.sc31 = 20;

Output Code:

OUT -> BigQuery_09.sql
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:

IN -> BigQuery_10.sql
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:

OUT -> BigQuery_10.sql
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:

IN -> BigQuery_11.sql
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:

OUT -> BigQuery_11.sql
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;

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:

IN -> BigQuery_12.sql
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:

OUT -> BigQuery_12.sql
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);
  1. SSC-FDM-BQ0010: Struct converted to VARIANT. Some of its usages might have functional differences.

  2. SSC-EWI-BQ0012: Unable to generate correct OBJECT_CONSTRUCT parameter.

  3. SSC-FDM-BQ0008: Where clause references a column of STRUCT type.

Last updated