Schema Configuration

This page explains how to configure schema settings in a kdb Insights Enterprise package, including defining tables, columns, and keys within a database package.

A schema describes a collection of tables. Schemas are used to describe all of the tables within a kdb Insights Database.

Schemas can either be defined as a set of static YAML configuration files in a package or visually using the Web Interface. Within a package, this configuration sits under the tables directory. Each table within a schema is a collection of columns, attributes and metadata information.

tables/trace.yaml:

name: trace
description: Manufacturing trace data
type: partitioned
blockSize: 10000
prtnCol: updateTS
sortColsOrd: [sensorID]
sortColsDisk: [sensorID]
columns:
  - name: sensorID
    description: Sensor Identifier
    type: int
    attrMem: grouped
    attrOrd: parted
    attrDisk: parted
  - name: readTS
    description: Reading timestamp
    type: timestamp
  - name: captureTS
    description: Capture timestamp
    type: timestamp
  - name: valFloat
    description: Sensor value
    type: float
  - name: qual
    description: Reading quality
    type: byte
  - name: alarm
    description: Enumerated alarm flag
    type: byte
  - name: updateTS
    description: Ingestion timestamp
    type: timestamp

Fields

For each table in a schema, the following fields can be configured.

Table types

The list of supported table types is as follows:

Columns

For each column in a table, the following fields can be configured.

Types

Column types support all of the base datatypes supported in kdb+. For columns containing only atom values, use the singular form of the data type. To indicate that a column is intended to support vectors of a given type, use the plural of the type name, except for char, whose vector equivalent is string.

Type example

For a column of long numbers, use the type long.

text

Copy

x
-
0
1
2

For a column of vectors of long integers, use longs.

text

Copy

x
-----
0 1 2
4 5 6
7 8 9

For a column of vectors of characters, use string.

text

Copy

x
------
"abc"
"de"
"fghi"

To indicate a mixed type of 0h, provide the type field with an empty string.

- name: mixedData
 type: ""

Attributes

Attributes are used to tune the performance of queries in a kdb Insights database. You can set attributes differently depending on the different tiers of your database and you can tune attributes to optimize performance at each tier. Using attributes slightly increases storage space usage, RAM usage and processing required during ingest. Note that the sorted attribute is not the same as the sorting setting (sortColsOrd/sortColsDisk). The following column attributes are available:

• no attributes - requires a linear scan for filters against the column

• sorted (requires sorting) - ascending values - allows binary search for filters against the column

• parted (requires sorting) - maintains an index allowing constant time access to the start of a group of identical values

• grouped (does not require sorting) maintains a separate index for the column, identifying the positions of each distinct value. note - this index can take up significant storage space.

• unique - (requires all values be distinct) allows a constant-time lookup - typically used for primary keys

More information about attributes, their use, and tradeoffs is available in the kdb+ documentation.

Within the table configuration, an attribute property can be set on particular columns within the table. There are three levels of attributes that can be set, attrMem, attrOrd and attrDisk. The memory level attribute applies when data is in memory which is used for any real-time tiers (RDB) in your database configuration. The ordinal level attribute is used for data that is stored intermittently throughout the day. This applies to the IDB tier of a database. Lastly, the disk attribute applies to all disk tiers including the HDB and subsequent tiers such as object storage.

KX recommends that you use the parted attribute on the column that is used for filtering most frequently. You can only have one column in a table with the sorted or parted attribute. You can apply the grouped attribute to any column without needing to sort it. Setting the parted attribute requires sorting, which requires a significant amount of memory.

Modifications

Offline modifications to schemas are supported when using a package. You can make the following modifications:

• Adding tables

• Renaming tables

• Deleting tables

• Adding columns

• Renaming columns

• Removing columns

• Reordering columns

• Changing column attributes

• Changing data types of columns

• Enabling and disabling encryption

The Storage Manager performs schema modification during initialization. To trigger this process, you must first stop your package or Storage Manager, make the desired modification and then start it again. If SM detects a difference in the schema of the table currently stored on disk and the provided schema, it starts a schema conversion. SM does not accept any new data until the schema conversion is complete.

Modifying the package

Conversion operations are triggered by changing the schema in the package. Most schema changes can be expressed in a straightforward way by modifying the tables section. Table and column renames need to be specified explicitly to link tables and columns to their previous names by setting the oldName attribute. If a table or column is found that matches the oldName of a table or column, a rename takes place. To avoid confusing scenarios, oldName values for different tables or columns must not match, nor may they match the current name of another table or column. The restriction on columns applies within the columns of a table. Therefore more complex operations such as swapping two columns requires a multi-step process with only one column name being changed in each upgrade. Once the renaming is complete, it is recommended to update the schema again to remove the oldName values.

Step-by-step process

1. Teardown the package, either from the Web interface or the CLI.

2. Update the package configuration.

3. Start SM and DA processes (Before 1.4.0, wait for the conversion to end before starting DAPs), either from the Web Interface or the CLI.

4. Check the logs of EOI for conversion status. When the conversion starts, Performing on-disk conversion is logged by EOI. Then each operation is logged as it is performed. Upon successful completion, On-disk conversion complete, elapsed=XXX is logged with the actual elapsed time. If there is an object storage tier, the conversion checks if the object storage matches the schema in the package. Any discrepancies are printed out and the EOI process aborts.

