7 ms·

OctoSQL allows you to join data from different sources using SQL

114 points by BerislavLopac 4y ago

cube2222 4y ago
Hey, author of OctoSQL here, happy to see it submitted and happy to answer any questions!
- jl6 4y ago
  Great work. If you could run it as a server that other applications could connect to (e.g. over JDBC or some other common protocol), you would have the makings of an open data virtualization platform.
  - cube2222 4y ago
    Thanks! Definitely considering adding a server-mode with Postgres wire protocol compatibility. It's tricky for the more dynamic/dataflow'y parts, as OctoSQL is able to give you a live updating output table (which Postgres wire protocol doesn't support), but I can go with a similar approach as Materialize[0] does for those use cases - creating a live-updating materialized view that you can query from. That said, for now I'm still concentrating on the overall local usage experience and ergonomics, there's still much to improve there. [0]: https://materialize.com https://materialize.com
    - sitkack 4y ago
      Materialize is really neat, also checkout https://github.com/mit-pdos/noria https://github.com/mit-pdos/noria. It inverts the query problem and processes the data on insert. Exactly like what most applications end up doing using a no-sql solution.
- WaxProlix 4y ago
  It certainly seems more lightweight and modular, but I'm curious if you see this overlapping with Presto at all, in terms of product fit, scale/performance, etc. I'll almost certainly use this to pull parquet, orc, and csv data into postgres at some point in the next month or two, so thanks.
  - cube2222 4y ago
    The main difference in product fit is that OctoSQL is single-machine-only and I have no plans for distributed execution. I'm not sure about the performance of Presto, but I'd expect OctoSQL to be of fairly similar speed for complex operations/data types (operating on strings, objects, running some functions on them) and slower for vectorizable arithmetics (avg of a column of floats in a parquet file). Other than that, OctoSQL should be much easier to write plugins for, much easier to use, and even though there's no plugin for it yet, it should have much better support for streaming inputs (i.e. Kafka).
- Cwizard 4y ago
  How does the plugin system work? I thought Go didn’t allow for dynamically loading code, where you able to find a work around?
  - cube2222 4y ago
    When a plugin starts, it begins listening on a local unix socket with a gRPC server. The main OctoSQL process then connects to that gRPC server for each plugin. Omitting a bunch of details, each record stream will end up being a gRPC streaming connection. This approach is cross-platform (unix sockets work on Windows since recently!) and more than fast enough (millions of records per second), while being easy to keep organized (everything stays on the file system). That said, all of this is abstracted away by the plugin SDK, so you only have to implement the right interface and the rest will get handled by the SDK. Here's an example plugin that simple and fairly easy to read: https://github.com/cube2222/octosql-plugin-random_data https://github.com/cube2222/octosql-plugin-random_data And here's the gRPC schema for plugins, which might be useful to get a better picture: https://github.com/cube2222/octosql/blob/main/plugins/internal/plugins/plugins.proto https://github.com/cube2222/octosql/blob/main/plugins/intern...
- alexisread 4y ago
  Hi, this looks like a great addition to the dataflow toolbox! I wondered if OctoSQL supports memory-mapping parquet files like Vaex, or the whole file has to be loaded into memory first?
  - cube2222 4y ago
    I'm not sure how Vaex works, but for what it's worth, OctoSQL doesn't load whole Parquet files into memory. It will only read the columns that are used in your query and will read those columns page by page (Parquet columns are organized into pages on disk) and process pages record by record. It won't ever buffer the whole thing if it's not necessary for the query as a whole.
- fweimer 4y ago
  How does it compare to Teiid? It seems to be at least partially in the same space.
  - cube2222 4y ago
    Thanks for mentioning it! I've never heard of Teiid and it indeed does seem to have some architectural commonalities. To me, the main differences look like they are 1. Teiid is mainly targeted at OLTP workloads, while OctoSQL is more OLAP-oriented; 2. OctoSQL is to be used as a standalone CLI, while Teiid integrates into your Java Application (if I understand their docs correctly).
    - tom9729 4y ago
      Teiid can also be deployed as a standalone server (see the Wildfly route). There is a JDBC driver to connect to it, and they emulate the Postgres wire protocol so you can even use psql to connect to it and run queries.
- rubyist5eva 4y ago
  How hard would it to use this as a library? Our application does a lot of CSV processing and would love to try and see if this could help us streamline a lot of that. Our application is written in Ruby so I was thinking just writing a wrapper which shells out to the cli.
  - cube2222 4y ago
    Probably not too hard. The root.go file (in the root directory) does all the plumbing, so you should be able to either use OctoSQL as a CLI, or create your own based on that file.
    - rubyist5eva 4y ago
      Just did a quick search about embedding go code in Ruby applications and it looks relatively straightforward…looks like I have a weekend project on my hands :)
