MariaDB 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.

TileDB integrates with MariaDB through a pluggable storage engine called MyTile, which can be found in the TileDB-MariaDB repo. MyTile is written to take advantage of query predicate pushdown to TileDB. MyTile allows a user to dynamically access any TileDB array, whether it is local to the machine, on S3 or on TileDB Cloud. This provides a powerful C++ based SQL engine for TileDB arrays.

A docker image is provided to allow for quick testing of the MyTile storage engine. The docker image starts a MariaDB server and connects to it from the shell for you.

# Run MariaDB
docker run -it tiledb/tiledb-mariadb

# Run MariaDB adding your S3 access keys as env variables
docker run -e AWS_ACCESS_KEY_ID="<key>" -e AWS_SECRET_ACCESS_KEY="<secret>" -it tiledb/tiledb-mariadb

# Run MariaDB by mounting an existing local array
docker run -it --rm -v /local/array/path:/data/local_array tiledb/tiledb-mariadb

Creating TileDB Arrays

The following example shows you how to create a 2D sparse TileDB array with a single attribute. See Configuration Parameters for details on the MyTile and core TileDB options you can set. Note that filters are optional for creating a table.

create table test_array (
  dim0 integer DIMENSION=1 lower_bound="0" upper_bound="100" tile_extent="10",
  dim1 integer DIMENSION=1 lower_bound="0" upper_bound="100" tile_extent="10",
  attr1 varchar(255) filters="GZIP=-1"
) engine=MyTile array_type='SPARSE' coordinate_filters="NONE" offset_filters="POSITIVE_DELTA=128";

If you wish to create an array on S3, the command is similar. Only the array name needs to change to an S3 URI:

create table `<s3-array-uri>` (
dim0 integer DIMENSION=1 lower_bound="0" upper_bound="100" tile_extent="10",
  dim1 integer DIMENSION=1 lower_bound="0" upper_bound="100" tile_extent="10",
  attr1 varchar(255) filters="GZIP=-1"
) engine=MyTile array_type='SPARSE' coordinate_filters="NONE" offset_filters="POSITIVE_DELTA=128";

You can see the array schema as follows:

show create table `<array-uri>`;

A TileDB array created through MariaDB 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

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

// Insert some values
insert into  test_array (dim0, dim1, attr1) 
values (1, 1, "cell 1"), (1, 2, "cell 2"), (2, 1, "cell 3");

// Read the array
select * from test_array;

Note that, if your array URI is longer than 64 characters, you need to use MariaDB's assisted table discovery for long names by simply doing the following:

// The following does not create an array, it just informs MariaDB about its existence
create table my_array 
engine=mytile uri='s3://my_bucket/my_array';

// Now you can query the array using the new short name
select * from my_array;

Encrypted TileDB Arrays

MyTile supports querying encrypted TileDB arrays by passing the encryption key as a parameter to the assisted table discovery. Note that due to a limitation of MariaDB, if you pass an encryption key it will be shown in the create table statement for any MariaDB user which has permissions to view the created table.

// The following does not create an array, it just informs MariaDB about its existence
create table my_array 
engine=mytile uri='s3://my_bucket/my_array'
encryption_key='0123456789abcdeF0123456789abcdeF;

// Now you can query the array using the new short name
select * from my_array;

Querying Array Metadata

Array metadata can be queried using a special suffix for the array URI. Adding @metadata to the URI will trigger MyTile to query the array metadata instead of the array data. The results are returned in two columns, key and value, both with string (TEXT) datatype in MariaDB. If a metadata value contains more than one value then a comma delimited string is returned.

select * from `s3://my_bucket/my_array@metadata`
key	value
key4	25.1,26.2,27.3,28.4
key3	25.1
key1	25
key5	This is TileDB array metadata
key2	25,26,27,28

Time Traveling

Time traveling for TileDB arrays is supported in two methods.

Dynamic Discovery With @timestamp

You can use the @<timestamp> keyword on the array URI to trigger MyTile to query the array at the given timestamp. This is equivalent to using open_at in other APIs.

select * from `s3://my_bucket/my_array@123`

Create Table

You can also use time traveling with the open_at=<timestamp> parameter with create table discovery.

// The following does not create an array, it just informs MariaDB about its existence
create table my_array 
engine=mytile uri='s3://my_bucket/my_array'
open_at=123;

// Now you can query the array and it will be opened at the timestamp 123
select * from my_array;

Geospatial Data

You can also use certain geospatial functions that leverage the performance benefits of TileDB. These are: ST_Intersects, ST_Overlaps, ST_Equals.In addtion, all of the standard MariaDB spatial functions are available.

Loading data to arrays with geometry types

You can load data into your TileDB arrays using either the OGR TileDB driver or the TileDB-Py API.

Query TileDB arrays

