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/
by captaintobs 4y ago
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/executor/python.py https://github.com/tobymao/sqlglot/blob/main/sqlglot/executo.... It does the whole shebang, parsing, optimizations, logical planning, physical execution.
- gavinray 4y agoHoly smokes, this is super impressive! I have a personal question if you don't mind -- I do some SQL query generation and transpilation for both work and hobby. One headache I've run into recently is generating nested EXISTS() subqueries. Imagine you have something like this: // WHERE Name = 'Audioslave' { type: "binary_op", operator: "equal", column: { path: [], name: "Name" }, value: { type: "scalar", value: "Audioslave" }, } This is all fine, but what if you want to say "A binary operation on related entities": { type: "binary_op", operator: "equal", column: { path: ["Albums", "Tracks"], name: "AlbumId" }, value: { type: "scalar", value: 40 }, }, Which you want to generate something like: WHERE EXISTS(SELECT 1 FROM Albums WHERE Albums.ForeignKey = t.PrimaryKey AND EXISTS(SELECT 1 FROM Tracks WHERE Tracks.ForeignKey = Albums.PrimaryKey AND AlbumId = 40)) How to do this is giving me a headache for a lot of reasons and I can't seem to come up with a good way. Any tips, references, or search terms to google? Thank you, look forward to digging in more + gave your repo a star!
- captaintobs 4y agoi 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
- zasdffaa 4y agoExists can be very efficient as it allows execution to stop immediately when something is found.
- diehunde 4y agoHey, great work! Can you talk a bit about the use case that inspire you to write the tool?
- captaintobs 4y agoAt 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.
- travisjungroth 4y agoWhere I work, we handle it by having Data Scientists running experiments on our platform commit their queries as Python code to a repository of metrics.
- captaintobs 4y agoI 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.
- travisjungroth 4y agoThought you might catch that! I've actually helped swap a few things to SQLGlot from pypika.
- diehunde 4y agoGot it. I'm asking because I worked on something similar. The idea was to unit test some Airflow workflows locally. The production workflows were using Hive, but having a local Hive container was too slow for tests, so we wrote a small parser to translate the Hive queries into SQLite queries at runtime. In the end, we had a decent PoC but couldn't complete it because of all the Hive features, but it was super fun.
- ramraj07 4y agoAny plans on supporting snowflake? May I submit a PR?
- captaintobs 4y agoId love to support more engines. PRs are very welcome!
- zasdffaa 4y agoOptimisation is a tricky one as you need the various cardinalities, how do you handle that?
- captaintobs 4y agoI 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.
- contravariant 4y agoWow this looks neat. I've been looking for an easy to use sql parser (I've tried sqlparse). How permissive is the parser? Do you need to know the exact dialect up front or can it make some intelligent guesses for unknown UDFs etc?
- captaintobs 4y agoThe parser is very permissive. You don’t need the exact dialect upfront, it handles unknown udfs internally as anonymous funcs.
- contravariant 4y agoThanks, I'll definitely have a look.