martypitt 4y ago
Congratulations, very cool project. I'm interested in learning about how federated data source query engines like this handle query planning and optimization. If I wanted to study your implementation for hints where would I start?
- cube2222 4y ago
  Thanks! I'll start by saying that OctoSQL is single-machine-only, as I'm not sure what exactly you meant with "federated". I'd recommend starting by going with a debugger through the execution of the root function in root.go, as that calls out to all the macro transformations. Then, you can take a look at the optimizer (optimizer directory) and the Postgres plugin source[0], as an example of a plugin that is able to push down predicates to the underlying database. As well as the Typecheck (logical -> physical) and Materialize (physical -> execution) transformations. I'm planning to write a few technical documents about the implementation soon, while writing some actual usage documentation as well. [0]: https://github.com/cube2222/octosql-plugin-postgres https://github.com/cube2222/octosql-plugin-postgres
  - martypitt 4y ago
    Great, thanks!
- whoevercares 4y ago
  Join AWS and study Athena Federated query feature
truth_seeker 4y ago
Ever heard of PostgreSQL FDW (Foreign Data Wrapper) and associated community developed extensions ?
- wodenokoto 4y ago
  No, I haven’t.
- cube2222 4y ago
  Yes! There's even a great project built around them to streamline their use, Steampipe[0]. That said, the last time I tried it, it was orders of magnitude slower than OctoSQL. Moreover, reusing the Postgres query engine has its advantages, but it also takes away flexibility you might want to customize the dialect - in the case of OctoSQL, that's i.e. support for streaming and live updating queries. [0]: https://steampipe.io https://steampipe.io
  - truth_seeker 4y ago
    > The last time I tried it, it was orders of magnitude slower than OctoSQL. Any link to blogpost ? what was the use case ?
    - cube2222 4y ago
      I tried the Steampipe CSV plugin on the taxi dataset that is used in the OctoSQL benchmarks. Simple grouping.
yevpats 4y ago
I like the idea of that, though Im curious if an EL approach is the right way to go especially at scale where queries might get all blocked run out of mem in OctoSQL.
- cube2222 4y ago
  Could you please explain in more detail what you mean (I'm not sure what you mean with "EL approach")? OctoSQL isn't really meant to be used "at scale", as it's not distributed. It's not trying to compete with Spark, Flink, or BigQuery. You should be good to use it with data up to the order of Gigabytes / Tens of Gigabytes, locally. OctoSQL never loads the whole table into memory if it doesn't need to. Processing happens record by record, and if predicates allow it, only part of the underlying table will ever be read (by pushing down predicates to a SQL DB you're reading from for example).
eatonphil 4y ago
OctoSQL is an awesome project and Kuba has a lot of great experience to share from building this project I'm excited to learn from. And while building a custom database engine does allow you to do pretty quick queries, there are a few issues. First, the SQL implemented is nonstandard. As I was looking for documentation and it pointed me to `SELECT * FROM docs.functions fs`. I tried to count the number of functions but octosql crashed (a Go panic) when I ran `SELECT count(1) FROM docs.functions fs` and `SELECT count(*) FROM docs.functions fs` which is what I lazily do in standard SQL databases. (`SELECT count(fs.name) FROM docs.function fs` worked.) This kind of thing will keep happening because this project just doesn't have as much resources today as SQLite, Postgres, DuckDB, etc. It will support a limited subset of SQL. Second, the standard library seems pretty small. When I counted the builtin functions there were only 29. Now this is an easy thing to rectify over time but just noting about the state today. And third this project only has builtin support for querying CSV and JSON files. Again this could be easy to rectify over time but just mentioning the state today. octosql is a great project but there are also different ways to do the same thing. I build dsq [0] which runs all queries through SQLite so it avoids point 1. It has access to SQLite's standard builtin functions plus a battery of extra statistic aggregation, string manipulation, url manipulation, date manipulation, hashing, and math functions custom built to help this kind of interactive querying developers commonly do [1]. And dsq supports not just CSV and JSON but parquet, excel, ODS, ORC, YAML, TSV, and Apache and nginx logs. A downside to dsq is that it is slower for large files (say over 10GB) when you only want a few columns whereas octosql does better in some of those cases. I'm hoping to improve this over time by adding a SQL filtering frontend to dsq but in all cases dsq will ultimately use SQLite as the query engine. You can find more info about similar projects in octosql's Benchmark section but I also have a comparison section in dsq [2] and an extension of the octosql benchmark with different set of tools [3] including duckdb. Everyone should check out duckdb. :) [0] https://github.com/multiprocessio/dsq https://github.com/multiprocessio/dsq [1] https://github.com/multiprocessio/go-sqlite3-stdlib https://github.com/multiprocessio/go-sqlite3-stdlib [2] https://github.com/multiprocessio/dsq#comparisons https://github.com/multiprocessio/dsq#comparisons [3] https://github.com/multiprocessio/dsq#benchmark https://github.com/multiprocessio/dsq#benchmark*
- cube2222 4y ago
kevincox 4y ago
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.
    - 4y ago
