Starburst KDB+ connector#

Note

The Starburst KDB+ connector is available as a public preview in Starburst Enterprise. Contact your Starburst account team with questions or feedback.

The KDB+ connector allows querying kdb+ time series databases from SEP using standard SQL. kdb+ is a high-performance columnar database commonly used for storing and processing large volumes of time series and financial data.

Requirements#

To use the KDB+ connector, you need:

kdb+ 4.1 or later, accessible from the SEP coordinator and all workers.
Network access from the SEP coordinator and workers to the kdb+ host and port (default: 5000).
If kdb+ is configured with access control, valid credentials with at least read access.

Note

The connector has been tested against kdb+ 4.1. Earlier versions might work but are not officially supported.

Note

The KDB+ connector is read-only. Write operations are not supported.

Configuration#

To configure the KDB+ connector, create a catalog properties file that specifies the connector by setting the connector.name to starburst_kdb.

connector.name=starburst_kdb
kdb.host=kdb-host.example.com
kdb.port=5000

If authentication is required, configure these additional properties:

kdb.user=myuser
kdb.password=secret

To authenticate to kdb+ with the credentials of each SEP user instead of a shared service account, use Password credential pass-through.

Configuration properties#

KDB+ configuration properties#
Property name	Description	Default
`kdb.host`	Hostname or IP address of the kdb+ server.	`localhost`
`kdb.port`	TCP port of the kdb+ server. Must be between 1 and 65535.	`5000`
`kdb.authentication.type`	Authentication type to use for connections to the kdb+ server. Possible values are: `PASSWORD` uses the static `kdb.user` and `kdb.password` credentials from the catalog properties file. `PASSWORD_PASS_THROUGH` uses the credentials of the user running the query. See Password credential pass-through.	`PASSWORD`
`kdb.user`	Username for authenticating with the kdb+ server. If omitted, the connector connects without credentials. Must not be set when `kdb.authentication.type` is `PASSWORD_PASS_THROUGH`.
`kdb.password`	Password for authenticating with the kdb+ server. Must not be set when `kdb.authentication.type` is `PASSWORD_PASS_THROUGH`.

Schemas and tables#

The KDB+ connector maps kdb+ namespaces to SEP schemas and kdb+ tables to SEP tables. The root namespace (tables defined at the top level of a kdb+ process) appears as the default schema in SEP. Named namespaces (for example, .myns) appear as additional schemas.

The following system namespaces are always excluded from schema discovery because they contain kdb+ internals rather than user data: q, Q, h, j, o, s, z.

Table and schema names must consist only of alphanumeric characters and underscores ([a-zA-Z0-9_]). The connector can’t access names that contain other characters.

Type mapping#

kdb+ uses single-character type codes internally. The connector maps these codes to Trino types as described in the following table.

kdb+ to Trino type mapping#

