TileDB-Trino is a data source connector for Trino, which allows you to run SQL queries on TileDB arrays. The connector supports column subselection on attributes and predicate pushdown on dimension fields, leading to superb performance for projection and range queries.

The TileDB-Trino connector supports most SQL operations from Trino. Arrays can be referenced dynamically and are not required to be "pre-registered" with Trino. No external service (such as Apache Hive) is required.

The TileDB connector supports most of Trino functionality. Below is a list of the features not currently supported.

Encrypted Arrays

The connector does not currently support creating/writing/reading encrypted arrays

OpenAt Timestamp

The connector does not currently support the TileDB openAt functionality to open an array at a specific timestamp.

Datatypes

TileDB Trino connector supports the following SQL datatypes:

• BOOLEAN

• TINYINT

• INTEGER

• BIGINT

• REAL

• DOUBLE

• DECIMAL (treated as doubles)

• STRING*

• VARCHAR*

• CHAR*

• VARBINARY

No other datatypes are supported.

Unsigned Integers

The TileDB Trino connector does not have full support for unsigned values. Trinno and all connectors are written in Java, and Java does not have unsigned values. As a result of this Java limitation, an unsigned 64-bit integer can overflow if it is larger than 2^63 - 1. Unsigned integers that are 8, 16 or 32 bits are treated as larger integers. For instance, an unsigned 32-bit value is read into a Java type of long.

Variable-length Char/Varchar fields

For varchar, and char datatypes the special case of char(1) or varchar(1) is stored on disk as a fixed-sized attribute of size 1. Any char/varchar greater than 1 is stored as a variable-length attribute in TileDB. TileDB will not enforce the length parameter but Trino will for inserts.

Decimal Type

Decimal types are currently treated as doubles. TileDB does not enforce the precision or scale of the decimal types.

Create Table

Create table is supported, however only a limited subset of TileDB parameters is supported.

• No support for creating encrypted arrays

• No support for setting custom filters on attributes, coordinates or offsets

Splits

The current split implementation is naive and splits domains evenly with user defined predicates (WHERE clause) or from the non-empty domains. This even splitting will likely produce sub optimal splits for sparse domains. Future work will move splitting into core TileDB where better heuristics will be used to produce even splits.

For now, if splits are highly uneven consider increasing the number of splits via the tiledb.splits session parameter or add where clauses to limit the data set to non-empty regions of the array.

Below are various examples for querying data with the TileDB Trino connector.

SQL Examples

Selecting Data

Typical select statements work as expected. This include predicate pushdown for dimension fields.

Select all columns and all data from an array:

SELECT * FROM tiledb.tiledb."file:///opt/tiledb_example_arrays/dense_global"

Select subset of columns:

SELECT rows, cols FROM tiledb.tiledb."file:///opt/tiledb_example_arrays/dense_global"

Select with predicate pushdown:

SELECT * FROM tiledb.tiledb."file:///opt/tiledb_example_arrays/dense_global" WHERE rows between 1 and 2

Showing Query Plans

Get the query plan without running the query:

EXPLAIN SELECT * FROM tiledb.tiledb."file:///opt/tiledb_example_arrays/dense_global" WHERE rows between 1 and 2

Analyze the query but running and profiling:

EXPLAIN ANALYZE SELECT * FROM tiledb.tiledb."file:///opt/tiledb_example_arrays/dense_global" WHERE rows between 1 and 2

Creating a TileDB Array

It is possible to create TileDB array from Trino. Not all array schema options are currently supported from Trino though (see Limitations for more details).

Minimum create table:

CREATE TABLE region(
  regionkey bigint WITH (dimension=true),
  name varchar,
  comment varchar
  ) WITH (uri = 's3://bucket/region')

Create table with all options specified:

CREATE TABLE region(
  regionkey bigint WITH (dimension=true, lower_bound=0, upper_bound=3000, extent=50),
  name varchar,
  comment varchar
  ) WITH (uri = 's3://bucket/region', type = 'SPARSE', cell_order = 'COL_MAJOR', tile_order = 'ROW_MAJOR', capacity = 10)

Inserting Data

Data can be inserted into TileDB arrays through Trino. Inserts can be from another table or individual values.

Copy data from one table to another:

INSERT INTO tiledb.tiledb."s3://bucket/region" select * from tpch.tiny.region

Data can be inserted using the VALUES method for single row inserts. This is not recommended because each insert will create a new fragment and cause degraded read performance as the number of fragments increases.

INSERT INTO tiledb.tiledb."s3://bucket/region" VALUES (1, "Test Region", "Example")

Trino and TileDB have slight differences in their supported datatypes. This document serves as a mapping between the (core) TileDB datatypes and the MariaDB datatypes for easy reference.

Currently, the TileDB-Τrino connector is built as a plugin. It must be packaged and installed on the Trino instances. You can download the or build the connector from source using the following command from the top level directory of the TileDB-Trino repo.

Installation on a Trino instance

First clone Trino

Install Trino

Create a TileDB directory

Build and copy the TileDB-Trino jars to the TileDB directory

Create two nested directories "etc/catalog" which include the tiledb.properties file and move them to:

Launch the Trino Server

Launch the Trino-CLI with the TileDB plugin

This document contains all custom SQL options defined by the TileDB Trino connector.

Create Table

The following properties can be configured for creating a TileDB array in Trino.

Table properties

Column Properties

Creating a New TileDB Array

It is possible to create TileDB array from Trino. Not all array schema options are currently supported from Trino though (see for more details). An example is shown below.

Note that <array-uri> can be any path, local (e.g., file://) or remote (e.g., s3://).

You can see the array schema as follows:

A TileDB array created through Trino is and behaves exactly like any other TileDB array. Therefore, it is accessible by all TileDB APIs (e.g., Python) and integrations (e.g., Spark).

Querying TileDB Arrays

Trino can dynamically discover existing TileDB arrays, i.e., even if they were created and populated externally from Trino. Therefore, you can just insert data into a TileDB array or query it as follows:

Trino uses the form of catalog.schema.<array-uri> for querying. TileDB does not have a concept of a table schema, so any valid string can be used for the schema name when querying and tiledb is used only for convenience in the examples. <array-uri> is the array URI and can be local (file://) or remote (s3://).

Plugin Parameters

A single configuration file is needed. The config file should be placed in the catalog folder (e.g.,/etc/trino/conf/catalog on EMR) and named tiledb.properties.

Sample file contents:

connector.name=tiledb
# Set read buffer to 10M per attribute
read-buffer-size=10485760

The following parameters can be configured in the tiledb.properties and are plugin-wide.

Session Parameters

These can be set as follows:

set session tiledb.<param>=<value>
// E.g., set session tiledb.splits=10

Unset session parameters inherit the plugin configuration defaults. The list of session parameters is summarized below"

Table properties

These are set upon table creation as follows:

create table my_table(
  ...
  ) with (uri = '<array-uri>', type='SPARSE', cell_order='ROW_MAJOR`, ...);

Column Parameters

These are set upon table creation as follows:

create table my_table(
  dim0 bigint with (dimension=true, lower_bound=0, upper_bound=100, extent=10),
  ...
  ) with (uri = '<array-uri>', type = 'SPARSE');