Cwizard 4y ago
I gave this a try. Looks neat however one thing I noticed is that it does not seem very performant in querying parquet files. Take this simple query for example: 'select * from ./order.parquet limit 5;' It takes roughly 38seconds to execute on my machine (I ran it a few times). The order file is about 300mb with 7.5m records in it. To give some comparison DuckDB can run this query in less than a second (while also directly accessing the parquet file).
- cube2222 4y ago
  Thanks for writing about this! This is a performance regression in the v0.8.0 release it seems, where the outer LIMIT clause doesn't correctly short-circuit evaluation. Overall, for a `LIMIT 10` query, it should basically be instantaneous (and is, with v0.7.3). Will fix this asap. That said, DuckDB will *definitely* be much faster for processing Parquet files, that's for sure.
  - cube2222 4y ago
    This is now fixed[0]. I've released a new version (v0.8.1) and now a query like this should once again take just ~tens of milliseconds to complete. [0]: https://github.com/cube2222/octosql/commit/ba4a8896be35ffdcc47c208faa86dbfff154c663 https://github.com/cube2222/octosql/commit/ba4a8896be35ffdcc...

114 points by BerislavLopac 4y ago

cube2222 4y ago

Hey, author of OctoSQL here, happy to see it submitted and happy to answer any questions!

