A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://docs.snowflake.com/en/sql-reference/sql/create-iceberg-table-delta below:

Website Navigation


CREATE ICEBERG TABLE (Delta files in object storage)

CREATE ICEBERG TABLE (Delta files in object storage)

Creates or replaces an Apache Iceberg™ table in the current/specified schema using Delta table files in object storage (external cloud storage). This type of Iceberg table requires a catalog integration.

This topic refers to Iceberg tables as simply “tables” except where specifying Iceberg tables avoids confusion.

See also:

ALTER ICEBERG TABLE , DROP ICEBERG TABLE , SHOW ICEBERG TABLES , DESCRIBE ICEBERG TABLE , UNDROP ICEBERG TABLE

Syntax 
CREATE [ OR REPLACE ] ICEBERG TABLE [ IF NOT EXISTS ] <table_name>
  [ EXTERNAL_VOLUME = '<external_volume_name>' ]
  [ CATALOG = '<catalog_integration_name>' ]
  BASE_LOCATION = '<relative_path_from_external_volume>'
  [ REPLACE_INVALID_CHARACTERS = { TRUE | FALSE } ]
  [ AUTO_REFRESH = { TRUE | FALSE } ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ WITH CONTACT ( <purpose> = <contact_name> [ , <purpose> = <contact_name> ... ] ) ]

Copy

Required parameters
table_name

Specifies the identifier (name) for the table; must be unique for the schema in which the table is created.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (for example, "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more information, see Identifier requirements.

BASE_LOCATION = 'relative_path_from_external_volume'

Specifies a relative path from the table’s EXTERNAL_VOLUME location to a directory where Snowflake can access your Delta table files.

The base location must point to a directory and cannot point to a single file. It must contain the Delta transaction log subfolder (for example, my/base/location/_delta_log/).

Optional parameters
EXTERNAL_VOLUME = 'external_volume_name'

Specifies the identifier (name) for the external volume where the Iceberg table stores its metadata files and data in Parquet format. Iceberg metadata and manifest files store the table schema, partitions, snapshots, and other metadata.

If you don’t specify this parameter, the Iceberg table defaults to the external volume for the schema, database, or account. The schema takes precedence over the database, and the database takes precedence over the account.

CATALOG = 'catalog_integration_name'

Specifies the identifier (name) of the catalog integration for this table.

If not specified, the Iceberg table defaults to the catalog integration for the schema, database, or account. The schema takes precedence over the database, and the database takes precedence over the account.

REPLACE_INVALID_CHARACTERS = { TRUE | FALSE }

Specifies whether to replace invalid UTF-8 characters with the Unicode replacement character (�) in query results. You can only set this parameter for tables that use an external Iceberg catalog.

If not specified, the Iceberg table defaults to the parameter value for the schema, database, or account. The schema takes precedence over the database, and the database takes precedence over the account.

Default: FALSE

AUTO_REFRESH = { TRUE | FALSE }

Specifies whether Snowflake should automatically poll your external cloud storage for updates.

If no value is specified for the REFRESH_INTERVAL_SECONDS parameter on the catalog integration, Snowflake uses a default refresh interval of 30 seconds.

For more information, see automated refresh.

Default: FALSE

Note

Using AUTO_REFRESH with INFER_SCHEMA isn’t supported.

COMMENT = 'string_literal'

Specifies a comment for the table.

Default: No value

TAG ( tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ] )

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quota for objects.

WITH CONTACT ( purpose = contact [ , purpose = contact ...] )

Associate the new object with one or more contacts.

Access control requirements

A role used to execute this operation must have the following privileges at a minimum:

Privilege

Object

Notes

CREATE ICEBERG TABLE

Schema

CREATE EXTERNAL VOLUME

Account

Required to create a new external volume.

USAGE

External Volume

Required to reference an existing external volume.

CREATE INTEGRATION

Account

Required to create a new catalog integration.

USAGE

Catalog integration

Required to reference an existing catalog integration.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes Examples

The following example command creates an Iceberg table from Delta table files in object storage with automated refresh.

The example specifies an external volume associated with the cloud location of the Delta table files, a catalog integration configured for Delta, and a value for the required BASE_LOCATION parameter.

CREATE ICEBERG TABLE my_delta_iceberg_table
  CATALOG = delta_catalog_integration
  EXTERNAL_VOLUME = delta_external_volume
  BASE_LOCATION = 'relative/path/from/ext/vol/'
  AUTO_REFRESH = TRUE;

Copy

If the Delta table uses a partitioning scheme, Snowflake automatically interprets the scheme from the Delta log.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4