CREATE TABLE¶

Creates a new table in the current/specified schema or replaces an existing table. A table can have multiple columns, with each column definition consisting of a name, data type, and optionally whether the column:

  • Requires a value (NOT NULL).

  • Has a default value.

  • Has any referential integrity constraints (primary key, foreign key, etc.).

In addition, this command supports the following variants:

  • CREATE TABLE … AS SELECT (creates a populated table; also referred to as CTAS)

  • CREATE TABLE … USING TEMPLATE (creates a table with the column definitions derived from a set of staged files)

  • CREATE TABLE … LIKE (creates an empty copy of an existing table)

  • CREATE TABLE … CLONE (creates a clone of an existing table)

See also:

ALTER TABLE , DROP TABLE , SHOW TABLES , DESCRIBE TABLE

Syntax¶

CREATE [ OR REPLACE ]
    [ { [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | VOLATILE | TRANSIENT } ]
  TABLE [ IF NOT EXISTS ] <table_name> (
    -- Column definition
    <col_name> <col_type>
      [ inlineConstraint ]
      [ NOT NULL ]
      [ COLLATE '<collation_specification>' ]
      [
        {
          DEFAULT <expr>
          | { AUTOINCREMENT | IDENTITY }
            [
              {
                ( <start_num> , <step_num> )
                | START <num> INCREMENT <num>
              }
            ]
            [ { ORDER | NOORDER } ]
        }
      ]
      [ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col_name> , <cond_col1> , ... ) ] ]
      [ [ WITH ] PROJECTION POLICY <policy_name> ]
      [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
      [ COMMENT '<string_literal>' ]

    -- Additional column definitions
    [ , <col_name> <col_type> [ ... ] ]

    -- Out-of-line constraints
    [ , outoflineConstraint [ ... ] ]
  )
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ ENABLE_SCHEMA_EVOLUTION = { TRUE | FALSE } ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ CHANGE_TRACKING = { TRUE | FALSE } ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
  [ [ WITH ] AGGREGATION POLICY <policy_name> ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
Copy

Where:

inlineConstraint ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE
    | PRIMARY KEY
    | [ FOREIGN KEY ] REFERENCES <ref_table_name> [ ( <ref_col_name> ) ]
  }
  [ <constraint_properties> ]
Copy

For additional inline constraint details, see CREATE | ALTER TABLE … CONSTRAINT.

outoflineConstraint ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE [ ( <col_name> [ , <col_name> , ... ] ) ]
    | PRIMARY KEY [ ( <col_name> [ , <col_name> , ... ] ) ]
    | [ FOREIGN KEY ] [ ( <col_name> [ , <col_name> , ... ] ) ]
      REFERENCES <ref_table_name> [ ( <ref_col_name> [ , <ref_col_name> , ... ] ) ]
  }
  [ <constraint_properties> ]
Copy

For additional out-of-line constraint details, see CREATE | ALTER TABLE … CONSTRAINT.

Variant Syntax¶

CREATE TABLE … AS SELECT (also referred to as CTAS)¶

Creates a new table populated with the data returned by a query:

CREATE [ OR REPLACE ] TABLE <table_name> [ ( <col_name> [ <col_type> ] , <col_name> [ <col_type> ] , ... ) ]
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ COPY GRANTS ]
  AS SELECT <query>
  [ ... ]
Copy

A masking policy can be applied to a column in a CTAS statement. Specify the masking policy after the column data type. Similarly, a row access policy can be applied to the table. For example:

CREATE TABLE <table_name> ( <col1> <data_type> [ WITH ] MASKING POLICY <policy_name> [ , ... ] )
  ...
  [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col1> [ , ... ] )
  AS SELECT <query>
  [ ... ]
Copy

Note

In a CTAS, the COPY GRANTS clause is valid only when combined with the OR REPLACE clause. COPY GRANTS copies permissions from the table being replaced with CREATE OR REPLACE (if it already exists), not from the source table(s) being queried in the SELECT statement. CTAS with COPY GRANTS allows you to overwrite a table with a new set of data while keeping existing grants on that table.

For more details about COPY GRANTS, see COPY GRANTS in this document.

