Modify Table Schema

DolphinDB supports flexible table schema modifications through SQL statement and built-in functions, allowing you to add, delete, rename, reorder columns, and change data types.

Use the following script to create an in-memory table t and a DFS table pt, which are used in the examples below.

t = table(2023.10.01 + take(0..9,100) as date, take(["A01","B01","C01","D01"],100) as sym, 1..100 as val)
db = database("dfs://olapdemo",VALUE,2023.10.01..2023.10.10)
pt = db.createPartitionedTable(t,`pt,`date)
pt.append!(t)

Add Columns

The addColumn function can be used to add columns to DFS tables, as well as in-memory tables.

For example, to add a column "new" to table t and pt:

addColumn(t,`new,DOUBLE)
addColumn(pt,`new,DOUBLE)

The UPDATE statement and the update! function can be used to add columns with specified names to in-memory tables:

For example, to add a column "new_1" to table t:

// SQL UPDATE
UPDATE t SET new_1 = 1..100

// or the update! function
update!(t,"new_1",1..100)

Columns can also be added to in-memory tables by assignment.

For example, to add a column "new_2" to table t:

t[`new_2] = string(NULL)

Note:

  • To add columns to stream tables, only the addColumn function is supported.
  • After adding new columns, data with the original schema can be inserted until the first insert of data with the new schema. Afterwards, only new-schema data is accepted.

Delete Columns

The dropColumns! function can be used to delete columns from in-memory tables and DFS tables (for OLAP engine only):

dropColumns!(t,`val)
dropColumns!(pt,`val)

Note: The SYMBOL column and any partitioning columns cannot be deleted from the DFS table.

Reorder Columns

The reorderColumns! function can be used to reorder the columns of non-partitioned and non-shared in-memory tables.

For example, to change the order of columns "sym" and "val":

reorderColumns!(t,`date`val`sym)

If only some columns are reordered, they will be arranged as the first few columns in the specified order, followed by the remaining columns in the original order.

For example, to specify columns "date" and "val" as the first two columns:

reorderColumns!(t,`date`val)

Rename Columns

The rename! function can be used to modify the column names of non-shared in-memory tables and DFS tables (for OLAP engine only).

For example, to rename column "sym" as "sym_new":

rename!(t,`sym,`sym_new)

Modify Column Type

The replaceColumn! function can be used to modify the data types of columns. This applies tonon-shared in-memory tables and DFS tables (for OLAP engine only).

For non-shared in-memory tables, the newCol vector must contain the same number of elements as the rows in table. After execution, the data type and values of the colName column match those of newCol.

For example, to change the data type of column "val" in the in-memory table t to DOUBLE:

replaceColumn!(table=t,colName="val",newCol=double(t.val))

For DFS tables in the OLAP engine, replaceColumn! can only modify the data types of columns, so there is no restriction on the length of the newCol vector. After execution, the data type of the colName column matches newCol and the original data remains intact.

For example, to change the data type of column "val" in the DFS table pt to DOUBLE:

newType = array(DOUBLE,0,1) 
replaceColumn!(pt,"val",newType)

Rename Tables

The renameTable function can be used to rename DFS tables. After modification, it is necessary to reload the metadata of the table with the loadTable function:

renameTable(db,"pt","pt_new") 
pt = loadTable(db,"pt_new")

Note: To improve performance when modifying DFS tables with rename!, dropColumns!, or replaceColumn!, manually flush the cache first using flushOLAPCache. This avoids delays caused by automatic cache flushing and redo log cleanup that occur during these operations.