Hacker News Reader

Comment by cube2222 | Hacker News Reader

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

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.

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

Materialize is really neat, also checkout 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.

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.

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

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.

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.

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

How does it compare to Teiid? It seems to be at least partially in the same space.

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

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.

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?

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.

How does the plugin system work? I thought Go didn’t allow for dynamically loading code, where you able to find a work around?

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

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/intern...

Reply on news.ycombinator.com