create

The create statement is used to create a database or a table. The syntax is as follows:

create DFS databases

The create statement only supports creating DFS databases.

create database directory partitioned by partitionType(partitionScheme),
[engine='OLAP'], [atomic='TRANS'], [chunkGranularity='TABLE']

Please refer to the related function database for details. The number of partitionType indicates the partition levels. You can specify one to three partitionType for a database and use more than one partitionType to create a composite database.

create tables

This statement only supports creating regular in-memory tables and DFS tables.

create table dbPath.tableName (
    schema[columnDescription]
)
[partitioned by partitionColumns],
[sortColumns|primaryKey],
[keepDuplicates=ALL],
[sortKeyMappingFunction]
[softDelete=false]
[comment]

dbPath is a string indicating the path of a dfs database.

tableName is a string indicating the table name, or a vector indicating the table object.

schema indicates the table schema, including two columns: columnName and columnType.

columnDescription uses keywords to add a description to a column, including:

  • comment adds a comment to a column;
  • compress specifies the compression method, which includes: "lz4", "delta", "zstd", "chimp".

partitionColumns specifies the partitioning column(s). For a composite partition, this parameter is a STRING vector.

comment is a STRING scalar for setting a comment for a DFS table.

Please refer to the related function createPartitionedTable / createDimensionTable for details.

Create temporary in-memory tables

To create a temporary in-memory table, add keywords local temporary (case insensitive) to create:

create local temporary table  tableName(
schema
) [on commit preserve rows]

where,

tableName is a string indicating the table name, or a variable of the table object

schema is the table schema which contains 2 columns: columnName and columnType.

on commit preserve rows (optional) specifies that the temporary table is session-specific. It is case-insensitive.

Note:

  • In DolphinDB, the create local temporary table statement is equivalent to create table as it creates a local temporary in-memory table that is only valid in the current session.
  • Currently, global temporary tables and the keyword on commit delete rows are not supported.

Examples

Creating an In-memory Table

create table tb(
            id SYMBOL,
            val DOUBLE
    )
     go;   //Parse and run codes with the go statement first, otherwise an error of unrecognized variable tb will be reported.
     tb.schema()

/* output
    partitionColumnIndex->-1
    chunkPath->
    colDefs->
    name typeString typeInt comment
    ---- ---------- ------- -------
    id   SYMBOL     17
    val  DOUBLE     16
*/

Creating an OLAP Database

if(existsDatabase("dfs://test")) dropDatabase("dfs://test")
     create database "dfs://test" partitioned by VALUE(1..10), HASH([SYMBOL, 40]), engine='OLAP'
Creating a partitioned table
create table "dfs://test"."pt"(
        id INT,
        deviceId SYMBOL,
        date DATE[comment="time_col", compress="delta"],
        value DOUBLE,
        isFin BOOL
    )
    partitioned by ID, deviceID,

pt = loadTable("dfs://test","pt")
pt.schema()

/* output 
partitionSchema->([1,2,3,4,5,6,7,8,9,10],40)
partitionSites->
partitionColumnType->[4,17]
partitionTypeName->[VALUE,HASH]
chunkGranularity->TABLE
chunkPath->
partitionColumnIndex->[0,1]
colDefs->
name     typeString typeInt comment 
-------- ---------- ------- --------
id       INT        4               
deviceId SYMBOL     17              
date     DATE       6       time_col
value    DOUBLE     16              
isFin    BOOL       1               

partitionType->[1,5]
partitionColumnName->[id,deviceId]
*/

Creating a dimension table

create table "dfs://test"."pt1"(
        id INT,
        deviceId SYMBOL,
        date DATE[comment="time_col", compress="delta"],
        value DOUBLE,
        isFin BOOL
    )

 pt1 = loadTable("dfs://test","pt1")
 pt1.schema()

/* output
chunkPath->
partitionColumnIndex->-1
colDefs->
name     typeString typeInt comment 
-------- ---------- ------- --------
id       INT        4               
deviceId SYMBOL     17              
date     DATE       6       time_col
value    DOUBLE     16              
isFin    BOOL       1
*/

Creating a TSDB Database

if(existsDatabase("dfs://test")) dropDatabase("dfs://test")
create database "dfs://test" partitioned by VALUE(1..10), HASH([SYMBOL, 40]), engine='TSDB'

Creating a partitioned table

create table "dfs://test"."pt"(
        id INT,
        deviceId SYMBOL,
        date DATE[comment="time_col", compress="delta"],
        value DOUBLE,
        isFin BOOL
    )
    partitioned by ID, deviceID,
    sortColumns=[`deviceId, `date],
    keepDuplicates=ALL

 pt = loadTable("dfs://test","pt")
 pt.schema()

/* output
engineType->TSDB
keepDuplicates->ALL
partitionColumnIndex->[0,1]
colDefs->
name     typeString typeInt extra comment 
-------- ---------- ------- ----- --------
id       INT        4                     
deviceId SYMBOL     17                    
date     DATE       6             time_col
value    DOUBLE     16                    
isFin    BOOL       1                     

partitionType->[1,5]
partitionColumnName->[id,deviceId]
partitionSchema->([1,2,3,4,5,6,7,8,9,10],40)
partitionSites->
partitionColumnType->[4,17]
partitionTypeName->[VALUE,HASH]
sortColumns->[deviceId,date]
softDelete->false
tableOwner->admin
chunkGranularity->TABLE
chunkPath-> 
*/

Creating a dimension table

create table "dfs://test"."pt1"(
        id INT,
        deviceId SYMBOL,
        date DATE[comment="time_col", compress="delta"],
        value DOUBLE,
        isFin BOOL
    )
    sortColumns=[`deviceId, `date]

 pt1 = loadTable("dfs://test","pt1")
 pt1.schema()

/* output
sortColumns->[deviceId,date]
softDelete->false
tableOwner->admin
engineType->TSDB
keepDuplicates->ALL
chunkGranularity->TABLE
chunkPath->
partitionColumnIndex->-1
colDefs->
name     typeString typeInt extra comment 
-------- ---------- ------- ----- --------
id       INT        4                     
deviceId SYMBOL     17                    
date     DATE       6             time_col
value    DOUBLE     16                    
isFin    BOOL       1 
*/

Creating a Temporary In-memory Table

create local temporary table "tb" (
        id SYMBOL,
        val DOUBLE
    ) on commit preserve rows
     tb.schema()

    partitionColumnIndex->-1
    chunkPath->
    colDefs->
    name typeString typeInt extra comment
    ---- ---------- ------- ----- -------
    id   SYMBOL     17
    val  DOUBLE     16