4 ms·

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 :)

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 :)