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

BigQuery

CREATE OR REPLACE TABLE test.structTypes
(
    COL1 STRUCT,
    COL2 STRUCT<sc2 STRING(10)>,
    COL3 STRUCT<sc3 STRUCT<sc31 INT64, sc32 INT64>>,
    COL4 STRUCT<sc4 ARRAY>,
    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>
);

Snowflake

CREATE OR REPLACE TABLE test.structTypes
(
  COL1 VARIANT /*** MSC-WARNING - MSCEWI1036 - STRUCT<INT> DATA TYPE CONVERTED TO VARIANT ***/,
  COL2 VARIANT /*** MSC-WARNING - MSCEWI1036 - STRUCT<STRING(10)> DATA TYPE CONVERTED TO VARIANT ***/,
  COL3 VARIANT /*** MSC-WARNING - MSCEWI1036 - STRUCT<STRUCT<INT64, INT64>> DATA TYPE CONVERTED TO VARIANT ***/,
  COL4 VARIANT /*** MSC-WARNING - MSCEWI1036 - STRUCT<> DATA TYPE CONVERTED TO VARIANT ***/,
  COL5 VARIANT /*** MSC-WARNING - MSCEWI1036 - STRUCT<INT, INT> DATA TYPE CONVERTED TO VARIANT ***/,
  COL7 VARIANT /*** MSC-WARNING - MSCEWI1036 - STRUCT<INT, BOOLEAN> DATA TYPE CONVERTED TO VARIANT ***/,
  COL8 VARIANT /*** MSC-WARNING - MSCEWI1036 - STRUCT<INT, BOOLEAN> DATA TYPE CONVERTED TO VARIANT ***/
);

CREATE OR REPLACE TABLE test.tuple_sample (
  COL1 VARIANT /*** MSC-WARNING - MSCEWI1036 - STRUCT<INT, INT> DATA TYPE CONVERTED TO VARIANT ***/
);

Insert INT Data Type to STRUCT column

BigQuery

INSERT INTO test.structTypes (COL1) VALUES
(STRUCT(1)), 
(STRUCT<INT64>(2)), 
(STRUCT<a INT64>(3)), 
(STRUCT<sc1 INT64>(4)), 
(STRUCT<sc1 INT64>(5));

Snowflake

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

BigQuery

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

Snowflake

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

BigQuery

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)));

Snowflake

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

BigQuery

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]));

Snowflake

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

BigQuery

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));

Snowflake

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

BigQuery

INSERT INTO test.tuple_sample
VALUES
  ((12, 34)),
  ((56, 78)),
  ((9, 99)),
  ((12, 35));

Snowflake

INSERT INTO test.tuple_sample
----** MSC-ERROR - MSC-BQ0016 - 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

BigQuery

UPDATE test.structTypes
SET col1 = STRUCT(100 AS number)
WHERE col1.sc1 = 4;

Snowflake

UPDATE test.structTypes
  SET col1 = OBJECT_CONSTRUCT('sc1', 100 :: INT)
WHERE col1:sc1 = 4;

Update STRUCT column field

BigQuery

UPDATE test.structTypes
SET col3 = STRUCT(STRUCT(80,90))
WHERE col3.sc3.sc31 = 20;

Snowflake

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

BigQuery

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;

Snowflake

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

BigQuery

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;

Snowflake

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

SELECT * FROM test.structTypes WHERE COL1 NOT IN (COL2);
SELECT * FROM test.structTypes WHERE COL1 <> (COL2);
SELECT * FROM test.structTypes WHERE COL1 != (COL2);

Snowflake

SELECT * FROM
test.structTypes2
--** MSC-WARNING - MSCINF0009 - WHERE CLAUSE REFERENCES A COLUMN OF STRUCT TYPE. COMPARISON OPERATIONS MAY PRODUCE DIFFERENT RESULTS IN SNOWFLAKE. **
WHERE COL1 NOT IN (COL2);
SELECT * FROM
test.structTypes2
--** MSC-WARNING - MSCINF0009 - WHERE CLAUSE REFERENCES A COLUMN OF STRUCT TYPE. COMPARISON OPERATIONS MAY PRODUCE DIFFERENT RESULTS IN SNOWFLAKE. **
WHERE COL1 <> (COL2);
SELECT * FROM
test.structTypes2
--** MSC-WARNING - MSCINF0009 - WHERE CLAUSE REFERENCES A COLUMN OF STRUCT TYPE. COMPARISON OPERATIONS MAY PRODUCE DIFFERENT RESULTS IN SNOWFLAKE. **
WHERE COL1 != (COL2);