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 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 2.00.2, column names can only use letters, digits or underscores (_), and must start with letters.
Since version 2.00.2, 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 |
Dropping tables
-
To delete data from a DFS table, you can use function dropTable or truncate.
-
To delete specific records from an in-memory or DFS table, you can use SQL delete statement.
See Drop Database and Table for details.