jl6 4y ago
Great work. If you could run it as a server that other applications could connect to (e.g. over JDBC or some other common protocol), you would have the makings of an open data virtualization platform.
- cube2222 4y ago
  Thanks! Definitely considering adding a server-mode with Postgres wire protocol compatibility. It's tricky for the more dynamic/dataflow'y parts, as OctoSQL is able to give you a live updating output table (which Postgres wire protocol doesn't support), but I can go with a similar approach as Materialize[0] does for those use cases - creating a live-updating materialized view that you can query from. That said, for now I'm still concentrating on the overall local usage experience and ergonomics, there's still much to improve there. [0]: https://materialize.com https://materialize.com
  - sitkack 4y ago
    Materialize is really neat, also checkout https://github.com/mit-pdos/noria https://github.com/mit-pdos/noria. It inverts the query problem and processes the data on insert. Exactly like what most applications end up doing using a no-sql solution.
WaxProlix 4y ago
It certainly seems more lightweight and modular, but I'm curious if you see this overlapping with Presto at all, in terms of product fit, scale/performance, etc. I'll almost certainly use this to pull parquet, orc, and csv data into postgres at some point in the next month or two, so thanks.
- cube2222 4y ago
  The main difference in product fit is that OctoSQL is single-machine-only and I have no plans for distributed execution. I'm not sure about the performance of Presto, but I'd expect OctoSQL to be of fairly similar speed for complex operations/data types (operating on strings, objects, running some functions on them) and slower for vectorizable arithmetics (avg of a column of floats in a parquet file). Other than that, OctoSQL should be much easier to write plugins for, much easier to use, and even though there's no plugin for it yet, it should have much better support for streaming inputs (i.e. Kafka).
Cwizard 4y ago
How does the plugin system work? I thought Go didn’t allow for dynamically loading code, where you able to find a work around?
- cube2222 4y ago
  When a plugin starts, it begins listening on a local unix socket with a gRPC server. The main OctoSQL process then connects to that gRPC server for each plugin. Omitting a bunch of details, each record stream will end up being a gRPC streaming connection. This approach is cross-platform (unix sockets work on Windows since recently!) and more than fast enough (millions of records per second), while being easy to keep organized (everything stays on the file system). That said, all of this is abstracted away by the plugin SDK, so you only have to implement the right interface and the rest will get handled by the SDK. Here's an example plugin that simple and fairly easy to read: https://github.com/cube2222/octosql-plugin-random_data https://github.com/cube2222/octosql-plugin-random_data And here's the gRPC schema for plugins, which might be useful to get a better picture: https://github.com/cube2222/octosql/blob/main/plugins/internal/plugins/plugins.proto https://github.com/cube2222/octosql/blob/main/plugins/intern...
alexisread 4y ago
Hi, this looks like a great addition to the dataflow toolbox! I wondered if OctoSQL supports memory-mapping parquet files like Vaex, or the whole file has to be loaded into memory first?
- cube2222 4y ago
  I'm not sure how Vaex works, but for what it's worth, OctoSQL doesn't load whole Parquet files into memory. It will only read the columns that are used in your query and will read those columns page by page (Parquet columns are organized into pages on disk) and process pages record by record. It won't ever buffer the whole thing if it's not necessary for the query as a whole.
fweimer 4y ago
How does it compare to Teiid? It seems to be at least partially in the same space.
- cube2222 4y ago
  Thanks for mentioning it! I've never heard of Teiid and it indeed does seem to have some architectural commonalities. To me, the main differences look like they are 1. Teiid is mainly targeted at OLTP workloads, while OctoSQL is more OLAP-oriented; 2. OctoSQL is to be used as a standalone CLI, while Teiid integrates into your Java Application (if I understand their docs correctly).
  - tom9729 4y ago
    Teiid can also be deployed as a standalone server (see the Wildfly route). There is a JDBC driver to connect to it, and they emulate the Postgres wire protocol so you can even use psql to connect to it and run queries.
rubyist5eva 4y ago
How hard would it to use this as a library? Our application does a lot of CSV processing and would love to try and see if this could help us streamline a lot of that. Our application is written in Ruby so I was thinking just writing a wrapper which shells out to the cli.
- cube2222 4y ago
  Probably not too hard. The root.go file (in the root directory) does all the plumbing, so you should be able to either use OctoSQL as a CLI, or create your own based on that file.
  - rubyist5eva 4y ago
    Just did a quick search about embedding go code in Ruby applications and it looks relatively straightforward…looks like I have a weekend project on my hands :)

martypitt 4y ago

Congratulations, very cool project. I'm interested in learning about how federated data source query engines like this handle query planning and optimization. If I wanted to study your implementation for hints where would I start?

cube2222 4y ago
Thanks! I'll start by saying that OctoSQL is single-machine-only, as I'm not sure what exactly you meant with "federated". I'd recommend starting by going with a debugger through the execution of the root function in root.go, as that calls out to all the macro transformations. Then, you can take a look at the optimizer (optimizer directory) and the Postgres plugin source[0], as an example of a plugin that is able to push down predicates to the underlying database. As well as the Typecheck (logical -> physical) and Materialize (physical -> execution) transformations. I'm planning to write a few technical documents about the implementation soon, while writing some actual usage documentation as well. [0]: https://github.com/cube2222/octosql-plugin-postgres https://github.com/cube2222/octosql-plugin-postgres
- martypitt 4y ago
  Great, thanks!
whoevercares 4y ago
Join AWS and study Athena Federated query feature

truth_seeker 4y ago

Ever heard of PostgreSQL FDW (Foreign Data Wrapper) and associated community developed extensions ?

wodenokoto 4y ago
No, I haven’t.
cube2222 4y ago
Yes! There's even a great project built around them to streamline their use, Steampipe[0]. That said, the last time I tried it, it was orders of magnitude slower than OctoSQL. Moreover, reusing the Postgres query engine has its advantages, but it also takes away flexibility you might want to customize the dialect - in the case of OctoSQL, that's i.e. support for streaming and live updating queries. [0]: https://steampipe.io https://steampipe.io
- truth_seeker 4y ago
  > The last time I tried it, it was orders of magnitude slower than OctoSQL. Any link to blogpost ? what was the use case ?
  - cube2222 4y ago
    I tried the Steampipe CSV plugin on the taxi dataset that is used in the OctoSQL benchmarks. Simple grouping.

