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),[partitionType(partitionScheme),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 in-memory tables, DFS tables, and dimension tables.

create table dbPath.tableName (
    schema[columnDescription]
)
[partitioned by partitionColumns] // This clause needs to be specified when creating a DFS table.

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”

partitionColumns is a string or a string vector indicating the partitioning column(s). For a composite partition, partitionColumns is a string vector.

Please refer to the related function createPartitionedTable / createTable 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

(1) create an in-memory table

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

partitionColumnIndex->-1
chunkPath->
colDefs->
name typeString typeInt comment
---- ---------- ------- -------
id   INT        4
val  DOUBLE     16

(2) create a DFS database

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

(3) create a DFS 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()
...
partitionColumnIndex->[0,1]
...

(4) create 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()
 ...
 partitionColumnIndex->-1
 ...

(5) Create 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