captaintobs

ah i see

why did you choose polars over duckdb?

Even if you don't have much data, SQLMesh will save you time over dbt. Iteration is an important part to developing data pipelines, and fully refreshing your warehouse every time is wasteful and can slow you down.

Trying out SQLMesh is easy, you can import your existing project and leverage SQLMesh's runtime. At the very least, you can add unit tests to your project.

You can see a more detailed comparison here https://sqlmesh.readthedocs.io/en/stable/comparisons/.

Nice work! As a software / data engineer, I totally agree. I always default to SQL and then only write Spark code when needed.

I've run SQLGlot on the browser using Pyodide or something before. Since it has no deps, it didn't have any issues.

thanks!

Cool, sorry that SQLGlot didn't satisfy your needs. But if you file issues, we fix them quickly.

I believe one of the main benefits of it being in Python is that it's easy to follow and jump in.

Haha, well they are two very different projects with different goals :)

it can do everything that can be done with the query and schema. it doesn't yet do indices or physical statistics

recursion!

https://github.com/tobymao/sqlglot/tree/main/sqlglot/optimiz...

I'm the author of SQLGlot. Optimizing SQL is quite common and means things like projection / predicate push downs and logical simplification. SQL can be optimized relatively easily compared to a programming language because it is declarative. For example

SELECT * FROM (SELECT * FROM x) WHERE z = 1

can be optimized into

SELECT * FROM x where z = 1

i made one as well called SAQ!

https://github.com/tobymao/saq

Looks like you use BLPOP, but there could be race conditions or lost jobs if your workers crash, check out BLMOVE and RPOPLPUSH.

For this use case, it’s intentional, since only the outcome is important to us. That’s kind of the draw of a declarative language, you ask what you want and don’t have too much control over how that’s done (the engine should optimize that away).

Yes. SQLGlot is very customizable and you can pretty much override everything. It kind of needs to be flexible because even common sql dialects vary greatly.

Here's an example of how we use SQLGlot to output raw Python code directly from SQL.

https://github.com/tobymao/sqlglot/blob/main/sqlglot/executo...

It's easy to add errors for dialects by calling #unsupported when a function is used.

In terms of versioning of engines, I haven't implemented that yet, but presumably it could be done by adding a dialect subclass and having versioning route to it, so we could do something like parse(sql, dialect="spark", version=...) which could then route to a 2.3 version of spark.

Happy to chat more about this and we can see about adding it (or feel free to make a pr). You can DM me on twitter or some other avenue as well if you want to dive in deep.

This is what the sqlglot optimizer is used for. The optimizer converts EXISTS and IN into a canonicalized SQL (some variant of left join) which can then be compared to another query.

So if you run the optimizer first and then the diff tool, it could solve this kind of use case.

yep, we do the same but using duckdb, it's got a lot more analytical functions and closer to hive than sqlite

I've added support now for any, all, and some. Some is identical to Any so I always default to Any.

You can see what it took to add this in this commit.

https://github.com/tobymao/sqlglot/commit/ab49a3a2964bdff3a3...

SQLGlot supports nested queries and EXISTS. SOME, ANY, and ALL aren't fully supported yet but it's only a couple lines to add support since I already support EXISTS.

https://github.com/tobymao/sqlglot/blob/main/tests/fixtures/... https://github.com/tobymao/sqlglot/blob/main/tests/fixtures/...

You should try it out and see. Here are the test cases

https://github.com/tobymao/sqlglot/blob/main/tests/fixtures/...

I can even parse and optimize TPC-H https://github.com/tobymao/sqlglot/blob/main/tests/fixtures/....

SQLGlot's parser handles a superset of SQL, so it's much more flexible than any one engine.

The parser is very permissive. You don’t need the exact dialect upfront, it handles unknown udfs internally as anonymous funcs.

Id love to support more engines. PRs are very welcome!

I don’t do join order optimizations because that relies on cardinality estimation, I leave that up to the physical plan. But there’s plenty more to optimize like predicate and projection pushdown.

I actually built the system you work on :). It was the main inspiration for this project because PyPika is not a great experience for data scientists.

Awesome, let me know if you have any questions or suggestions if you check it out!

At the large tech companies I've been working at, there are many different big data engines that speak different dialects of sql (presto / spark). People write SQL queries in one language and want to run it in another, but it doesn't just work, there are many parts of the query that need to be manually changed in order for it to run which is tedious and error prone.

i rarely ever use exists and prefer to do left joins or left semi joins (in spark)

i'm not exactly sure what you're asking though, in terms of sql generation, it's not difficult for me because i just take in sql and output sql from the ast

performance is not that important, usually the time to run a sql query takes 100ms > 1 hour, and so it doesn't matter that parsing it takes 0.5 ms.

Author here, feel free to ask me any questions!

Something that I'm working on is a pure python SQL engine https://github.com/tobymao/sqlglot/blob/main/sqlglot/executo.... It does the whole shebang, parsing, optimizations, logical planning, physical execution.