leverage ibis expression for getting readablerelations

sh-rp commented 6 days ago

Description

This is a quick and dirty implementation of the idea to use ibis expressions for selecting from our native relations. Basically I use a ReadableRelation as a Wrapper/Proxy around an ibis expression and convert it to sql at the moment of execution, which gives us a very powerful interface for free.

Please read the last test in test_read_interfaces. This test passes against duckdb (which is the hardcoded dialect for this example). It shows how you can limit, join, select, order and aggregate and also chain all of these things. More stuff is also available on the ibis expression side.

Maybe an ibis expression will actuall give us the schema of the result when executed, so if we find a way to convert our schemas into the ibis schema, including all hints with precision etc, we will be able to discover the resultschema and convert that back into a dlt schema. That would be really cool.

Stuff to figure out:

Will it work for all of our sql destinations? This depends on wether there is a sqlglot dialect available for them. It looks like dremio is the only one that is not supported.
Should we do this and if so, should this be a different relation type than the one we have, or should it replace it?
I added a few ToDos and Notes in the example implementation that point out a few things.

netlify[bot] commented 6 days ago

Deploy Preview for dlt-hub-docs canceled.

Name	Link
Latest commit	390f9a061c2312c54cd2e55045077a11f5dba045
Latest deploy log	https://app.netlify.com/sites/dlt-hub-docs/deploys/67350d363944f000099c8136

sh-rp commented 5 days ago

Another note to self, we probably need to run columns names through the normalizer. Or we assume the user will use normalized names as they are present in the schema when building these expressions.

dlt-hub / dlt