3 ms·

You should look at Apache Calcite[0]. Like OctoSQL, you can join data from different data sources. It's also relatively easy to add your own data sources ("adap

You should look at Apache Calcite[0]. Like OctoSQL, you can join data from different data sources. It's also relatively easy to add your own data sources ("adapters" in Calcite lingo) and rules to efficiently query those sources. Calcite already has adapters that do things like read from HTML tables over HTTP, files on your file system, running processes, etc. This is in addition to connecting to a bunch of traditional databases including SQL databases, MongoDB, Redis, and several others. Disclaimer: I am a member of the PMC, but not unfortunately not particularly active atm. [0] https://calcite.apache.org/ https://calcite.apache.org/

kevincox 4y ago
That looks pretty cool. I might reach for that next time. I was hoping for something where sources were more "on the fly" though. Like where I could run an arbitrary command to make a row. It seems like making an "adapter" is a bit more of an investment here.
- michaelmior 4y ago
  I'm curious what you mean by "arbitrary command to make a row." Could you give more of an example of a potential use case?
  - kevincox 4y ago
    The point is that it is very flexible. For example I could compute a hash of an input string, run a virus scan on a file or whatever else I wanted. In one concrete example I was trying to explore some things about Super Mario Bros so the command would be to run the emulator based on the input and output some information about the game state (character location, speed, level...)
    - michaelmior 4y ago
      I'm still not totally following your example. If it helps, it wouldn't be too challenging to make an adapter which pulls data from arbitrary shell commands.
- remram 4y ago
  Exposing a "row" is not enough for the query system though, it has to be able to do filtering and joining. If it has to scan every row for any operation, you might as well have an endpoint that returns every row... and then you're back at the currently supported workflow.
  - kevincox 4y ago
    That's what the complexity is. Some data sources will be expensive and can't be filtered so you want to avoid looking up rows as much as possible (do the filtering using other predicates if possible, do the loopup only if required). Some data sources will support efficient filtering with pushed down predicates. Ideally when "asking questions" I don't need to worry about it. The optimizer can use the info provided to do something sensible.
    - remram 4y ago
      How can the info be provided if you don't write a full adapter? I feel like I'm misunderstanding what you were asking for.
      - kevincox 4y ago
        I guess you do end up a "full adapter" but you can do it piecemeal. For most of my use cases my custom tables don't need to be highly optimal, I can just give a rough expectation of cost, and if I don't provide a batch get then obviously you can't do push-down of predicates. Some metadata can be provided by the definition, and maybe some can be learned on-the-fly (like latency and data distribution?)
        obi1kenobi 4y ago
        I designed Trustfall[0] out of frustration with existing systems that either: - didn't allow optimizations like batch get and predicate pushdown even when I knew they'd help, or - made it really difficult to add them piecemeal after-the-fact on an existing schema, by either demanding everything be batched and optimized, or that nothing be batched and optimized. In my experience, it's usually a small component of the schema that is causing most of the runtime cost, and it's not always predictable ahead of time where it's going to be. It's dependent on the queries and the data size and shape, so it changes over time. So I wanted to be able to write a working schema and adapter quickly, and then optimize the parts that were slow in practice for my workload and not just ones that "hypothetically might be slow," and this is what Trustfall lets me do. More info in my reply on an ancestor comment in the thread. [0]: https://github.com/obi1kenobi/trustfall https://github.com/obi1kenobi/trustfall
gavinray 4y ago
Echoing this, you have essentially described Calcite. The community is incredible, too.

kevincox 4y ago

That looks pretty cool. I might reach for that next time. I was hoping for something where sources were more "on the fly" though. Like where I could run an arbitrary command to make a row. It seems like making an "adapter" is a bit more of an investment here.

michaelmior 4y ago
I'm curious what you mean by "arbitrary command to make a row." Could you give more of an example of a potential use case?
- kevincox 4y ago
  The point is that it is very flexible. For example I could compute a hash of an input string, run a virus scan on a file or whatever else I wanted. In one concrete example I was trying to explore some things about Super Mario Bros so the command would be to run the emulator based on the input and output some information about the game state (character location, speed, level...)
  - michaelmior 4y ago
    I'm still not totally following your example. If it helps, it wouldn't be too challenging to make an adapter which pulls data from arbitrary shell commands.
remram 4y ago
Exposing a "row" is not enough for the query system though, it has to be able to do filtering and joining. If it has to scan every row for any operation, you might as well have an endpoint that returns every row... and then you're back at the currently supported workflow.
- kevincox 4y ago
  That's what the complexity is. Some data sources will be expensive and can't be filtered so you want to avoid looking up rows as much as possible (do the filtering using other predicates if possible, do the loopup only if required). Some data sources will support efficient filtering with pushed down predicates. Ideally when "asking questions" I don't need to worry about it. The optimizer can use the info provided to do something sensible.
  - remram 4y ago
    How can the info be provided if you don't write a full adapter? I feel like I'm misunderstanding what you were asking for.
    - kevincox 4y ago
      I guess you do end up a "full adapter" but you can do it piecemeal. For most of my use cases my custom tables don't need to be highly optimal, I can just give a rough expectation of cost, and if I don't provide a batch get then obviously you can't do push-down of predicates. Some metadata can be provided by the definition, and maybe some can be learned on-the-fly (like latency and data distribution?)
      - obi1kenobi 4y ago
        I designed Trustfall[0] out of frustration with existing systems that either: - didn't allow optimizations like batch get and predicate pushdown even when I knew they'd help, or - made it really difficult to add them piecemeal after-the-fact on an existing schema, by either demanding everything be batched and optimized, or that nothing be batched and optimized. In my experience, it's usually a small component of the schema that is causing most of the runtime cost, and it's not always predictable ahead of time where it's going to be. It's dependent on the queries and the data size and shape, so it changes over time. So I wanted to be able to write a working schema and adapter quickly, and then optimize the parts that were slow in practice for my workload and not just ones that "hypothetically might be slow," and this is what Trustfall lets me do. More info in my reply on an ancestor comment in the thread. [0]: https://github.com/obi1kenobi/trustfall https://github.com/obi1kenobi/trustfall

gavinray 4y ago

Echoing this, you have essentially described Calcite. The community is incredible, too.