3 ms·

What I really want to see is a general purpose query engine. This has some of the features but I want to see features like: - Very easy to model HTTP APIs as a

What I really want to see is a general purpose query engine. This has some of the features but I want to see features like: - Very easy to model HTTP APIs as a table. - Easy to model basically anything as a table for example files on my filesystem. - Optional caching to make development quicker (don't need to hit external services for every iteration). - The ability to build up my own personal library of "tables" or "graphs" over time in an incremental way. - A decent query planner so that I can avoid expensive things (like API calls) if I can determine if I need the object based on something cheaper (like a local disk access). - Ideally a language more compostable than SQL so that I can define common filters and views that can be shared across queries. I think the main fundamental difference is that this wants all of the data upfront in a data file. I want something that is easy to extend to sources that are possibly non-listable or at the very least I don't want to have all of the data available. IIUC this is possible with something like a Postgres or Sqlite foreign data wrapper but IIUC the optimization isn't always great and they are difficult to write.

michaelmior 4y ago
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.
      - 4y ago
- gavinray 4y ago
  Echoing this, you have essentially described Calcite. The community is incredible, too.
cube2222 4y ago
Hey! > I think the main fundamental difference is that this wants all of the data upfront in a data file. Absolutely not! Moreover, OctoSQL can push down predicates to databases so that it only has to download a small subset of the table, if the datasource and query allow it. > Very easy to model HTTP APIs as a table. "Very easy" is relative, but you can take a look at the random_data[0] datasource which is exactly this. I'm also planning to add a GitHub datasource fairly soon. That said, there is Steampipe[1] for which this is the main use case afaik (hitting API's and exposing them as tables through Postgres FWD's written in Go), so it might be a smoother and more polished experience. There's also tons of plugins already available for it. > Easy to model basically anything as a table for example files on my filesystem. Yep, definitely. That's the idea behind OctoSQL. Strive to create a tool for easily exposing anything through SQL (like your machine's processes list, an API, and join that with a file, or database). There's still lot's of documentation work left to do though, in order to make the plugin authoring experience easier. > A decent query planner so that I can avoid expensive things (like API calls) if I can determine if I need the object based on something cheaper (like a local disk access). Probably depends on the use-case, and it sometimes needs you to be fairly explicit, but OctoSQL does in fact do that. It will push down predicates to underlying databases, which means joining something small with something very big (while only taking very small amounts of the latter) can be very fast with LOOKUP JOIN's. > I want something that is easy to extend to sources that are possibly non-listable or at the very least I don't want to have all of the data available. Doable. An example of this is the `plugins.available_versions` table[2]. It requires you to provide the plugin name as a predicate, as the versions need to be downloaded from the plugin's own repository (and listing all plugin repositories on each query isn't really what you want to be doing; You can also LOOKUP JOIN with the `plugins.available_plugins` table if that is indeed what you want). [0]: https://github.com/cube2222/octosql-plugin-random_data https://github.com/cube2222/octosql-plugin-random_data [1]: https://steampipe.io https://steampipe.io [2]: https://github.com/cube2222/octosql/blob/main/datasources/plugins/available_versions.go https://github.com/cube2222/octosql/blob/main/datasources/pl...
lgas 4y ago
Based on your "model everything as a table" sensibility, you might enjoy https://www.visidata.org/ https://www.visidata.org/. It basically treats everything (CSVs, Excel files, the filesystem, SQLite files, etc) as tables and then lets you interact with them through a uniform table manipulation interface.
hansvm 4y ago
The rest sounds great too, but I particularly like the analogy of higher levels of abstraction as compost -- using composability to enrich your queries.
obi1kenobi 4y ago
I'm building out Trustfall[0] with most of the same goals in mind: query everything with a single composable syntax and fast performance, incrementally evolve the schema, and build up the types of objects it can understand and query into your own personal (or shared!) graph. I recently gave a conference talk on how we use queries over Dockerfiles + project configuration + deployment info to triage bugs and prevent them from coming back at my workplace. An example query is "find Python projects whose pyproject.toml specifies Python 3.9 but which get deployed with a Dockerfile that installs Python 3.7 instead." Here's the talk link: https://www.hytradboi.com/2022/how-to-query-almost-everything https://www.hytradboi.com/2022/how-to-query-almost-everythin... If you'd like to see Trustfall in action, I recently used it to build a Rust semver-checking tool[1] that's just a bunch of Trustfall queries like "find structs that used to be public but now aren't public anymore"[2] etc. Trustfall definitely doesn't yet do everything I'd like it to (e.g. caching), and the Adapter API needs some more polish to improve ergonomics. But if you're interested in trying it out despite those caveats, I'd love to hear what you think! [0]: https://github.com/obi1kenobi/trustfall https://github.com/obi1kenobi/trustfall [1]: https://crates.io/crates/cargo-semver-checks https://crates.io/crates/cargo-semver-checks [2]: https://github.com/obi1kenobi/cargo-semver-check/blob/main/src/queries/struct_missing.ron#L7 https://github.com/obi1kenobi/cargo-semver-check/blob/main/s... -- the query is in GraphQL syntax, you can copy-paste it into an editor to get syntax highlighting
jayd16 4y ago
Yeah, something like a client side only GraphQL would be nice. Define your service/object relations and then query remote (REST or otherwise) services as a connected graph. Data is woven together client side but the benefit is services are agnostic to it and you could weave together many public services.
- kevincox 4y ago
  Yeah, something like that would be really cool. Although I think the actual computational abilities of graphql are less than I am envisioning, but I'm not too familiar. I'm thinking of SQL-like (or even more powerful) aggregations and computations.
