Creates new table in the database.
tableName - name used to reference the table in SQL statements. Internally the
table name is used as directory name on the file system. It can contain both
ASCII and Unicode characters.
Table name containing spaces or
. must be enclosed in single quotes, for
tableName must be unique
columnName - name used to reference the columns of table. Just like table
name, column name is used as a part of file name internally. \
Although it does support both ASCII and Unicode characters, character restrictions
specific to the file system still apply.
Maximum number of columns in a table is 2,147,483,647
columnName must be unique in context of table and must not contain '.'
typeDef - column type name with additional
distinctValueEstimate- optionally you can hint QuestDB how many distinct values this column is going to have. QuestDB will use this value to size data structures used to support symbol. These data structures will resize themselves when necessary to allow QuestDB to function correctly. Under-estimating symbol value count might result in drop of performance whereas over-estimating - in higher disk space and memory consumption.
distinctValueEstimate is not specified, a configuration default is used
CACHE | NOCACHE- a flag to tell QuestDB how to cache a symbol.
CACHEmeans that QuestDB will use Java Heap based Map to resolve symbol values and keys. When column has large number of distinct symbol values (over 100,000) heap impact might be significant and depending on heap size might cause OutOfMemory error. To avoid Java Heap impact,
NOCACHEwill leverage off-heap structure, which can deal with larger value count but is slower.
Default option is
inlineIndexDef- when present, QuestDB will create and maintain index for
indexCapacityDef- storage options for the index
valueBlockSize- index storage parameter. This value is optional and will default to the value of configuration key
valueBlockSizetells QuestDB how many row IDs to store in a single storage block on disk. Consider the following example. Your table has 200 unique stock symbols and 1,000,000,000 stock quotes over time. Index will have to store 1,000,000,000/200 row IDs for each symbol, e.g. 5,000,000 per symbol. When
valueBlockSizeis 1,048,576 QuestDB will use 5 blocks to store the row IDs, but when
valueBlockSizeis 1,024, block count will be 4,883. To attain better performance the fewer blocks are used to store row IDs the better. At the same time over-sizing
valueBlockSizewill result in higher than necessary disk space usage.
castDef- casts type of cherry-picked column.
columnRefmust reference existing column in the
indexDef- instructs QuestDB to create an index for one of table's columns. This clause references column name to be indexed. The referenced column muse be of type
timestamp - references a column in new table, which will be the nominated
timestamp. Such column must be of type
The designated timestamp cannot be changed after table is created. This will be implemented in a future release.
partition by - the partitioning strategy for the
The partitioning strategy cannot be changed after table is created. A new table will have to be created.
designated timestamp and not partitioned.Without
Such table can accept data in any order.
With this setting, QuestDB enforce chronological order of
CREATE TABLE AS
Cloning existing SQL structure
When SQL is
select * from tab or any arbitrary SQL result, the table data will
be copied with the corresponding structure.
where false condition.
Create a new table using SQL structure and data
Let's assume we imported a text file into the table
now we want to turn this data into time series thru ordering trips by
pickup_time, assign dedicated timestamp and partition by month: