Define schemas with pydantic

[choucavalier]

Is there a way to define table schemas as pydantic models and use that as the source of truth for what the data contains, irrespective of the backend used (duckdb in my case)?

[gforsyth]

Hey @tgy!

We don't have explicit support for pydantic models, but you can create table definitions irrespective of data in a backend (or, in fact, without ever using or connecting to a backend). We call these UnboundTables:

>>> import ibis

>>> sch = ibis.schema({"a": "array<int>", "b": "float32", "c": "str"})

>>> ibis.table(sch, name="my_table")
UnboundTable: my_table
  a array<int64>
  b float32
  c string

Or if you prefer to not use our string-parsing to get the datatypes, you can use explicit ibis dtypes:

>>> import ibis.expr.datatypes as dt

>>> sch = ibis.schema({"a": dt.Array(dt.int), "b": dt.float32, "c": dt.str})

>>> ibis.table(sch, name="my_table")
UnboundTable: my_table
  a array<int64>
  b float32
  c string

Alternatively, if you have a schema-like thing defined in any one of numpy, pandas, polars, or pyarrow, you can do:

>>> from ibis.expr.schema import Schema

>>> sch = Schema.from_polars(schema_obj)  # or `pyarrow`, etc...

Now you can operate on this table like any other table

>>> t = ibis.table(sch, name="my_table")

>>> expr = t.mutate(c=t.c.replace("foo", "bar"))

>>> expr
r0 := UnboundTable: my_table
  a array<int64>
  b float32
  c string

Project[r0]
  a: r0.a
  b: r0.b
  c: StringReplace(r0.c, pattern='foo', replacement='bar')

Once you have the expression how you want it (or just to test it out) you can run it against actual data by doing something like

con = ibis.duckdb.connect("my_db_with_that_actual_table_definition.ddb")

con.to_pandas(expr)

[choucavalier]

Thanks a lot for taking the time to write this well-written and very clear answer.

My use case is as follows: I'm manipulating objects that will go into a table. Each object is a row in the table. The attributes of the object are the columns of the table. Thing is, I sometimes want to manipulate these objects individually (and not at the table-level). I was wondering if there was a way to define a Python class (e.g. with Pydantic or dataclasses) and use this as the type definition both for the objects that will be manipulated and the table that's going to represent a large collection of these objects, that will be queried using ibis.

It's nice to define a schema for the table, but I would like the types of my objects to be defined in a single place, and this definition is used both at the Python-level (single object or list of objects being manipulated) and at the ibis(table)-level.

Is there any way to do that? Having a single type definition (both the object class and the table schema)?

[gforsyth]

You could do something like this? Depending on how you want to type the dataclass, you might have to add in some type mapping (like if you were typing things using python builtin types instead of Ibis types).

>>> from dataclasses import dataclass, fields

>>> import ibis.expr.datatypes as dt

>>> @dataclass
      ...: class MyTable:
      ...:     a: dt.int32
      ...:     b: dt.str
      ...: 
      ...:     def schema(self):
      ...:         for field in fields(self):
      ...:             yield (field.name, field.type)

>>> t1 = MyTable(1, "b")

>>> t1
MyTable(a=1, b='b')

>>> ibis.schema(t1.schema())
ibis.Schema {
  a  int32
  b  string
}

[choucavalier]

Thanks! That could work indeed. What would be nicer would be to have Python types, and map them to ibis datatypes in the schema method. But this requires more work.