SSC-FDM-BQ0009

Struct converted to VARIANT. Some of its usages might have functional differences.

Description

Snowflake does not natively support the STRUCT data type. SnowConvert automatically converts STRUCT to VARIANT. When used in INSERT statements, STRUCT data will be handled using OBJECT_CONSTRUCT. Be aware that this conversion may introduce functional differences in some use cases.

Code Example

Input Code:

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

Output Code:

OUT -> BigQuery_01.sql

CREATE OR REPLACE TABLE test.structTypes
(
  COL1 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT> CONVERTED TO VARIANT. REVIEW FUNCTIONALITY FOR POTENTIAL CHANGES. ***/,
  COL2 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<STRING(10)> CONVERTED TO VARIANT. REVIEW FUNCTIONALITY FOR POTENTIAL CHANGES. ***/,
  COL3 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<STRUCT<INT64, INT64>> CONVERTED TO VARIANT. REVIEW FUNCTIONALITY FOR POTENTIAL CHANGES. ***/,
  COL4 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<> CONVERTED TO VARIANT. REVIEW FUNCTIONALITY FOR POTENTIAL CHANGES. ***/,
  COL5 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT, INT> CONVERTED TO VARIANT. REVIEW FUNCTIONALITY FOR POTENTIAL CHANGES. ***/,
  COL7 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT, BOOLEAN> CONVERTED TO VARIANT. REVIEW FUNCTIONALITY FOR POTENTIAL CHANGES. ***/,
  COL8 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT, BOOLEAN> CONVERTED TO VARIANT. REVIEW FUNCTIONALITY FOR POTENTIAL CHANGES. ***/
);

Recommendations

PreviousSSC-FDM-BQ0008 NextSSC-FDM-BQ0010

Last updated 5 days ago

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.structTypes ( COL1 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT> CONVERTED TO VARIANT. REVIEW FUNCTIONALITY FOR POTENTIAL CHANGES. ***/, COL2 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<STRING(10)> CONVERTED TO VARIANT. REVIEW FUNCTIONALITY FOR POTENTIAL CHANGES. ***/, COL3 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<STRUCT<INT64, INT64>> CONVERTED TO VARIANT. REVIEW FUNCTIONALITY FOR POTENTIAL CHANGES. ***/, COL4 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<> CONVERTED TO VARIANT. REVIEW FUNCTIONALITY FOR POTENTIAL CHANGES. ***/, COL5 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT, INT> CONVERTED TO VARIANT. REVIEW FUNCTIONALITY FOR POTENTIAL CHANGES. ***/, COL7 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT, BOOLEAN> CONVERTED TO VARIANT. REVIEW FUNCTIONALITY FOR POTENTIAL CHANGES. ***/, COL8 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT, BOOLEAN> CONVERTED TO VARIANT. REVIEW FUNCTIONALITY FOR POTENTIAL CHANGES. ***/ );