prql VS ibis

I'm very surprised to learn that PRQL does not natively support `like`, but you can add it like this: https://github.com/PRQL/prql/issues/1123#issuecomment-135385...

I feel the same way about SQL too, it's set and difficult to shift. But I also look at PRQL longingly - https://prql-lang.org/

For what it's worth, SQL kind of sucks. It's just the de facto choice because it's extremely widespread and good enough for 80% of the use cases out there and what's missing can be kludged on top of it, either by specific DB vendors, or by various extensions.

It's not too hard to come up with alternatives that improve upon individual aspects of SQL like https://prql-lang.org/ but the barrier of entry is about as high as trying to make a huge social media network, most attempts will remain niche.

Have a look at PRQL [1] for analytical queries. That's exactly what it's designed for. Disclaimer: I'm a contributor.

That said. I like Logica and Datalog. For me the main use case is "recursive" queries as they are simpler to express that way. PRQL has made some progress there with the loop operator but it could still be better. If you have any ideas for improvement, please reach out!

1: https://prql-lang.org/

https://github.com/PRQL/prql

PRQL - the Pipelined Relational Query Language, pronounced "Prequel".

PRQL is a modern language for transforming data — a simple, powerful, pipelined SQL replacement. Like SQL, it's readable, explicit and declarative. Unlike SQL, it forms a logical pipeline of transformations, and supports abstractions such as variables and functions. It can be used with any database that uses SQL, since it compiles to SQL.

Lots of ideas for making PRQL the best language for working with and transforming data in any environment. As a completely community driven project with no corporate association or sponsor, velocity is limited by volunteer availability and contributions so help is welcome. From compiler work in Rust, to front-end improvements on the website, and CLIs, language integrations, alternative backends, etc ... in between.

Ibis also solves this problem by providing a portable dataframe API that works across multiple backends (DuckDB by default): https://ibis-project.org/

I just want to add an additional entry to the Other cool stuff you might like in the summary: https://ibis-project.org/

It's a portable dataframe library that defaults to a DuckDB backend, but you can also use polars and pandas (among the 20 backends that it supports).

There is is still much to do especially on large table formats (iceberg/delta) and memory management when running on bigger boxes on cloud. Eg the elusive "Failed to allocate ..." bug[1] is an inhibitor to the claim that big data is dead[2]. As it is, we tried and abandoned DuckDB as a cheaper replacement for some databricks batch jobs.

[0] https://github.com/ibis-project/ibis

`_` definitely clashes with IPython and Jupyter's use of it.

However, if you import it from Ibis then it ceases to be used as "most recent result" and remains the ibis underscore object (unless of course you explicitly assign it to something else).

Regarding backend compatibility, there are definitely a few kinds of things that we don't currently abstract over. One is regular expression syntax and another is floating point math (e.g., various algebraic properties that are violated that result in slightly different outputs).

Hope you give it a go, and please report issues at https://github.com/ibis-project/ibis.

tea-tasting accepts data either as a Pandas DataFrame or an Ibis Table. Ibis is a Python package which serves as a DataFrame API to various data backends. It supports 20+ backends including BigQuery, ClickHouse, PostgreSQL/GreenPlum, Snowflake, and Spark. You can write an SQL query, wrap it as an Ibis Table, and pass it to tea-tasting.

I really don't understand the appeal of dbt vs a proper programming language. The templating approach leads to massive spaghetti. I look forward to trying out something like Ibis [0]

0: https://ibis-project.org/