Skip to main content

External Table

Configuration can be provided to Snowflake Object Lifecycle Engine for the following operation with External Table:

  • Manage Lifecycle of new and existing External Table
  • Manage Grants of External Table

Supported Parameters

The engine supports the parameters listed below.

  • FILE_FORMAT: Specifies the file format for the external table.
    • REQUIRED
    • Configuration key: file_format
    • Data Type: Map . See here for file_format parameters.
      File Format Options

      See Snowflake Docs section for file format .

  • LOCATION: Specifies a location for the external table.
    • REQUIRED
    • Configuration key: location
    • Data Type: String/Map. See here for definition of Location
  • COLUMNS: Definitions of a column to create in the external table. Minimum one required.
    • REQUIRED
    • Configuration key: columns
    • Data Type: Object. See here for definition of Column
  • AWS_SNS_TOPIC: Specifies the aws sns topic for the external table.
    • Configuration key: aws_sns_topic
    • Data Type: String
  • PARTITION_BY: Specifies any partition columns to evaluate for the external table.
    • Configuration key: partition_by
    • Data Type: List of Column names
  • REFRESH_ON_CREATE: Specifies whether to refresh when an external table is created.
    • Configuration key: refresh_on_create
    • Data Type: Boolean
  • AUTO_REFRESH: Specifies whether to automatically refresh the external table metadata once, immediately after the external table is created.
    • Configuration key: auto_refresh
    • Data Type: Boolean
  • COPY_GRANTS: Specifies to retain the access permissions from the original table when an external table is recreated using the CREATE OR REPLACE TABLE variant.
    • Configuration key: copy_grants
    • Data Type: Boolean
  • OWNER: Name of the role that owns the external table.
    • Configuration key: owner
    • Data Type: String
  • COMMENT: Specifies a comment for the external table.
    • Configuration key: comment
    • Data Type: String
  • PATTERN: A regular expression pattern string.
    • Configuration key: pattern
    • Data Type: String
  • MANAGE_MODE: Configures what properties to manage for the external table.
    • Configuration key: manage_mode
    • Data Type: String
    • Possible Values:
      • none
      • grants
      • all(Default)
  • GRANTS: List of Privileges and Roles to which privileges are granted to on the current external table.
    • Configuration key: grants
    • Data Type: Map

File format

One can configure file_format options for external table and the configurations it accepts can be found here. Regardless of the configurations, some parameters are specific to external table:

  • The type configuration key is format_type and it accepts following values:
    • CSV
    • JSON
    • AVRO
    • ORC
    • PARQUET
  • If specified format_type is ORC then compression parameter is not supported.
  • For format_type: PARQUET, below are possible values for compression parameter:
    • AUTO
    • SNAPPY
    • NONE

Summary of supported parameters by file format

  • If format_type is CSV
    • compression
    • record_delimiter
    • field_delimiter
    • skip_header
    • skip_blank_lines
  • If format_type is JSON
    • compression
  • If format_type is AVRO
    • compression
  • If format_type is ORC
    • trim_space
    • null_if
  • If format_type is PARQUET
    • compression

Examples

Specifying/referencing file format

file_format:
format_database: "SALES"
format_schema: "PUBLIC"
format_name: "CSV_FORMAT"

Specifying file format type

file_format:
format_type: "CSV"
compression: "AUTO"
field_delimiter: ","
skip_header: 1
skip_blank_lines: false

Location

In the location parameter of the external table, users can specify either just the name of the stage (if the stage belongs to the same schema and database as the external table), or the name of schema and database as well.

See Database-Level Object Reference section for more information

The Location parameter supports the following parameters if explicitly provided:

  • STAGE: Name of the Stage
    • REQUIRED
    • Configuration key: stage
    • Data Type: String
  • SCHEMA: Name of the schema, in which the stage exists
    • REQUIRED if Stage is present in another Database or another Schema than the current one
    • Configuration key: schema
    • Data Type: String
  • DATABASE: Name of the database, in which the stage exists
    • REQUIRED if Stage is present in another Database than the current one
    • Configuration key: database
    • Data Type: String
  • PATH: Path in the external stage
    • Configuration key: path
    • Data Type: String

Example

location:
database: "DUMMY_DATABASE"
schema: "PUBLIC"
stage: "LIFECYCLE_STAGE"
path: "lifecycle_runner"

Column

In the column parameter, users can specify the columns of the external table to be managed.

Multiples columns in an object format can be listed in the columns parameter with each having the following supported parameters:

  • TYPE: Data-type of the Column
    • REQUIRED
    • Configuration key: type
    • Data Type: String
  • AS: String that specifies the expression for the column. When queried, the column returns results derived from this expression.
    • REQUIRED
    • Configuration key: as
    • Data Type: String

Example

columns:
NAME:
type: "text"
as: "(value:c1::text)"
YEAR:
type: "text"
as: "(value:c2::text)"

Basic syntax

databases:
<database-name>:
schemas:
<schema-name>:
external_tables:
<external-table-name>:
<configuration-key>: <value>
grants:
<privilege>:
- <role-name>
- <role-name>

Supported External Table Grants to Roles

Following is the list of Privileges Grant to Roles that can be specified in the external table definition

  • ALL PRIVILEGES
  • SELECT
  • OWNERSHIP

Examples

databases:
SALES_RECORD:
schemas:
SALES:
external_tables:
EXTERNAL_TABLE_1:
file_format:
format_type: "CSV"
compression: "AUTO"
location:
database: "DUMMY_DATABASE"
schema: "PUBLIC"
stage: "LIFECYCLE_STAGE"
path: "lifecycle_runner"
columns:
NAME:
type: "text"
as: "(value:c1::text)"
YEAR:
type: "text"
as: "(value:c2::text)"
comment: "Test external table"
pattern: ".*[.]csv"
auto_refresh: true
copy_grants: false
refresh_on_create: true