COLUMN DEFINITION

Grammar syntax

column :=
  column_name column_schema

column_schema :=
   {
     simple_type
     | STRUCT<field_list>
     | ARRAY<array_element_schema>
   }
   [PRIMARY KEY NOT ENFORCED | REFERENCES table_name(column_name) NOT ENFORCED]
   [DEFAULT default_expression]
   [NOT NULL]
   [OPTIONS(column_option_list)]

simple_type :=
  { data_type | STRING COLLATE collate_specification }

field_list :=
  field_name column_schema [, ...]

array_element_schema :=
  { simple_type | STRUCT<field_list> }
  [NOT NULL]

Sample Source Patterns

Description option

Input Code:

BigQuery

IN -> BigQuery_01.sql

CREATE TABLE table1 (
    col1 VARCHAR(20) OPTIONS(description="A repeated STRING field")
);

Output Code:

Snowflake

OUT -> BigQuery_01.sql

CREATE TABLE table1 (
    col1 VARCHAR(20) COMMENT = 'A repeated STRING field'
);

COLLATE

Input Code:

BigQuery

IN -> BigQuery_02.sql

CREATE TABLE table1 (
    col1 STRING COLLATE 'und:ci'
);

Output Code:

Snowflake

OUT -> BigQuery_02.sql

CREATE TABLE table1 (
    col1 STRING COLLATE 'und-ci'
);

Known Issues

1. Rounding mode not supported

Snowflake does not support specifying a default rounding mode on columns.

Input Code:

BigQuery

IN -> BigQuery_03.sql

CREATE TABLE table1 (
    col1 STRING OPTIONS(rounding_mode = "ROUND_HALF_EVEN")
);

Output Code:

Snowflake

OUT-> BigQuery_03.sql

CREATE TABLE table1 (
    col1 STRING
    !!!RESOLVE EWI!!! /*** SSC-EWI-BQ0001 - SNOWFLAKE DOES NOT SUPPORT THE OPTIONS: ROUNDING_MODE. ***/!!!
    OPTIONS(
        rounding_mode = "ROUND_HALF_EVEN"
    )
)

Related EWIs

1. SSC-EWI-BQ0001: Snowflake does not support the options clause.