- obi1kenobi 4y ago
  Trustfall (see sibling comment: https://news.ycombinator.com/item?id=32120007 https://news.ycombinator.com/item?id=32120007 ) is my attempt at what you're describing: GraphQL syntax with custom directives like @recurse / @optional / aggregations / arbitrary filters, executed client-side over whatever remote APIs are available (REST or otherwise). It's by no means fully polished yet and lacks many features that'd be useful to have, but I'd love your thoughts if you decide to take a peek! It supports Rust, Python, and (extremely soon!) WASM in the browser. https://github.com/obi1kenobi/trustfall https://github.com/obi1kenobi/trustfall
gavinray 4y ago
We've done something like this at Hasura. It allows users to write adapters for datasources in any language, expose them over an HTTP API, and then combine them together into a federated GraphQL API: (Disclaimer: I work on this feature) https://techcrunch.com/2022/06/28/hasura-now-lets-developers-turn-any-data-source-into-a-graphql-api/ https://techcrunch.com/2022/06/28/hasura-now-lets-developers... https://hasura.io/blog/hasura-graphql-data-connectors/ https://hasura.io/blog/hasura-graphql-data-connectors/
HyperSane 4y ago

michaelmior 4y ago

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.
    - 4y ago
gavinray 4y ago
Echoing this, you have essentially described Calcite. The community is incredible, too.

cube2222 4y ago

Hey! > I think the main fundamental difference is that this wants all of the data upfront in a data file. Absolutely not! Moreover, OctoSQL can push down predicates to databases so that it only has to download a small subset of the table, if the datasource and query allow it. > Very easy to model HTTP APIs as a table. "Very easy" is relative, but you can take a look at the random_data[0] datasource which is exactly this. I'm also planning to add a GitHub datasource fairly soon. That said, there is Steampipe[1] for which this is the main use case afaik (hitting API's and exposing them as tables through Postgres FWD's written in Go), so it might be a smoother and more polished experience. There's also tons of plugins already available for it. > Easy to model basically anything as a table for example files on my filesystem. Yep, definitely. That's the idea behind OctoSQL. Strive to create a tool for easily exposing anything through SQL (like your machine's processes list, an API, and join that with a file, or database). There's still lot's of documentation work left to do though, in order to make the plugin authoring experience easier. > A decent query planner so that I can avoid expensive things (like API calls) if I can determine if I need the object based on something cheaper (like a local disk access). Probably depends on the use-case, and it sometimes needs you to be fairly explicit, but OctoSQL does in fact do that. It will push down predicates to underlying databases, which means joining something small with something very big (while only taking very small amounts of the latter) can be very fast with LOOKUP JOIN's. > I want something that is easy to extend to sources that are possibly non-listable or at the very least I don't want to have all of the data available. Doable. An example of this is the `plugins.available_versions` table[2]. It requires you to provide the plugin name as a predicate, as the versions need to be downloaded from the plugin's own repository (and listing all plugin repositories on each query isn't really what you want to be doing; You can also LOOKUP JOIN with the `plugins.available_plugins` table if that is indeed what you want). [0]: https://github.com/cube2222/octosql-plugin-random_data https://github.com/cube2222/octosql-plugin-random_data [1]: https://steampipe.io https://steampipe.io [2]: https://github.com/cube2222/octosql/blob/main/datasources/plugins/available_versions.go https://github.com/cube2222/octosql/blob/main/datasources/pl...

