Create Databases and Tables

In DolphinDB, you can create databases and tables using either SQL statements or built-in functions.

Create Databases

This section will provide examples of how to create distributed databases and in-memory databases.

Create Distributed Databases

DolphinDB allows the creation of databases using either the CREATE DATABASE statementor the database function.

Before proceeding, make sure that you have administrator access or the DB_OWNER privilege (which can be checked with the getUserAccess function). If you do not have the necessary permissions, please contact the administrator for authorization. Alternatively, log in with the default admin account using login(`admin,`123456).

The following examples create a TSDB database "dfs://valuedb", which is value-partitioned with 365 partitions ranging from 2023.01.01 to 2023.12.31. Note that the directory must start with "dfs://", which identifies the distributed nature of the database, and the database name must be unique.

Create with CREATE DATABASE

CREATE DATABASE "dfs://valuedb" 
PARTITIONED BY VALUE(2023.01.01..2023.12.31),
ENGINE="TSDB"

For detailed usage, see create.

Create with the database function

database(directory="dfs://valuedb", partitionType=VALUE, 
  partitionScheme=2023.01.01..2023.12.31, engine='TSDB')

For detailed usage, see database.

The difference between the two lies in their return values. The CREATE DATABASE statement has no return values, while the database function returns a database handle.

Storage Engine (engine)

DolphinDB provides multiple storage engines for its distributed database system, including TSDB, OLAP, and PKEY. The choice of storage engine (specified with parameter engine) is made when the database is initially created. Once the engine is specified, it cannot be altered afterwards. For more storage engines, see Multi-model Database.

Data Partitioning (partitionType & partitionScheme)

DolphinDB distributed databases support the following basic partitioning types: VALUE, HASH, RANGE, LIST. Also, DolphinDB can distribute data by implementing multi-level partitioning, specified as COMPO, which combines basic partitioning types. For more details on how to choose partition strategy, see Data Partitioning.

For example, create a COMPO-partitioned database that utilizes both value partitioning and hash partitioning.

Create with CREATE DATABASE

CREATE DATABASE "dfs://compodb"
PARTITIONED BY VALUE(2020.01.01..2021.01.01),HASH([SYMBOL,25])

Create with the database function

db1 = database("", VALUE, 2020.01.01..2021.01.01)
db2 = database("", HASH, [SYMBOL,25])
compoDb=database(directory="dfs://compodb", partitionType=COMPO, partitionScheme=[db1,db2])

All tables within a single database share the same partitioning scheme. Configuring appropriate partitioning at database creation can help to achieve even data distribution. The recommended size for data partitions is between 100MB and 1GB.

Only databases using value partitioning or range partitioning can be expanded. See Add Partitions for details.

Create In-Memory Databases

An in-memory database is a purpose-built database that relies primarily on memory for data storage. It minimizes response time by eliminating the need to access disk. Furthermore, the ability to create partitioned in-memory tables allows parallel processing, leading to great improvements in data processing efficiency.

In-memory databases can only be created using the database function. Simply specify the directory parameter as an empty string. For example,

mdb = database(directory="", partitionType=VALUE, partitionScheme=1..10)

Create Tables

This section will provide examples of how to create DFS tables and in-memory tables within the databases we created earlier.

Create DFS Tables

Before proceeding, make sure that you are the owner of the database or have the DBOBJ_CREATE/DB_MANAGE privilege (which can be checked with the getUserAccess function). If you do not have the necessary permissions, please contact the administrator for authorization.

Create Partitioned DFS Tables

DolphinDB allows the creation of partitioned DFS tables using either the CREATE TABLE statement or the createPartitionedTable function.

The following examples create a partitioned table "pt" within the TSDB database "dfs://valuedb". We'll use the date column for partitioning and sym and time as the sort columns.

Create with CREATE TABLE

CREATE TABLE "dfs://valuedb"."pt"(
    date DATE,
    time TIME,
    sym SYMBOL,
    price DOUBLE
)
PARTITIONED BY date,
sortColumns=[`sym, `time]

For detailed usage, see create.

Create with the createPartitionedTable function

