SQL/ abstraction discussion

[blythed]

In order to make our claim that we serve datastores credible, we should work out and implement a few other datastore solutions.

Currently the foreseen interfaces/ abstract base classes for doing this are:

• db.base.databackend.DataBackend
• db.base.artifact_store.ArtifactStore
• db.base.metadata.MetaDatastore
• db.base.query.*

Currently the ArtifactStore and MetaDatastore are reasonably clean and separated. The primary focus then needs to be on:

• db.base.databackend.DataBackend
• db.base.query.*

The things which we need to be able to do are the following:

Prediction

Reading

• Iterate through data, given a user-specified query

Updating/ Writing

• Write a batch of outputs where each line is identified by an id

Fitting/ Training/ Learning

Reading

• Load single lines of data inside a batch specified by a query

Querying

[blythed]

Here is a proposal which @thejumpman2323 and @blythed came up with:

import typing as t
import dataclasses as dc
from abc import ABC, abstractmethod


callbacks = {
    'insert_many': [
        lambda: ...,
    ],
}


@dc.dataclass
class Collection:
    name: str
    
    def __getattr__(self, k):
        return Query(self, query_type=k, members=[k])


@dc.dataclass
class Query(ABC):
    collection: Collection
    query_type: str
    members: t.List[str]
    args: t.Optional[t.List] = None
    kwargs: t.Optional[t.Dict] = None
    is_callable: t.Optional[bool] = None

    def __getattr__(self, k):
        return Query(self, query_type=k, members=[*self.members, k])

    def __call__(self, *args, **kwargs):
        return Query(
            collection=self.collection,
            query_type=self.query_type,
            members=self.members,
            args=args,
            kwargs=kwargs
        )

    @abstractmethod
    def execute(self, db):
        pass


@db.dataclass
class MongoQuery(Query):
    def execute(db):
        if len(self.members) == 1:
            for f in self.pre_callbacks:
                ... # TBD

            out = getattr(db[self.collection], self.members[0])(*self.args, **self.kwargs)
            for f in self.post_callbacks:
                f()
            return out
        else:
            parent = ...
            return getattr(parent, self.members[-1])(*self.args, **self.kwargs)


@db.dataclass
class IbisQuery(Query):
    def __eq__(self, other):
        ...
        
    def execute(db):
        # Main difficulty is distinguishing between do we have a callable member or
        # do we have an attribute member
        # E.g. ``employees.filter(employees.department == 'HR').aggregate(avg_age=employees.age.mean())``
        # Here ``employees.department == 'PR'`` is (table, attribute, comparison)
        # Here ``employees.age.mean()`` is (table, attribute, callable)

        if len(self.members) == 1:
            for f in self.pre_callbacks:
                ... # TBD

            if self.is_callable:
                out = getattr(db[self.table], self.members[0])(*self.args, **self.kwargs)
            else:
                return getattr(self.linked_members[-2], self.members[-1])
            for f in self.post_callbacks:
                f()
            return out

[blythed]

Discussion of outputs in tabular databases

Situations

1. For MongoDB we currently use the reserved the "_outputs" field for storing outputs.
2. In SQL/ tabular databases, this is not an option - would need another table.
3. Even for some use-cases, due to write permissions, users may want to write outputs to local file, s3, other database etc..

Want to extend model to include all of the above.

Questions:

• Does IBIS allow me to add extra columns to a table?
• How will we choose the destination for outputs (model/key/query?)
• If tables are immutable in IBIS, then every new model/key/query combination will need a new table?
• Does this cause any issues?

[blythed]

https://miro.com/app/board/uXjVMvnWgwI=/

[thejumpman2323]

We should probably have separate classes for SQL/Tabular and NoSQL databases.

so for SQL we can have a generalised class with IBIS and for mongodb we can have a generalised class MongoQuery

@blythed

[blythed]

@thejumpman2323 to write a sketch of his proposal for IBIS here.

[blythed]

The goal for ibis is to be able to "trace" or serialize an IBIS query of the form:

table.filter(table.brand == 'Nike')
table.like({'x': torch.randn(10)}).filter(table.brand == 'Nike')
table.filter(table.brand == 'Nike').like({'x': torch.randn(10)})
table['brand', 'id'].filter(...).like(...)
table['brand', 'id'].like(...).filter(...)
table.insert(data)

Limited support (but not for predict or Watcher) for fetching/ querying data.

table....aggregate()

Important methods to implement:

query.select_ids
query.select_from_ids
...

[blythed]

Let us iterate with SQLlite? @thejumpman2323?

[blythed]

For vector search, the basic algorithm will be:

table.like({...}).filter(table.brand == ...)...     # perform vector-search with `VectorDatabase` and fetch `ids`
-> table.filter(table.brand. == 'Nike' and table.id in ...)...      # use `ids` to manipulate query