DMdmoura·Jun 3, 2023
I prefer a SQL-like format. It’s not as complete but it cover most of the day-to-day use cases. Take a look at https://github.com/dcmoura/spyql (I am the author). Congrats on fq!
In Fq: Jq for Binary Formatsdcmoura
spyql
Jupyter Notebook2022–2023
dcmoura / spyql
spyql
Query data on the command line with SQL-like SELECTs powered by Python expressions
5Distinct people
13Mentions
0Explicit recommenders
926GitHub stars
What to know before exploring
What it is
Query data on the command line with SQL-like SELECTs powered by Python expressions
GitHub
Where it is used
No documented product use has been verified yet.
What people are saying
No recommendation phrase was found in the indexed comments.
No classified warning is attached to this page. Read the discussions before treating silence as approval.
Also in these discussions
No other repository is repeatedly linked in the same discussions.
This week on Hacker News
Nov 7–Nov 14 UTC · Direct repository links and separately labeled related project stories. Indexed through 11/14/2026, 3:56:07 PM 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 month13 dated mentions
Observed mentions: 2022-01-24 12:35:01 UTC to 2023-06-03 15:31:59 UTC.
Counts come from the repository’s dated source evidence. Index cutoff: 2026-11-02 08:16:59 UTC.
View mention counts and date ranges
| Period | Start (UTC) | End (exclusive, UTC) | Mentions | Submissions | Comments |
|---|---|---|---|---|---|
| Jan 2022 | 2022-01-01 00:00:00 UTC | 2022-02-01 00:00:00 UTC | 5 | 3 | 2 |
| Feb 2022 | 2022-02-01 00:00:00 UTC | 2022-03-01 00:00:00 UTC | 0 | 0 | 0 |
| Mar 2022 | 2022-03-01 00:00:00 UTC | 2022-04-01 00:00:00 UTC | 0 | 0 | 0 |
| Apr 2022 | 2022-04-01 00:00:00 UTC | 2022-05-01 00:00:00 UTC | 2 | 1 | 1 |
| May 2022 | 2022-05-01 00:00:00 UTC | 2022-06-01 00:00:00 UTC | 0 | 0 | 0 |
| Jun 2022 | 2022-06-01 00:00:00 UTC | 2022-07-01 00:00:00 UTC | 0 | 0 | 0 |
| Jul 2022 | 2022-07-01 00:00:00 UTC | 2022-08-01 00:00:00 UTC | 1 | 0 | 1 |
| Aug 2022 | 2022-08-01 00:00:00 UTC | 2022-09-01 00:00:00 UTC | 0 | 0 | 0 |
| Sep 2022 | 2022-09-01 00:00:00 UTC | 2022-10-01 00:00:00 UTC | 0 | 0 | 0 |
| Oct 2022 | 2022-10-01 00:00:00 UTC | 2022-11-01 00:00:00 UTC | 2 | 1 | 1 |
| Nov 2022 | 2022-11-01 00:00:00 UTC | 2022-12-01 00:00:00 UTC | 2 | 0 | 2 |
| Dec 2022 | 2022-12-01 00:00:00 UTC | 2023-01-01 00:00:00 UTC | 0 | 0 | 0 |
| Jan 2023 | 2023-01-01 00:00:00 UTC | 2023-02-01 00:00:00 UTC | 0 | 0 | 0 |
| Feb 2023 | 2023-02-01 00:00:00 UTC | 2023-03-01 00:00:00 UTC | 0 | 0 | 0 |
| Mar 2023 | 2023-03-01 00:00:00 UTC | 2023-04-01 00:00:00 UTC | 0 | 0 | 0 |
| Apr 2023 | 2023-04-01 00:00:00 UTC | 2023-05-01 00:00:00 UTC | 0 | 0 | 0 |
| May 2023 | 2023-05-01 00:00:00 UTC | 2023-06-01 00:00:00 UTC | 0 | 0 | 0 |
| Jun 2023 | 2023-06-01 00:00:00 UTC | 2023-07-01 00:00:00 UTC | 1 | 0 | 1 |
What Hacker News says
13 sourcesHere is a comparison of various SQL engines in command line:
https://github.com/dcmoura/spyql/blob/master/notebooks/json_...
In Command-line data analyticsThis is great!
I am the author of SPyQL [1]. Combining JC with SPyQL you can easily query the json output and run python commands on top of it from the command-line :-) You can do aggregations and so forth in a much simpler and intuitive way than with jq.
I just wrote a blogpost [2] that illustrates it. It is more focused on CSV, but the commands would be the same if you were working with JSON.
[1] https://github.com/dcmoura/spyql
[2] https://danielcmoura.com/blog/2022/spyql-cell-towers/
In JC – JSONifies the output of many CLI toolsThe fastest command-line tools for querying large JSON datasets
6 points · Open discussionClickHouse supports ad-hoc analytics, real-time reporting, and time series workloads at the same time. It is perfectly suited for user-facing analytics services. It supports low-latency (<100ms) queries for real-time analytics as well as high query throughput (500 QPS and more) - all of this with real-time data ingestion of logs, events, and time series.
Take some notable examples from the list: https://clickhouse.com/docs/en/about-us/adopters/, something around web analytics, APM, ad networks, telecom data... ClickHouse is perfectly suited for these use cases. But if you try to align these scenarios with, say, BigQuery, they will become almost impossible or prohibitively expensive or just slow.
There are specialized systems for real-time analytics like Druid and Pinot, but ClickHouse does it better:
https://benchmark.clickhouse.com/
Ther
Read the rest
e are specialized systems for time-series workloads like InfluxDB and TimescaleDB, but ClickHouse does it better:
https://gitlab.com/gitlab-org/incubation-engineering/apm/apm...
https://arxiv.org/pdf/2204.09795.pdf
http://cds.cern.ch/record/2667383/
There are specialized systems for logs and APM, but ClickHouse does it better:
https://blog.cloudflare.com/log-analytics-using-clickhouse/
There are specialized systems for ad-hoc analytics, but ClickHouse does it better as well:
https://github.com/crottyan/mgbench
Well, even if you want to process a text file, ClickHouse will do it better than any other tool:
https://github.com/dcmoura/spyql/blob/master/notebooks/json_...
And ClickHouse looks like a normal relational database - there is no need for multiple components for different tiers (like in Druid), no need for manual partitioning into "daily", "hourly" tables (like you do in Spark and Bigquery), no need for lambda architecture... It's refreshing how something can be both simple and fast.
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_...
Thank you all for your feedback. The benchmark was updated and the fastest tool is NOT written in Python. Here are the highlights:
* Added ClickHouse (written in C++) to the benchmark: I was unaware that the clickhouse-local tool would handle these tasks. ClickHouse is now the fastest (together with OctoSQL);
* OctoSQL (written in Go) was updated as a response to the benchmark: updates included switching to fastjson, short-circuiting LIMIT, and eagerly printing when outputting JSON and CSV. Now, OctoSQL is one of the fastest and memory is stable;
* SPyQL (written in Python) is now third: SPyQL leverages orjson (Rust) to parse JSONs, while the query engine is written in Python. When processing 1GB of input data, SPyQL takes 4x-5x more time than the best, while still achieving up to 2x higher performance than jq (written in C);
* I remove
Read the rest
d Pandas from the benchmark and focused on command-line tools. I am planning a separate benchmark on Python libs where Pandas, Polars and Modin (and eventually others) will be included.
This benchmark is a living document.
If you are interested in receiving updates, please subscribe to the following issue: https://github.com/dcmoura/spyql/issues/72
Thank you!
The fastest tool for querying JSON files is *not* written in Python (revised)
12 points · Open discussionI took a look to see how this works, expecting a super-complex parser/compiler - and it's mainly some straight-forward regular expression replacements! https://github.com/dcmoura/spyql/blob/4b5ea3ff52875d2c1a544f...
I'm very impressed - this is some very neat pragmatic software design.
In Show HN: SPyQL – SQL with Python in the middleSpyql: Query data on the command line with SQL
2 points · Open discussionShow HN: SPyQL – SQL with Python in the middle
SPyQL (https://github.com/dcmoura/spyql) is SQL with Python in the middle, an open-source project fully written in Python for making command-line data processing more intuitive, readable and powerful. Try mixing in the same pot: a SQL SELECT for providing the structure, Python expressions for defining transformations and conditions, the essence of awk as a data-processing language, and the JSON handling capabilities of jq.
How does a SPyQL query looks like?
$ spyql “
IMPORT pendulum AS p
SELECT
(p.now() - p.from_timestamp(purchase_ts)).in_days() AS days_ago,
sum_agg(price * quantity) AS total
FROM csv
WHERE department.upper() == 'IT' and purchase_ts is not Null
GROUP BY 1
ORDER BY 1
TO json” < my_purchases.csv
In a single statement we are 1) reading a CSV (of purchases) with automatic header
Read the rest
detection, dialect detection, type inference and casting, 2) filtering out records that do not belong to the IT department or do not have a purchase timestamp 3) summing the total purchases and grouping by how many days ago they happened, 4) sorting from the most to the least recent day and 5) writing the result in JSON format. All this without loading the full dataset into memory.
The Readme is loaded with recipes and there is also a demo video: https://vimeo.com/danielcmoura/spyqldemo
Any feedback is welcomed! Thank you.
I am writing a language and CLI that mixes SQL with Python (spyql: https://github.com/dcmoura/spyql), and it is interesting to see that we are tackling some of the same problems:
- code/formula reutilization where we need to repeat logic over the query
- functionalities like `EXCEPT` and `REPLACE` modifiers for `SELECT *`, like in google bigquery (in most SQL databases its frustrating when you have a large number of columns and you only want to hide or replace a couple of them)
I do think SQL is all over the place, and while not perfect, it’s familiar and we got used to express our queries the SQL way. At the end, since you are generating SQL to interact with databases, you would have to understand SQL in order to optimise your queries (it might be challenging to get the perfect SQL query from PRQL as you do not know statistics about the
Read the rest
tables or which indexes are available).
With SPyQL I am taking a different approach that tries to extend simple SQL SELECT statements so that some of these annoying features are tackled. In addition, by using Python to define expressions and conditions you solve another problem typically present on databases: extensibility. By including an IMPORT clause in your query you can import any Python module, so the sky is the limit. You also get a simple and intuitive way to work with objects and hierarchical data (like JSON).
I do find the language you are proposing very readable and flexible, bringing several advantages to SQL. If you build a parser I would love to bring it to spyql :-) The issues I have brought earlier would not be a problem to spyql since it is a tool to query files and data-streams in the command-line.
SPyQL – SQL with Python in the Middle
69 points · Open discussion