Examples

Backfilling a column

When adding a new column, by default it is populated with the null values for its type. However, it may be desirable to populate it with calculated values instead. The backfill property can be used for this purpose.

See an example schema definition below. In this example, colB and colC are assumed to be newly-defined columns in your schema. The custom functions you wish to run to generate your new colB and colC are referenced in the backfill field.

tables/tbl.yaml:

name: tbl
columns:
  - name: sym
    description: Symbol name
    type: symbol
  - name: realTime
    description: Time
    type: timestamp
  - name: colA
    description: colA
    type: long
  - name: colB
    description: colB
    type: long
    backfill: .my.calcColB
  - name: colC
    description: colC
    type: float
    backfill: .my.calcColC

tables/trade.yaml:

name: trade
columns:
  - name: sym
    description: Symbol name
    type: symbol
  - name: realTime
    description: Time
    type: timestamp
  - name: size
    description: size
    type: long
  - name: price
    description: price
    type: float

With the custom function referenced, .my.calcColB and .my.calcColC are called for every partition (for intraday data, this means every interval individually) during the database conversion that adds your new column.

To provide the definition of the two functions, we must first create a package with a storage-manager entrypoint. The manifest.yaml file would look like:

name: test-package
version: 0.0.1
metadata:
  description: ''
  authors:
  - name: root
    email: ''
entrypoints:
  storage-manager: convert.q

And in the convert.q file we can define the function:

.my.calcColB:{[tn;part;data]
  exec `long$4 mavg colA from data
  }

.my.calcColC:{[tn;part;data]
  exec `float$sum size*price from .kxi.db.getTableData[`trade;part]
  }

To load the package, add it to the list of packages loaded by SM using the KXI_PACKAGES environment variable in the shard file:

   sm:
    [...]
      env:
        - name: KXI_PACKAGES
          value: "test-package:0.0.1"

The function is called with the following parameters:

• tn (symbol): the table name

• part (opaque): a unique partition identifier

• data (table): the contents of the table for the given partition

The function must return an atom or list of the correct type. If the return value is a list, it must be the same length as the other columns in data. Failing to meet these requirements results in a failure during conversion.

If the value of the new column depends only on the other columns in the same table, the data parameter can be used to inspect the other columns. If the data comes from another table, the function .kxi.db.getTableData[tn;part] can be used to retrieve a partition from an arbitrary table in the same package. The part parameter to .kxi.db.getTableData should be the same part as the one received by the backfill function.

Advanced operations like making the column depend on data from mismatching partitions (such as partition d depending on data from partition d+1 or d-1) or other packages is not supported via the backfill property. Furthermore tables or columns that are being renamed in the same conversion should not be accessed, as either the old or the new name may be invalid at the point the backfill function is called.

Renaming a column

Starting with the following schema:

tables/tbl.yaml:

name: tbl
columns:
  - name: sym
    description: Symbol name
    type: symbol
  - name: realTime
    description: Time
    type: timestamp
  - name: colA
    description: colA
    type: long
  - name: colB
    description: colB
    type: long

To rename colB to colC, we need to change the name in the schema from colB to colC, as well as add the oldName attribute to colC with the value of colB to ensure that SM sees this as a rename and not the deletion and addition of a column:

tables/tbl.yaml:

name: tbl
columns:
  - name: sym
    description: Symbol name
    type: symbol
  - name: realTime
    description: Time
    type: timestamp
  - name: colA
    description: colA
    type: long
  - name: colC
    description: colC
    type: long
    oldName: colB

After the conversion is finished, the schema can be updated again to remove the oldName attribute:

tables/tbl.yaml:

name: tbl
columns:
  - name: sym
    description: Symbol name
    type: symbol
  - name: realTime
    description: Time
    type: timestamp
  - name: colA
    description: colA
    type: long
  - name: colC
    description: colC
    type: long

Renaming a table

Renaming a table works similarly to renaming a column, except we are adding the oldName attribute to the table itself.

Starting from the following schema:

tables/tbl.yaml:

name: tbl
description: Example table
type: partitioned
blockSize: 10000
prtnCol: realTime
sortColsOrd: sym
sortColsDisk: sym
columns:
  - name: sym
    description: Symbol name
    type: symbol
  - name: realTime
    description: Time
    type: timestamp
  - name: colA
    description: colA
    type: long
  - name: colB
    description: colB
    type: long

To rename tbl to tbl2, we need to change the file name and the name in the schema from tbl to tbl2, as well as add the oldName attribute to tbl2 with a value of tbl to ensure that SM sees this as a rename and not the deletion and addition of a table:

tables/tbl2.yaml:

name: tbl2
description: Example table
type: partitioned
blockSize: 10000
prtnCol: realTime
sortColsOrd: sym
sortColsDisk: sym
oldName: tbl
columns:
  - name: sym
    description: Symbol name
    type: symbol
  - name: realTime
    description: Time
    type: timestamp
  - name: colA
    description: colA
    type: long
  - name: colB
    description: colB
    type: long

Once the conversion is done we can remove the oldName attribute.

Enabling encryption

dbSettings:
  encryption:
    encryptAll: true

See the database docs for details.

Known issues

In version earlier than 1.4, DAPs could load the database while conversion was in progress resulting in errors. After 1.4, DAPs now wait until the conversion is complete before connecting to SM.