If your wkb_geometry column is paired with a spatial index as created by the OGR TileDB driver, the spatial filter will be pushed down to take advantage of TileDB's powerful dimension slicing for a significant performance boost. Without the spatial index, queries will return the same results but using a full scan.

The examples below show the geospatial functions we push down to TileDB. These queries can run on arrays created by MyTile, by TileDB Open Source, or any other of our supported APIs and integrations.

// Create a test array 
create table t1 (
  _X           double DIMENSION=1 lower_bound="0" upper_bound="120" tile_extent="10",
  _Y           double DIMENSION=1 lower_bound="0" upper_bound="120" tile_extent="10",
  name         varchar(255) filters="GZIP=-1",
  wkb_geometry blob NOT NULL
)
engine=MyTile
array_type='SPARSE'
;

// Insert 2 polygon geometries for testing purposes
set @g1 = GeometryFromText('POLYGON((10.0 10.0, 20.0 10.0, 20.0 20.0, 10.0 20.0, 10.0 10.0))');
set @g2 = GeometryFromText('POLYGON((84.0 84.0, 94.0 84.0, 94.0 94.0, 84.0 94.0, 84.0 84.0))');
set @centroid1 = ST_Centroid(ST_Envelope(@g1));
set @centroid2 = ST_Centroid(ST_Envelope(@g2));
insert into t1 (_X, _Y, name, wkb_geometry) values
  (ST_X(@centroid1), ST_Y(@centroid1), "building1", aswkb(@g1)),
  (ST_X(@centroid2), ST_Y(@centroid2), "building2", aswkb(@g2))
;

// ST_Intersects
set @point_text = 'POINT(90.0 90.0)';
set @buffer_distance = 10;
select name, ST_Area(GeometryFromWkb(wkb_geometry))
from t1
where ST_Intersects(
  ST_Buffer(GeometryFromText(@point_text), @buffer_distance),
  GeometryFromWkb(wkb_geometry)
);

// ST_Overlaps
set @point_text = 'POINT(70.0 70.0)';
set @buffer_distance = 25;
select name, ST_Area(GeometryFromWkb(wkb_geometry))
from t1
where ST_Overlaps(
  ST_Buffer(GeometryFromText(@point_text), @buffer_distance),
  GeometryFromWkb(wkb_geometry)
);

// ST_EQUALS
select name, ST_Area(GeometryFromWkb(wkb_geometry))
from t1
where ST_EQUALS(@g2, GeometryFromWkb(wkb_geometry));

Visual representation:

Joins

Enabling Batched Key Access

You must enable the following join cache level and optimizer switches.

Join Batch Sizes

It is also important to adjust both join_buffer_space_limit and join_buffer_size to a size that is appropriate for your host. The size of the join buffer limits the amount of keys that are batched at one time. Too small of a value will yield bad performance for TileDB having to issue a large number of small queries. The size should be adjusted to a larger value based on the available memory size.

Prerequisites

MyTile requires TileDB to be installed on the system (see TileDB Installation). You can install all the other dependencies as follows.

dnf builddep mariadb-server

yum install git \
      gcc \
      gcc-c++ \
      bison \
      libxml2-devel \
      libevent-devel \
      rpm-build \
      ncurses-devel \
      gnutls-devel

apt-get build-dep mariadb-server

brew install cmake jemalloc traildb/judy/judy openssl boost gnutls

Depending on your platform there may be other dependencies needed. See https://mariadb.com/kb/en/library/compiling-mariadb-from-source/ for details.

Compiling

When compiling from source, you will need to fetch the MariaDB server and then build MariaDB with the MyTile source included. You must build MariaDB and MyTile together in one source tree, as MariaDB requires that all compilation flags and optimizations be the same between the server and the plugins.

For simplicity the cmake command below disables many of the optional storage engines to reduce the build size. You can toggle each storage engine as you see fit.

The CMAKE_INSTALL_PREFIX can also be set to switch locations of the installation. By default, we set it to $HOME/mytile_server

git clone https://github.com/TileDB-Inc/TileDB-MariaDB.git 
git clone https://github.com/MariaDB/server -b 10.5
cd server
ln -s `pwd`/../TileDB-MariaDB storage/mytile
mkdir build && cd build
cmake .. -DCMAKE_INSTALL_PREFIX=$HOME/mytile_server -DPLUGIN_TOKUDB=NO -DPLUGIN_ROCKSDB=NO -DPLUGIN_MROONGA=NO -DPLUGIN_SPIDER=NO -DPLUGIN_SPHINX=NO -DPLUGIN_FEDERATED=NO -DPLUGIN_FEDERATEDX=NO -DPLUGIN_CONNECT=NO -DCMAKE_BUILD_TYPE=Release -DPLUGIN_MYTILE=YES
make -j4
make install

