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
Copy 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
Copy 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
Copy --** 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
Copy CREATE EXTERNAL TABLE external_table_test
WITH CONNECTION 'test/myconnection'
OPTIONS (
format = 'CSV'
);
Snowflake OutputCode
Copy --** 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
Copy 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
Copy --** 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
Copy CREATE OR REPLACE EXTERNAL TABLE external_table_test
WITH PARTITION COLUMNS
OPTIONS (
format = 'CSV' ,
field_delimiter = ',' ,
uris = ['gs://my_bucket/file.csv']
);
Snowflake OutputCode
Copy --** 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
Last updated 5 months ago