Tables
DDL - Tables
These functions are used to create and modify data tables in HEAVY.AI.
Nomenclature Constraints
Table names must use the NAME format, described in regex notation as:
Table and column names can include quotes, spaces, and the underscore character. Other special characters are permitted if the name of the table or column is enclosed in double quotes (" ").
Spaces and special characters other than underscore (_) cannot be used in Heavy Immerse.
Column and table names enclosed in double quotes cannot be used in Heavy Immerse
CREATE TABLE
Create a table named <table>
specifying <columns>
and table properties.
Supported Datatypes
* In OmniSci release 4.4.0 and higher, you can use existing 8-byte DATE
columns, but you can create only 4-byte DATE
columns (default) and 2-byte DATE
columns (see DATE ENCODING FIXED(16)
).
For more information, see Datatypes and Fixed Encoding.
For geospatial datatypes, see Geospatial Primitives.
Examples
Create a table named tweets
and specify the columns, including type, in the table.
Create a table named delta and assign a default value San Francisco
to column city.
Default values currently have the following limitations:
Only literals can be used for column DEFAULT values; expressions are not supported.
You cannot define a DEFAULT value for a shard key. For example, the following does not parse:
CREATE TABLE tbl (id INTEGER NOT NULL DEFAULT 0, name TEXT, shard key (id)) with (shard_count = 2);
For arrays, use the following syntax:
ARRAY[A, B, C, …. N]
The syntax
{A, B, C, ... N}
is not supported.Some literals, like NUMERIC and GEO types, are not checked at parse time. As a result, you can define and create a table with malformed literal as a default value, but when you try to insert a row with a default value, it will throw an error.
Supported Encoding
WITH Clause Properties
Sharding
Sharding partitions a database table across multiple servers so each server has a part of the table with the same columns but with different rows. Partitioning is based on a sharding key defined when you create the table.
Without sharding, the dimension tables involved in a join are replicated and sent to each GPU, which is not feasible for dimension tables with many rows. Specifying a shard key makes it possible for the query to execute efficiently on large dimension tables.
Currently, specifying a shard key is useful for joins, only:
If two tables specify a shard key with the same type and the same number of shards, a join on that key only sends a part of the dimension table column data to each GPU.
For multi-node installs, the dimension table does not need to be replicated and the join executes locally on each leaf.
Constraints
A shard key must specify a single column to shard on. There is no support for sharding by a combination of keys.
One shard key can be specified for a table.
Data are partitioned according to the shard key and the number of shards (
shard_count
).A value in the column specified as a shard key is always sent to the same partition.
The number of shards should be equal to the number of GPUs in the cluster.
Sharding is allowed on the following column types:
DATE
INT
TEXT ENCODING DICT
TIME
TIMESTAMP
Tables must share the dictionary for the column to be involved in sharded joins. If the dictionary is not specified as shared, the join does not take advantage of sharding. Dictionaries are reference-counted and only dropped when the last reference drops.
Recommendations
Set
shard_count
to the number of GPUs you eventually want to distribute the data table across.Referenced tables must also be
shard_count
-aligned.Sharding should be minimized because it can introduce load skew accross resources, compared to when sharding is not used.
Examples
Basic sharding:
Sharding with shared dictionary:
Temporary Tables
Using the TEMPORARY argument creates a table that persists only while the server is live. They are useful for storing intermediate result sets that you access more than once.
Adding or dropping a column from a temporary table is not supported.
Example
CREATE TABLE AS SELECT
Create a table with the specified columns, copying any data that meet SELECT statement criteria.
WITH Clause Properties
Examples
Create the table newTable
. Populate the table with all information from the table oldTable
, effectively creating a duplicate of the original table.
Create a table named trousers
. Populate it with data from the columns name
, waist
, and inseam
from the table wardrobe
.
Create a table named cosmos
. Populate it with data from the columns star
and planet
from the table universe where planet has the class M.
ALTER TABLE
Examples
Rename the table tweets to retweets.
Rename the column source to device in the table retweets.
Add the column pt_dropoff to table tweets with a default value point(0,0).
Add multiple columns a, b, and c to table table_one with a default value of 15
for column b.
Default values currently have the following limitations:
Only literals can be used for column DEFAULT values; expressions are not supported.
For arrays, use the following syntax:
ARRAY[A, B, C, …. N]
. The syntax{A, B, C, ... N}
is not supported.Some literals, like NUMERIC and GEO types, are not checked at parse time. As a result, you can define and create a table with a malformed literal as a default value, but when you try to insert a row with a default value, it throws an error.
Add the column lang to the table tweets using a TEXT ENCODING DICTIONARY.
Add the columns lang and encode to the table tweets using a TEXT ENCODING DICTIONARY for each.
Drop the column pt_dropoff from table tweets.
Limit on-disk data growth by setting the number of allowed epoch rollbacks to 50:
You cannot add a dictionary-encoded string column with a shared dictionary when using ALTER TABLE ADD COLUMN.
Currently, HEAVY.AI does not support adding a geo column type (POINT, LINESTRING, POLYGON, or MULTIPOLYGON) to a table.
HEAVY.AI supports ALTER TABLE RENAME TABLE and ALTER TABLE RENAME COLUMN for temporary tables. HEAVY.AI does not support ALTER TABLE ADD COLUMN to modify a temporary table.
DROP TABLE
Deletes the table structure, all data from the table, and any dictionary content unless it is a shared dictionary. (See the Note regarding disk space reclamation.)
Example
DUMP TABLE
Archives data and dictionary files of the table <table>
to file <filepath>
.
Valid values for <compression_program>
include:
gzip (default)
pigz
lz4
none
If you do not choose a compression option, the system uses gzip if it is available. If gzip is not installed, the file is not compressed.
The file path must be enclosed in single quotes.
Dumping a table locks writes to that table. Concurrent reads are supported, but you cannot import to a table that is being dumped.
The
DUMP
command is not supported on distributed configurations.You must have a least GRANT CREATE ON DATABASE privilege level to use the
DUMP
command.
Example
RENAME TABLE
Rename a table or multiple tables at once.
Examples
Rename a single table:
Swap table names:
Swap table names multiple times:
RESTORE TABLE
Restores data and dictionary files of table <table>
from the file at <filepath>
. If you specified a compression program when you used the DUMP TABLE
command, you must specify the same compression method during RESTORE
.
Restoring a table decompresses and then reimports the table. You must have enough disk space for both the new table and the archived table, as well as enough scratch space to decompress the archive and reimport it.
The file path must be enclosed in single quotes.
You can also restore a table from archives stored in S3-compatible endpoints:
s3_region
is required. All features discussed in the S3 import documentation, such as custom S3 endpoints and server privileges, are supported.
Restoring a table locks writes to that table. Concurrent reads are supported, but you cannot import to a table that is being restored.
The
RESTORE
command is not supported on distributed configurations.You must have a least GRANT CREATE ON DATABASE privilege level to use the
RESTORE
command.
Do not attempt to use RESTORE TABLE with a table dump created using a release of HEAVY.AI that is higher than the release running on the server where you will restore the table.
Examples
Restore table tweets
from /opt/archive/tweetsBackup.gz:
Restore table tweets
from a public S3 file or using server privileges (with the allow-s3-server-privileges
server flag enabled):
Restore table tweets
from a private S3 file using AWS access keys:
Restore table tweets
from a private S3 file using temporary AWS access keys/session token:
Restore table tweets
from an S3-compatible endpoint:
TRUNCATE TABLE
Use the TRUNCATE TABLE
statement to remove all rows from a table without deleting the table structure.
This releases table on-disk and memory storage and removes dictionary content unless it is a shared dictionary. (See the note regarding disk space reclamation.)
Removing rows is more efficient than using DROP TABLE. Dropping followed by recreating the table invalidates dependent objects of the table requiring you to regrant object privileges. Truncating has none of these effects.
Example
When you DROP or TRUNCATE, the command returns almost immediately. The directories to be purged are marked with the suffix \_DELETE_ME_. The files are automatically removed asynchronously.
In practical terms, this means that you will not see a reduction in disk usage until the automatic task runs, which might not start for up to five minutes.
You might also see directory names appended with \_DELETE_ME_. You can ignore these, with the expectation that they will be deleted automatically over time.
OPTIMIZE TABLE
Use this statement to remove rows from storage that have been marked as deleted via DELETE
statements.
When run without the vacuum option, the column-level metadata is recomputed for each column in the specified table. HeavyDB makes heavy use of metadata to optimize query plans, so optimizing table metadata can increase query performance after metadata widening operations such as updates or deletes. If the configuration parameter enable-auto-metadata-update
is not set, HeavyDB does not narrow metadata during an update or delete — metadata is only widened to cover a new range.
When run with the vacuum option, it removes any rows marked "deleted" from the data stored on disk. Vacuum is a checkpointing operation, so new copies of any vacuum records are deleted. Using OPTIMIZE with the VACUUM option compacts pages and deletes unused data files that have not been repopulated.
Beginning with Release 5.6.0, OPTIMIZE should be used infrequently, because UPDATE, DELETE, and IMPORT queries manage space more effectively.
VALIDATE
Performs checks for negative and inconsistent epochs across table shards for single-node configurations.
If VALIDATE
detects epoch-related issues, it returns a report similar to the following:
If no issues are detected, it reports as follows:
VALIDATE CLUSTER
Perform checks and report discovered issues on a running HEAVY.AI cluster. Compare metadata between the aggregator and leaves to verify that the logical components between the processes are identical.
VALIDATE CLUSTER
also detects and reports issues related to table epochs. It reports when epochs are negative or when table epochs across leaf nodes or shards are inconsistent.
Examples
If VALIDATE CLUSTER
detects issues, it returns a report similar to the following:
If no issues are detected, it will report as follows:
You can include the WITH(REPAIR_TYPE)
argument. (REPAIR_TYPE='NONE')
is the same as running the command with no argument. (REPAIR_TYPE='REMOVE')
removes any leaf objects that have issues. For example:
Epoch Issue Example
This example output from the VALIDATE CLUSTER
command on a distributed setup shows epoch-related issues:
Last updated