Tables API Reference

reference
tables
This section contains the list of APIs built and supported by TileDB for tables.

TileDB SQL follows MariaDB’s SQL Dialect. Specific extensions are added for create table and relevant parameters (shown below). For TileDB’s main APIs that support dataframes, the Arrays API Reference.

Note

TileDB’s SQL query support is now provided by TileDB Tables. The TileDB storage engine for MariaDB, imported as tiledb.sql, and also known as MyTile or TileDB-MariaDB, is now deprecated.

Table parameters

You can set table parameters during table creation as follows:

create table test_array (
  ...
) engine=MyTile <table_parameters>; // for example, array_type='SPARSE'
Property Description Default Possible Values Required
uri Array URI "" * Yes
array_type Array type SPARSE SPARSE, DENSE No
cell_order Cell order ROW_MAJOR ROW_MAJOR, COL_MAJOR No
tile_order Tile order ROW_MAJOR ROW_MAJOR, COL_MAJOR No
capacity Tile capacity 10000L >0 No
coordinate_filters Filters to use for coordinates NOOP CSV list of filters (refer to Filter options) No
offset_filters Filters to use for offsets NOOP CSV list of filters (refer to Filter options) No

Column parameters

You can also set column parameters during table creation as follows:

create table test_array (
  <column_parameters> // for example, dim0 integer DIMENSION=1 lower_bound="0" upper_bound="100" tile_extent="1"
) engine=MyTile <table_parameters>;
Property Description Datatype Default Possible Values Required
dimension Column is a dimension boolean 0 0, 1 No
lower_bound Domain lower bound string "0" Any value of datatype No
upper_bound Domain upper bound string Datatype max Any value of datatype No
tile_extent Tile extent string "10" Any Value of Datatype No
filters Filters for attributes string NOOP CSV list of filters (refer to Filter options) No

Filter options

For filter parameters, either table or column, the following table includes the list of supported filters. You can pass any number of filters in a CSV string. If a filter takes a parameter, you can set it with key=value (for example, filters="GZIP=-1,LZ4=4").

Filter Name Accepted Value
NOOP N/A
GZIP Integer
ZSTD Integer
LZ4 Integer
RLE N/A
BZIP2 Integer
DOUBLE_DELTA N/A
BIT_WIDTH_REDUCTION Integer
BITSHUFFLE N/A
BYTESHHUFFLE N/A
POSITIVE_DELTA Integer

TileDB parameters

You can set any of the TileDB configuration parameters in a CSV string. Here is an example:

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 at worst. It’s recommended not to enable this setting for small queries.
  • Command line: --mytile-compute-table-records
  • Scope: Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: false