Ingestion with SQL

Learn how to create tables and add rows to them using SQL.
How to run this tutorial

We recommend running this tutorial, as well as the other tutorials in the Tutorials section, inside TileDB Cloud. By using TileDB Cloud, you can experiment while avoiding all the installation, deployment, and configuration hassles. Sign up for the free tier, spin up a TileDB Cloud notebook with a Python kernel, and follow the tutorial instructions. If you wish to learn how to run tutorials locally on your machine, read the Tutorials: Running Locally tutorial.

This tutorial shows you how to create tables and add rows to them using SQL. You’ll first perform some basic setup steps, and then you’ll create two different types of tables, one represented as a 1D dense array, and one as a 2D sparse array. If you wish to understand their differences and impact on performance, read the Tables Data Model section.

Setup

First, import the necessary libraries, set the URIs you’ll use in this tutorial, and delete any already-created tables with the same name.

import tiledb
import warnings

warnings.filterwarnings("ignore")
import tiledb.sql
import pandas as pd
import shutil
import os.path

# Print library versions
print("TileDB core version: {}".format(tiledb.libtiledb.version()))
print("TileDB-Py version: {}".format(tiledb.version()))
print("TileDB-SQL version: {}".format(tiledb.sql.version))

# Set table dataset URIs
dense_table_uri = "my_dense_table"
sparse_table_uri = "my_sparse_table"

# Clean up the tables if they already exist
if os.path.exists(dense_table_uri):
    shutil.rmtree(dense_table_uri)
if os.path.exists(sparse_table_uri):
    shutil.rmtree(sparse_table_uri)
TileDB core version: (2, 25, 0)
TileDB-Py version: (0, 31, 1)
TileDB-SQL version: 2.1.5

Next, prepare a SQL connection and cursor, with which you can perform SQL queries with TileDB.

conn = tiledb.sql.connect(init_command="SET GLOBAL time_zone='+00:00'")
cursor = conn.cursor()

Tables as 1D dense arrays

Use a classic CREATE TABLE statement to create the table. You’ll configure the table to have a 'DENSE' array_type and add one dimension, row_id, set with the dimension=1 option. The table will have two other fields (that is, columns or attributes).

cursor.execute(f"""
CREATE TABLE my_table (
    row_id int tile_extent="10" dimension=1,
    attr0 varchar(255),
    attr1 float
) engine=MyTile uri='{dense_table_uri}' array_type='DENSE'
""")
Tip

You can find all options for the CREATE TABLE statement in the Tables API Reference.

Now that the table exists, insert records by using an INSERT statement:

# Insert single record
cursor.execute(f"""
INSERT INTO `{dense_table_uri}` (row_id, attr0, attr1) VALUES (0, "a", 0)
""")

# Insert multiple rows
cursor.execute(f"""
INSERT INTO `{dense_table_uri}` (row_id, attr0, attr1) VALUES (1, "b", 1), (2, "c", 2), (3, "d", 3), (4, "e", 4), (5, "f", 5)
""")

Read some data to confirm TileDB inserted the records successfully:

# Query with SQL
cursor.execute(f"SELECT * from `{dense_table_uri}` WHERE row_id >= 3")

# fetchall will return tuples of the records in row form
print(cursor.fetchall())
((3, 3.0, 'd'), (4, 4.0, 'e'), (5, 5.0, 'f'))

Along with using the DBI cursor directly, you can use pandas to fetch the data and load it directly into a pandas DataFrame for output.

# Query with SQL through pandas
pd.read_sql(sql=f"SELECT * FROM `{dense_table_uri}`", con=conn)
row_id attr1 attr0
0 0 0.0 a
1 1 1.0 b
2 2 2.0 c
3 3 3.0 d
4 4 4.0 e
5 5 5.0 f

TileDB also offers native Python slicing, which will return the data in a dataframe. First, open the table for reading.

# Open the table in read mode
table = tiledb.open(dense_table_uri, mode="r")

Check the schema of the underlying array.

# Show the schema of the underlying array
print(table.schema)
ArraySchema(
  domain=Domain(*[
    Dim(name='row_id', domain=(-2147483648, 2147483637), tile=10, dtype='int32', filters=FilterList([ZstdFilter(level=-1), ])),
  ]),
  attrs=[
    Attr(name='attr0', dtype='ascii', var=True, nullable=True, enum_label=None),
    Attr(name='attr1', dtype='float32', var=False, nullable=True, enum_label=None),
  ],
  cell_order='row-major',
  tile_order='row-major',
  sparse=False,
)