// obtain the handle of the database created above
valueDb = database("dfs://valuedb")
// create in-memory table schemaTb
schemaTb = table(1:0,`date`time`sym`price,[DATE,TIME,SYMBOL,DOUBLE])
// create a partitioned table based on the schema of schemaTb
pt = createPartitionedTable(dbHandle=valueDb, table=schemaTb, tableName=`pt, partitionColumns=`date, sortColumns=`sym`time)

For detailed usage, see createPartitionedTable.

Partitioning Column (partitionColumns)

partitionColumns defines the database's partitioning strategy. Ensure that the number of partitioning columns aligns with the chosen partition scheme.

For example, for database "dfs://compodb" which employs a two-level partitioning, two columns must be specified as partitioning columns when creating a partitioned table.

Create with CREATE TABLE

CREATE TABLE "dfs://compodb"."pt"(
    data DATE,
    time TIME,
    sym SYMBOL,
    price DOUBLE
)
partitionColumns=`date`sym

Create with the createPartitionedTable function

// obtain the handle of the database created above
compoDb = database("dfs://compodb")
// create in-memory table schemaTb
schemaTb = table(1:0,`date`time`sym`price,[DATE,TIME,SYMBOL,DOUBLE])
// create a partitioned table based on the schema of schemaTb
pt = createPartitionedTable(dbHandle=compoDb, table=schemaTb, tableName=`pt, partitionColumns=`date`sym)

Create Dimension (Non-Partitioned DFS) Tables

DolphinDB allows the creation of non-partitioned DFS tables, i.e., dimension table, using either the CREATE TABLE statement or the createDimensionTable function.

The following examples create a dimension table "dt" within the TSDB database "dfs://valuedb".

Create with CREATE TABLE

CREATE TABLE "dfs://valuedb"."dt"(
    date DATE,
    time TIME,
    sym SYMBOL,
    price DOUBLE
)
sortColumns=`sym`time

Create with the createDimensionTable function

// obtain the handle of the database created above
valueDb = database("dfs://valuedb")
// create in-memory table schemaTb
schemaTb = table(1:0,`date`time`sym`price,[DATE,TIME,SYMBOL,DOUBLE])
// create a dimension table based on the schema of schemaTb
dt = createDimensionTable(dbHandle=valueDb, table=schemaTb, tableName=`dt, sortColumns=`sym`time)

For detailed usage, see createDimensionTable.

Create In-Memory Tables

DolphinDB provides support for a variety types of in-memory tables, including regular, indexed, keyed, streaming, MVCC, and cached in-memory tables. The following will demonstrate how to create regular and partitioned in-memory tables. For more information on other types and instructions on how to create them, see Table - In-Memory Tables for details.

Create Regular In-Memory Tables

DolphinDB allows the creation of regular in-memory tables using either the CREATE LOCAL TEMPORARY TABLE statement or the table function.

The following examples create an in-memory table "t".

Create with CREATE LOCAL TEMPORARY TABLE

CREATE LOCAL TEMPORARY TABLE t(
      col1 INT,
      col2 DOUBLE,
      col3 STRING
)

Create with the table function

// create an empty table of a fixed data type
t = table(1:0, `ool1`col2`col3, [INT,DOUBLE,STRING])

// create a table with existing vectors
t = table(1 2 as col1, 1.1 2.2 as col2, `A1`B2 as col3)

For detailed usage, see table.

The difference between the two is that the CREATE statement can only create an empty table, while the table function allows you to convert other data forms into tables, i.e., create from existing data.

Create Partitioned In-Memory Tables

DolphinDB allows the creation of partitioned in-memory tables using the createPartitionedTable function.

The following example creates an partitioned in-memory table "mpt" within database "mdb".

// create an in-memory database
mdb = database("", VALUE, 1..10)
// create an in-memory table
t = table(1:0,`id`sym`price`qty,[INT,SYMBOL,DOUBLE,INT])
// create a partitioned in-memory table based on the schema of t
mpt = createPartitionedTable(dbHandle=mdb, table=t, tableName=`pt, partitionColumns=`id)