Embedded SQL

It is possible for MariaDB to act as an embedded SQL library in TileDB. By using this embedded library, you can avoid installing and maintaining an active MariaDB server on your machine. MariaDB acts as any other library dependency to TileDB, and contains TileDB as a pluggable storage engine so that you can query any TileDB array via SQL. When embedding MariaDB, you should note that you are subject to the GPL license of MariaDB itself.
You can use embedded SQL either via a Python, or directly from the MariaDB REPL, explained below.
Usage in Python
For convenience, TileDB offers a python package TileDB-SQL-Py, which is a fork of mysqlclient-python. The changes in the fork include adding back support for embedded linking, and packaging with binary wheels in Conda, PyPi and a Docker image. This allows you to simply import the Python package and run SQL queries against TileDB Arrays using Pandas.
You can install the package as follows:
Docker
Conda
1
docker run -it --rm tiledb/tiledb-sql-py
Copied!
1
# Create a new conda environment
2
conda create -n tiledb
3
conda activate tiledb
4
​
5
# Install libtiledb-sql-py
6
conda install -c conda-forge libtiledb-sql-py
Copied!
You can run SQL queries against any TileDB array (on any backend) as follows:
Python
1
import tiledb.sql, pandas
2
​
3
db = tiledb.sql.connect()
4
pandas.read_sql(sql="select * from `<array_uri>`", con=db)
Copied!
To set TileDB configuration parameters, you just need an init_command argument in connect:
Python
1
import tiledb.sql, pandas
2
​
3
db = tiledb.sql.connect(db="test",
4
        init_command="set mytile_tiledb_config='rest.token=yyy,rest.server_address=https://api.tiledb.com,...'")
5
pandas.read_sql(sql="select * from `<array_uri>`", con=db)
Copied!
When importing tiledb.sql you may get the following errors:
Got ERROR: "Could not open mysql.plugin table. Some plugins may be not loaded" errno: 2000 
Got ERROR: "Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist" errno: 2000 
Got ERROR: "Can't open the mysql.func table. Please run mysql_upgrade to create it." errno: 2000
You can safely ignore these, since they are an artifact of the way we embed MariaDB as a library; MariaDB is looking for tables that do not exist as we do not run it as a server. We will remove those errors soon.
Usage via MariaDB REPL
The MariaDB client REPL is also available to use from Conda for exploring arrays directly.
Conda
1
conda create -n tiledb
2
conda activate tiledb
3
​
4
conda install -c conda-forge libtiledb-sql
5
​
6
# Copy error messages to expected directory
7
cp ${CONDA_PREFIX}/share/english/errmsg.sys ${CONDA_PREFIX}/errmsg.sys
8
​
9
# Create empty test dir for database connection
10
mkdir ${CONDA_PREFIX}/test
11
​
12
mysql_embedded test
Copied!
You can issue any SQL queries:
SQL
1
select * from `<array_uri>`;
Copied!

How To - Previous

Groups

Next - How To

Configuration

Last modified 1yr ago

Copy link

Contents

Usage in Python

Usage via MariaDB REPL