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;

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:

Visual representation of the geometries in the examples

In order for TileDB to identify the spatial data attribute, your array must either have an attribute named "wkb_geometry" or have a "GEOMETRY_ATTRIBUTE_NAME" metadata field that specifies the name of the spatial attribute.