kdb+ to Trino type mapping#
kdb+ type code	kdb+ type name	Trino type	Notes
`b`	boolean	`BOOLEAN`	Maps directly. kdb+ booleans have no null value.
`x`	byte	`TINYINT`	Maps directly. kdb+ bytes have no null value.
`h`	short	`SMALLINT`	`Short.MIN_VALUE` (`0Nh`) is treated as `NULL`.
`i`	int	`INTEGER`	`Integer.MIN_VALUE` (`0Ni`) is treated as `NULL`.
`j`	long	`BIGINT`	`Long.MIN_VALUE` (`0Nj`) is treated as `NULL`.
`e`	real	`REAL`	IEEE 754 `Float.NaN` (`0Ne`) is treated as `NULL`.
`f`	float	`DOUBLE`	IEEE 754 `Double.NaN` (`0n`) is treated as `NULL`.
`c`	char	`VARCHAR`	A single character. The space character (`' '`) is treated as `NULL`.
`C`	char list (string)	`VARCHAR`	kdb+ strings (char lists) map to `VARCHAR`. An empty string is returned as an empty `VARCHAR` value, not `NULL`.
`s`	symbol	`VARCHAR`	The empty symbol (`) is treated as `NULL`.
`g`	GUID	`VARCHAR(36)`	UUID values are converted to their standard hyphenated string representation (`xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`). The all-zero UUID (`00000000-0000-0000-0000-000000000000`) is treated as `NULL`.
`p`	timestamp	`TIMESTAMP(6)`	Nanosecond-precision timestamps stored relative to the kdb+ epoch (2000-01-01). The connector converts them to microseconds relative to the Unix epoch (1970-01-01). A `Long.MIN_VALUE` nanosecond count is treated as `NULL`.
`m`	month	`VARCHAR`	Formatted as `YYYY.MMm` (for example, `2024.01m`). Month values are stored as integer offsets from January 2000.
`d`	date	`DATE`	Day counts stored relative to the kdb+ epoch (2000-01-01) are converted to the Unix epoch (1970-01-01). `Integer.MIN_VALUE` is treated as `NULL`.
`z`	datetime	`TIMESTAMP(3)`	Floating-point day fractions relative to the kdb+ epoch, converted to millisecond-precision Unix timestamps. `Double.NaN` and `Long.MIN_VALUE` are treated as `NULL`.
`n`	timespan	`BIGINT`	Stored as a raw nanosecond count. `Long.MIN_VALUE` is treated as `NULL`.
`u`	minute	`INTEGER`	Minutes since midnight as an integer. `Integer.MIN_VALUE` is treated as `NULL`.
`v`	second	`INTEGER`	Seconds since midnight as an integer. `Integer.MIN_VALUE` is treated as `NULL`.
`t`	time	`TIME(3)`	Milliseconds since midnight. `Integer.MIN_VALUE` is treated as `NULL`.

Columns with unsupported types are not visible in SEP.

Note

kdb+ designates a per-type sentinel value to represent NULL because its columnar storage model does not support nullable wrapper objects. The connector detects these sentinel values and converts them to SQL NULL before returning data to SEP.

SQL support#

The connector provides read-only access to kdb+ data. It supports the SELECT statement only.

Performance#

Pushdown#

The KDB+ connector supports Limit pushdown.

The connector doesn’t support predicate or aggregate pushdown. SEP performs all filtering, aggregation, and sorting after fetching the full table from kdb+. For large tables, this can result in significant data transfer.

To minimize data transfer, query only the columns you need:

-- Preferred: fetch only required columns
SELECT sym, price, ts FROM kdb.default.trades;

-- Avoid: fetches all columns
SELECT * FROM kdb.default.trades;

Security and encryption#

The current release uses an unencrypted TCP connection to kdb+. Deploy the connector only on trusted private networks or over an encrypted tunnel (for example, a VPN or SSH port forward).

Password credential pass-through#

The connector supports password credential pass-through. With pass-through enabled, SEP sends the username and password of the user running the query to kdb+, rather than the shared kdb.user and kdb.password from the catalog properties file. kdb+ can then authenticate and audit access for each individual user.

To enable password credential pass-through, set the authentication type in the catalog properties file and remove the static credentials:

connector.name=starburst_kdb
kdb.host=kdb-host.example.com
kdb.port=5000
kdb.authentication.type=PASSWORD_PASS_THROUGH

Important

Remove kdb.user and kdb.password from the catalog properties file. When kdb.authentication.type is PASSWORD_PASS_THROUGH, the connector doesn’t use these properties, and the catalog fails to start with an error such as Configuration property 'kdb.user' was not used.

Clients must supply the credentials with each query. To configure SEP to accept and forward them, and to supply them from the JDBC driver or the CLI, follow the steps in Password credential pass-through.

Because user passwords are sent to SEP, use TLS-secured connections between clients and the cluster. See TLS and HTTPS.

If a query runs without pass-through credentials, kdb+ refuses the connection and the query fails.