1 of 13

Serverless SQL

Basic Usage

SQL queries use , and can be executed with the TileDB Cloud client as follows.

import tiledb.cloud

tiledb.cloud.login(username="my_username", password="my_password")
# or tiledb.cloud.login(token="my_token")

# res is a Pandas dataframe
res = tiledb.cloud.sql.exec(
    query = "select `rows`, AVG(a) as avg_a from `tiledb://TileDB-Inc/quickstart_dense` GROUP BY `rows`")

library(tilebcloud)

tiledbcloud::login(username="my_username", password="my_password")
# or tiledbcloud::login(api_key="my_token")

# res is a data.frame
res <- execute_sql_query(
  "select `cols`, AVG(a) as avg_a from `tiledb://TileDB-Inc/quickstart_dense` GROUP BY `cols`",
)
show(res)

// Login using a TileDBLogin object
TileDBClient tileDBClient = new TileDBClient(
                        new TileDBLogin(
                        null,
                        null,
                        "<TILEDB_API_TOKEN>",
                        true,
                        true,
                        true));

// Create an SQL query
SQLParameters sql = new SQLParameters();
sql.setQuery("select `cols`, AVG(a) as avg_a from `tiledb://TileDB-Inc/quickstart_dense` GROUP BY `cols`");

// Create a TileDBSQL object
TileDBSQL tiledbSQL = new TileDBSQL(tileDBClient, "NAMESPACE", sql);

// Get the resutls back in arrow format
Pair<ArrayList<ValueVector>, Integer> results = tiledbSQL.execArrow();

Supposing that there is an array tiledb://user/array_name that you have write permissions for, you can run a SQL query that writes the results to that array as follows:

import tiledb.cloud

tiledb.cloud.login(username="my_username", password="my_password")
# or tiledb.cloud.login(token="my_token")

tiledb.cloud.sql.exec(
    query = "select `rows`, AVG(a) as avg_a from `tiledb://TileDB-Inc/quickstart_dense` GROUP BY `rows`",
    output_uri = "tiledb://user/array_name")

// Create a TileDB-Client and login using a TileDBLogin object
TileDBClient tileDBClient = new TileDBClient(
                        new TileDBLogin(
                        null,
                        null,
                        "<TILEDB_API_TOKEN>",
                        true,
                        true,
                        true));

// Create an SQL query
SQLParameters sql = new SQLParameters();
sql.setQuery("select `cols`, AVG(a) as avg_a from `tiledb://TileDB-Inc/quickstart_dense` GROUP BY `cols`");
// Setting the output URI
sql.setOutputUri("tiledb://user/array_name");

// Create a TileDBSQL object
TileDBSQL tiledbSQL = new TileDBSQL(tileDBClient, "NAMESPACE", sql);

// Run
List<Object> results = tiledbSQL.exec();

You can run any SQL statement, including CTEs.

import tiledb.cloud

tiledb.cloud.login(username="my_username", password="my_password")
# or tiledb.cloud.login(token="my_token")

# INSERT INTO ...
tiledb.cloud.sql.exec(query = "insert into `tiledb://user1/output1` select AVG(a) from `tiledb://user1/myarray1`")

# CTE
tiledb.cloud.sql.exec(query = 
  """
  WITH t1 AS (select `dim`, `dim2`, AVG(a), AVG(a) OVER (partition by dim2) FROM `tiledb://user1/array1`)
  insert into `tiledb://user/array_name`
  select * from t1
  """)

// Create a TileDB-Client and login using a TileDBLogin object
TileDBClient tileDBClient = new TileDBClient(
                        new TileDBLogin(
                        null,
                        null,
                        "<TILEDB_API_TOKEN>",
                        true,
                        true,
                        true));

// Create an SQL query
SQLParameters sql = new SQLParameters();
sql.setQuery("insert into `tiledb://user1/output1` select AVG(a) from `tiledb://user1/myarray1`");

// Create a TileDBSQL object
TileDBSQL tiledbSQL = new TileDBSQL(tileDBClient, "NAMESPACE", sql);

// Run
List<Object> results = tiledbSQL.exec();

// CTE
sql.setQuery("WITH t1 AS (select `dim`, `dim2`, AVG(a), AVG(a) OVER (partition by dim2) FROM `tiledb://user1/array1`)insert into `tiledb://user/array_name`select * from t1");

// Run
List<Object> results = tiledbSQL.exec();

If the array does not exist, you will just need to create and pass an array schema that complies with the result schema.

import tiledb.cloud
import numpy as np
import pandas

