Pivotal: a DSL for data analysis in Python

[nealbob]

I have been working on language for data analysis, has some similarities to IBIS (one syntax multiple backends). Except its a DSL that compiles to Python (pandas, polars, duckdb) intended to be used within Jupyter notebooks:

https://nealhughes.net/pivotal/

https://github.com/nealbob/pivotal-py

Would really welcome any feedback on this. The first link above explains my motivation. (Sorry if this is unwelcome)

[NickCrews]

This is cool! Can you compare/contrast with PRQL?

[NickCrews]

The other real value-adds that I think a next-gen relational-algebra DSL should have:

• type safety, eg renaming a column upstream gives errors when downstream calculations rely on the old name
• serializability, eg being able to define a query with a html builder, then being able to persist this in a DB or push it over the network so the server can execute it
• extensibility, eg when someone wants to support a new backend, they can do that with NO needed support from the core. Otherwise you end up like ibis where I feel really bogged down trying to keep the flink tests passing even though I have never and will never use that not very popular backend.
• excellent developer tooling, eg LSP, autocomplete, etc

If you are interested, these properties are the motivation behind https://github.com/NickCrews/tybis, an experiment in trying to take all the good parts of ibis and make them even better, in typescript.

[nealbob]

Hi Nick, thanks for the response.

Yes, PRQL especially if used in DuckDB / Python is probably closest to Pivotal in spirit. I think the key difference is the audience / use case: I'm targeting analytical work with small /medium in-memory data in Python (this is what I do for work, I don't really use DBs / SQL much).

I do have a simple comparison of Pivotal and PRQL syntax here and touched on it briefly in this post:

In some respects, DuckDB and PRQL address similar problems as Pivotal, just from the opposite direction: trying to modify SQL to bring it closer to Python, rather than building a native Python workflow that is more SQL like.
Ultimately, there are limits to how far you can bend SQL to suit Python analytical workflows. In practice, the mental (and performance) overhead of moving data between a SQL engine and Python can slow things down (especially for exploratory work on small datasets).

Syntax wise PRQL is still slightly more verbose than Pivotal (more brackets, aliases in merge etc.). But it gets back to the point above: PRQL is built on SQL and SQL is built for databases. There are lot of things in Python workflows that don’t fit naturally in SQL like, linking operations to metadata, saving results, plotting, functions / loops. Piped SQL / PRQL helps a bit, but why not have something built for in-memory analytical work from the start.

[nealbob]

On your other points.

• I'm interested in type safety but haven't looked into it yet. It reminds me of Pluto.jl reactive notebooks, this idea that MS Excel solved this upstream/downstream dependency thing a long time ago and notebooks have gone backwards in a sense. Seems like you could build a DAG from a pipeline style DSL without too much trouble, and use it for this purpose.
• Serializability may be less relevant for our use case
• I've tried to keep extensibility in mind, but its still Python centric. Longer-term I would like to extend Pivotal beyond Python, with a common core (maybe in Rust) that could be linked to R or Julia
• No LSP yet, but there is auto-complete etc. via VS Code / Jupyter Lab extensions