CREATE EXTERNAL TABLE

DESCRIPTION

Snowflake supports CREATE EXTERNAL TABLE but with a more limited number of features, but as data storage is generally not a major concern. It is often feasible to convert external tables to normal Snowflake tables. However, this process requires additional effort because data stored in external BigQuery tables must be transferred to the Snowflake database.

Grammar Syntax

CREATE [ OR REPLACE ] EXTERNAL TABLE [ IF NOT EXISTS ] table_name
[(
  column_name column_schema,
  ...
)]
[WITH CONNECTION connection_name]
[WITH PARTITION COLUMNS
  [(
      partition_column_name partition_column_type,
      ...
  )]
]
OPTIONS (
  external_table_option_list,
  ...
);

Click here to go to the specification for this syntax.

Basic

Sample InputCode

CREATE OR REPLACE EXTERNAL TABLE external_table_test
(
  col1 INTEGER,
  col2 STRING,
  col2 STRING
)
OPTIONS (
  format = 'CSV',
  field_delimiter = ',',
  uris = ['gs://my_bucket/file.csv']
);

Snowflake OutputCode

--** MSC-INFORMATION - MSCINF0058 - EXTERNAL TABLE TRANSLATED TO REGULAR TABLE **
CREATE OR REPLACE TABLE external_table_test (
  col1 INTEGER,
  col2 STRING,
  col3 STRING
)
--OPTIONS(
--  format = 'CSV',
--  field_delimiter = ',',
--  uris = ['gs://my_bucket/file.csv']
--)
 ;                                                      ;

WITH CONNECTION

Sample InputCode

CREATE EXTERNAL TABLE external_table_test
WITH CONNECTION 'test/myconnection'
OPTIONS (
  format = 'CSV'
);

Snowflake OutputCode

--** MSC-INFORMATION - MSCINF0058 - EXTERNAL TABLE TRANSLATED TO REGULAR TABLE **
--** MSC-ERROR - MSC-BQ0014 - THE RESULTING TABLE HAS NO COLUMNS **
CREATE TABLE `external_table_test (
)
--OPTIONS (
--  format = 'CSV'
--)
 ;

PARTITION COLUMNS

WITH COLUMNS NAMES

Sample InputCode

CREATE OR REPLACE EXTERNAL TABLE external_table_test
(
  col1 INTEGER,
  col2 STRING,
  col3 STRING
)
WITH PARTITION COLUMNS (
  col4 INTEGER
)
OPTIONS (
  format = 'CSV',
  field_delimiter = ',',
  uris = ['gs://my_bucket/file.csv']
);

Snowflake OutputCode

--** MSC-INFORMATION - MSCINF0058 - EXTERNAL TABLE TRANSLATED TO REGULAR TABLE **
CREATE OR REPLACE TABLE external_table_test3 (
  col1 INTEGER,
  col2 STRING,
  col3 STRING,
  col4 INTEGER
)
--OPTIONS(
--  format = 'CSV',
--  field_delimiter = ',',
--  uris = ['gs://my_buchet/file.csv']
--)
 ;

WITHOUT COLUMNS NAMES

Sample InputCode

CREATE OR REPLACE EXTERNAL TABLE external_table_test
WITH PARTITION COLUMNS
OPTIONS (
  format = 'CSV',
  field_delimiter = ',',
  uris = ['gs://my_bucket/file.csv']
);

Snowflake OutputCode

--** MSC-INFORMATION - MSCINF0058 - EXTERNAL TABLE TRANSLATED TO REGULAR TABLE **
--** MSC-ERROR - MSC-BQ0014 - THE RESULTING TABLE HAS NO COLUMNS **
CREATE OR REPLACE TABLE external_table_test (
)
--OPTIONS (
--  format = 'CSV',
--  field_delimiter = ',',
--  uris = ['gs://my_bucket/file.csv']
--)
 ;

Related EWIs

1. MSC-BQ0015: The resulting table has no columns.

2. MSCINF0058: External table translated to regular table.