sqlglot

Could the agent traces from this be used to improve sqlglot? tobymao/sqlglot: Python SQL Parser and Transpiler; with tests and support for 30+ dialects: https://github.com/tobymao/sqlglot Ibis depends upon sqlglot: https://github.com/tobymao/sqlglot/network/dependents

I'm curious because I have a similar use case for a querying frontend. Did you consider using https://github.com/tobymao/sqlglot? If so, what was missing to justify writing your own parser?

If not having to adjust queries is a major driver for your considerations, then I would highly recommend looking at SQLGlot (https://github.com/tobymao/sqlglot), a transpiler that makes you (more) independent of query dialects. They already support 30 dialects (big vendors such as Snowflake, Databricks, BigQuery, but also loads of the specialists such as ClickHouse, SingleStore or Exasol). Repo is maintained extremely well. Picking the best solution for your concrete workload (and your future demands) should be equally important to the implementation effort, to avoid that you run into walls later on. At least as long as data volume, query complexity or concurrency scalability can be challenges.

Agreed, and it's an amazingly well-maintained GitHub repo: https://github.com/tobymao/sqlglot Big kudos to Toby and the team.

Ibis has sqlglot for parsing and rewriting SQL query graphs; and there's sql-to-ibis: https://github.com/ibis-project/ibis/issues/9529 sqlglot: https://github.com/tobymao/sqlglot : > SQLGlot is a no-dependency SQL parser, transpiler, optimizer, and engine [written in Python]. It can be used to format SQL or translate between 24 different dialects like DuckDB, Presto / Trino, Spark / Databricks, Snowflake, and BigQuery. It aims to read a wide variety of SQL inputs and output syntactically and semantically correct SQL in the targeted dialects.

Thanks for the suggestion! I am using https://github.com/tobymao/sqlglot, which magically supports most SQL dialects. And yes, support for DuckDB is also in future plans

I recommend anyone who works with databases to write a simple engine. It's a lot simpler than you may think and it's a great exercise. If using python, sqlglot (https://github.com/tobymao/sqlglot) let's you skip all the parsing and it even does some simple optimizations. From the parsed query tree it's pretty straightforward to build a logical plan and execute that. You can even use python's builtin ast module to convert sql expressions into python ones (so no need for a custom interpreter!)

You can compose SQL with https://ibis-project.org/tutorials/ibis-for-sql-users, which is using https://github.com/tobymao/sqlglot to parse the SQL under the hood. As an alternative to parsing the SQL yourself, DuckDB's https://duckdb.org/docs/api/python/relational_api allows you to compose SQL expressions efficiently and lazily, which I've used when playing around with things like https://gist.github.com/ajfriend/eea0795546c7c44f1c24ab0560a...

Since you're using python, have you looked into sqlglot? I think it has some pretty-print options. https://github.com/tobymao/sqlglot

SQLFluff is a linter formatter written in Python https://sqlfluff.com/ Heard there is a faster one called sqruff, but I have not tried it https://www.quary.dev/blog/sqruff-launch Might also be able to cajole sqlglot into being a formatter but it's designed for forgiving dialect conversion, not formatting https://github.com/tobymao/sqlglot

I just started using sqlglot to convert Microsoft SQL Server code to Databricks SQL, and it has been able to automate 80% of the translation (assuming it's just a select statement). You might take a look. https://github.com/tobymao/sqlglot

This is probably referring to "zero changes to your driver code" and not "zero changes to the SQL you send over this driver". Translating between SQL dialects is notoriously hard and attempts to translate [1] are working in 95% of cases. But the last 5% would require 5x amount of work. That's because "SQL dialect" also includes weird edge cases of type inference of things like COALESCE(5, FALSE) and emulation of system catalogs (pg_catalog, information_schema). [1] https://github.com/tobymao/sqlglot

Recommend checking out https://github.com/tobymao/sqlglot if you are interested in this capability for other SQL dialects Tools like this are helpful for: - Rendering SQL in a consistent way, eg for snapshot testing - Testing SQL business logic in CI against a dialect with less heavyweight dependencies - Applying AST transformations to take advantage of dialect-specific optimizations

SQLGlot brands itself as an sql optimizer also: https://github.com/tobymao/sqlglot?tab=readme-ov-file#sql-op... but I haven't tried that aspect of it personally