tiledb.cloud.login(username="my_username", password="my_password")
# or tiledb.cloud.login(token="my_token")

# The output array has a single dimension
dom = tiledb.Domain(tiledb.Dim(name="rows", domain=(1, 4), tile=4, dtype=np.int32))

# The output array has a single attribute
attr = tiledb.Attr(name="avg_a", dtype=np.float32)

# The output array is sparse
schema = tiledb.ArraySchema(domain=dom, sparse=True, attrs=[attr])

# The following will create the output array and write the results to it
tiledb.cloud.sql.exec(
    query = "select `rows`, AVG(a) as avg_a from `tiledb://TileDB-Inc/quickstart_dense` GROUP BY `rows`",
    output_schema = schema,
    output_uri = "tiledb://user/s3://my_bucket/my_array") # Notice the full path here

We also provide an auxiliary function exec_and_fetch, which executes a SQL query as above, but then opens the resulting output array so that it is ready for use.

import tiledb.cloud
import pandas

tiledb.cloud.login(username="my_username", password="my_password")
# or tiledb.cloud.login(token="my_token")

A = tiledb.cloud.sql.exec_and_fetch(
    query = "select `rows`, AVG(a) as avg_a from `tiledb://TileDB-Inc/quickstart_dense` GROUP BY `rows`",
    output_uri="tiledb://user/array_name")
    
# `A` is an opened array for reading, ready for use
print(A.df[:])

Selecting Who to Charge

If you are a member of an organization, then by default the organization is charged for your SQL query. If you would like to charge the SQL query to yourself, you just need to add one extra argument namespace.

import tiledb.cloud

tiledb.cloud.login(username="my_username", password="my_password")
# or tiledb.cloud.login(token="my_token")

tiledb.cloud.sql.exec(
    namespace ="my_username", # Who to charge the query to
    query = "select `rows`, AVG(a) as avg_a from `tiledb://TileDB-Inc/quickstart_dense` GROUP BY `rows`",
    output_uri = "tiledb://user/array_name")

// Create a TileDB-Client and login using a TileDBLogin object
TileDBClient tileDBClient = new TileDBClient(
                        new TileDBLogin(
                        null,
                        null,
                        "<TILEDB_API_TOKEN>",
                        true,
                        true,
                        true));

// Create an SQL query
SQLParameters sql = new SQLParameters();
sql.setQuery("select `cols`, AVG(a) as avg_a from `tiledb://TileDB-Inc/quickstart_dense` GROUP BY `cols`");

// Create a TileDBSQL object
// Charges will occur according to the namespace parameter provided here
TileDBSQL tiledbSQL = new TileDBSQL(tileDBClient, "NAMESPACE", sql);

// Get the resutls back in arrow format
Pair<ArrayList<ValueVector>, Integer> results = tiledbSQL.execArrow();

Resource Classes

Each serverless SQL runs by default in an isolated environment with 2 CPUs and 2 GB of memory. You can choose an alternative runtime environment from the following list:

Charges are based on the total number of CPUs selected, not on actual use.

To run a serverless SQL in a specific environment, set the resource_class parameter to the name of the environment.

import tiledb.cloud

tiledb.cloud.login(username="my_username", password="my_password")
# or tiledb.cloud.login(token="my_token")

tiledb.cloud.sql.exec(
    resource_class ="large",
    query = "select `rows`, AVG(a) as avg_a from `tiledb://TileDB-Inc/quickstart_dense` GROUP BY `rows`",
    output_uri = "tiledb://user/array_name")

// Create a TileDB-Client and login using a TileDBLogin object
TileDBClient tileDBClient = new TileDBClient(
                        new TileDBLogin(
                        null,
                        null,
                        "<TILEDB_API_TOKEN>",
                        true,
                        true,
                        true));

// Create an SQL query
SQLParameters sql = new SQLParameters();
sql.setQuery("select `cols`, AVG(a) as avg_a from `tiledb://TileDB-Inc/quickstart_dense` GROUP BY `cols`");
// Setting the resource class
sql.setResourceClass("large");

// Create a TileDBSQL object
TileDBSQL tiledbSQL = new TileDBSQL(tileDBClient, "NAMESPACE", sql);

// Get the resutls back in arrow format
Pair<ArrayList<ValueVector>, Integer> results = tiledbSQL.execArrow();

Async Execution

An asynchronous version of serverless SQL is available. The _async version returns a future.

import tiledb.cloud

tiledb.cloud.login(username="my_username", password="my_password")
# or tiledb.cloud.login(token="my_token")

