Usage

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.

SQL

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:

SQL

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:

SQL

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:

SQL

// 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:

SQL

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

SQL

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

SQL

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.

SQL

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.

SQL

// 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;