The script dbmaint.q contains utility functions for maintenance of partitioned database tables in kdb+. It should not be used for splayed tables which are not partitioned, tables stored as single files or in memory tables.
You should always check the effect of using these functions on a test database first. To test you can create a sample taq database with https://github.com/KxSystems/kdb/blob/master/tq.q, but note that you should test on a sample of your own database before attempting to use any of these functions in production.
A few of the functions either do not support nested columns or can only be used for certain nested types, these are noted below.
If you are making changes to current database you need to reload (\l .) to make modifications visible.
If the database you’ve been modifying is a tick database, remember to adjust the schema (tick/???.q) to reflect your changes to the data.
In the following:
dbdir
: a file symbol for the database folder
table
: the symbol naming a table
col
: the symbol name of a column
Examples:
The examples in this document use a trade dir generated with the following sample data.
n:100; trade:([]time:.z.t;sym:n?`3;price:n?10000f;size:n?10000);
.Q.dpft[`:data/taq;2023.01.01;`sym;`trade];on disk
data/taq
├── 2023.01.01
│ └── trade
│ ├── price
│ ├── size
│ ├── sym
│ └── time
└── symmeta
c | t f a
-----| -----
date | d
sym | s p
time | t
price| f
size | j addcol[dbdir;table;col;defaultvalue]
Adds new column col to table with value defaultvalue in each row.
Example:
addcol[`:data/taq;`trade;`noo;0h]Changes:
on disk
data/taq
├── 2023.01.01
│ └── trade
│ ├── noo
│ ├── price
│ ├── size
│ ├── sym
│ └── time
└── symmeta
c | t f a
-----| -----
date | d
sym | s p
time | t
price| f
size | j
noo | h castcol[dbdir;table;col;newtype]
Cast the values in the column to the newtype and save. newtype can be specified in short or long form, e.g. "f" or `float for a cast to float. This can be used to cast nested types as well, but to cast a nested character column to symbol use fncol instead.
Example:
castcol[`:data/taq;`trade;`size;`short]Changes:
meta
c | t f a
-----| -----
date | d
sym | s p
time | t
price| f
size | h clearattrcol[dbdir;table;col]
Remove any attributes from column col.
Example:
clearattrcol[`:data/taq;`trade;`sym]Changes:
meta
c | t f a
-----| -----
date | d
sym | s
time | t
price| f
size | j copycol[dbdir;table;oldcol;newcol]
Copy the values from oldcol into a new column named newcol, undefined in the table. This does not support nested columns.
Example:
copycol[`:data/taq;`trade;`size;`size2]Changes:
on disk
data/taq
├── 2023.01.01
│ └── trade
│ ├── price
│ ├── size
│ ├── size2
│ ├── sym
│ └── time
└── symmeta
meta
c | t f a
-----| -----
date | d
sym | s p
time | t
price| f
size | j
size2| j deletecol[dbdir;table;col]
Delete column col from table.
This doesn’t delete the col# files for nested columns (the files containing the actual values) – you will need to delete these manually.
Example:.
deletecol[`:data/taq/;`trade;`size]Changes:
on disk
data/taq
├── 2023.01.01
│ └── trade
│ ├── price
│ ├── sym
│ └── time
└── symmeta
c | t f a
-----| -----
date | d
sym | s p
time | t
price| f findcol[dbdir;table;col]
Print a list of the partition directories where col exists and its type in each, and a *NOT*FOUND* message for partition directories where col is missing.
Example:
findcol[`:data/taq;`trade;`iz]Output:
2023.02.17 10:23:09 column iz *NOT*FOUND* in `:data/taq/2023.01.01/tradefixtable[dbdir;table;goodpartition]
Adds missing columns to to all partitions of a table, given the location of a good partition. This doesn’t delete extra columns – do that manually. Also this does not add tables to partitions in which they are missing, use .Q.chk for that.
Example:
`:data/taq/2023.01.02/trade/ set .Q.en[`:data/taq] delete size from trade;
fixtable[`:data/taq;`trade;`:data/taq/2023.01.01/trade]Changes:
on disk
data/taq
├── 2023.01.01
│ └── trade
│ ├── price
│ ├── size
│ ├── sym
│ └── time
├── 2023.01.02
│ └── trade
│ ├── price
│ ├── size
│ ├── sym
│ └── time
└── symfncol[dbdir;table;col;fn]
Apply a function to the list of values in col and save the results as its values.
Example:
fncol[`:data/taq;`trade;`price;2*]Changes:
before
q)select price from trade
price
--------
4812.041
1399.557
9491.982
5034.046
4882.605
...after
q)select price from trade
price
--------
9624.081
2799.113
18983.96
10068.09
9765.209
...listcols[dbdir;table]
List the columns of table (relies on the first partition).
Example:
listcols[`:data/taq;`trade]Output:
`sym`time`price`sizerenamecol[dbdir;table;oldname;newname]
Rename column oldname to newname, which must be undefined in the table. This does not support nested columns.
Example:
renamecol[`:data/taq;`trade;`woz;`iz]Changes:
on disk
data/taq
├── 2023.01.01
│ └── trade
│ ├── price
│ ├── size
│ ├── sym
│ └── time
└── symmeta
c | t f a
-----| -----
date | d
sym | s p
time | t
price| f
size | j reordercols[dbdir;table;neworder]
Reorder the columns of table. neworder is a full list of the column names as they appear in the updated table.
Example:
reordercols[`:data/taq;`trade;reverse cols trade]Changes:
meta
c | t f a
-----| -----
date | d
size | j
price| f
sym | s p
time | t setattrcol[dbdir;table;col;newattr]
Apply an attribute to col. The data in the column must be valid for that attribute. No sorting occurs.
Example:
setattrcol[`:data/taq;`trade;`sym;`g]Changes:
meta
c | t f a
-----| -----
date | d
sym | s g
time | t
price| f
size | j addtable[dbdir;tablename;table]
Add a table called tablename with an empty table with the same schema as table created in each partition of the new table.
Example:
addtable[`:data/taq;`trade1;.Q.en[`:data/taq]([]time:0#0Nt;sym:0#`;price:0#0n;size:0#0)]Changes:
on disk
data/taq
├── 2023.01.01
│ ├── trade
│ │ ├── price
│ │ ├── size
│ │ ├── sym
│ │ └── time
│ └── trade1
│ ├── price
│ ├── size
│ ├── sym
│ └── time
└── symrentable[dbdir;old;new]
Rename table old to new.
Example:
rentable[`:data/taq;`trade;`transactions]Changes:
on disk
data/taq
├── 2023.01.01
│ └── transactions
│ ├── price
│ ├── size
│ ├── sym
│ └── time
└── sym