CREATE HYBRID TABLE¶

Syntax¶

CREATE [ OR REPLACE ] HYBRID TABLE [ IF NOT EXISTS ] <table_name>
  ( <col_name> <col_type>
    [
      {
        DEFAULT <expr>
        | { AUTOINCREMENT | IDENTITY }
          [
            {
              ( <start_num> , <step_num> )
              | START <num> INCREMENT <num>
            }
          ]
          [ { ORDER | NOORDER } ]
      }
    ]
    [ NOT NULL ]
    [ inlineConstraint ]
    [ , <col_name> <col_type> [ ... ] ]
    [ , outoflineConstraint ]
    [ , outoflineIndex ]
    [ , ... ]
  )
  [ COMMENT = '<string_literal>' ]

Where:

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

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> ]

outoflineIndex ::=
  INDEX <index_name> ( <col_name> [ , <col_name> , ... ] )
    [ INCLUDE ( <col_name> [ , <col_name> , ... ] ) ]

Copy

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

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 (for example, "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more information, 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.

PRIMARY KEY ( col_name [ , col_name , ... ] )

Specifies the required primary key constraint for the table, either within a column definition (inline) or separately (out-of-line). See also Constraints for hybrid tables.

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

Optional parameters¶

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 HYBRID TABLE AS SELECT statement:

DEFAULT expr

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

• Constant value.

• Simple expression.

• Sequence reference (seq_name.NEXTVAL).

A simple expression is an expression that returns a scalar value; however, the expression cannot contain references to:

• Subqueries.

• Aggregates.

• Window functions.

• External functions.

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

When AUTOINCREMENT is used, the default value for the column starts with a specified number and each successive value is automatically generated. Values generated by an AUTOINCREMENT column are guaranteed to be unique. The difference between any pair of the generated values is guaranteed to be a multiple of the increment amount.

The optional ORDER and NOORDER parameters specify whether or not the generated values provide ordering guarantees as specified in Sequence Semantics. NOORDER is the default option for AUTOINCREMENT columns on hybrid tables. NOORDER typically provides significantly better performance for point writes.

These parameters can only be used for columns with numeric data types (NUMBER, INT, FLOAT, etc.)

AUTOINCREMENT and IDENTITY are synonymous. If either is specified for a column, Snowflake utilizes a sequence to generate the values for the column. For more information about sequences, see Using Sequences.

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

Default: No value (the column has no default value)

Note

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

• For performance-sensitive workloads, NOORDER is the recommended option for AUTOINCREMENT columns.

CONSTRAINT ...

Defines an inline or out-of-line constraint for the specified column(s) in the table. Unique and foreign key constraints are optional for hybrid table columns. See also Constraints for hybrid tables.

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

INDEX index_name ( col_name [ , col_name , ... ]

Specifies a secondary index on one or more columns in the table. (When you define constraints on hybrid table columns, indexes are automatically created on those columns.)

Indexes cannot be defined on semi-structured columns (VARIANT, OBJECT, ARRAY) because of space constraints associated with the underlying storage engines for the key of each record.

Indexes cannot be defined on geospatial columns (GEOGRAPHY, GEOMETRY).

Indexes can be defined when the table is created, or with the CREATE INDEX command. For more information about creating indexes for hybrid tables, see CREATE INDEX.

INCLUDE ( col_name [ , col_name , ... ] )

Specifies one or more included columns for a secondary index. Using included columns with a secondary index is particularly useful when queries frequently contain a set of columns in the projection list but not in the list of predicates. See Create a secondary index with an INCLUDE column.

INCLUDE columns cannot be semi-structured columns (VARIANT, OBJECT, ARRAY) or geospatial columns (GEOGRAPHY, GEOMETRY).

INCLUDE columns can be specified only when a table is created with a secondary index.

COMMENT = 'string_literal'

Specifies a comment for the table.

Default: No value

Usage notes¶

• To recreate or replace a hybrid table, call the GET_DDL function to see the definition of the hybrid table before running a CREATE OR REPLACE HYBRID TABLE command.

• You cannot create hybrid tables that are temporary or transient. In turn, you cannot create hybrid tables within transient schemas or databases.

• 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.

  Note that the drop and create actions occur in a single atomic operation. 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.

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

• 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.

Constraints for hybrid tables¶

• A hybrid table must be created with a primary key constraint.

  Multi-column (or composite) primary keys are supported. To define a multi-column primary key, use the syntax shown in the following example, where the constraint is defined “out of line” and refers to multiple columns that were previously defined for the table:

  CREATE OR REPLACE HYBRID TABLE ht2pk (
    col1 INTEGER NOT NULL,
    col2 INTEGER NOT NULL,
    col3 VARCHAR,
    CONSTRAINT pkey_1 PRIMARY KEY (col1, col2)
    );
  

  Copy

• Primary key, unique, and foreign key constraints are enforced on hybrid tables. For information about limitations on these constraints, see Unsupported features and limitations for hybrid tables.

• Primary key, unique, and foreign key constraints all build their own underlying indexes. These indexes result in additional data being stored. Secondary (or covering) indexes can also be defined explicitly when the table is created, using the outoflineIndex syntax.

CREATE HYBRID TABLE … AS SELECT (CTAS)¶

Creates a new hybrid table that contains the results of a query:

CREATE [ OR REPLACE ] HYBRID TABLE <table_name> [ ( <col_name> [ <col_type> ] , <col_name> [ <col_type> ] , ... ) ]
  AS <query>
  [ ... ]

Copy

Note

When using CTAS to create a hybrid table, define the table schema explicitly, including column definitions, the primary key, indexes, and other constraints. Do not rely on inferring the schema from a SELECT statement.

The number of column names specified must match the number of SELECT list items in the query.

To create the table with rows in a specific order, use an ORDER BY clause at the end of the query.

CREATE HYBRID TABLE … LIKE¶

Creates a new hybrid table with the same column definitions as an existing hybrid table, but without copying data from the existing table.

Column names, types, defaults, and constraints are copied to the new table:

CREATE [ OR REPLACE ] HYBRID TABLE <table_name> LIKE <source_hybrid_table>
  [ ... ]

Copy

Note

CREATE HYBRID TABLE … LIKE only supports another hybrid table as the source table type.

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

Examples¶

Create a hybrid table in the current database with customer_id as the primary key, a unique constraint on email, and a secondary index on full_name:

CREATE HYBRID TABLE mytable (
  customer_id INT AUTOINCREMENT PRIMARY KEY,
  full_name VARCHAR(255),
  email VARCHAR(255) UNIQUE,
  extended_customer_info VARIANT,
  INDEX index_full_name (full_name)
);

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

Insert a row into this table:

INSERT INTO mytable (customer_id, full_name, email, extended_customer_info)
  SELECT 100, 'Jane Doe', 'jdoe@gmail.com',
    parse_json('{"address": "1234 Main St", "city": "San Francisco", "state": "CA", "zip":"94110"}');

+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+

The primary key must be unique. For example, if you try to insert the same primary key from the previous example a second time, the command fails with the following error:

200001 (22000): Primary key already exists

The email address must also follow the inline UNIQUE constraint. For example, if you attempt to insert two records with the same email address, the statement fails with the following error:

Duplicate key value violates unique constraint "SYS_INDEX_MYTABLE_UNIQUE_EMAIL"

View table properties and metadata. Note the value of the is_hybrid column:

SHOW TABLES LIKE 'mytable';

+-------------------------------+---------+---------------+-------------+-------+-----------+---------+------------+------+-------+--------+----------------+----------------------+-----------------+---------------------+------------------------------+---------------------------+-------------+
| created_on                    | name    | database_name | schema_name | kind  | is_hybrid | comment | cluster_by | rows | bytes | owner  | retention_time | automatic_clustering | change_tracking | search_optimization | search_optimization_progress | search_optimization_bytes | is_external |
|-------------------------------+---------+---------------+-------------+-------+-----------+---------+------------+------+-------+--------+----------------+----------------------+-----------------+---------------------+------------------------------+---------------------------+-------------|
| 2022-02-23 23:53:19.707 +0000 | MYTABLE | MYDB          | PUBLIC      | TABLE | Y         |         |            | NULL |  NULL | MYROLE | 10             | OFF                  | OFF             | OFF                 |                         NULL |                      NULL | N           |
+-------------------------------+---------+---------------+-------------+-------+-----------+---------+------------+------+-------+--------+----------------+----------------------+-----------------+---------------------+------------------------------+---------------------------+-------------+

View details for all hybrid tables:

SHOW HYBRID TABLES;

+-------------------------------+---------------------------+---------------+-------------+--------------+--------------+------+-------+---------+
| created_on                    | name                      | database_name | schema_name | owner        | datastore_id | rows | bytes | comment |
|-------------------------------+---------------------------+---------------+-------------+--------------+--------------+------+-------+---------|
| 2022-02-24 02:07:31.877 +0000 | MYTABLE                   | DEMO_DB       | PUBLIC      | ACCOUNTADMIN |         2002 | NULL |  NULL |         |
+-------------------------------+---------------------------+---------------+-------------+--------------+--------------+------+-------+---------+

Display information about the columns in the table:

DESCRIBE TABLE mytable;

+-------------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name              | type         | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|-------------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| CUSTOMER_ID       | NUMBER(38,0) | COLUMN | N     | NULL    | Y           | N          | NULL  | NULL       | NULL    | NULL        |
| FULL_NAME         | VARCHAR(256) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| APPLICATION_STATE | VARIANT      | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
+-------------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+

Select data from the table:

SELECT customer_id, full_name, email, extended_customer_info
  FROM mytable
  WHERE extended_customer_info['state'] = 'CA';

+-------------+-----------+----------------+------------------------------+
| CUSTOMER_ID | FULL_NAME | EMAIL          | EXTENDED_CUSTOMER_INFO       |
|-------------+-----------+----------------+------------------------------|
|         100 | Jane Doe  | jdoe@gmail.com | {                            |
|             |           |                |   "address": "1234 Main St", |
|             |           |                |   "city": "San Francisco",   |
|             |           |                |   "state": "CA",             |
|             |           |                |   "zip": "94110"             |
|             |           |                | }                            |
+-------------+-----------+----------------+------------------------------+

CREATE HYBRID TABLE employee (
    employee_id INT PRIMARY KEY,
    employee_name STRING,
    employee_department STRING,
    INDEX idx_department (employee_department) INCLUDE (employee_name)
);

INSERT INTO employee VALUES
  (1, 'John Doe', 'Marketing'),
  (2, 'Jane Smith', 'Sales'),
  (3, 'Bob Johnson', 'Finance'),
  (4, 'Alice Brown', 'Marketing');

SELECT employee_name FROM employee WHERE employee_department = 'Marketing';
SELECT employee_name FROM employee WHERE employee_department IN ('Marketing', 'Sales');