octosql
sqlite-utils
Our great sponsors
octosql | sqlite-utils | |
---|---|---|
34 | 34 | |
4,683 | 1,454 | |
- | - | |
4.3 | 8.4 | |
6 months ago | 12 days ago | |
Go | Python | |
Mozilla Public License 2.0 | Apache License 2.0 |
Stars - the number of stars that a project has on GitHub. Growth - month over month growth in stars.
Activity is a relative number indicating how actively a project is being developed. Recent commits have higher weight than older ones.
For example, an activity of 9.0 indicates that a project is amongst the top 10% of the most actively developed projects that we are tracking.
octosql
-
Wazero: Zero dependency WebAssembly runtime written in Go
Never got it to anything close to a finished state, instead moving on to doing the same prototype in llvm and then cranelift.
That said, here's some of the wazero-based code on a branch - https://github.com/cube2222/octosql/tree/wasm-experiment/was...
It really is just a very very basic prototype.
- Analyzing multi-gigabyte JSON files locally
-
DuckDB: Querying JSON files as if they were tables
This is really cool!
With their Postgres scanner[0] you can now easily query multiple datasources using SQL and join between them (i.e. Postgres table with JSON file). Something I strived to build with OctoSQL[1] before.
It's amazing to see how quickly DuckDB is adding new features.
Not a huge fan of C++, which is right now used for authoring extensions, it'd be really cool if somebody implemented a Rust extension SDK, or even something like Steampipe[2] does for Postgres FDWs which would provide a shim for quickly implementing non-performance-sensitive extensions for various things.
Godspeed!
[0]: https://duckdb.org/2022/09/30/postgres-scanner.html
[1]: https://github.com/cube2222/octosql
[2]: https://steampipe.io
-
Show HN: ClickHouse-local – a small tool for serverless data analytics
Congrats on the Show HN!
It's great to see more tools in this area (querying data from various sources in-place) and the Lambda use case is a really cool idea!
I've recently done a bunch of benchmarking, including ClickHouse Local and the usage was straightforward, with everything working as it's supposed to.
Just to comment on the performance area though, one area I think ClickHouse could still possibly improve on - vs OctoSQL[0] at least - is that it seems like the JSON datasource is slower, especially if only a small part of the JSON objects is used. If only a single field of many is used, OctoSQL lazily parses only that field, and skips the others, which yields non-trivial performance gains on big JSON files with small queries.
Basically, for a query like `SELECT COUNT(*), AVG(overall) FROM books.json` with the Amazon Review Dataset, OctoSQL is twice as fast (3s vs 6s). That's a minor thing though (OctoSQL will slow down for more complicated queries, while for ClickHouse decoding the input is and remains the bottleneck).
-
Steampipe – Select * from Cloud;
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.
[0]: https://github.com/cube2222/octosql
[1]: https://github.com/apache/arrow-datafusion
Disclaimer: author of OctoSQL
-
Go runtime: 4 years later
Actually, folks just use gRPC or Yaegi in Go.
See Terraform[0], Traefik[1], or OctoSQL[2].
Although 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
- Run SQL on CSV, Parquet, JSON, Arrow, Unix Pipes and Google Sheet
-
Beginner interested in learning SQL. Have a few question that I wasn’t able to find on google.
Through more magic, you COULD of course use stuff like Spark, or easier with programs like TextQL, sq, OctoSQL.
-
How I Used DALL·E 2 to Generate the Logo for OctoSQL
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!
- A SQLite extension for reading large files line-by-line
sqlite-utils
-
Why you should probably be using SQLite
Sounds like your problem is with SQLAlchemy, not with SQLite.
My https://sqlite-utils.datasette.io library might be a better fit for you. It's a much thinner abstraction than SQLAlchemy.
-
Welcome to Datasette Cloud
There are a few things you can do here.
SQLite is great at JSON - so I often dump JSON structures in a TEXT column and query them using https://www.sqlite.org/json1.html
I also have plugins for running jq() functions directly in SQL queries - https://datasette.io/plugins/datasette-jq and https://github.com/simonw/sqlite-utils-jq
I've been trying to drive the cost of turning semi-structured data into structured SQL queries down as much as possible with https://sqlite-utils.datasette.io - see this tutorial for more: https://datasette.io/tutorials/clean-data
This is also an area that I'm starting to explore with LLMs. I love the idea that you could take a bunch of messy data, tell Datasette Cloud "I want this imported into a table with this schema"... and it does that.
I have a prototype of this working now, I hope to turn it into an open source plugin (and Datasette Cloud feature) pretty soon. It's using this trick: https://til.simonwillison.net/gpt3/openai-python-functions-d...
-
SQLite Functions for Working with JSON
I've baked a ton of different SQLite tricks - including things like full-text indexing support and advanced alter table methods - into my sqlite-utils CLI tool and Python library: https://sqlite-utils.datasette.io
My Datasette project provides tools for exploring, analyzing and publishing SQLite databases, plus ways to expose them via a JSON API: https://datasette.io
I've also written a ton of stuff about SQLite on my two blogs:
-
Show HN: Trogon – An automatic TUI for command line apps
This is really fun. I have an experimental branch of my sqlite-utils CLI tool (which has dozens of sub-commands) running with this now and it really did only take 4 lines of code - I'm treating Trogon as an optional dependency because people using my package as a Python library rather than a CLI tool may not want the extra installed components:
https://github.com/simonw/sqlite-utils/commit/ec12b780d5dcd6...
There's an animated GIF demo of the result here: https://github.com/simonw/sqlite-utils/issues/545#issuecomme...
-
I'm sure I'm being stupid.. Copying data from an API and making a database
My project https://datasette.io/ is ideal for this kind of thing. You can use https://sqlite-utils.datasette.io/ to load JSON data into a SQLite database, then publish it with Datasette.
-
Just: A Command Runner
I've been using this for about six months now and I absolutely love it.
Make never stuck for me - I couldn't quite get it to fit inside my head.
Just has the exact set of features I want.
Here's one example of one of my Justfiles: https://github.com/simonw/sqlite-utils/blob/fc221f9b62ed8624... - documented here: https://sqlite-utils.datasette.io/en/stable/contributing.htm...
I also wrote about using Just with Django in this TIL: https://til.simonwillison.net/django/just-with-django
-
Ask HN: What Do You Use for a Personal Database
SQLite with the open source toolchain I've been building over the past five years:
https://datasette.io as the interface for running queries against (and visualizing) my data.
https://sqlite-utils.datasette.io/ as a set of tools for creating and modifying my databases (inserting JSON or CSV data, enabling full text search text)
https://dogsheep.github.io as a suite of tools for importing my personal data - see also this talk I gave about that project: https://simonwillison.net/2020/Nov/14/personal-data-warehous...
-
The Perfect Commit
> After identifying about 7 commits (with pretty basic/useless messages, and no PR link!), I then had to find the corresponding PRs based on timestamps, and search the PR history for PRs merged around those timestamps.
Not sure if this would save any time, but it is possible to search PRs by commit. For example, say git blame led me to this commit: https://github.com/simonw/sqlite-utils/commit/129141572f249e...
I could have found PR #373 via this search: https://github.com/simonw/sqlite-utils/pulls?q=bb16f52681b6d...
> I thus treat PRs as ephemeral
I think I see what you're saying but as others have pointed out, sometimes you want to add screenshots etc to the context, and you can't capture this kind of info in commit messages. So then you have two choices: issues or PRs.
> Then any review comments are preferably not addressed directly in the PR
I would think that sometimes you really do want to have a back and forth conversation in the PR, rather than just a "make this change" -> "ok done" type of feedback loop.
I view the PR as an decent place for all of this because it's basically a commit of commits, capturing the related changes/conversation/context all in a single place at the point of merge.
Is this example too large or about the right size for you? https://github.com/simonw/sqlite-utils/commit/ab8d4aad0c42f9...
-
Big data, CSV file exploration. How do you do it? Here's my way
You can use https://sqlite-utils.datasette.io/ to load data into SQLite. It's also possible to import CSVs directly using the `sqlite3` command line tool - I wrote about that here: https://til.simonwillison.net/sqlite/import-csv
What are some alternatives?
duckdb - DuckDB is an in-process SQL OLAP Database Management System
sqlmodel - SQL databases in Python, designed for simplicity, compatibility, and robustness.
q - q - Run SQL directly on delimited files and multi-file sqlite databases
trdsql - CLI tool that can execute SQL queries on CSV, LTSV, JSON, YAML and TBLN. Can output to various formats.
sqlitebrowser - Official home of the DB Browser for SQLite (DB4S) project. Previously known as "SQLite Database Browser" and "Database Browser for SQLite". Website at:
sqliteviz - Instant offline SQL-powered data visualisation in your browser
textql - Execute SQL against structured text like CSV or TSV
ImportExcel - PowerShell module to import/export Excel spreadsheets, without Excel
sqlog - Ingest Nginx logs into a SQLite database for easy querying
lnav - Log file navigator
Scoop - A command-line installer for Windows.