# res is a future
res = tiledb.cloud.sql.exec_async(
    query = "select `rows`, AVG(a) as avg_a from `tiledb://TileDB-Inc/quickstart_dense` GROUP BY `rows`")
    
# Call res.get() to block on the results
print(res.get())

Creating New Arrays

It is also possible to use SQL to create a new array.

import tiledb.cloud

tiledb.cloud.login(username="my_username", password="my_password")
# or tiledb.cloud.login(token="my_token")

# Create an array that will be registered in TileDB Cloud and stored in the
# S3 bucket and location specied in the `uri` parameter. The `uri` parameter
# overrides the normal use of the table name `example`.
# The array is automatically registered in TileDB cloud
# under `my_username`/`create_example_1`
tiledb.cloud.sql.exec("""
CREATE TABLE `example` (
  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",
  attr2 FLOAT filters="GZIP=-1",
  attr3 DATETIME filters="GZIP=-1"
) engine=MyTile
array_type='SPARSE'
coordinate_filters="NONE"
offset_filters="POSITIVE_DELTA=128"
uri='tiledb://my_username/s3://my_bucket/create_example_1'; 
""")

# You can also use `Primary Key` to specify which fields are the dimensions
# This replaces the `DIMENSION=1` parameter
# The array is automatically registered in TileDB cloud
# under `my_username`/`create_example_2`
tiledb.cloud.sql.exec("""
CREATE TABLE `example` (
  dim0 integer lower_bound="0" upper_bound="100" tile_extent="10" NOT NULL,
  dim1 bigint lower_bound="0" upper_bound="100" tile_extent="10" NOT NULL,
  attr1 varchar(255) filters="GZIP=-1",
  PRIMARY KEY(dim0, dim1)
) engine=MyTile
array_type='SPARSE'
coordinate_filters="NONE"
offset_filters="POSITIVE_DELTA=128"
uri='tiledb://my_username/s3://my_bucket/create_example_2'; 
""")

# Lastly you can also use `INDEX()` to set dimensions and allow duplicates
# By default duplicate coordinates for dimensions are not allowed,
# uniqueness is enforced just like a primary key. Using `Index()`
# instead triggers the array to be created allowing duplicates coordinate values.
# The array is automatically registered in TileDB cloud
# under `my_username`/`create_example_3`
tiledb.cloud.sql.exec("""
CREATE TABLE `example` (
  dim0 integer lower_bound="0" upper_bound="100" tile_extent="10" NOT NULL,
  dim1 varchar(255) NOT NULL,
  attr1 varchar(255) filters="GZIP=-1",
  INDEX(dim0, dim1)
) engine=MyTile
array_type='SPARSE'
coordinate_filters="NONE"
offset_filters="POSITIVE_DELTA=128"
uri='tiledb://my_username/s3://my_bucket/create_example_3'; 
""")

// Create a TileDB-Client and login using a TileDBLogin object
TileDBClient tileDBClient = new TileDBClient(
                        new TileDBLogin(
                        null,
                        null,
                        "<TILEDB_API_TOKEN>",
                        true,
                        true,
                        true));

// Create an SQL query
SQLParameters sql = new SQLParameters();
sql.setQuery("CREATE TABLE `example` (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\",attr2 FLOAT filters=\"GZIP=-1\",attr3 DATETIME filters=\"GZIP=-1\") engine=MyTilearray_type='SPARSE'coordinate_filters=\"NONE\"offset_filters=\"POSITIVE_DELTA=128\"uri='tiledb://my_username/s3://my_bucket/create_example_1';");

// Create a TileDBSQL object
TileDBSQL tiledbSQL = new TileDBSQL(tileDBClient, "NAMESPACE", sql);

// Get the resutls back in arrow format
List<Object> results = tiledbSQL.exec();

Retry Settings

See Retry Settings for more information on this topic.

TileDB-Cloud JDBC

is a type 4 Java Database Connectivity (JDBC) driver that allows you to connect to and interact with TileDB Cloud using the JDBC API. It provides a seamless integration between your Java applications and TileDB Cloud, enabling you to perform various database operations and execute SQL queries.

This documentation will guide you through the installation process, configuration options, and usage examples of the TileDB Cloud JDBC library.

Installation

To use the TileDB Cloud JDBC library, follow these steps:

Ensure you have Java Development Kit (JDK) version 11 or higher installed on your system.
Download the latest release of the TileDB Cloud JDBC library from the .
Add the tiledb-cloud-jdbc-x.x.x.jar file to your project's classpath. Replace x.x.x with the version number of the library.
To load the driver at runtime write Class.forName("io.tiledb.TileDBCloudDriver")