lgas 4y ago

Based on your "model everything as a table" sensibility, you might enjoy https://www.visidata.org/ https://www.visidata.org/. It basically treats everything (CSVs, Excel files, the filesystem, SQLite files, etc) as tables and then lets you interact with them through a uniform table manipulation interface.

hansvm 4y ago

The rest sounds great too, but I particularly like the analogy of higher levels of abstraction as compost -- using composability to enrich your queries.

obi1kenobi 4y ago

I'm building out Trustfall[0] with most of the same goals in mind: query everything with a single composable syntax and fast performance, incrementally evolve the schema, and build up the types of objects it can understand and query into your own personal (or shared!) graph. I recently gave a conference talk on how we use queries over Dockerfiles + project configuration + deployment info to triage bugs and prevent them from coming back at my workplace. An example query is "find Python projects whose pyproject.toml specifies Python 3.9 but which get deployed with a Dockerfile that installs Python 3.7 instead." Here's the talk link: https://www.hytradboi.com/2022/how-to-query-almost-everything https://www.hytradboi.com/2022/how-to-query-almost-everythin... If you'd like to see Trustfall in action, I recently used it to build a Rust semver-checking tool[1] that's just a bunch of Trustfall queries like "find structs that used to be public but now aren't public anymore"[2] etc. Trustfall definitely doesn't yet do everything I'd like it to (e.g. caching), and the Adapter API needs some more polish to improve ergonomics. But if you're interested in trying it out despite those caveats, I'd love to hear what you think! [0]: https://github.com/obi1kenobi/trustfall https://github.com/obi1kenobi/trustfall [1]: https://crates.io/crates/cargo-semver-checks https://crates.io/crates/cargo-semver-checks [2]: https://github.com/obi1kenobi/cargo-semver-check/blob/main/src/queries/struct_missing.ron#L7 https://github.com/obi1kenobi/cargo-semver-check/blob/main/s... -- the query is in GraphQL syntax, you can copy-paste it into an editor to get syntax highlighting

jayd16 4y ago

Yeah, something like a client side only GraphQL would be nice. Define your service/object relations and then query remote (REST or otherwise) services as a connected graph. Data is woven together client side but the benefit is services are agnostic to it and you could weave together many public services.

kevincox 4y ago
Yeah, something like that would be really cool. Although I think the actual computational abilities of graphql are less than I am envisioning, but I'm not too familiar. I'm thinking of SQL-like (or even more powerful) aggregations and computations.
obi1kenobi 4y ago
Trustfall (see sibling comment: https://news.ycombinator.com/item?id=32120007 https://news.ycombinator.com/item?id=32120007 ) is my attempt at what you're describing: GraphQL syntax with custom directives like @recurse / @optional / aggregations / arbitrary filters, executed client-side over whatever remote APIs are available (REST or otherwise). It's by no means fully polished yet and lacks many features that'd be useful to have, but I'd love your thoughts if you decide to take a peek! It supports Rust, Python, and (extremely soon!) WASM in the browser. https://github.com/obi1kenobi/trustfall https://github.com/obi1kenobi/trustfall

gavinray 4y ago

We've done something like this at Hasura. It allows users to write adapters for datasources in any language, expose them over an HTTP API, and then combine them together into a federated GraphQL API: (Disclaimer: I work on this feature) https://techcrunch.com/2022/06/28/hasura-now-lets-developers-turn-any-data-source-into-a-graphql-api/ https://techcrunch.com/2022/06/28/hasura-now-lets-developers... https://hasura.io/blog/hasura-graphql-data-connectors/ https://hasura.io/blog/hasura-graphql-data-connectors/

HyperSane 4y ago