Skip to main content

SQL

superduperdb supports SQL databases via the ibis project. With superduperdb, queries may be built which conform to the ibis API, with additional support for complex data-types and vector-searches.

Setup

The first step in working with an SQL table, is to define a table and schema

from superduperdb.backends.ibis import dtype, Table
from superduperdb import Encoder, Schema

my_enc = Encoder('my-enc')

schema = Schema('my-schema', fields={'img': my_enc, 'text': dtype('str'), 'rating': dtype('int')})

db = superduper()

t = Table('my-table', schema=schema)

db.add(t)

Inserting data

Table data must correspond to the Schema for that table:

import pandas

pandas.DataFrame([
PIL.Image.open('image.jpg'), 'some text', 4,
PIL.Image.open('other_image.jpg'), 'some other text', 3,
])

t.insert(dataframe)

Selecting data

superduperdb supports selecting data via the ibis query API.

The following are equivalent:

db.execute(
t.filter(t.rating > 3).limit(5).select(t.image)
)

Vector-searches are supported via the like operator:

db.execute(
t.like({'text': 'something like this'}, vector_index='my-index')
.filter(t.rating > 3)
.limit(5)
.select(t.image, t.id)
)

Vector-searches are either first or last in a chain of operations:

db.execute(
t.filter(t.rating > 3)
.limit(5)
.select(t.image, t.id)
.like({'text': 'something like this'}, vector_index='my-index')
)

Support for raw-sql

... the first query above is equivalent to:

db.execute(
'SELECT img FROM my-table WHERE rating > 3 LIMIT 5;'
)

... the second will be equivalent to:

db.execute(
'''
SELECT img FROM my-table
LIKE text = 'something like this'
WHERE rating > 3
LIMIT 5;
'''
)

Updating data

Updates are not covered for superduperdb SQL integrations.

Deleting data

db.databackend.drop_table('my-table')