Run TileDB-Tables Locally

tutorials
tables
local access
This tutorial shows how to use TileDB’s tabular offering on your own machine.

This tutorial explains how to install TileDB and its SQL package (via MariaDB), as well as how to use it on TileDB Cloud.

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.

Installation

You need to install the following libraries and programs:

Conda and mamba are the preferred mechanisms for installing TileDB-VCF.

# enter the following two lines if you are on a M1 Mac
CONDA_SUBDIR=os
conda config --env --set subdir osx-64

# create the conda environment
conda create -n tiledb-tables "python<3.10"
conda activate tiledb-tables

# mamba is a faster and more reliable alternative to conda
conda install -c conda-forge mamba

# Install the required libraries
mamba install -y -c conda-forge -c tiledb tiledb-py libtiledb-sql-py pandas pyarrow numpy

# Install the TileDB Cloud Python client
pip install tiledb-cloud

Local storage

Using TileDB’s tabular offering on local storage is similar to the Tables Quickstart tutorial, as well as the other Tables tutorials.

Storage on S3

Using TileDB’s tabular offering to interact with Amazon S3 from your local storage is similar to the Basic S3 Example with Tables tutorial.

Storage on TileDB Cloud

Take note of the following when interacting with TileDB Cloud outside of the notebook environments in the TileDB Cloud service:

  1. You need to create a REST API token from the TileDB Cloud console.
  2. You need to set the REST API token in an environment variable and login using the token value.
  3. When creating a new table, you need to use a URI in the form tiledb://<your_username>/<S3_path>/<table_name>, where S3_path is the location on S3 where you wish to physically store the table.
  4. When referring to the table after creating it (for example, when submitting queries), use a URI in the form tiledb://<your_username>/<table_name> (that is, no need to specify the S3 physical path anymore).

Start by importing the necessary libraries, loading the appropriate token and username, logging in, setting up the URIs and cleaning up any already-created table with the same name.

import warnings

import tiledb
import tiledb.cloud

warnings.filterwarnings("ignore")
import os.path

import pandas as pd
import tiledb.sql

# You should set the appropriate environment variables with
# your TileDB Cloud token and username.
token = os.environ["TILEDB_REST_TOKEN"]
username = os.environ["TILEDB_REST_USERNAME"]

# Get the bucket from an environment variable
s3_bucket = os.environ["S3_BUCKET"]

# Login to TileDB Cloud
tiledb.cloud.login(token=token)

# Set URIs
table_name = "my_table"
table_uri = "tiledb://" + username + "/" + table_name
table_reg_uri = "tiledb://" + username + "/" + s3_bucket + "/" + table_name
csv_uri = (
    "s3://tiledb-inc-demo-data/examples/notebooks/nyc_yellow_tripdata/taxi_first_10.csv"
)

# Define config values and context
cfg = {"vfs.s3.no_sign_request": "true", "vfs.s3.region": "us-east-1"}
ctx = tiledb.cloud.Ctx(cfg)

# Clean up past data
if tiledb.array_exists(table_uri):
    tiledb.Array.delete_array(table_uri)

Ingest some data, using the URI that incorporates the S3 physical path as explained earlier:

tiledb.from_csv(
    table_reg_uri,
    csv_uri,
    ctx=ctx,
    parse_dates=["tpep_dropoff_datetime", "tpep_pickup_datetime"],
)

Run a SQL query to see that it works, but now you can use the TileDB URI that doesn’t incorporate the S3 physical path.

# Query with SQL
db = tiledb.sql.connect(init_command="SET GLOBAL time_zone='+00:00'")
pd.read_sql(sql=f"SELECT * FROM `{table_uri}`", con=db)
__tiledb_rows congestion_surcharge improvement_surcharge tip_amount total_amount mta_tax extra tpep_dropoff_datetime fare_amount payment_type PULocationID store_and_fwd_flag tpep_pickup_datetime VendorID trip_distance RatecodeID tolls_amount passenger_count DOLocationID
0 0 2.5 0.3 1.47 11.27 0.5 3.0 2019-12-31 19:33:03 6.00 1 238 N 2019-12-31 19:28:15 1 1.20 1 0 1 239
1 1 2.5 0.3 1.50 12.30 0.5 3.0 2019-12-31 19:43:04 7.00 1 239 N 2019-12-31 19:35:39 1 1.20 1 0 1 238
2 2 2.5 0.3 1.00 10.80 0.5 3.0 2019-12-31 19:53:52 6.00 1 238 N 2019-12-31 19:47:41 1 0.60 1 0 1 238
3 3 0.0 0.3 1.36 8.16 0.5 0.5 2019-12-31 20:00:14 5.50 1 238 N 2019-12-31 19:55:23 1 0.80 1 0 1 151
4 4 0.0 0.3 0.00 4.80 0.5 0.5 2019-12-31 19:04:16 3.50 2 193 N 2019-12-31 19:01:58 2 0.00 1 0 1 193
5 5 0.0 0.3 0.00 3.80 0.5 0.5 2019-12-31 19:10:37 2.50 2 7 N 2019-12-31 19:09:44 2 0.03 1 0 1 193
6 6 0.0 0.3 0.01 3.81 0.5 0.5 2019-12-31 19:39:29 2.50 1 193 N 2019-12-31 19:39:25 2 0.00 1 0 1 193
7 7 2.5 0.3 0.00 2.81 0.0 0.0 2019-12-18 10:28:59 0.01 1 193 N 2019-12-18 10:27:49 2 0.00 5 0 1 193
8 8 2.5 0.3 0.00 6.30 0.5 0.5 2019-12-18 10:31:35 2.50 1 193 N 2019-12-18 10:30:35 2 0.00 1 0 4 193
9 9 2.5 0.3 2.35 14.15 0.5 3.0 2019-12-31 19:40:28 8.00 1 246 N 2019-12-31 19:29:01 1 0.70 1 0 2 48

Clean up.

# Clean up
if tiledb.array_exists(table_uri):
    tiledb.Array.delete_array(table_uri)