tuor713/trino-kdb - Githubissues

Trino Plugin for kdb+

A Trino plugin for kdb+ - currently in beta state. Feedback (bugs, feature requests, questions) welcome!

Sample catalog definition:

connector.name=kdb
kdb.host=localhost
kdb.port=8000

Features

The plugin currently supports:

All the KDB primitive types and nested arrays
Table and pass through queries
- (New) Support for polymorphic table function for native queries (similar to JDBC connectors) such as select * from TABLE(system.query(query => 'select from CaseSensitiveTable'))
- Inside traditional dynamic queries upper case letters must be escaped as \\ since Trino converts all "table names" to lower case
Basic schema introspection
Limited filter and limit pass through
- Experimental support for like '<pattern>' push down (see push.down.like property). Support is limited: cases with special escape characters, multiple like expressions on the same column for example will not be pushed down.
Aggregation push down for count, sum, max, min, stddev, variance, count_if, bool_and, bool_or
Supports tables in nested namespaces
(Alpha) Insertion support for plain in-memory tables

Fine Print

KDB columns of unknown type () will be mapped to VARCHAR and converted with Object.toString (except for char arrays, which convert with String.valueOf)

Configuration Options

Settings that can be used in catalog file:

Config	Description
`kdb.host`	Hostname of KDB server
`kdb.port`	Port of KDB server
`kdb.user`	(Optional) User for authenticating with KDB server
`kdb.password`	(Optional) Password for authenticating with KDB server
`page.size`	(Optional) Size of pages (in number of rows) retrieved from KDB (default: 50,000)
`use.stats`	(Optional) Support stats for KDB either pre-generated or calculated on the fly (see `dynamic.stats`) (default: true)
`dynamic.stats`	(Optional) Support on the fly stats generation. Note this can have a detrimental effect on query planning speed for large tables (default: false)
`kdb.metadata.refresh.interval.seconds`	(Optional) Refresh interval, in seconds, for KDB metadata (default: 3600 = 1 hour)
`push.down.aggregation`	(Optional) Enable aggregation push down (default: true)
`virtual.tables`	(Optional) Treat all tables as virtual - not supporting features such as direct `select [x]` queries (default: false)
`insert.function`	(Optional) Insert function to use to insert data into KDB tables (default: insert)
`push.down.like`	(Optional, experimental) Push down like filters (default: false)
`kdb.extra.credential.user`	(Optional) Extra credential key for session level credentials: user
`kdb.extra.credential.user`	(Optional) Extra credential key for session level credentials: password

Session Property overrides

Property	Default
`push_down_aggregation`	Session override for catalog property `push.down.aggregation`
`use_stats`	Session override for catalog property `use.stats`
`dynamic_stats`	Session override for catalog property `dynamic.stats`
`page_size`	Session override for catalog property `page.size`
`virtual_tables`	Session override for catalog property `virtual.tables`
`insert_function`	Session override for catalog property `insert.function`
`push_down_like`	Session override for catalog property `push.down.like`

Pre-Generated Stats

Stats generation is still quite raw and can take extremely long for partitioned tables. As an alternatives stats can be precomputed and stored in two tables:

.trino.stats:([table: `symbol$()] rowcount: `long$())

.trino.colstats:([table: `symbol$(); column: `symbol$()] 
  distinct_count: `long$(); 
  null_fraction: `double$();
  size: `long$()`; 
  min_value: `double$();
  max_value: `double$())

Building

This library depends on javakdb, which can be built from GitHub. To build:

mvn package

The resulting shaded jar then needs to be dropped into ${TRINO_HOME}/plugins/kdb/.

The unit tests currently require a local instance of KDB running at port 8000.