HackerReposA FIELD GUIDE TO OPEN SOURCE
ProSponsor the shelf♧ 0
← Back to top all-time
cube2222
octosql
Go2019–2024
cube2222 / octosql

octosql

OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases and file formats using SQL.

Command LineData & Databases
Explore on GitHubRead Hacker News discussions
9Distinct people
72Mentions
1Explicit recommenders
5.3KGitHub stars
REPOSITORY AT A GLANCE

What to know before exploring

What it is

OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases and file formats using SQL.

GitHub

Where it is used

No documented product use has been verified yet.

What people are saying

This looks really cool! Especially using datafusion underneath means that it probably is blazingly fast. If you like this, I recommend taking a look at OctoSQL[0], which I'm…

cube2222 used a recommendation phrase. 4 indexed comments match. This is a phrase match, not a verdict.

No classified warning is attached to this page. Read the discussions before treating silence as approval.

Also in these discussions

Mentioned in the same Hacker News threads. That is not proof they are substitutes.

This week on Hacker News

Nov 8–Nov 15 UTC · Direct repository links and separately labeled related project stories. Indexed through 11/15/2026, 10:56:20 AM UTC.

No direct repository links were found in the indexed HN discussions over the past seven days. Older discussions are available below.

Mentions over time

HN mentions by year72 dated mentions

Observed mentions: 2019-07-16 13:04:35 UTC to 2024-10-07 01:11:57 UTC.

05101520252019-01-01 00:00:00 UTC to 2020-01-01 00:00:00 UTC (exclusive): 9 mentions; 3 submissions, 6 comments20192020-01-01 00:00:00 UTC to 2021-01-01 00:00:00 UTC (exclusive): 23 mentions; 2 submissions, 21 comments2021-01-01 00:00:00 UTC to 2022-01-01 00:00:00 UTC (exclusive): 4 mentions; 0 submissions, 4 comments20212022-01-01 00:00:00 UTC to 2023-01-01 00:00:00 UTC (exclusive): 25 mentions; 1 submissions, 24 comments2023-01-01 00:00:00 UTC to 2024-01-01 00:00:00 UTC (exclusive): 9 mentions; 0 submissions, 9 comments2024-01-01 00:00:00 UTC to 2025-01-01 00:00:00 UTC (exclusive): 2 mentions; 0 submissions, 2 comments2024

Counts come from the repository’s dated source evidence. Index cutoff: 2026-11-02 08:16:59 UTC.

View mention counts and date ranges
UTC ranges include their start and exclude their end. Select a period to filter the source evidence.
PeriodStart (UTC)End (exclusive, UTC)MentionsSubmissionsComments
20192019-01-01 00:00:00 UTC2020-01-01 00:00:00 UTC936
20202020-01-01 00:00:00 UTC2021-01-01 00:00:00 UTC23221
20212021-01-01 00:00:00 UTC2022-01-01 00:00:00 UTC404
20222022-01-01 00:00:00 UTC2023-01-01 00:00:00 UTC25124
20232023-01-01 00:00:00 UTC2024-01-01 00:00:00 UTC909
20242024-01-01 00:00:00 UTC2025-01-01 00:00:00 UTC202

What Hacker News says

