CREATE TABLE
Creating tables is one of the most complicated operations for many databases because you might need to:
- Manually specify the engine
- Manually specify the indexes
- And even specify the data partitions or data shard
Databend aims to be easy to use by design and does NOT require any of those operations when you create a table. Moreover, the CREATE TABLE statement provides these options to make it much easier for you to create tables in various scenarios:
- CREATE TABLE: Creates a table from scratch.
- CREATE TABLE ... LIKE: Creates a table with the same column definitions as an existing one.
- CREATE TABLE ... AS: Creates a table and inserts data with the results of a SELECT query.
- CREATE TRANSIENT TABLE: Creates a table without storing its historical data for Time Travel.
- CREATE TABLE ... EXTERNAL_LOCATION: Creates a table and specifies an S3 bucket for the data storage instead of the FUSE engine.
CREATE TABLE
CREATE [ OR REPLACE ] [ TRANSIENT ] TABLE [ IF NOT EXISTS ] [ <database_name>. ]<table_name>
(
<column_name> <data_type> [ NOT NULL | NULL ]
[ { DEFAULT <expr> } ]
[ AS (<expr>) STORED | VIRTUAL ]
[ COMMENT '<comment>' ],
<column_name> <data_type> ...
...
)
-
For available data types in Databend, see Data Types.
-
Databend suggests avoiding special characters as much as possible when naming columns. However, if special characters are necessary in some cases, the alias should be enclosed in backticks, like this: CREATE TABLE price(`$CA` int);
-
Databend will automatically convert column names into lowercase. For example, if you name a column as Total, it will appear as total in the result.
CREATE TABLE ... LIKE
Creates a table with the same column definitions as an existing table. Column names, data types, and their non-NUll constraints of the existing will be copied to the new table.
Syntax:
CREATE TABLE [IF NOT EXISTS] [db.]table_name
LIKE [db.]origin_table_name
This command does not include any data or attributes (such as CLUSTER BY
, TRANSIENT
, and COMPRESSION
) from the original table, and instead creates a new table using the default system settings.
TRANSIENT
andCOMPRESSION
can be explicitly specified when you create a new table with this command. For example,
create transient table t_new like t_old;
create table t_new compression='lz4' like t_old;
CREATE TABLE ... AS
Creates a table and fills it with data computed by a SELECT command.
Syntax:
CREATE TABLE [IF NOT EXISTS] [db.]table_name
AS SELECT query
This command does not include any attributes (such as CLUSTER BY, TRANSIENT, and COMPRESSION) from the original table, and instead creates a new table using the default system settings.
TRANSIENT
andCOMPRESSION
can be explicitly specified when you create a new table with this command. For example,
create transient table t_new as select * from t_old;
create table t_new compression='lz4' as select * from t_old;
CREATE TRANSIENT TABLE
Creates a transient table.
Transient tables are used to hold transitory data that does not require a data protection or recovery mechanism. Dataebend does not hold historical data for a transient table so you will not be able to query from a previous version of the transient table with the Time Travel feature, for example, the AT clause in the SELECT statement will not work for transient tables. Please note that you can still drop and undrop a transient table.
Transient tables help save your storage expenses because they do not need extra space for historical data compared to non-transient tables. See example for detailed explanations.
Concurrent modifications (including write operations) on transient tables may cause data corruption, making the data unreadable. This defect is being addressed. Until fixed, please avoid concurrent modifications on transient tables.
Syntax:
CREATE TRANSIENT TABLE ...