CREATE TABLE … USING TEMPLATE¶

Creates a new table with the column definitions derived from a set of staged files using the INFER_SCHEMA function. This feature supports Apache Parquet, Apache Avro, ORC, JSON, and CSV files.

CREATE [ OR REPLACE ] TABLE <table_name>
  [ COPY GRANTS ]
  USING TEMPLATE <query>
  [ ... ]
Copy

Note

If the statement is replacing an existing table of the same name, then the grants are copied from the table being replaced. If there is no existing table of that name, then the grants are copied from the source table being cloned.

For more details about COPY GRANTS, see COPY GRANTS in this document.

CREATE TABLE … LIKE¶

Creates a new table with the same column definitions as an existing table, but without copying data from the existing table. Column names, types, defaults, and constraints are copied to the new table:

CREATE [ OR REPLACE ] TABLE <table_name> LIKE <source_table>
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ COPY GRANTS ]
  [ ... ]
Copy

For more details about COPY GRANTS, see COPY GRANTS in this document.

Note

CREATE TABLE … LIKE for a table with an auto-increment sequence accessed through a data share is currently not supported.

CREATE TABLE … CLONE¶

Creates a new table with the same column definitions and containing all the existing data from the source table, without actually copying the data. This variant can also be used to clone a table at a specific time/point in the past (using Time Travel):

CREATE [ OR REPLACE ] TABLE <name> CLONE <source_table>
  [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]
  [ COPY GRANTS ]
  [ ... ]
Copy

Note

If the statement is replacing an existing table of the same name, then the grants are copied from the table being replaced. If there is no existing table of that name, then the grants are copied from the source table being cloned.

For more details about COPY GRANTS, see COPY GRANTS in this document.

For more details about cloning, see CREATE <object> … CLONE.

Required Parameters¶

name