25 sources
cube2222·Nov 3, 2022
SPyQL is really cool and its design is very smart, with it being able to leverage normal Python functions! As far as similar tools go, if you're interested, I recommend taking a look at DataFusion[0], dsq[1], and OctoSQL[2]. DataFusion is a very (very very) fast command-line SQL engine but with limited support for data formats. dsq is based on SQLite which means it has to load data into SQLite first, but then gives you the whole breath of SQLite, it also supports many data formats, but is slower at the same time. OctoSQL is faster, extensible through plugins, and supports incremental query execution, so you can i.e. calculate and display a running group by + count while tailing a log file. It also supports normal databases, not just file formats, so you can i.e. join with a Postgres table. [0]: https://github.com/apache/arrow-datafusion [
Read the rest
1]: https://github.com/multiprocessio/dsq [2]: https://github.com/cube2222/octosql Disclaimer: Author of OctoSQL
In Command-line data analytics
cube2222·Oct 3, 2022
This is really impressive! I've been following Materialize as their blog posts are a great source of inspiration when working on OctoSQL[0] (a CLI SQL dataflow engine), but was a bit surprised with how few data sources they were supporting (basically Kafka and Postgres based on their docs), but now that they're switching/pivoting to being a database themselves, this makes much more sense. I also think the architecture is really cool. Cloud-native is the way to go for modern databases and will make adoption much easier than something you'd have to host on bare metal. One question though, does this mean the open-source version is basically deprecated now and further development is closed-source, or does the open-source project represent the "compute" part of the "next gen Materialize"? Congrats and good luck with further development! [0]: h
Read the rest
ttps://github.com/cube2222/octosql
In The next generation of Materialize
cube2222·Sep 30, 2022
To add somewhat of a counterpoint to the other response, I've tried the Steampipe CSV plugin and got 50x slower performance vs OctoSQL[0], which is itself 5x slower than something like DataFusion[1]. The CSV plugin doesn't contact any external API's so it should be a good benchmark of the plugin architecture, though it might just not be optimized yet. That said, I don't imagine this ever being a bottleneck for the main use case of Steampipe - in that case I think the APIs themselves will always be the limiting part. But it does - potentially - speak to what you can expect if you'd like to extend your usage of Steampipe to more than just DevOps data. I've used the benchmark available in the OctoSQL README. [0]: https://github.com/cube2222/octosql [1]: https://github.com/apache/arrow-datafusion Disclaimer: author of OctoSQL
In Select * from cloud
cube2222·Sep 26, 2022
Actually, folks usually just use gRPC or Yaegi in Go. You can write an SDK for your software that abstracts all of that away for the plugin developer. See Terraform[0], Traefik[1], or OctoSQL[2]. Though I agree plugins would be welcome, especially for performance reasons, though also to be able to compile and load go code into a running go process (JIT-ish). [0]: https://github.com/hashicorp/terraform [1]: https://github.com/traefik/traefik [2]: https://github.com/cube2222/octosql Disclaimer: author of OctoSQL
In Go runtime: 4 years later
sjellis·Sep 25, 2022
I am currently evaluating dsq and its partner desktop app DataStation. AIUI, the developer of DataStation realised that it would be useful to extract the underlying pieces into a standalone CLI, so they both support the same range of sources. dsq CLI - https://github.com/multiprocessio/dsq DataStation desktop app - https://datastation.multiprocess.io/ Two alternative CLI tools that I looked at: sq - https://sq.io/ octosql - https://github.com/cube2222/octosql Honourable mentions to: Miller - https://miller.readthedocs.io/en/latest/index.html Dasel - https://daseldocs.tomwright.me/ These don't use SQL. VisiData is also amazing for data navigation, although it requires some effort to get the model: https://www.visidata.org/
In Run SQL on CSV, Parquet, JSON, Arrow, Unix Pipes and Google Sheet
cube2222·Sep 24, 2022Recommendation phrase
This looks really cool! Especially using datafusion underneath means that it probably is blazingly fast. If you like this, I recommend taking a look at OctoSQL[0], which I'm the author of. It's plenty fast and easier to add new data sources for as external plugins. It can also handle endless streams of data natively, so you can do running groupings on i.e. tailed JSON logs. Additionally, it's able to push down predicates to the database below, so if you're selecting 10 rows from a 1 billion row table, it'll just get those 10 rows instead of getting them all and filtering in memory. [0]: https://github.com/cube2222/octosql
In Run SQL on CSV, Parquet, JSON, Arrow, Unix Pipes and Google Sheet
cube2222·Aug 2, 2022
Hey, author here, happy to answer any questions! The logo was created for OctoSQL[0] and in the article you can find a lot of sample phrase-image combinations, as it describes the whole path (generation, variation, editing) I went down. Let me know what you think! And btw. if you get access take a look at [1] before you start using it. A ton of useful bits and pieces for your phrases. TLDR: DALL·E 2 is really cool, though takes quite a bit of work to arrive at a useful picture. Moreover, some types of images work better than others ("pencil sketch" is consistently awesome). As with programming, it's difficult to realize how much pieces you have to specify if you're not an artist - you don't know what you don't know. [0]: https://github.com/cube2222/octosql [1]: http://dallery.gallery/wp-content/uploads/2022/07/The-DALL%C...
In I used DALL·E 2 to generate a logo
cube2222·Jul 30, 2022
Hey! OctoSQL[0] author here, this is really impressive! I like this much more than the approach taken by other sqlite-based tools which first load stuff into SQLite and then let you query it. On the other hand, it does have a cons that it doesn't automatically infer the schema of the input JSON and you still have to manually parse the raw lines. Maybe it would be possible to surmount this by exposing a json-dedicated file reading function which also does SQL inference (I'm not knowledgable about SQLite internals)? One piece of feedback is with regard to the benchmarks: I think it would be worth it to add additional benchmarks which work on slightly more complex datasets than the one used here. I did a comparison of this vs OctoSQL on the Brazil dataset, and - as expected - sqlite-lines wiped the floor with it. However, then I ran the foll
Read the rest
owing queries on a slightly more complex dataset (the Amazon review dataset in this case, from SPyQL's benchmark notebook[1]): ~> time OCTOSQL_NO_TELEMETRY=1 ./octosql "SELECT COUNT(*), AVG(overall) FROM books.json WHERE reviewerName = 'James'" +-------+-------------------+ | count | avg_overall | +-------+-------------------+ | 3010 | 4.402325581395349 | +-------+-------------------+ real 0m49.805s user 0m32.169s sys 0m9.163s ~> time ./lines0-linux-amd64-sqlite3 :memory: "SELECT COUNT(*), AVG(json_extract(line, '$.overall')) FROM lines_read('books.json') WHERE json_extract(line, '$.reviewerName') = 'James'" 3010|4.40232558139535 real 1m47.933s user 1m27.024s sys 0m11.559s and as you can see, the results go in a very different direction. But anyhow, congrats on the project, and I'm pumped to see what you come up with next! [0]: https://github.com/cube2222/octosql [1]: https://github.com/dcmoura/spyql/blob/master/notebooks/json_...
In A SQLite extension for reading large files line-by-line
cube2222·Jul 16, 2022
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.
Read the rest
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 [1]: https://steampipe.io [2]: https://github.com/cube2222/octosql/blob/main/datasources/pl...
In OctoSQL allows you to join data from different sources using SQL
cube2222·Jul 16, 2022
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/cube
Read the rest
2222/octosql/blob/main/plugins/intern...
In OctoSQL allows you to join data from different sources using SQL
cube2222·Jul 15, 2022
This is a really great guide! Nice to have something official and in-depth. I have two tips I can share based on my experience optimizing OctoSQL[0]. First, some applications might have a fairly constant live heap size at any given point in time, but do a lot of allocations (like OctoSQL, where each processed record is a new allocation, but they might be consumed by a very-slowly-growing group by). In that case the GC threshold (which is based on the last live heap size) can be low and result in very frequent garbage collection runs, even though your application is using just megabytes of memory. In that case, using debug.SetGCPercent to modify that threshold at startup to be closer to 10x the live heap size will yield enormous performance benefits, while sacrificing very little memory. Second, even if the CPU profiler tells you the GC is
Read the rest
consuming a lot of time, that doesn't mean it's taking it away from your app, if it's single-threaded. `go tool trace` can give you a much better overview of how computationally intensive and problematic the GC really is, even though reading it takes some getting used to. [0]: https://github.com/cube2222/octosql
In A Guide to the Go Garbage Collector
aargh_aargh·Jun 21, 2022
Hi cube2222! Just today I tried octosql for the first time when I wanted to correlate a handful of CSV files based on identifiers present in roughly equal form. Great idea but I immediately ran into many rough edges in what I think was a simple use case. Here are my random observations. Missing FULL JOIN (this was a dealbreaker for me). LEFT/RIGHT join gave me "panic: implement me". It took me a while to figure out how to quote CSV column names with non-ASCII characters and spaces. It's not documented as far as I've seen (please document quoting rules). This worked: octosql 'SELECT `tablename.Mötley Crüe` FROM tablename.csv' replace() is documented [1] as replace(old, new, text) but actually is replace(text, old, new) just like in postgres and mysql. index() is documented [1] as index(substring, text) (postgresql equivalent: position
Read the rest
( substring text IN string text ) → integer) octosql "SELECT index('y', 'Mötley Crüe')" Error: couldn't parse query: invalid argument syntax error at position 13 near 'index' octosql "SELECT index('Mötley Crüe', 'y')" Error: couldn't parse query: invalid argument syntax error at position 13 near 'index' Hope this helps and I wish you all the best. [1] https://github.com/cube2222/octosql/wiki/Function-Documentat...
In One-liner for running queries against CSV files with SQLite

Showing the 15 most recent of 25 sources. Enable JavaScript to search and browse every indexed mention.

MAKE YOURSELF AT HOME

A few shortcuts

/
Search the collection
?
Open this guide
Esc
Close dialogs and navigation

Your saved repositories and interests stay in this browser.