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

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

Snowflake

OUT -> BigQuery_01.sql

CREATE OR REPLACE TABLE test.structTypes
(
    COL1 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/,
    COL2 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<STRING(10)> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/,
    COL3 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<STRUCT<INT64, INT64>> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/,
    COL4 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/,
    COL5 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT, INT> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/,
    COL7 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT, BOOLEAN> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/,
    COL8 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT, BOOLEAN> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/
);

CREATE OR REPLACE TABLE test.tuple_sample (
  COL1 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT, INT> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/
);

Insert INT Data Type to STRUCT column

Input Code:

BigQuery

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:

Snowflake

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:

BigQuery

IN -> BigQuery_03.sql

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

Output Code:

Snowflake

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:

BigQuery

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:

Snowflake

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:

BigQuery

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:

Snowflake

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:

BigQuery

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:

Snowflake

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

Translation of tuple syntax values is currently not supported.

Input Code:

BigQuery

IN -> BigQuery_07.sql

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

Output Code:

Snowflake

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:

BigQuery

IN -> BigQuery_08.sql

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

Output Code:

Snowflake

OUT -> BigQuery_08.sql

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

Update STRUCT column field

Input Code:

BigQuery

IN -> BigQuery_09.sql

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

Output Code:

Snowflake

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:

BigQuery

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:

Snowflake

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:

BigQuery

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:

Snowflake

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;

Create a view using an anonymous STRUCT definition

Input Code:

BigQuery

IN -> BigQuery_12.sql

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:

Snowflake

OUT -> BigQuery_12.sql

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:

BigQuery

IN -> BigQuery_13.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:

Snowflake

OUT -> BigQuery_13.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);

Related EWIs

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.