Specifies the identifier (i.e. 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 (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier requirements.

col_name

Specifies the column identifier (i.e. name). All the requirements for table identifiers also apply to column identifiers.

For more details, see Identifier requirements and Reserved & Limited Keywords.

Note

In addition to the standard reserved keywords, the following keywords cannot be used as column identifiers because they are reserved for ANSI-standard context functions:

  • CURRENT_DATE

  • CURRENT_ROLE

  • CURRENT_TIME

  • CURRENT_TIMESTAMP

  • CURRENT_USER

For the list of reserved keywords, see Reserved & Limited Keywords.

col_type

Specifies the data type for the column.

For details about the data types that can be specified for table columns, see SQL Data Types Reference.

query
Required if using USING TEMPLATE

Subquery that calls the INFER_SCHEMA function and formats the output as an array.

Alternatively, USING TEMPLATE accepts the INFER_SCHEMA output as a string literal or variable.

Optional Parameters¶

{ [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | VOLATILE | TRANSIENT }

Specifies that the table persists only for the duration of the session that you created it in. A temporary table and all its contents are dropped at the end of the session.

The synonyms and abbreviations for TEMPORARY (e.g. GLOBAL TEMPORARY) are provided for compatibility with other databases (e.g. to prevent errors when migrating CREATE TABLE statements). Tables created with any of these keywords appear and behave identically to a table created with the TEMPORARY keyword.

Default: No value. If a table is not declared as TEMPORARY or TRANSIENT, the table is permanent.

If you want to avoid unexpected conflicts, avoid naming temporary tables after tables that already exist in the schema.

If you created a temporary table with the same name as another table in the schema, all queries and operations used on the table only affect the temporary table in the session, until you drop the temporary table. If you drop the table, you drop the temporary table, and not the table that already exists in the schema.

For information about temporary or transient tables, and how they can affect storage and cost, refer to the following resources:

TRANSIENT

Specifies that the table is transient.

Like a permanent table, a transient table exists until explicitly dropped and is visible to any user with the appropriate privileges. However, transient tables have a lower level of data protection than permanent tables, meaning that data in a transient table might be lost in the event of a system failure. As such, transient tables should only be used for data that can be recreated externally to Snowflake.

Default: No value. If a table is not declared as TRANSIENT or TEMPORARY, the table is permanent.

Note

Transient tables have some storage considerations.

For more information about these and other considerations when deciding whether to create temporary or transient tables, see Working with Temporary and Transient Tables and Storage Costs for Time Travel and Fail-safe.

CONSTRAINT ...

Defines an inline or out-of-line constraint for the specified column(s) in the table.

For syntax details, see CREATE | ALTER TABLE … CONSTRAINT. For more information about constraints, see Constraints.

COLLATE 'collation_specification'

Specifies the collation to use for column operations such as string comparison. This option applies only to text columns (VARCHAR, STRING, TEXT, etc.). For more details, see Collation Specifications.

DEFAULT ... or . AUTOINCREMENT ...

Specifies whether a default value is automatically inserted in the column if a value is not explicitly specified via an INSERT or CREATE TABLE AS SELECT statement:

DEFAULT expr

Column default value is defined by the specified expression which can be any of the following:

  • Constant value.

  • Sequence reference (seq_name.NEXTVAL).

  • Simple expression that returns a scalar value.

    The simple expression can include a SQL UDF (user-defined function) if the UDF is not a secure UDF.

    Note

    If a default expression refers to a SQL UDF, then the function is replaced by its definition at table creation time. If the user-defined function is redefined in the future, this does not update the column’s default expression.

    The simple expression cannot contain references to:

    • Subqueries.

    • Aggregates.

    • Window functions.

    • Secure UDFs.

    • UDFs written in languages other than SQL (e.g. Java, JavaScript).

    • External functions.

{ AUTOINCREMENT | IDENTITY } . [ { ( start_num , step_num ) | START num INCREMENT num } ] . [ { ORDER | NOORDER } ]

When you specify AUTOINCREMENT or IDENTITY, the default value for the column starts with a specified number and each successive value automatically increments by the specified amount.

AUTOINCREMENT and IDENTITY are synonymous and can be used only for columns with numeric data types, such as NUMBER, INT, FLOAT.

Caution

Snowflake uses a sequence to generate the values for an auto-incremented column. Sequences have limitations; see Sequence Semantics.

The default value for both the start value and the step/increment value is 1.

Use ORDER or NOORDER to specify whether or not the values are generated for the auto-incremented column in increasing or decreasing order.

  • ORDER specifies that the values generated for a sequence or auto-incremented column are in increasing order (or, if the interval is a negative value, in decreasing order).

    For example, if a sequence or auto-incremented column has START 1 INCREMENT 2, the generated values might be 1, 3, 5, 7, 9, etc.

  • NOORDER specifies that the values are not guaranteed to be in increasing order.

    For example, if a sequence has START 1 INCREMENT 2, the generated values might be 1, 3, 101, 5, 103, etc.

    NOORDER can improve performance when multiple insert operations need to be performed concurrently (for example, when multiple clients are executing multiple INSERT statements).

If you do not specify ORDER or NOORDER, the NOORDER_SEQUENCE_AS_DEFAULT parameter determines which property is set.

Note

DEFAULT and AUTOINCREMENT are mutually exclusive; only one can be specified for a column.

MASKING POLICY = policy_name

Specifies the masking policy to set on a column.

PROJECTION POLICY policy_name

Specifies the projection policy to set on a column.

COMMENT 'string_literal'

Specifies a comment for the column.

(Note that comments can be specified at the column level or the table level. The syntax for each is slightly different.)

USING ( col_name , cond_col_1 ... )

Specifies the arguments to pass into the conditional masking policy SQL expression.

The first column in the list specifies the column for the policy conditions to mask or tokenize the data and must match the column to which the masking policy is set.

The additional columns specify the columns to evaluate to determine whether to mask or tokenize the data in each row of the query result when a query is made on the first column.

If the USING clause is omitted, Snowflake treats the conditional masking policy as a normal masking policy.

CLUSTER BY ( expr [ , expr , ... ] )

Specifies one or more columns or column expressions in the table as the clustering key. For more details, see Clustering Keys & Clustered Tables.

Default: No value (no clustering key is defined for the table)

Important

Clustering keys are not intended or recommended for all tables; they typically benefit very large (i.e. multi-terabyte) tables.

Before you specify a clustering key for a table, you should understand micro-partitions. For more information, see Understanding Snowflake Table Structures.

ENABLE_SCHEMA_EVOLUTION = { TRUE | FALSE }

Enables or disables automatic changes to the table schema from data loaded into the table from source files, including:

  • Added columns.

    By default, schema evolution is limited to a maximum of 10 added columns per load operation. To request more than 10 added columns per load operation, contact Snowflake Support.

  • The NOT NULL constraint can be dropped from any number of columns missing in new data files.

Setting it to TRUE enables automatic table schema evolution. The default FALSE disables automatic table schema evolution.

Note

Loading data from files evolves the table columns when all of the following are true:

  • The COPY INTO <table> statement includes the MATCH_BY_COLUMN_NAME option.

  • The role used to load the data has the EVOLVE SCHEMA or OWNERSHIP privilege on the table.

Additionally, for schema evolution with CSV, when used with MATCH_BY_COLUMN_NAME and PARSE_HEADER, ERROR_ON_COLUMN_COUNT_MISMATCH must be set to false.

DATA_RETENTION_TIME_IN_DAYS = integer

Specifies the retention period for the table so that Time Travel actions (SELECT, CLONE, UNDROP) can be performed on historical data in the table. For more details, see Understanding & Using Time Travel and Working with Temporary and Transient Tables.

For a detailed description of this object-level parameter, as well as more information about object parameters, see Parameters.

Values:

  • Standard Edition: 0 or 1

  • Enterprise Edition:

    • 0 to 90 for permanent tables

    • 0 or 1 for temporary and transient tables

Default:

  • Standard Edition: 1

  • Enterprise Edition (or higher): 1 (unless a different default value was specified at the schema, database, or account level)

Note

A value of 0 effectively disables Time Travel for the table.

MAX_DATA_EXTENSION_TIME_IN_DAYS = integer

Object parameter that specifies the maximum number of days for which Snowflake can extend the data retention period for the table to prevent streams on the table from becoming stale.

For a detailed description of this parameter, see MAX_DATA_EXTENSION_TIME_IN_DAYS.

CHANGE_TRACKING = { TRUE | FALSE }

Specifies whether to enable change tracking on the table.

  • TRUE enables change tracking on the table. This setting adds a pair of hidden columns to the source table and begins storing change tracking metadata in the columns. These columns consume a small amount of storage.

    The change tracking metadata can be queried using the CHANGES clause for SELECT statements, or by creating and querying one or more streams on the table.

  • FALSE does not enable change tracking on the table.

Default: FALSE

DEFAULT_DDL_COLLATION = 'collation_specification'

Specifies a default collation specification for the columns in the table, including columns added to the table in the future.

For more details about the parameter, see DEFAULT_DDL_COLLATION.

COPY GRANTS

Specifies to retain the access privileges from the original table when a new table is created using any of the following CREATE TABLE variants:

  • CREATE OR REPLACE TABLE

  • CREATE TABLE … LIKE

  • CREATE TABLE … CLONE

The parameter copies all privileges, except OWNERSHIP, from the existing table to the new table. The new table does not inherit any future grants defined for the object type in the schema. By default, the role that executes the CREATE TABLE statement owns the new table.

If the parameter is not included in the CREATE TABLE statement, then the new table does not inherit any explicit access privileges granted on the original table, but does inherit any future grants defined for the object type in the schema.

Note:

  • With data sharing:

    • If the existing table was shared to another account, the replacement table is also shared.

    • If the existing table was shared with your account as a data consumer, and access was further granted to other roles in the account (using GRANT IMPORTED PRIVILEGES on the parent database), access is also granted to the replacement table.

  • The SHOW GRANTS output for the replacement table lists the grantee for the copied privileges as the role that executed the CREATE TABLE statement, with the current timestamp when the statement was executed.

  • The operation to copy grants occurs atomically in the CREATE TABLE command (i.e. within the same transaction).

COMMENT = 'string_literal'

Specifies a comment for the table.

Default: No value

(Note that comments can be specified at the column level or the table level. The syntax for each is slightly different.)

ROW ACCESS POLICY policy_name ON ( col_name [ , col_name ... ] )

Specifies the row access policy to set on a table.

AGGREGATION POLICY policy_name

Specifies the aggregation policy to set on a table.

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 quotas for objects and columns.

Note

Do not specify file format options or copy options using the CREATE STAGE or CREATE TABLE commands. It is recommended that you use the COPY INTO <table> command to specify file format options or copy options.

Access Control Requirements¶

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

Privilege

Object

Notes

CREATE TABLE

Schema

Note that creating a temporary table does not require the CREATE TABLE privilege.

SELECT

Table, external table, view

Required on queried tables and/or views only when cloning a table or executing CTAS statements.

APPLY

Masking policy, row access policy, tag

Required only when applying a masking policy, row access policy, object tags, or any combination of these governance features when creating tables.

USAGE (external stage) or READ (internal stage)

Stage

Required to derive table column definitions from staged files using CREATE TABLE … USING TEMPLATE statements.

OWNERSHIP

Table

A role must be granted or inherit the OWNERSHIP privilege on the object to create a temporary object that has the same name as the object that already exists in the schema.

Note that in a managed access schema, only the schema owner (i.e. the role with the OWNERSHIP privilege on the schema) or a role with the MANAGE GRANTS privilege can grant or revoke privileges on objects in the schema, including future grants.

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and 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¶

  • A schema cannot contain tables and/or views with the same name. When creating a table:

    • If a view with the same name already exists in the schema, an error is returned and the table is not created.

    • If a table with the same name already exists in the schema, an error is returned and the table is not created, unless the optional OR REPLACE keyword is included in the command.

      Important

      Using OR REPLACE is the equivalent of using DROP TABLE on the existing table and then creating a new table with the same name; however, the dropped table is not permanently removed from the system. Instead, it is retained in Time Travel. This is important to note because dropped tables in Time Travel can be recovered, but they also contribute to data storage for your account. For more information, see Storage Costs for Time Travel and Fail-safe.

      CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.

      This means that any queries concurrent with the CREATE OR REPLACE TABLE operation use either the old or new table version.

      Recreating or swapping a table drops its change data. Any stream on the table becomes stale. In addition, any stream on a view that has this table as an underlying table, becomes stale. A stale stream is unreadable.

  • Similar to reserved keywords, ANSI-reserved function names (CURRENT_DATE, CURRENT_TIMESTAMP, etc.) cannot be used as column names.

  • CREATE TABLE … CLONE:

    If the source table has clustering keys, then the new table has clustering keys. By default, Automatic Clustering is suspended for the new table – even if Automatic Clustering was not suspended for the source table.

  • CREATE TABLE … CHANGE_TRACKING = TRUE:

    When change tracking is enabled, the table is locked for the duration of the operation. Locks can cause latency with some associated DDL/DML operations. For more information, refer to Resource Locking.

  • CREATE TABLE … LIKE:

    If the source table has clustering keys, then the new table has clustering keys. By default, Automatic Clustering is not suspended for the new table – even if Automatic Clustering was suspended for the source table.

  • CREATE TABLE … AS SELECT (CTAS):

    • If the aliases for the column names in the SELECT list are valid columns, then the column definitions are not required in the CTAS statement; if omitted, the column names and types are inferred from the underlying query:

      CREATE TABLE <table_name> AS SELECT ...
      
      Copy

      Alternatively, the names can be explicitly specified using the following syntax:

      CREATE TABLE <table_name> ( <col1_name> , <col2_name> , ... ) AS SELECT ...
      
      Copy

      The number of column names specified must match the number of SELECT list items in the query; the types of the columns are inferred from the types produced by the query.

    • When clustering keys are specified in a CTAS statement:

      • Column definitions are required and must be explicitly specified in the statement.

      • By default, Automatic Clustering is not suspended for the new table – even if Automatic Clustering is suspended for the source table.

    • If you want the table to be created with rows in a specific order, then use an ORDER BY sub-clause in the SELECT clause of the CTAS. Specifying CLUSTER BY does not cluster the data at the time that the table is created; instead, CLUSTER BY relies on automatic clustering to recluster the data over time.

      The ORDER BY sub-clause in a CREATE TABLE statement does not affect the order of the rows returned by future SELECT statements on that table. To specify the order of rows in future SELECT statements, use an ORDER BY sub-clause in those statements.

  • Inside a transaction, any DDL statement (including CREATE TEMPORARY/TRANSIENT TABLE) commits the transaction before executing the DDL statement itself. The DDL statement then runs in its own transaction. The next statement after the DDL statement starts a new transaction. Therefore, you can’t create, use, and drop a temporary or transient table within a single transaction. If you want to use a temporary or transient table inside a transaction, then create the table before the transaction, and drop the table after the transaction.

  • Recreating a table (using the optional OR REPLACE keyword) drops its history, which makes any stream on the table stale. A stale stream is unreadable.

  • A single masking policy that uses conditional columns can be applied to multiple tables provided that the column structure of the table matches the columns specified in the policy.

  • When creating a table with a masking policy on one or more table columns, or a row access policy added to the table, use the POLICY_CONTEXT function to simulate a query on the column(s) protected by a masking policy and the table protected by a row access policy.

  • Regarding metadata:

    Attention

    Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata Fields in Snowflake.

Examples¶

Create a simple table in the current database and insert a row in the table:

CREATE TABLE mytable (amount NUMBER);

+-------------------------------------+
| status                              |
|-------------------------------------|
| Table MYTABLE successfully created. |
+-------------------------------------+

INSERT INTO mytable VALUES(1);

SHOW TABLES like 'mytable';

+---------------------------------+---------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+
| created_on                      | name    | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner        | retention_time |
|---------------------------------+---------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------|
| Mon, 11 Sep 2017 16:32:28 -0700 | MYTABLE | TESTDB        | PUBLIC      | TABLE |         |            |    1 |  1024 | ACCOUNTADMIN | 1              |
+---------------------------------+---------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+

DESC TABLE mytable;

+--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name   | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| AMOUNT | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
Copy

Create a simple table and specify comments for both the table and the column in the table:

CREATE TABLE example (col1 NUMBER COMMENT 'a column comment') COMMENT='a table comment';

+-------------------------------------+
| status                              |
|-------------------------------------|
| Table EXAMPLE successfully created. |
+-------------------------------------+

SHOW TABLES LIKE 'example';

+---------------------------------+---------+---------------+-------------+-------+-----------------+------------+------+-------+--------------+----------------+
| created_on                      | name    | database_name | schema_name | kind  | comment         | cluster_by | rows | bytes | owner        | retention_time |
|---------------------------------+---------+---------------+-------------+-------+-----------------+------------+------+-------+--------------+----------------|
| Mon, 11 Sep 2017 16:35:59 -0700 | EXAMPLE | TESTDB        | PUBLIC      | TABLE | a table comment |            |    0 |     0 | ACCOUNTADMIN | 1              |
+---------------------------------+---------+---------------+-------------+-------+-----------------+------------+------+-------+--------------+----------------+

DESC TABLE example;

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+------------------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment          |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+------------------|
| COL1 | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | a column comment |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+------------------+
Copy

Create a table by selecting from an existing table:

CREATE TABLE mytable_copy (b) AS SELECT * FROM mytable;

DESC TABLE mytable_copy;

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| B    | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

CREATE TABLE mytable_copy2 AS SELECT b+1 AS c FROM mytable_copy;

DESC TABLE mytable_copy2;

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| C    | NUMBER(39,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

SELECT * FROM mytable_copy2;

+---+
| C |
|---|
| 2 |
+---+
Copy

More advanced example of creating a table by selecting from an existing table; in this example, the values in the summary_amount column in the new table are derived from two columns in the source table:

CREATE TABLE testtable_summary (name, summary_amount) AS SELECT name, amount1 + amount2 FROM testtable;
Copy

Create a table by selecting columns from a staged Parquet data file:

CREATE OR REPLACE TABLE parquet_col (
  custKey NUMBER DEFAULT NULL,
  orderDate DATE DEFAULT NULL,
  orderStatus VARCHAR(100) DEFAULT NULL,
  price VARCHAR(255)
)
AS SELECT
  $1:o_custkey::number,
  $1:o_orderdate::date,
  $1:o_orderstatus::text,
  $1:o_totalprice::text
FROM @my_stage;

+-----------------------------------------+
| status                                  |
|-----------------------------------------|
| Table PARQUET_COL successfully created. |
+-----------------------------------------+

DESC TABLE parquet_col;

+-------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name        | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|-------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| CUSTKEY     | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| ORDERDATE   | DATE         | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| ORDERSTATUS | VARCHAR(100) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| PRICE       | VARCHAR(255) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+-------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
Copy

Create a table with the same column definitions as another table, but with no rows:

CREATE TABLE mytable (amount NUMBER);

INSERT INTO mytable VALUES(1);

SELECT * FROM mytable;

+--------+
| AMOUNT |
|--------|
|      1 |
+--------+

CREATE TABLE mytable_2 LIKE mytable;

DESC TABLE mytable_2;

+--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name   | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| AMOUNT | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

SELECT * FROM mytable_2;

+--------+
| AMOUNT |
|--------|
+--------+
Copy

Create a table with a multi-column clustering key:

CREATE TABLE mytable (date TIMESTAMP_NTZ, id NUMBER, content VARIANT) CLUSTER BY (date, id);

SHOW TABLES LIKE 'mytable';

+---------------------------------+---------+---------------+-------------+-------+---------+------------------+------+-------+--------------+----------------+
| created_on                      | name    | database_name | schema_name | kind  | comment | cluster_by       | rows | bytes | owner        | retention_time |
|---------------------------------+---------+---------------+-------------+-------+---------+------------------+------+-------+--------------+----------------|
| Mon, 11 Sep 2017 16:20:41 -0700 | MYTABLE | TESTDB        | PUBLIC      | TABLE |         | LINEAR(DATE, ID) |    0 |     0 | ACCOUNTADMIN | 1              |
+---------------------------------+---------+---------------+-------------+-------+---------+------------------+------+-------+--------------+----------------+
Copy

Specify collation for columns in a table:

CREATE TABLE collation_demo (
  uncollated_phrase VARCHAR, 
  utf8_phrase VARCHAR COLLATE 'utf8',
  english_phrase VARCHAR COLLATE 'en',
  spanish_phrase VARCHAR COLLATE 'sp'
  );

INSERT INTO collation_demo (uncollated_phrase, utf8_phrase, english_phrase, spanish_phrase) 
   VALUES ('pinata', 'pinata', 'pinata', 'piñata');
Copy

Create a table where the column definitions are derived from a set of staged files that contain Avro, Parquet, or ORC data.

Note that the mystage stage and my_parquet_format file format referenced in the statement must already exist. A set of files must already be staged in the cloud storage location referenced in the stage definition.

The following example creates a table using the detected schema from staged files and sorts the columns by order_id. It builds on an example in the INFER_SCHEMA topic.

CREATE TABLE mytable
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    WITHIN GROUP (ORDER BY order_id)
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@mystage',
          FILE_FORMAT=>'my_parquet_format'
        )
      ));
Copy

Note that sorting the columns by order_id only applies if all staged files share a single schema. If the set of staged data files includes multiple schemas with shared column names, the order represented in the order_id column might not match any single file.

Note

Using * for ARRAY_AGG(OBJECT_CONSTRUCT()) may result in an error if the returned result is larger than 16MB. It is recommended to avoid using * for larger result sets, and only use the required columns, COLUMN NAME, TYPE, and NULLABLE, for the query. Optional column ORDER_ID can be included when using WITHIN GROUP (ORDER BY order_id).

Create a temporary table that is dropped automatically at the end of the session:

CREATE TEMPORARY TABLE demo_temporary (i INTEGER);
CREATE TEMP TABLE demo_temp (i INTEGER);
Copy

For compatibility with other vendors, Snowflake also supports using the keywords below as synonyms for TEMPORARY:

CREATE LOCAL TEMPORARY TABLE demo_local_temporary (i INTEGER);
CREATE LOCAL TEMP TABLE demo_local_temp (i INTEGER);

CREATE GLOBAL TEMPORARY TABLE demo_global_temporary (i INTEGER);
CREATE GLOBAL TEMP TABLE demo_global_temp (i INTEGER);

CREATE VOLATILE TABLE demo_volatile (i INTEGER);
Copy