Table

Tables are database objects that hold all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record. For each column in a table, the column name and data type must be specified. You can insert, delete, update or query tables in DolphinDB.

Tables can be divided into the following categories:

  • In-memory tables
    Table Function Description
    In-memory table table Regular in-memory table for temporary data storage
    Partitioned in-memory table createPartitionedTable Enables parallel computing on large datasets through data partitioning
    Indexed table indexedTable Enables fast storage and retrieval of key-value pairs, optimized for range queries
    Keyed table keyedTable Enables fast storage and retrieval of key-value pairs, optimized for point queries
    Stream table

    streamTable

    haStreamTable

    		 Enables real-time ingestion and storage of streaming data
    MVCC table mvccTable Optimized for read-intensive workloads using multi-version concurrency control
    Cached table cachedTable Enables caching and scheduled updates for data which does not require immediate synchronization
    IMOLTP table createIMOLTPTable Enables primary and secondary keys, optimized for low-latency queries

  • DFS tables

    Table Function Description

    Partitioned DFS table

    		 createPartitionedTable

    Regular DFS table for distributed storage and parallel processing of large datasets

    Dimension table

    		 createDimensionTable

    Non-partitioned DFS table for storing small datasets that are infrequently updated

Creating tables

Before version 1.30.14, column names can only use letters, digits or underscores (_), and must start with letters.

Since version 1.30.14, column names generated by pivot by or addColumn can contain special characters or start with digits.

Note:

  • When a column name containing special characters or starting with digits is referenced in a SQL statement, enclose it in double quotes and use an underscore as an identifier before it, for example: _"IBM.N", _"000001.SH";

  • Column names containing special characters or starting with digits can also be accessed by tb["col"] or tb."col".

  • If a column generated by pivot by is composed of NULL values, it is named as "NULL". To refer to the column, use _"NULL" to follow the first note mentioned above.

To allow for code compatibility with previous versions, the configuration parameter removeSpecialCharInColumnName is introduced. The default value is false, indicating that column names can contain special characters. Set to true to be compatible with previous versions.

Example 1. 3 ways to create an in-memory table

(1) table(X as col, [X1 as col1], [X2 as col2], .....)

t0=table(1 2 3 as a, `x`y`z as b, 10.8 7.6 3.5 as c);
t0;
a b c
1 x 10.8
2 y 7.6
3 z 3.5

(2) table(X, [X1], [X2], .....)

x=1 2 3;
y=4 5 6;
t1=table(x,y);
t1;
x y
1 4
2 5
3 6

(3) table(capacity:size, colNames, colTypes)

t2=table(200:10, `name`id`value, [STRING,INT,DOUBLE]);
t2;
name id value
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0

Example 2. Create and access tables whose column names contain special characters

t3=table(1 2 3 as `_a, 4 5 6 as "2 ab");
t3;
_a 2 ab
1 4
2 5
3 6 
select _"_a" as "_aa", _"2 ab" as "2ab" from t3;
_aa 2ab
1 4
2 5
3 6

Example 3. Convert vectors/matrices into tables

a=([1,2],[3.2,4.3],[2019.01.02,2019.05.03]);
table(a);
C0 C1 C2
1 3.2 2019.01.02
2 4.3 2019.05.03 
m=1..12$3:4;
table(m);
C0 C1 C2 C3
1 4 7 10
2 5 8 11
3 6 9 12

Accessing tables

Example 1. Use <tableName>([X],[Y]) to access tables, where X and Y are scalars/pairs for selecting rows and columns respectively. The range of table indexing starts from 0 and is upper bound exclusive. For examples, 1:3 means 1 and 2; 2:0 indicates 1 and 0.

t1[1:3, 1];
y
5
6 
t1[,t1.columns()-1];
y
4
5
6 
t1.keys();
// output
["x","y"]

t1.values();
// output
([1,2,3],[4,5,6])

Example 2. Access tables with conditions specified.

t1[t1.x>2];      // retrieve the records where x>2
or
t1[t1[`x]>2];
x y
3 6 
t1[t1.x in (1 3)];       // retrieve the records where x=1 or x=3
x y
1 4
3 6 
t1[t1.x>1 && t1.y<6];       // retrieve the records where x>1 and y<6
x y
2 5

Updating tables

Example 1. Update in-memory tables with conditions specified.

t1[`x, t1[`x] < 2] = 3
or
t1[`x, <x < 2>] = 3
x y
3 4
2 5
3 6

Example 2. Create an empty table and then update it

t = table(100:0, `x`y`z, `STRING`DATE`DOUBLE);
//Create a table with columns x, y, and z, and column types STRING, DATE, and DOUBLE. Its initial capacity is 100 and size is 0.

t;
x y z 
insert into t values(take(`MS,3),2010.01.01 2010.01.02 2010.01.03, 1 2 3);
t;
x y z
MS 2010.01.01 1
MS 2010.01.02 2
MS 2010.01.03 3

To add or update table columns:

t=table(1 2 3 as id, 4 5 6 as value);
t;
id value
1 4
2 5
3 6 
t[`id`name]=[7 8 9, `IBM`MSFT`GOOG];
t;
id value name
7 4 IBM
8 5 MSFT
9 6 GOOG

Example 3. Update tables with SQL update clause

n=10
colNames = `time`sym`id
colTypes = [DATE,SYMBOL,INT]
t = table(n:0, colNames, colTypes)
insert into t values(2020.01.05 13:30:10.008, `A1, 1)
insert into t values(2020.01.06 13:30:10.008, `A2, 2)
// When the data types of inserted temporal values do not match the column types, the inserted data is automatically converted.
insert into t values(2020.06M, `A3, 3)

update t set time=2020.06.13 13:30:10 where sym=`A1
select * from t
time sym id
2020.06.13 A1 1
2020.01.06 A2 2
2020.06.01 A3 3