create

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.

This statement only supports creating standard in-memory tables, DFS tables, and dimension tables.

create table dbPath.tableName (
  schema[columnDescription]
)
[partitioned by partitionColumns],
[sortColumns],
[keepDuplicates=ALL]

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.

Parameters for TSDB storage engine only:

sortColumns is a required argument for TSDB engine. It is a string scalar/vector indicating the columns based on which the table is sorted.

keepDuplicates specifies how to deal with records with duplicate sortColumns values. The default value is ALL. It can have the following values:

• ALL: keep all records

• LAST: only keep the last record

• FIRST: only keep the first record

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

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.

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

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

(2) create a DFS database in TSDB engine

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

(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,
    sortColumns=[`deviceId, `date],
    keepDuplicates=ALL

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

    keepDuplicates->ALL
    engineType->TSDB
    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
    )
    sortColumns=[`deviceId, `date]

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

    sortColumns->[deviceId,date]
    engineType->TSDB
    keepDuplicates->ALL
    chunkGranularity->TABLE
    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