CREATE EXTERNAL TABLE

DESCRIPTION

While Snowflake supports a limited set of functionality for external tables compared to Greenplum, data storage is generally not a concern in Snowflake. As a result, external tables can often be transformed into regular Snowflake tables. However, there is an extra effort associated with this translation because the data stored in the files of the external tables in Greenplum, must be somehow moved into the Snowflake database. When an external table is converted it will be flagged with a warning, without impacting the conversation rate. But please consider looking at these warnings in case the original application had an importance reliance on using external table data. All the external table options of the original table are commented out in the output code.

Grammar Syntax

The CREATE EXTERNAL TABLE syntax is a grammar that does not fit on just one page, but here is an overview.

CREATE [READABLE] EXTERNAL [TEMPORARY | TEMP] TABLE <table_name>     
    ( <column_name> <data_type> [, ...] | LIKE <other_table >)
     LOCATION ('file://<seghost>[:<port>]/<path>/<file>' [, ...])
       | ('gpfdist://<filehost>[:<port>]/<file_pattern>[#transform=<trans_name>]'
           [, ...]
       | ('gpfdists://<filehost>[:<port>]/<file_pattern>[#transform=<trans_name>]'
           [, ...])
       | ('pxf://<path-to-data>?PROFILE=<profile_name>[&SERVER=<server_name>][&<custom-option>=<value>[...]]'))
       | ('s3://<S3_endpoint>[:<port>]/<bucket_name>/[<S3_prefix>] [region=<S3-region>] [config=<config_file> | config_server=<url>]')
     [ON MASTER]
     FORMAT 'TEXT' 
           [( [HEADER]
              [DELIMITER [AS] '<delimiter>' | 'OFF']
              [NULL [AS] '<null string>']
              [ESCAPE [AS] '<escape>' | 'OFF']
              [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
              [FILL MISSING FIELDS] )]
          | 'CSV'
           [( [HEADER]
              [QUOTE [AS] '<quote>'] 
              [DELIMITER [AS] '<delimiter>']
              [NULL [AS] '<null string>']
              [FORCE NOT NULL <column> [, ...]]
              [ESCAPE [AS] '<escape>']
              [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
              [FILL MISSING FIELDS] )]
          | 'CUSTOM' (Formatter=<<formatter_specifications>>)
    [ OPTIONS ( <key> '<value>' [, ...] ) ]
    [ ENCODING '<encoding>' ]
      [ [LOG ERRORS [PERSISTENTLY]] SEGMENT REJECT LIMIT <count>
      [ROWS | PERCENT] ]

Click here to go to the Greenplum specification for this syntax. Navigate to the following pages to get more details about the translation spec for CREATE EXTERNAL TABLE syntax.

Sample Source Patterns

Greenplum

CREATE EXTERNAL TABLE TABLE01 (
  COL1 INT
) LOCATION (
    'location.com'
)
ON MASTER
FORMAT 'text'
LOG ERRORS PERSISTENTLY SEGMENT REJECT LIMIT 100000 ROWS;

Snowflake

/*** MSC-WARNING - MSC-GP0001 - EXTERNAL TABLE TRANSLATED TO REGULAR TABLE ***/  
CREATE TABLE   PUBLIC.TABLE01 (
   COL1 INT
 )
-- ** MSC-WARNING - MSC-GP0000 - EXTERNAL TABLE LOCATION NOT SUPPORTED. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE **
--   LOCATION (
--     'location.com'
-- )
-- ** MSC-WARNING - MSC-GP0000 - EXTERNAL TABLE ON OPTION NOT SUPPORTED. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE **
-- ON MASTER
-- ** MSC-WARNING - MSC-GP0000 - EXTERNAL TABLE FORMAT NOT SUPPORTED. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE **
-- FORMAT 'text'
-- ** MSC-WARNING - MSC-GP0000 - EXTERNAL TABLE SEGMENT REJECT NOT SUPPORTED. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE **
-- LOG ERRORS PERSISTENTLY SEGMENT REJECT LIMIT 100000 ROWS
                                                         ;

Related EWIs

MSC-GP0000: Data storage is automatically handled by Snowflake.

MSC-GP0001: External table translated to regular table.