Create a hybrid table

This topic provides an overview on creating Hybrid tables in Snowflake.

Creating a hybrid table

You can create a hybrid table using SQL commands in one of the following ways:

-- Create hybrid table
CREATE OR REPLACE HYBRID TABLE icecream (
  id NUMBER PRIMARY KEY AUTOINCREMENT START 1 INCREMENT 1,
  col1 VARCHAR NOT NULL,
  col2 VARCHAR NOT NULL
);

-- Insert data into hybrid table
INSERT INTO icecream VALUES(1, 'A1', 'B1');
INSERT INTO icecream VALUES(2, 'A2', 'B2');
INSERT INTO icecream VALUES(3, 'A3', 'B3');
INSERT INTO icecream VALUES(4, 'A4', 'B4');

-- Update data in hybrid table
UPDATE icecream SET col2 = 'B3-updated' WHERE id = 3;

-- Delete data from hybrid table
DELETE FROM icecream WHERE id = 4;

-- Select data from hybrid table
SELECT * FROM icecream;
Copy

  • Use the variant syntax CREATE HYBRID TABLE ... AS SELECT and CREATE HYBRID TABLE ... LIKE. See CREATE HYBRID TABLE. For example:

CREATE OR REPLACE HYBRID TABLE table1 (
  employee_id INT PRIMARY KEY,
  department_id VARCHAR(200)
) as
SELECT * FROM table2;
Copy

Loading data

Note

To create a hybrid table, you must have a running warehouse that is specified as the current warehouse for your session. Errors may occur if no running warehouse is specified when you create a hybrid table. For more information, see Working with Warehouses.

You can bulk load data into hybrid tables by copying from a data stage or other tables (that is, using CREATE TABLE … AS SELECT, COPY, or INSERT INTO … SELECT), but bulk loading data into a hybrid table using a CREATE TABLE … AS SELECT statement is strongly recommended. Bulk loading is optimized for performance and enables CREATE TABLE … AS SELECT operations to be approximately 10 times faster than other options, in most cases, when loading multiple millions of records.

Bulk loading via INSERT or COPY is also supported, but data loading is slower for large amounts of data. You might experience long wait times or even timeouts when loading large data sets. Queries against recently loaded data will also be slower. Bulk load optimization for DML statements such as COPY is planned for the near future.

Attention

CREATE TABLE … AS SELECT does not support foreign key constraints. If you must use foreign key constraints in your hybrid table, you need use another option such as COPY or INSERT INTO … SELECT.

If your source data exists in an external stage instead of a Snowflake table, use CREATE TABLE ... AS SELECT * FROM @stage/data.csv rather than COPY FROM @stage/data.csv to benefit from the more efficient loading method.

When not using CREATE TABLE … AS SELECT, you should be able to load up to approximately 1 million records per minute. But note that this approximation is dependent on the structure of the table (for example, the size of records, where larger records are slower to load). If your loading operations are taking too long and failing, test with a smaller data set or break your loads into smaller chunks.

Note

Other methods of loading data into Snowflake tables (for example, Snowpipe) are not currently supported.

Add an index to a hybrid table

You can define all keys, indexes, and constraints when you create a Hybrid Table. For example:

CREATE OR REPLACE HYBRID TABLE targethybridtable (
  col1 VARCHAR(32) PRIMARY KEY,
  col2 NUMBER(38,0) UNIQUE,
  col3  NUMBER(38,0),
    INDEX index_col3 (col3))
AS
  (SELECT
     col1,
     col2,
     col3
   FROM sourcetable);
Copy

When adding an index to a existing hybrid table, it is recommended that you recreate the hybrid table.

Optionally, you can create an index for an existing hybrid table using CREATE INDEX. When adding an index to an existing hybrid table that is being used for a workload, use CREATE INDEX. CREATE INDEX will build an index concurrently without the table getting locked during the operation.

A hybrid table requires a unique primary key. The data in your hybrid table is ordered by this primary key. You can add additional secondary indexes to non-primary key attributes to accelerate lookups along that attribute. Indexes can be leveraged for =, >, >=, <, <=, IN predicates to reduce the number of records to be scanned.

Attention

To add a secondary index, you must use a role that is granted the SELECT privilege on the hybrid table. If you have access to a view of the the data in the hybrid table, but not the table itself, you will not be able to add a secondary index.

If you have common, repeated queries with predicates on a specific attribute or a composite group of attributes, consider adding an index to that attribute or group of attributes to improve performance. But be aware of the following considerations when using indexes:

  • Increase in storage consumption when storing additional copies of the subset of data in the index.

  • Addition of overhead to DMLs because indexes are maintained synchronously.

Language: English