Configuration

The TileDB Cloud JDBC library can be configured programmatically in your Java code. The configuration options include:

apiKey(String): Your TileDB-Cloud API Token. (recommended)
username(String): Your TileDB-Cloud username
password(String): Your TileDB-Cloud password
rememberMe(boolean): Whether the JDBC driver will remeber your login credentials in the future.
verifySSL(boolean): Whether the JDBC driver will use SSL
overwritePrevious(boolean): Whether the JDBC driver will overwrite existing credentials. This option can be combined with rememberMe.

Here's an example of configuring the driver where NAMESPACE is your TileDB-Cloud namespace

Properties properties  = new Properties();
properties.setProperty("apiKey", "KEY");
properties.setProperty("rememberMe", "true");

Connection conn = DriverManager.getConnection("jdbc:tiledb-cloud:<NAMESPACE>", properties);

You can also include your API Token in the connection String like this:

Connection conn = DriverManager.getConnection("jdbc:tiledb-cloud:<NAMESPACE>:<API_TOKEN>", properties);

Executing SQL Queries

Once you have established a connection, you can execute SQL queries against the TileDB Cloud database. To do so, create a Statement object and call the executeQuery method with your SQL query.

You can then handle the results like this:

BI tools integration

The TileDB-Cloud JDBC driver provides seamless integration with popular Business Intelligence (BI) tools such as Tableau and Microsoft Power BI. With the driver, you can connect your BI tools directly to TileDB Cloud and leverage the powerful visualization and analytics capabilities of these tools.

Tableau

Tableau is a leading business intelligence and data visualization tool that allows users to create interactive and insightful visualizations, reports, and dashboards. Together with TileDB, Tableau empowers users to connect, explore, and visualize their data stored in TileDB Cloud, enabling seamless integration of advanced analytics and visualization capabilities into their data workflows.

To connect with , the TileDB-Cloud JDBC driver requires the use of our custom . Tableau has a built-in store for connectors, however, this TileDB connector is not currently available for download and needs to be manually placed in the appropriate directory.

To do this, copy the connector directory from the repo (ignoring the LICENSE and README files) to the following location:

MacOS ~/Documents/My\ Tableau\ Repository/Connectors
Windows C:\Users\[Windows User]\Documents\My Tableau Repository\Connectors

Step 2:

In addition to the connector placement, ensure that you have also placed the TileDB-Cloud JDBC driver (.jar file) in the appropriate directory. If the directory doesn't already exist, create it.

MacOS ~/Library/Tableau/Drivers
Windows C:\Program Files\Tableau\Drivers

Step 3:

To launch Tableau, use the following commands:

MacOS /Applications/Tableau\ Desktop\ [version].app/Contents/MacOS/Tableau -DConnectPluginsPath=/Users/<USER>/Documents/My\ Tableau\ Repository/Connectors
Windows tableau.exe -DConnectPluginsPath=C:\Users\[Windows User]\Documents\My Tableau Repository\Connectors

Step 4:

Once Tableau launches, choose TileDB-Cloud JDBC, by TileDB from the left sidebar and enter your credentials to login.

Step 5:

Once you login choose All TileDB arrays from the dropdown menu on the top left corner and you will be able to see all your owned and shared arrays. You can also add an array you have access to by using the Custom SQL Query option.

Power BI

, in conjunction with TileDB, offers a comprehensive business intelligence platform that enables users to connect, transform, and visualize data stored in TileDB Cloud. With Power BI's intuitive interface and robust analytics features, organizations can gain valuable insights, create interactive reports and dashboards, and make data-driven decisions effectively.

Set up

Then, click ok and your bridge should be set up. Now, open PowerBI Desktop and follow these steps:

Click on "Get Data" in the Home tab.
Select "More..." and search for "ODBC" in the data connectors list.
Choose the "ODBC" option and click "Connect".
In the ODBC dialog, select the bridged JDBC driver as a data source from the list.
By expanding the "Advanced options" section you can insert a custom SQL query. Otherwise click next and you will be displayed your owned, shared and public arrays from TileDB-Cloud

Limitations

Query results are limited to 2GBs in size.
This JDBC driver uses our custom Mytile MariaDB storage engine which comes with it's limitations as well. For more info see .

Python Database API 2.0

TileDB-Cloud-Py package includes a Python DB API 2.0 connector, which aligns with PEP 249. It offers a convenient way for Python developers to connect to TileDB-Cloud and perform all necessary operations.