yevpats 4y ago

I like the idea of that, though Im curious if an EL approach is the right way to go especially at scale where queries might get all blocked run out of mem in OctoSQL.

cube2222 4y ago
Could you please explain in more detail what you mean (I'm not sure what you mean with "EL approach")? OctoSQL isn't really meant to be used "at scale", as it's not distributed. It's not trying to compete with Spark, Flink, or BigQuery. You should be good to use it with data up to the order of Gigabytes / Tens of Gigabytes, locally. OctoSQL never loads the whole table into memory if it doesn't need to. Processing happens record by record, and if predicates allow it, only part of the underlying table will ever be read (by pushing down predicates to a SQL DB you're reading from for example).

eatonphil 4y ago

OctoSQL is an awesome project and Kuba has a lot of great experience to share from building this project I'm excited to learn from. And while building a custom database engine does allow you to do pretty quick queries, there are a few issues. First, the SQL implemented is nonstandard. As I was looking for documentation and it pointed me to `SELECT * FROM docs.functions fs`. I tried to count the number of functions but octosql crashed (a Go panic) when I ran `SELECT count(1) FROM docs.functions fs` and `SELECT count(*) FROM docs.functions fs` which is what I lazily do in standard SQL databases. (`SELECT count(fs.name) FROM docs.function fs` worked.) This kind of thing will keep happening because this project just doesn't have as much resources today as SQLite, Postgres, DuckDB, etc. It will support a limited subset of SQL. Second, the standard library seems pretty small. When I counted the builtin functions there were only 29. Now this is an easy thing to rectify over time but just noting about the state today. And third this project only has builtin support for querying CSV and JSON files. Again this could be easy to rectify over time but just mentioning the state today. octosql is a great project but there are also different ways to do the same thing. I build dsq [0] which runs all queries through SQLite so it avoids point 1. It has access to SQLite's standard builtin functions plus a battery of extra statistic aggregation, string manipulation, url manipulation, date manipulation, hashing, and math functions custom built to help this kind of interactive querying developers commonly do [1]. And dsq supports not just CSV and JSON but parquet, excel, ODS, ORC, YAML, TSV, and Apache and nginx logs. A downside to dsq is that it is slower for large files (say over 10GB) when you only want a few columns whereas octosql does better in some of those cases. I'm hoping to improve this over time by adding a SQL filtering frontend to dsq but in all cases dsq will ultimately use SQLite as the query engine. You can find more info about similar projects in octosql's Benchmark section but I also have a comparison section in dsq [2] and an extension of the octosql benchmark with different set of tools [3] including duckdb. Everyone should check out duckdb. :) [0] https://github.com/multiprocessio/dsq https://github.com/multiprocessio/dsq [1] https://github.com/multiprocessio/go-sqlite3-stdlib https://github.com/multiprocessio/go-sqlite3-stdlib [2] https://github.com/multiprocessio/dsq#comparisons https://github.com/multiprocessio/dsq#comparisons [3] https://github.com/multiprocessio/dsq#benchmark https://github.com/multiprocessio/dsq#benchmark*

cube2222 4y ago

kevincox 4y ago

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.
  - 4y ago

Cwizard 4y ago

I gave this a try. Looks neat however one thing I noticed is that it does not seem very performant in querying parquet files. Take this simple query for example: 'select * from ./order.parquet limit 5;' It takes roughly 38seconds to execute on my machine (I ran it a few times). The order file is about 300mb with 7.5m records in it. To give some comparison DuckDB can run this query in less than a second (while also directly accessing the parquet file).

cube2222 4y ago
Thanks for writing about this! This is a performance regression in the v0.8.0 release it seems, where the outer LIMIT clause doesn't correctly short-circuit evaluation. Overall, for a `LIMIT 10` query, it should basically be instantaneous (and is, with v0.7.3). Will fix this asap. That said, DuckDB will *definitely* be much faster for processing Parquet files, that's for sure.
- cube2222 4y ago
  This is now fixed[0]. I've released a new version (v0.8.1) and now a query like this should once again take just ~tens of milliseconds to complete. [0]: https://github.com/cube2222/octosql/commit/ba4a8896be35ffdcc47c208faa86dbfff154c663 https://github.com/cube2222/octosql/commit/ba4a8896be35ffdcc...