After the compilation has completed, in $HOME/mytile_server you will have MariaDB 10.4 with a working MyTile plugin.

Configuring

By default optional plugins are not loaded. You will need to edit the MariaDB configuration to load the plugin. Full configuration of MariaDB is beyond our scope as there is a plethora of options outside of MyTile settings.

Edit your ~/.my.cnf, under the [mysqld] section add plugin-load-add=mytileand plugin-maturity=experimental . If you do not /.my.cnf file, a sample one is provided below:

# /mysql-data-dir/my.cnf to get server specific options or
# ~/my.cnf for user specific options.
# 
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options

# This will be passed to all mysql clients
[client]


# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# The MySQL server
[mysqld]
port=3306
plugin-load-add=ha_mytile

######### Fix the two following paths
# Where you want to have your database
#datadir=~/mytile_server/data

# Where you have your mysql/MariaDB source + sql/share/english
language=~/mytile_server/share/english

plugin_dir=~/mytile_server/lib/plugin
plugin-maturity=experimental

Running

Now that you have installed and configured MariaDB, you can run the server. First you need to run MariaDB's setup script to initialize the server:

~/mytile_server/scripts/mysql_install_db

After the database has been initialized, in a terminal start the server with:

~/mytile_server/bin/mariadbd

This will launch the server in your terminal. After the server is successfully started, you can connect with:

~/mytile_server/bin/mariadb

Table Parameters

These are set upon table creation as follows:

create table test_array (
  ...
) engine=MyTile <table-parameters>; // e.g., array_type='SPARSE'

Column Parameters

The column parameters are set upon table creation as follows:

create table test_array (
  <column-parameters> // e.g., dim0 integer DIMENSION=1 lower_bound="0" upper_bound="100" tile_extent="1"
) engine=MyTile <table-parameters>; 

Filter Options

For filter parameters either table or column the following are the list of supported filters. Any number of filters can be passed in a csv string. If a filter takes a parameters it can be set using key=value. I.Efilters="GZIP=-1,LZ4=4"

TileDB Parameters

You can set any of the TileDB configuration parameters as follows:

set mytile_tiledb_config=<list-of-params>;
// E.g., set mytile_tiledb_config='vfs.num_threads=12,vfs.s3.aws_region=us-east-1';

MariaDB System Parameters

mytile_read_buffer_size

• Description: Size in bytes of the buffers to use for each attribute/coordinates when performing a read.

• Command line: --mytile-read-buffer-size

• Scope: Global

• Dynamic: Yes

• Data Type: long

• Default Value: 100M

mytile_write_buffer_size

• Description: Size in bytes of the buffers to use for each attribute/coordinates when performing a write.

• Command line: --mytile-write-buffer-size

• Scope: Global

• Dynamic: Yes

• Data Type: long

• Default Value: 100M

mytile_delete_arrays

• Description: Controls if a delete table statement causes the array to be deleted on disk or just deregistered from MariaDB. A true value causes actual deletions of data.

• Command line: --mytile-delete-arrays

• Scope: Global

• Dynamic: Yes

• Data Type: boolean

• Default Value: false

tiledb_config

• Description: Set TileDB configuration parameters, this is in csv form of key1=value1,key2=value2. Example: set mytile_tiledb_config = 'vfs.s3.aws_access_key_id=abc,vfs.s3.aws_secret_access_key=123';

• Command line: --mytile-tiledb-config

• Scope: Session

• Dynamic: Yes

• Data Type: string

• Default Value: ""

reopen_for_every_query

• Description: Closes and reopen the array for every query, this allows tiledb_config parameters to take effect without forcing a table flush, but any TileDB caching is removed.

• Command line: --mytile-reopen-for-every-query

• Scope: Session

• Dynamic: Yes

• Data Type: boolean

• Default Value: true

ready_query_layout

• Description: Set layout for ready queries, valid values are row-major, col-major, unordered or global-order

• Command line: --mytile-read-query-layout

• Scope: Session

• Dynamic: Yes

• Data Type: enum

• Default Value: unordered

dimensions_are_primary_keys

• Description: Should dimensions be treated and registered as primary keys.

• Command line: --mytile-dimensions-are-primary-keys

• Scope: Session

• Dynamic: Yes

• Data Type: boolean

• Default Value: true

enable_pushdown

• Description: Pushdown predicates where possible

• Command line: --mytile-enable-pushdown

• Scope: Session

• Dynamic: Yes

• Data Type: boolean

• Default Value: true

compute_table_records

• Description: Compute size of table (record count) on table opening. This is useful when you are performing a large join or group by and knowing the table size can affect the optimizer. Computing the table records can take several seconds (worst case) so this should not be enabled for small queries.

• Command line: --mytile-compute-table-records

• Scope: Session

• Dynamic: Yes

• Data Type: boolean

• Default Value: false