CREATE TABLE
On this page
Creates a new table.
Syntax
CREATE [ROWSTORE] [REFERENCE | TEMPORARY | GLOBAL TEMPORARY] TABLE [IF NOT EXISTS] <table_name>(<create_definition>,...)[<table_options>][[AS] SELECT ...]CREATE TABLE [IF NOT EXISTS] new_tbl_name{ LIKE original_tbl_name | (LIKE original_tbl_name) }[WITH DEEP | SHALLOW COPY]<create_definition>:<column_name> { <column_definition> | AS <computed_column_definition> }| [CONSTRAINT [symbol]] PRIMARY KEY [<index_type>] (<index_column_name>,...)[<index_option>] ...| { INDEX | KEY } [<index_name>] [<index_type>] (<index_col_name>,...)[<index_option>] ...| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY][<index_name>] [<index_type>] (<index_column_name>,...)[<index_option>] ...| [CONSTRAINT [symbol]] SHARD KEY [<index_type>] (<index_column_name>,...) [METADATA_ONLY][<index_option>] ...| SORT KEY (<index_column_name>,... [DESC])| COLUMN GROUP [column_group_name] ( * )| FULLTEXT[USING VERSION 1][<index_name>] (<index_column_name>,...)| FULLTEXT USING VERSION 2 [<index_name>] (<index_column_name>,...)| FOREIGN KEY [foreign_key_name](col1,..., coln) REFERENCES table_referenced (col_referenced))<column_definition>:<data_type> [NOT NULL | NULL] [DEFAULT <default_value>] [ON UPDATE <update_value>][AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [SPARSE] [SERIES TIMESTAMP]<computed_column_definition>:computed_column_expression PERSISTED [data_type | AUTO]<data_type>:BIT[(<length>)]| TINYINT[(<length>)] [UNSIGNED]| SMALLINT[(<length>)] [UNSIGNED]| INT[(<length>)] [UNSIGNED]| INTEGER[(<length>)] [UNSIGNED]| BIGINT[(<length>)] [UNSIGNED]| REAL[(<length>,<decimals>)] [UNSIGNED]| DOUBLE[(<length>,<decimals>)] [UNSIGNED]| DECIMAL[(<length>[,<decimals>])] [UNSIGNED]| NUMERIC[(<length>[,<decimals>])] [UNSIGNED]| DATETIME| DATETIME(6)| TIMESTAMP| TIMESTAMP(6)| DATE| TIME| CHAR[(<length>)][CHARACTER SET <character_set_name>] [COLLATE <collation_name>]| VARCHAR(<length>)[CHARACTER SET <character_set_name>] [COLLATE <collation_name>]| TINYBLOB| BLOB| MEDIUMBLOB| LONGBLOB| TINYTEXT [BINARY]| TEXT [BINARY]| MEDIUMTEXT [BINARY]| LONGTEXT [BINARY]| ENUM(<value1>,<value2>,<value3>,...)| SET(<value1>,<value2>,<value3>,...)| JSON [COLLATE <collation_name>]| GEOGRAPHY| GEOGRAPHYPOINT<index_column_name>:<column_name> [(<length>)] [ASC | DESC]<index_type>:| USING { BTREE | HASH }<index_option>:KEY_BLOCK_SIZE [=] <value>| <index_type>| COMMENT '<string>'| BUCKET_COUNT [=] <value>| WITH (<index_kv_options>)| UNENFORCED [RELY | NORELY]<index_kv_options>:<index_kv_option> [, <index_kv_option>] ...<index_kv_option>:RESOLUTION = <value>| COLUMNSTORE_SEGMENT_ROWS = <value>| COLUMNSTORE_FLUSH_BYTES = <value><table_options>:<table_option> [[,] <table_option>] ...<table_option>:AUTO_INCREMENT [=] <value>| COMMENT [=] '<string>'| AUTOSTATS_ENABLED = { TRUE | FALSE }| AUTOSTATS_CARDINALITY_MODE = {INCREMENTAL|PERIODIC|OFF}| AUTOSTATS_HISTOGRAM_MODE = {CREATE|UPDATE|OFF}| AUTOSTATS_SAMPLING = {ON|OFF}| COMPRESSION = SPARSE
CREATE { TABLE | TABLES } AS INFER PIPELINE
CREATE TABLE [IF NOT EXISTS] <table_name>AS INFER PIPELINE AS LOAD DATA{ LINK <link_name> |MONGODB "<collection>" CONFIG '<config_json>' CREDENTIALS '<credentials_json>' |MYSQL "<source_db>.<source_table>" CONFIG '<config_json>' CREDENTIALS '<credentials_json>' }FORMAT AVRO;
CREATE TABLES [IF NOT EXISTS]AS INFER PIPELINE AS LOAD DATA{ LINK <link_name> |MONGODB '*' CONFIG '<conf_json>' CREDENTIALS '<cred_json>' |MYSQL "*" CONFIG '<config_json>' CREDENTIALS '<credentials_json>' }FORMAT AVRO;
SingleStore supports replicating data from MongoDB® and MySQL data sources using Change Data Capture (CDC) pipelines.
Remarks
Note
Unless CREATE ROWSTORE TABLE .
or SORT KEY()
are specified, the value of the default_
engine variable determines the type of table (columnstore
or rowstore
) that is created.
When default_
is set to columnstore
, you can create a columnstore table using standard CREATE TABLE syntax.
default_
is set to columnstore
for SingleStore Helios workspaces.default_
.
The setting of default_
applies to temporary tables.GLOBAL TEMPORARY
tables, if default_CREATE ROWSTORE GLOBAL TEMPORARY TABLE
.GLOBAL TEMPORARY
is not supported on columnstore tables.
-
For more information about the data types listed above, and for an explanation of
UNSIGNED
, refer to the Data Types topic. -
The
SET
data type restricts the values that can be inserted for a table column.Only the set of strings that are listed for a column at the time of table creation can be inserted. -
<table_
is the name of the table to create in the SingleStore Helios database.name> -
The following note applies when the engine variable
table_
is set toname_ case_ sensitivity OFF
: After you create a table, you cannot create another table having the same table name with a different case.Refer to the Database Object Case Sensitivity topic for more information. -
CREATE TABLE
is slower in SingleStore Helios than in MySQL.See Code Generation for more information. -
The
KEY
syntax is equivalent to usingINDEX
syntax when used inCREATE TABLE
.The convention is to use the KEY
syntax.INDEX
syntax is generally used when creating an index on an existing table.See CREATE INDEX for more information. -
Foreign key referential integrity enforcement is not supported by SingleStore, but
FOREIGN KEY
syntax can be supported in SingleStore by setting theignore_
engine variable toforeign_ keys ON
.The default value for ignore_
isforeign_ keys OFF
.See the Specifying Unenforced Unique Constraints page for more information. -
The
BTREE
index type creates a skip list index in SingleStore Helios.This index has very similar characteristics to a BTREE index. -
If you do not want to specify a column (or columns) to sort on, or do not care about the sort order for your data, you can specify an empty key (e.
g. SORT KEY()
). -
The
SORT KEY()
order can be specified as ascending (SORT KEY(index_
) or descending (column_ name) SORT KEY(index_
).column_ name DESC) SingleStore does not support scanning a SORT KEY()
in reverse order to its sort order:CREATE TABLE ct_sort (col1 int, SORT KEY(col1 DESC));EXPLAIN SELECT * FROM ct_sort ORDER BY col1 DESC;+-------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------+ | Project [remote_0.col1] | TopSort limit:[@@SESSION.`sql_select_limit`] [remote_0.col1 DESC] | | Gather partitions:all alias:remote_0 parallelism_level:sub_partition | | Project [t1.col1] | | Top limit:[?] | | ColumnStoreFilter [<after per-thread scan begin> AND <before per-thread scan end>] | | OrderedColumnStoreScan test1.t1, SORT KEY col1 (col1 DESC) table_type:sharded_columnstore | +-------------------------------------------------------------------------------------------+
EXPLAIN SELECT * FROM ct_sort ORDER BY col1;+------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------+ | Project [remote_0.col1] | | TopSort limit:[@@SESSION.`sql_select_limit`] [remote_0.col1] | | Gather partitions:all alias:remote_0 parallelism_level:segment | | Project [t1.col1] | | TopSort limit:[?] [t1.col1] | | ColumnStoreScan test1.t1, SORT KEY col1 (col1 DESC) table_type:sharded_columnstore | +------------------------------------------------------------------------------------+
-
SORT KEY()
is not allowed when usingCREATE ROWSTORE TABLE .
.. . -
KEY() USING CLUSTERED COLUMNSTORE
is a legacy syntax that is equivalent toSORT KEY()
.SingleStore recommends using SORT KEY()
. -
BUCKET_
is specific to theCOUNT HASH
index type.It controls the bucket count of the hash table. It applies to rowstore hash indexes only and does not effect columnstore hash indexes. -
The
UNENFORCED
index option can be used on aUNIQUE
constraint to specify that the unique constraint is unenforced.See Unenforced Unique Constraints. -
RESOLUTION
is specific to index on geospatial columns.See Working with Geospatial Features for more information. -
COLUMNSTORE_
,SEGMENT_ ROWS COLUMNSTORE_
controls configuration variables specific to columnstore tables.FLUSH_ BYTES See Advanced Columnstore Configuration Options) for more information. -
SingleStore supports binary,
utf8
, andutf8mb4
character sets.The COLLATE
clause in theCREATE TABLE
statement can be used to override the server character set and collation, which are used as default values for the table. -
AUTOSTATS_
controls if automatic statistics should be collected on this table.ENABLED There are three categories of autostats - AUTOSTATS_
,CARDINALITY_ MODE AUTOSTATS_
, andHISTOGRAM_ MODE AUTOSTATS_
.SAMPLING SingleStore Helios allows you to independently control how each category of statistics is automatically gathered. Multiple autostats settings can be combined in a single CREATE TABLE
statement.See Automatic Statistics for more information. -
This command causes implicit commits.
Refer to COMMIT for more information. -
Specify the
AUTO
option instead of the<data_
in thetype> <computed_
clause to automatically infer the data type of the column from thecolumn_ definition> <computed_
.column_ expression> -
<computed_
defines the value of a computed column using other columns in the table, constants, built-in functions, operators, and combinations thereof.column_ expression> For more information see Persistent Computed Columns. -
Temporary tables, created with the
TEMPORARY
option, will be deleted when the client session terminates.For ODBC/JDBC, this is when the connection closes. For interactive client sessions, it is when the user terminates the client program. -
Global temporary tables, created with the
GLOBAL TEMPORARY
option, exist beyond the duration of a client session.If failover occurs, the global temporary tables lose data and enter an errored state; they need to be dropped and recreated. This command can be run only on the master aggregator. See Global Temporary Tables for details. -
The
SERIES TIMESTAMP
clause specifies a table column as the default column defining time order for implicit use by time series functions.This setting can be specified only for a single table column. The column can be one of the following data types: DATE
,TIME
,DATETIME
,DATETIME(6)
,TIMESTAMP
orTIMESTAMP(6)
.SingleStore recommends to use either of the DATETIME
orDATETIME(6)
types instead of one of theTIMESTAMP
types because the automatic update behavior ofTIMESTAMP
is subject to change.See Timestamp Behavior for details. -
The
SERIES TIMESTAMP
clause does not affect the data type of a table column, rather it specifies the behavior of the column in the time-series-specific functions likeFIRST()
,LAST()
, andTIME_
.BUCKET() -
Keyless sharding distributes data across partitions uniformly at random but with the limitation that it does not allow single partition queries or local joins since rows are not assigned to specific partitions.
Keyless sharding is the default for tables that do not have primary key or explicit shard key. You can explicitly declare a table as keyless sharded by specifying a shard key with an empty list of columns in the SHARD KEY()
constraint in the table definition. -
The
METADATA_
option on theONLY SHARD KEY
syntax prevents an index being created on the shard key.It will decrease overall memory usage. It can cause queries to run slower. It can only be used when creating your table. -
The optional
COLUMN GROUP
clause creates a materialized copy of each row as a separate index.It is supported on columnstore tables only. This structure can be used to accelerate full-row retrievals and updates for wide columnstore tables. Column group indexes use less RAM than rowstore tables which can reduce operation costs.
Using column group indexes on columnstores can allow you to get both fast lookups and fast analytics on the same table. The column group index improves the performance of lookups, and the standard columnar representation is available to give fast analytics. Using a column group index on a columnstore table is easier to manage than having to move data between rowstore and columnstore tables. The find the size of a column group index, refer to the How the Columnstore Works page.
The
column_
argument is optional.group_ name If a column group name is not specified when creating a table, one is chosen by the engine. Using COLUMN GROUP [column_
creates a column group index on all columns in the table.group_ name] (*) Column group indexes created on a subset of table columns are not supported. The following is an syntax example of a columnstore table that creates a column group on all columns:
CREATE TABLE col_group_1(id BIGINT, col1 VARCHAR(10), col2 VARCHAR(10), ..., coln INT, COLUMN GROUP col_gp_inx (*)); -
The
WITH DEEP COPY
argument of theCREATE TABLE new_
statement copies an existing table (tbl_ name LIKE original_ tbl_ name original_
) and creates a new table that will have the same definition as the original table - including all of the data and metadata (such as indexes) in the original table.tbl_ name Users must have SELECT
permissions to be able to execute SQL statements against the new table.Computed columns will be recomputed during the WITH DEEP COPY
process. -
The
WITH SHALLOW COPY
argument of theCREATE TABLE new_
statement copies an existing table (tbl_ name LIKE original_ tbl_ name original_
) and creates a new table that will have the same definition as the original table.tbl_ name The data is not physically copied to the new table, but referenced against the original table. So any SELECT
query made against either table produces the same result, until one of them is updated.Users must have SELECT
permissions to be able to execute SQL statements against the new table. -
Refer to the Permission Matrix for the required permission.
MySQL Compatibility
SingleStore Helios’s syntax differs from MySQL mainly in the data types and storage it supports, and some specific index hints.
-
KEY_
: value is currently ignored.BLOCK_ SIZE [=] <value>
DEFAULT Behavior
If DEFAULT <default_
is specified in <column_
, and no value is inserted in the column, then <default_
will be placed in the column during an INSERT
operation.
ON UPDATE Behavior
If ON UPDATE <update_
is specified in <column_
, and if any other column is updated but the specified column is not explicitly updated, then update_
will be placed in the column during an UPDATE
operation.TIMESTAMP
, TIMESTAMP(6)
, DATETIME
, or DATETIME(6)
, then you can update <update_
to one of the following values: CURRENT_
, CURRENT_
, NOW()
, or NOW(6)
.
ON UPDATE
can be used with these TIMESTAMP/DATETIME[(6)]
types only, and you can only use one of the time functions as the argument.
Storage of CHAR(<length>) as VARCHAR(<length>)
For a column defined as type CHAR
of length len
, store the column as a VARCHAR
of length len
if len
greater than or equal to the value of the engine variable varchar_
.0
, the column is not stored as a VARCHAR
.
Storing a CHAR
of length len
as a VARCHAR
of length len
will allow the column to realize the performance benefit of a VARCHAR
, in many cases.
Suppose the value of varchar_
is 3
and you run:
CREATE TABLE ct_char(a CHAR(4));
The column a
is stored as a VARCHAR(4)
.
Example(s)
CREATE TEMPORARY
CREATE TEMPORARY
or CREATE ROWSTORE TEMPORARY
(if default_
is set to columnstore, you must use the latter syntax) creates a table that will be deleted when the client session terminates.
CREATE TEMPORARY TABLE IF NOT EXISTS ct_temp_1 (id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, SHARD KEY(id));CREATE ROWSTORE TEMPORARY TABLE IF NOT EXISTS ct_temp_1 (id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, SHARD KEY(id));
CREATE ROWSTORE GLOBAL TEMPORARY
CREATE ROWSTORE GLOBAL TEMPORARY
creates a table that exists beyond the duration of a client session.
CREATE ROWSTORE GLOBAL TEMPORARY TABLE IF NOT EXISTS ct_temp_2 (id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, SHARD KEY(id));
CREATE TABLE with an AUTO_ INCREMENT Column
AUTO_
can be used to automatically generate a unique value for new rows.AUTO_
field is DEFAULT
, NULL
, or 0
, SingleStore Helios will automatically assign a value.AUTO_
only guarantees that automatically-generated values are unique.
-
are consecutive or sequential
-
are monotonically increasing
-
start from any particular value
-
are distinct from explicitly-set values
If you explicitly set a value in an INSERT
or UPDATE
statement, it may collide with past or future automatically-generated values.AUTO_
column.AGGREGATOR SYNC AUTO_
is run by the end user (which will reset the auto-increment counter to a value higher than 10).AUTO_
value reaches 10 eventually on its own as additional rows are added to the table.
CREATE TABLE ct_auto (c1 INT AUTO_INCREMENT PRIMARY KEY);INSERT INTO ct_auto (c1) VALUES (10);
A table can have only one AUTO_
column.AUTO_
column must be included in an index (not necessarily a PRIMARY
or UNIQUE
key, a regular key is also allowed).
If syntax such as CREATE TABLE table_
is used to create table_
where table_
has an AUTO_
column, it will be created as a non-auto-increment column in table_
.
See LAST_AUTO_
behavior.
If the AUTO_
behavior described here does not satisfy your requirements you can create your own sequence generator using LAST_
.
Warning
Restarting an aggregator, such as during upgrades or host machine maintenance, will introduce a large gap between any AUTO_
values inserted before the restart and any values inserted after.
These jumps are because each aggregator defines and manages its own range of values to start incrementing from to prevent collisions in a table.AUTO_
values increases to the next 1,000,000 after each restart (e.
AUTO_ INCREMENT in Sharded Tables
On a sharded (distributed) table, AUTO_
can only be used on a BIGINT
column (as they usually use the entire 64 bits).AUTO_
values and uses those values when new rows are added to a table.AUTO_
values in sharded tables are assigned using the high 14 bits to encode the aggregator ID and the bottom 50 bits for a per-aggregator unique value.AUTO_
values, the automatically-generated values from inserts across multiple aggregators are only unique, never sequential.
Here is an example to illustrate how AUTO_
values are generated across aggregators in a workspace as new rows are inserted into table ct_
:
SELECT * FROM ct_tb ORDER BY b;
+-------------------+------+------------+
| a | b | c |
+-------------------+------+------------+
| 1 | 1 | from MA |
| 2 | 2 | from MA |
| 3 | 3 | from MA |
| 4 | 4 | from MA |
| 5 | 5 | from MA |
| 13510798882111489 | 6 | from CA 96 |
| 13510798882111490 | 7 | from CA 96 |
| 13510798882111491 | 8 | from CA 96 |
| 13510798882111492 | 9 | from CA 96 |
| 13510798882111493 | 10 | from CA 96 |
| 14636698788954113 | 11 | from CA 20 |
| 14636698788954114 | 12 | from CA 20 |
| 14636698788954115 | 13 | from CA 20 |
| 14636698788954116 | 14 | from CA 20 |
| 14636698788954117 | 15 | from CA 20 |
| 6 | 16 | from MA |
| 15762598695796737 | 17 | from CA 17 |
| 13510798882111494 | 18 | from CA 96 |
| 7 | 19 | from MA |
| 14636698788954118 | 20 | from CA 20 |
+-------------------+------+------------+
As shown in the example above, automatically-generated AUTO_
values can differ depending on which aggregator you run the inserts on.
AUTO_ INCREMENT in Reference Tables
The AUTO_
value for a reference table is tracked by the master aggregator.AUTO_
value will always be greater than any value previously seen in this column.INSERT
or UPDATE
statement will not create a collision with future automatically generated values.
The next example shows some queries using AUTO_
fields on reference tables.
CREATE REFERENCE TABLE ct_ref_1(id INT AUTO_INCREMENT PRIMARY KEY);INSERT INTO ct_ref_1 VALUES();INSERT INTO ct_ref_1 VALUES(5);INSERT INTO ct_ref_1 VALUES();
SELECT id FROM ct_ref_1 ORDER BY id;
+----+
| id |
+----+
| 1 |
| 5 |
| 6 |
+----+
UPDATE ct_ref_1 SET id = 9 WHERE id = 5;INSERT INTO ct_ref_1();
SELECT id FROM ct_ref_1 ORDER BY id;
+----+
| id |
+----+
| 1 |
| 6 |
| 9 |
| 10 |
+----+
DELETE FROM ct_ref_1;INSERT INTO ct_ref_1 VALUES();
SELECT id FROM ct_ref_1 ORDER BY id;
+----+
| id |
+----+
| 11 |
+----+
Setting AUTO_ INCREMENT Starting Values
It is possible to override the starting AUTO_
value for reference tables by setting the AUTO_
option on a CREATE TABLE
statement.
The following example shows how to set the AUTO_
start value during table creation:
CREATE REFERENCE TABLE ct_ref_2 (id int AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 7;INSERT INTO ct_ref_2 VALUES (), ();
SELECT * FROM ct_ref_2;
+----+
| id |
+----+
| 7 |
| 8 |
+----+
This syntax has no effect on sharded tables.
AUTO_ INCREMENT During Replication
When replicating data between workspaces, the secondary workspace has all the replicated AUTO_
values from the primary workspace.AUTO_
values on every aggregator.
CREATE TABLE IF NOT EXISTS my_MemSQL_table (id INT PRIMARY KEY AUTO_INCREMENT, v VARCHAR(10) NOT NULL);
CREATE TABLE AS SELECT
CREATE TABLE AS SELECT
(also referred to as CREATE TABLE .
) can create one table from results of a SELECT
query.
Here is the basic syntax.
CREATE [ROWSTORE] [REFERENCE | TEMPORARY | GLOBAL TEMPORARY] TABLE [IF NOT EXISTS] <table_name_2>(column_name(s), [SHARD KEY(column_name)] | [SORT KEY(column_name)] | [KEY(column_name)]AS SELECT [*] | [column_name(s)] FROM table_name_1;
Here is an example of a CREATE TABLE AS SELECT
command with a shard key, sort key and an index:
CREATE TABLE ctas_table (a BIGINT, b BIGINT, SHARD KEY(a), SORT KEY(b), KEY(a)) AS SELECT * FROM orig_table;
The table will include a column for each column of the SELECT
query.
CREATE TABLE table_1 (PRIMARY KEY (a, b)) AS SELECT * FROM table_2;CREATE TABLE table_1 (SORT KEY (a, b)) AS SELECT * FROM table_2;CREATE TABLE table_1 (a int, b int) AS SELECT c, d FROM table_2;CREATE TABLE table_1 (b AS a+1 PERSISTED int) AS SELECT a FROM table_2;
In the case that the original table (table_
in the above examples) has an AUTO_
column, it will be created as a non-auto-increment column in the new table (table_
).
CREATE TABLE AS SELECT to Extract Data from One Column in an Existing Table
Extract time column from an event table to build a times table.
CREATE TABLE events (type VARCHAR(256), time TIMESTAMP);INSERT INTO events VALUES('WRITE', NOW());
CREATE TABLE times (id INT AUTO_INCREMENT KEY, time TIMESTAMP) AS SELECT time FROM events;
SELECT * FROM times;
+----+---------------------+
| id | time |
+----+---------------------+
| 1 | 2023-06-21 15:57:35 |
+----+---------------------+
CREATE TABLE AS SELECT to Extract Distinct Values from an Existing Table
SELECT * FROM courses ORDER BY course_code, section_number;
+-------------+----------------+-----------------+
| course_code | section_number | number_students |
+-------------+----------------+-----------------+
| CS-101 | 1 | 20 |
| CS-101 | 2 | 16 |
| CS-101 | 3 | 22 |
| CS-101 | 4 | 25 |
| CS-101 | 5 | 22 |
| CS-150 | 1 | 10 |
| CS-150 | 2 | 16 |
| CS-150 | 3 | 11 |
| CS-150 | 4 | 17 |
| CS-150 | 5 | 9 |
| CS-201 | 1 | 14 |
| CS-201 | 2 | 17 |
| CS-301 | 1 | 7 |
| CS-301 | 2 | 10 |
+-------------+----------------+-----------------+
CREATE TABLE IF NOT EXISTS distinct_courses (PRIMARY KEY(course_code))AS SELECT DISTINCT(course_code) FROM courses;
SELECT * FROM distinct_courses ORDER by course_code;
+-------------+
| course_code |
+-------------+
| CS-101 |
| CS-150 |
| CS-201 |
| CS-301 |
+-------------+
CREATE TABLE USING HASH
The USING HASH
clause creates a hash index in a table.
If a rowstore or columnstore table is being created, the following applies:
-
You can create single-column or multi-column hash indexes.
-
When you create a unique single-column hash index, the shard key can contain only one column and that column must be the same column that you have created the index on.
When you create a unique multi-column hash index, the shard key must be a subset of the columns that you have created the index on. -
You can create multiple single-column hash indexes on a reference table.
If a columnstore table is being created, the following applies:
-
You can create at most one unique hash index.
You can create multiple multi-column hash indexes. -
You cannot create a unique hash index on a
FLOAT
,REAL
, orDOUBLE
column.
CREATE TABLE with Multiple Hash Indexes
The following example creates a columnstore table with three hash indexes.
CREATE TABLE articles_3 (id INT UNSIGNED,month int UNSIGNED,year int UNSIGNED,title VARCHAR(200),body TEXT,SHARD KEY(title),SORT KEY (id),KEY (id) USING HASH,UNIQUE KEY (title) USING HASH,KEY (month,year) USING HASH);
The query SELECT * FROM articles WHERE title = 'Interesting title here';
uses title
’s hash index since the query uses an equality predicate.
The query SELECT * FROM articles WHERE year > 2010 AND month > 5;
does not use the hash index on month
and year
since the query does not use an equality predicate.
See the ColumnstoreFilter in the Query Plan Operations topic for an example EXPLAIN
plan for a columnstore query that uses a hash index.
See Highly Selective Joins for an example of a columnstore query with a join that uses a hash index.
CREATE TABLE with One Hash Index Containing Multiple Columns
KEY(<column 1 name>,<column 2 name>, .
is equivalent to KEY(<column 1 name>,<column 2 name>,.
.
CREATE TABLE ct_hash_1(a INT, b INT, c INT, KEY(a,b));
is equivalent to:
CREATE TABLE ct_hash_1(a INT, b INT, SORT KEY(), KEY(a,b) USING HASH);
A query against t
with an equality filter on a
, an equality filter on b
, or equality filters on both a
and b
could benefit from KEY(a,b) USING HASH
.
Depending on the cardinality, the performance of the query may be worse than the performance of the same query, where t
is a rowstore table and KEY(a,b)
is defined on that table.
CREATE TABLE with FULLTEXT Columns
SingleStore supports full-text search across text columns in a columnstore table using the FULLTEXT
index type.CREATE TABLE
or ALTER TABLE ADD FULLTEXT
statement and only on the text types CHAR
, VARCHAR
, TEXT
, and LONGTEXT
Data Types
If you query a column c
that is part of a multi-column FULLTEXT
index, where the query uses a FULLTEXT MATCH
on c
, the index on c
will be applied.
This differs from a multi-column non-FULLTEXT
index, where behavior is as follows: if you query column c
that is part of index i
, where the query uses an equality filter on c
, the index on c
will only be applied if c
is the leftmost column in i
.
Any column that is part of a FULLTEXT
index can be queried, even if it is not the leftmost.FULLTEXT
columns are done using the SELECT .
syntax.
CREATE TABLE with FULLTEXT Index on Two Columns
This example creates a FULLTEXT
index for both the title column and the body column.MATCH <column_
, and the index on the column would be applied.USING VERSION 1
syntax is optional.
CREATE TABLE articles_1 (id INT UNSIGNED,year int UNSIGNED,title VARCHAR(200),body TEXT,SORT KEY (id),FULLTEXT USING VERSION 1 (title,body));
CREATE TABLE with Version 2 FULLTEXT Index
This example creates a FULLTEXT
index for both the title column and the body column.MATCH (TABLE <table_
, and the index on the column will be applied.
Refer to Working with Full-Text Search for more information about version 2 full-text search.
CREATE TABLE articles (id INT UNSIGNED,year int UNSIGNED,title VARCHAR(200),body TEXT,SORT KEY (id),FULLTEXT USING VERSION 2 art_ft_index (title, body));
INSERT INTO articles (id, year, title, body) VALUES(1, 2021, 'Introduction to SQL', 'SQL is a standard language for accessing and manipulating databases.'),(2, 2022, 'Advanced SQL Techniques', 'Explore advanced techniques and functions in SQL for better data manipulation.'),(3, 2020, 'Database Optimization', 'Learn about various optimization techniques to improve database performance.'),(4, 2023, 'SQL in Web Development', 'Discover how SQL is used in web development to interact with databases.'),(5, 2019, 'Data Security in SQL', 'An overview of best practices for securing data in SQL databases.'),(6, 2021, 'SQL and Data Analysis', 'Using SQL for effective data analysis and reporting.'),(7, 2022, 'Introduction to Database Design', 'Fundamentals of designing a robust and scalable database.'),(8, 2020, 'SQL Performance Tuning', 'Tips and techniques for tuning SQL queries for better performance.'),(9, 2023, 'Using SQL with Python', 'Integrating SQL with Python for data science and automation tasks.'),(10, 2019, 'NoSQL vs SQL', 'A comparison of NoSQL and SQL databases and their use cases.');OPTIMIZE TABLE articles FLUSH;
SELECT * FROM articles WHERE MATCH (TABLE articles) AGAINST ('body:database');
+----+------+---------------------------------+-----------------------------------------------------------------------------+
| id | year | title | body |
+----+------+---------------------------------+-----------------------------------------------------------------------------+
| 7 | 2022 | Introduction to Database Design | Fundamentals of designing a robust and scalable database. |
| 3 | 2020 | Database Optimization | Learn about various optimization techniques to improve database performance.|
+----+------+---------------------------------+-----------------------------------------------------------------------------+
Refer to the MATCH page for more legacy and version 2 full-text examples.
Errors
These are the possible errors you may encounter when using FULLTEXT
.
Error |
Error String |
---|---|
Invalid Type specified for column |
Invalid type specified for |
Specifying |
|
Specifying the same column multiple times |
Column may only be specified once in a |
Specifying a column that is not defined on the table |
Column not defined |
Specifying |
Only column store tables may have a |
CREATE TABLE WITH DEEP COPY
The WITH DEEP COPY
feature creates a new table that will have the same table definition as the original table.CREATE TABLE WITH DEEP COPY
makes a full copy of the data and indexes in the original table.WITH DEEP COPY
process.INSERT
, UPDATE
, DELETE
, ALTER TABLE,
or DROP TABLE
) on the original table will not affect the copied table and vice versa.
The WITH DEEP COPY
feature eliminates the need to run a CREATE TABLE LIKE
followed by an INSERT SELECT
statement.
There are some cases where WITH DEEP COPY
is not supported.
Creating a rowstore deep copy of an existing table:
CREATE TABLE orig_table (a BIGINT, b BIGINT, SHARD KEY(a), SORT KEY(b));CREATE ROWSTORE TABLE ctdc_table LIKE orig_table WITH DEEP COPY;
ERROR 1706 ER_MEMSQL_FEATURE_LOCKDOWN: Feature 'CREATE Explicit ROWSTORE TABLE LIKE' is not supported by SingleStore.
Creating a reference deep copy of an existing table:
CREATE TABLE orig_table (a BIGINT, b BIGINT, SHARD KEY(a), SORT KEY(b));CREATE REFERENCE TABLE ctdc_table LIKE orig_table WITH DEEP COPY;
ERROR 1706 ER_MEMSQL_FEATURE_LOCKDOWN: Feature 'CREATE REFERENCE TABLE LIKE' is not supported by SingleStore.
CREATE TABLE WITH DEEP COPY Same Database
CREATE TABLE orig_table (a BIGINT, b BIGINT, SHARD KEY(a), SORT KEY(b));
INSERT INTO orig_table (a,b) VALUES (9,3), (5,2), (10,4), (12,7);
SELECT * FROM orig_table;
+----+----+
| a | b |
+----+----+
| 10 | 4 |
| 12 | 7 |
| 5 | 2 |
| 9 | 3 |
+----+----+
CREATE TABLE ctdc_table LIKE orig_table WITH DEEP COPY;
SELECT * FROM ctdc_table;
+----+----+
| a | b |
+----+----+
| 10 | 4 |
| 12 | 7 |
| 5 | 2 |
| 9 | 3 |
+----+----+
CREATE TABLE WITH DEEP COPY Across Databases
CREATE TABLE test1.orig_table (a BIGINT, b BIGINT, SHARD KEY(a), SORT KEY(b));
INSERT INTO test1.orig_table (a,b) VALUES (9,3), (5,2), (10,4), (12,7);
SELECT * FROM test1.orig_table;
+----+----+
| a | b |
+----+----+
| 10 | 4 |
| 12 | 7 |
| 5 | 2 |
| 9 | 3 |
+----+----+
CREATE TABLE test2.ctdc_table LIKE test1.orig_table WITH DEEP COPY;
SELECT * FROM test2.ctdc_table;
+----+----+
| a | b |
+----+----+
| 10 | 4 |
| 12 | 7 |
| 5 | 2 |
| 9 | 3 |
+----+----+
CREATE TABLE WITH SHALLOW COPY
The WITH SHALLOW COPY
feature creates a new table that will have the same structure as the original table.CREATE TABLE WITH SHALLOW COPY
is a metadata-only operation and will not duplicate the actual data.INSERT
, UPDATE
, DELETE
, ALTER TABLE
, or DROP TABLE
) on the original table will not affect the shallow copy table and vice versa.
The WITH SHALLOW COPY
feature is useful when you want to test new data models or perform operations on existing tables without risking live data.
Creating a shallow table copy is orders of magnitude faster and uses less disk space than creating a table (CREATE TABLE new_
) and then inserting all the rows from the original table into the new table (INSERT INTO new_
).
When a shallow copy of a table is created, SingleStore copies some of the in-memory metadata of the table from which the copy is created ("source table"), i.
To promote a shallow copy table to a main table, drop the original table and then rename the shallow copy table to the original table name.ALTER TABLE col_
before using the table.
When creating a shallow copy of an existing table:
-
The plancache of the source table is not reused.
-
Only the on-disk portion of the table is copied.
In-memory rowstore segment rows of the table are not copied. If you want the in-memory rowstore rows, do OPTIMIZE TABLE tbl_
on the source table before the shallow copy.name FLUSH -
Autostats on the new table are disabled.
As a result, queries on the new table may run with a worse execution plan. See Disabling and Enabling Automatic Statistics for how to enable autostats for the new table. -
The background merger is disabled for the new table.
This can be changed by running an ALTER TABLE
statement on the new table to enable it after the table is created:CREATE TABLE ctsc_table LIKE orig_table WITH SHALLOW COPY;ALTER TABLE ctsc_table BACKGROUND_MERGER=ON; -
An exclusive lock is taken on the original table which blocks operations on the table while a shallow copy is created.
-
To verify the memory usage of a shallow copy table, use the TABLE_
STATISTICS and INTERNAL_ TABLE_ STATISTICS views, just as with any other table. -
After a shallow copy is created, it is treated like any other columnstore table.
This means that information about shallow copies is not specifically tracked, and currently it is not possible to retrieve a list of all shallow copy tables from information_ schema.
CREATE TABLE WITH SHALLOW COPY Performance Example
This example creates a table ctsc_
with 4,194,304 rows, then does a shallow copy of it, and a full copy of it using a INSERT INTO.
statement.
First, create a table of dummy random id
values .
CREATE TABLE ctsc_r(id BINARY(16));
INSERT ctsc_r VALUES (sys_guid());
DELIMITER //DO DECLAREc BIGINT;BEGINSELECT COUNT(*) INTO c FROM ctsc_r;WHILE (c < 4*1024*1024) LOOPINSERT INTO ctsc_r SELECT sys_guid() FROM ctsc_r;SELECT COUNT(*) INTO c FROM ctsc_r;END LOOP;END //DELIMITER ;
SELECT COUNT(*) FROM ctsc_r;
+----------+
| COUNT(*) |
+----------+
| 4194304 |
+----------+
Make a shallow copy of all rows of ctsc_
to ctsc_
:
OPTIMIZE TABLE ctsc_r FLUSH;CREATE TABLE ctsc_r2 LIKE ctsc_r WITH SHALLOW COPY;/* shallow copy time = 0.02 sec */
SELECT COUNT(*) FROM ctsc_r2;
+----------+
| COUNT(*) |
+----------+
| 4194304 |
+----------+
All rows were copied to ctsc_
and took 0.
Next, make a full copy of all rows of ctsc_
to ctsc_
:
CREATE TABLE ctsc_r3 LIKE ctsc_r;INSERT INTO ctsc_r3 SELECT id FROM ctsc_r;/* full copy time = 4.86 sec */
SELECT COUNT(*) FROM ctsc_r3;
+----------+
| COUNT(*) |
+----------+
| 4194304 |
+----------+
All rows were copied to ctsc_
; however, it took almost 5 seconds to execute.
Restrictions
The WITH SHALLOW COPY
option is not supported for the following operations: copying across databases, copying rowstore tables, or copying temporary tables.
Across databases:
CREATE TABLE test1.orig_table (a BIGINT, b BIGINT, SHARD KEY(a), SORT KEY(b));CREATE TABLE test2.ctsc_table LIKE test1.orig_table WITH SHALLLOW COPY;
ERROR 1706 (HY000): Feature 'shallow copy of a table across databases' is not supported by SingleStore.
Rowstore tables:
CREATE ROWSTORE TABLE orig_table (a BIGINT, b BIGINT, SHARD KEY(a));CREATE TABLE ctsc_table LIKE orig_table WITH SHALLOW COPY;
ERROR 1706 (HY000): Feature 'shallow copy of non-columnstore tables' is not supported by SingleStore.
Temporary or rowstore global temporary tables:
CREATE ROWSTORE GLOBAL TEMPORARY TABLE orig_table (a BIGINT, b BIGINT, SHARD KEY(a));CREATE TABLE ctsc_table LIKE orig_table WITH SHALLOW COPY;
ERROR 1706 (HY000): Feature 'shallow copy of temporary and global temporary tables' is not supported by SingleStore.
CREATE TABLE with COMPRESSION = SPARSE
SingleStore Helios supports sparse data compression for rowstore tables.
Columns that use sparse data compression only store non-NULL
data values.
Sparse compression has the following limitations:
-
The
SPARSE
clause cannot be used for key columns.However, if a rowstore table uses sparse data compression using the COMPRESSION = SPARSE
clause, then the key columns are stored in-row. -
The
SPARSE
clause cannot be used for columns where the non-NULL
size of the column is greater than 15 bytes.
Refer to the Data Types topic for details.
CREATE TABLE with COMPRESSION = SPARSE All Sparse Columns
The following example demonstrates the COMPRESSION = SPARSE
clause.
CREATE ROWSTORE TABLE transaction_1(id BIGINT NOT NULL,explanation VARCHAR(70),shares DECIMAL(18, 2),share_price DECIMAL(18, 2),total_amount as shares * share_price PERSISTED DECIMAL(18,2),transaction_date DATE,dividend_exdate DATE,misc_expenses DECIMAL(18, 2),country_abbreviation CHAR(6),correction_date DATE,settlement_date DATE) COMPRESSION = SPARSE;
CREATE TABLE with COMPRESSION = SPARSE Selected Sparse Columns
The following example demonstrates the SPARSE
clause.