CREATE VIEW

In this section, you will find information about BigQuery Create Views and their Snowflake equivalent.

Grammar Syntax

CREATE [ OR REPLACE ] VIEW [ IF NOT EXISTS ] view_name
[(view_column_name_list)]
[OPTIONS(view_option_list)]
AS query_expression

view_column_name_list :=
  view_column[, ...]

view_column :=
  column_name [OPTIONS(view_column_option_list)]

Click here to go to the BigQuery specification for this syntax.

Simple case

BigQuery

CREATE OR REPLACE VIEW View1
AS SELECT * from table1;

Snowflake

CREATE OR REPLACE VIEW View1
AS
SELECT * from
  table1;

With View Column Name Option

The 'view column name' option is employed to append metadata to a column name. In BigQuery, the 'description' option is used for attaching metadata to a column name, whereas in Snowflake, the 'comment' option serves the same purpose.

BigQuery

CREATE OR REPLACE VIEW view2 (
    column1 OPTIONS(description="a unique id"),
    column2 OPTIONS(description="person name")
)
AS
SELECT column1, column2
FROM table1;

Snowflake

CREATE OR REPLACE VIEW view2
(
       column1 COMMENT 'a unique id',
       column2 COMMENT 'person name'
   )
AS
SELECT column1, column2
FROM
       table1;

With Options clause

The option clause will be commented because it is not compatible with Snowflake except for the description and labels options that can be transformed.

BigQuery

CREATE OR REPLACE VIEW view5 
OPTIONS(
    expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC",
    friendly_name="my_view"
)
AS
SELECT column1, column2
FROM table1;

Snowflake

CREATE OR REPLACE VIEW view5
----** MSC-ERROR - MSC-BQ0004 - THE OPTIONS CLAUSE WITHIN VIEW IS NOT SUPPORTED IN SNOWFLAKE WHEN IT CONTAINS THE FOLLOWING OPTIONS EXPIRATION_TIMESTAMP, FRIENDLY_NAME **
--OPTIONS(
--    expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC",
--    friendly_name="my_view"
--)
AS
SELECT column1, column2
FROM
    table1;

Description Option

The description option is employed to append metadata to a view, whereas in Snowflake, the 'comment' option serves the same purpose.

BigQuery

CREATE OR REPLACE VIEW view3 
OPTIONS(
    description='Events view'
)
AS
SELECT column1, column2
FROM table1;

Snowflake

CREATE OR REPLACE VIEW view3
COMMENT = 'Events view'
AS
SELECT column1, column2
FROM
    table1;

Labels Option

In BigQuery the labels associated with a view can be used to organize and group tables in the database administrative environment, in Snowflake the Tags can be used for the same functionality. But to ensure that the tag exists, SnowConvert will add the corresponding CREATE TAG before the CREATE VIEW if it contains labels.

BigQuery

CREATE OR REPLACE VIEW view4 
OPTIONS(
    labels=[("label1", "value1"), ("label2", "value2")]
)
AS
SELECT column1, column2
FROM table1;

Snowflake

CREATE OR REPLACE TAG "label1";
CREATE OR REPLACE TAG "label2";

CREATE OR REPLACE VIEW view4
WITH TAG( "label1" = "value1","label2" = "value2" )
AS
SELECT column1, column2
FROM
    table1

Related EWIs

1. MSC-BQ0004: The OPTIONS clause within View is not supported in Snowflake when it contains the following options.