CREATE HYBRID TABLE¶

Syntax¶

CREATE [ OR REPLACE ] HYBRID TABLE [ IF NOT EXISTS ] <table_name>
  ( <col_name> <col_type>
    [
      {
        DEFAULT <expr>
          /* AUTOINCREMENT (or IDENTITY) is supported only for numeric data types (NUMBER, INT, FLOAT, etc.) */
        | { AUTOINCREMENT | IDENTITY }
          [
            {
              ( <start_num> , <step_num> )
              | START <num> INCREMENT <num>
            }
          ]
          [ { ORDER | NOORDER } ]
      }
    ]
    [ NOT NULL ]
    [ inlineConstraint ]
    [ , <col_name> <col_type> [ ... ] ]
    [ , outoflineIndex ]
    [ , ... ]
  )
  [ COMMENT = '<string_literal>' ]

Where:

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

Copy

Important

• A hybrid table must have a primary key constraint.

• Foreign key constraints are enforced on hybrid tables.

• Unique and foreign key constraints each build their own underlying index. The index results in additional data being stored. Indexes can be defined at table creation time either by defining unique constraints, by defining foreign key constraints, or by defining indexes. The out-of-line index definition below was extended to allow the creation of covering indexes.

• Indexes can be defined only on columns that are not semi-structured (that is, variant, object, array). Included columns also have the same restrictions. This is due to the space constraints associated with the underlying storage engines for the key of each record. Included columns can be specified only when a table is created.

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

Copy

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

For information on limitations regardings hybrid tables constraints, see Unsupported features and limitations for hybrid tables.

Variant Syntax¶

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

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

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

Copy

Note

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

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_table>
  [ ... ]

Copy

Note

Currently, 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 currently not supported.

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.

query

Required if using AS SELECT (i.e. CTAS)

String that specifies the SELECT statement used to populate the table.

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.

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 constraint for the specified column(s) in the table.

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

COMMENT = 'string_literal'

Specifies a comment for the table.

Default: No value

Usage Notes¶

• A hybrid table must have a primary key constraint.

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

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

• CREATE HYBRID 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 HYBRID TABLE <table_name> AS SELECT ...
    

    Copy

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

    CREATE HYBRID 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.

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

  • While you can determine the schema for a table from the CTAS statement, it is recommended that you specify the schema explicitly.

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

• Indexes:

  • You can define an index for a hybrid table at creation time by defining unique constraints, foreign key constraints, or indexes. Note that the CREATE HYBRID TABLE command allows you to define a outoflineIndex parameter. For more information on creating an index for a hybrid table, see CREATE INDEX.

  • Because of space constraints associated with the underlying storage engines for the key of each record, you can only define indexes on columns that are not semi-structured (Variant, Object, Array).

Use Included Columns¶

Using included columns with a secondary index can be particularly useful when you have frequent queries where a set of columns is present in the projection list but not in the list of predicates. For example:

-- Create the employee table with a covering index
CREATE HYBRID TABLE employee (
    employee_id INT PRIMARY KEY,
    employee_name STRING,
    employee_department STRING,
    INDEX idx_department (employee_department) INCLUDE (employee_name)
);

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

-- Example Queries using covering index
SELECT employee_name FROM employee WHERE employee_department = 'Marketing';
SELECT employee_name FROM employee WHERE employee_department in ('Marketing', 'Sales');

In this example, both of the queries SELECT employee_name FROM employee WHERE employee_department = 'Marketing'; and SELECT employee_name FROM employee WHERE employee_department in ['Marketing','Sales']; will benefit from the covering index by avoiding lookups to the base table. But note that using included columns in indexes may cause an increase in storage consumption since additional columns will be stored in the secondary index.

Examples¶

Create a hybrid table in the current database with 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 in the table created in the previous example:

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, it 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:

DESC 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"             |
|             |           |                | }                            |
+-------------+-----------+----------------+------------------------------+