Query the table by using the df operator.

# Read entire dataset into a pandas dataframe
df = table.df[:]  # Equivalent to: A.df[0:9]
df
row_id attr0 attr1
0 0 a 0.0
1 1 b 1.0
2 2 c 2.0
3 3 d 3.0
4 4 e 4.0
5 5 f 5.0

Tables as 2D sparse arrays

Here, you’ll perform similar operations, but now you’ll define the table as 2D sparse.

Use a classic CREATE TABLE statement to create the table. The table is sparse, which is the default when array_type is missing. This table has two dimensions, dim0 and dim1, which TileDB infers from the composite primary key (and, thus, you don’t need to add the dimension=1 option in each line in the definition). The table will have two other fields (that is, columns or attributes). Also notice that you can set the compression filters for each dimension or attribute via the filters option, if you don’t want to use the default TileDB compressors.

cursor.execute(f"""
CREATE TABLE my_table (
    dim0 int tile_extent="10" filters="GZIP",
    dim1 int tile_extent="10",
    attr0 varchar(255),
    attr1 float,
    primary key(dim0, dim1) 
) engine=MyTile uri='{sparse_table_uri}'
""")
Tip

You can find all options for the CREATE TABLE statement in the Tables API Reference.

Now that the table exists, insert records by using an INSERT statement:

# Insert single record
cursor.execute(f"""
INSERT INTO `{sparse_table_uri}` (dim0, dim1, attr0, attr1) VALUES (0, 0, "a", 0)
""")

# Insert multiple rows
cursor.execute(f"""
INSERT INTO `{sparse_table_uri}` (dim0, dim1, attr0, attr1) VALUES (0, 1, "b", 1), (0, 2, "c", 2), (0, 3, "d", 3), (0, 4, "e", 4), (1, 0, "f", 5)
""")

Read some data to confirm that TileDB inserted the records successfully:

# Query with SQL
cursor.execute(f"SELECT * from `{sparse_table_uri}`")

# fetchall will return tuples of the records in row form
print(cursor.fetchall())
((0, 0, 0.0, 'a'), (0, 1, 1.0, 'b'), (1, 0, 5.0, 'f'), (0, 2, 2.0, 'c'), (0, 3, 3.0, 'd'), (0, 4, 4.0, 'e'))

Use pandas to fetch the data and load it directly into a pandas DataFrame for output.

# Query with SQL through pandas
pd.read_sql(sql=f"SELECT * FROM `{sparse_table_uri}`", con=conn)
dim0 dim1 attr1 attr0
0 0 0 0.0 a
1 0 1 1.0 b
2 1 0 5.0 f
3 0 2 2.0 c
4 0 3 3.0 d
5 0 4 4.0 e

Open the table for reading using the Python API.

# Open the table in read mode
table = tiledb.open(sparse_table_uri, mode="r")

Check the schema of the underlying array.

# Show the schema of the underlying array
print(table.schema)
ArraySchema(
  domain=Domain(*[
    Dim(name='dim0', domain=(-2147483648, 2147483637), tile=10, dtype='int32', filters=FilterList([ZstdFilter(level=-1), ])),
    Dim(name='dim1', domain=(-2147483648, 2147483637), tile=10, dtype='int32', filters=FilterList([ZstdFilter(level=-1), ])),
  ]),
  attrs=[
    Attr(name='attr0', dtype='ascii', var=True, nullable=True, enum_label=None),
    Attr(name='attr1', dtype='float32', var=False, nullable=True, enum_label=None),
  ],
  cell_order='row-major',
  tile_order='row-major',
  capacity=10000,
  sparse=True,
  allows_duplicates=False,
)

Query the table by using the df operator.

# Read entire dataset into a pandas dataframe
df = table.df[:]  # Equivalent to: A.df[0:9]
df
dim0 dim1 attr0 attr1
0 0 0 a 0.0
1 0 1 b 1.0
2 1 0 f 5.0
3 0 2 c 2.0
4 0 3 d 3.0
5 0 4 e 4.0

Clean up

Delete the created tables.

# Clean up the tables if they already exist
if os.path.exists(dense_table_uri):
    shutil.rmtree(dense_table_uri)
if os.path.exists(sparse_table_uri):
    shutil.rmtree(sparse_table_uri)