Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Open Source SQL Parsers

tokern.io

Post reply on HN
Print view View original

41–50 of 103 posts

Re: Open Source SQL Parsers

#41

Post by zmxz »

tehlike wrote: post #5

Not related to parsers, but i find sql syntax so backwards. Listing columns first Then table Then join Then filter Then group bys Then limit The order of operations are out of whack and makes pipeline ing a little hard. I found this to be closer to LINQ way https://github.com/prql/prql I hope in near future databases will come with better query languages...

In my case it's the opposite - SQL is perfectly readable and fits my mental model while the project you linked feels awkward and hard to read.

However, it's good to have options, I wonder if prql will get traction. It would be great if it did.

Re: Open Source SQL Parsers

#42

Post by vkazanov »

tehlike wrote: post #5

Not related to parsers, but i find sql syntax so backwards. Listing columns first Then table Then join Then filter Then group bys Then limit The order of operations are out of whack and makes pipeline ing a little hard. I found this to be closer to LINQ way https://github.com/prql/prql I hope in near future databases will come with better query languages...

...and there's more to sql trouble than just troublesome syntax, typical for languages of its generation!

1. Sql is non-modular

2. Non-orthogonal

3. Its parroting of the (beautiful) relational data model is... opinionated at best.

The standard is unreadable and not really implementable, one always has to resort to implementation docs.

Realistically there are no alternatives, but... sql is no good.

Re: Open Source SQL Parsers

#44

Post by zasdffaa »

i_s wrote: post #18
anitil wrote: post #6

I'd be interested to hear people's use cases for parsing SQL. The link talks about exploring sql history, but has anyone else got some interesting uses? A couple of times where I've needed to parse SQL I would typically write a module for sqlite3 and get it to do the parsing for me. But annoyingly I can't remember _why_ I did this or what I was trying to achieve.

At my work, we often parse and rewrite a query before handing it off to SQL Server, because there are a lot of cases where Microsoft misses obvious optimizations. Sometimes there are also optimizations we can do because of things we know at compile time, but don't fit in the type system of SQL. The impact varies all the way from just shaving off 10% of the execution time, to changing some queries from timing out in a…

Hmm? This doesn't make much sense. MSSQL does these already I think (except the first)

.

- Inlining scalar function calls (less impactful now with Sql Server 2019)

write them as table valued funcs and it'll inline them for you (ugly but it works and is easy)

.

- Removing joins from a query when we know it won't impact the number of records

That's just tree pruning. It does that

.

- Deepening where conditions against derived tables

If I understand you that's predicate pushdown. MSSQL does it well.

.

- Killing "branches" of union queries when they can be determined to not matter statically

Not sure what you mean. Can you give an example?

Re: Open Source SQL Parsers

#45

Post by zasdffaa »

i_s wrote: post #21
lmwnshn wrote: post #19

Earlier quoted context omitted.

> less impactful now with Sql Server 2019 Could I ask what your process is for detecting whether a rewrite rule is still useful in subsequent versions of the DBMS? Do you read the release notes and test things out manually, or do you have an automated A/B test thing going on? Additionally, have you ever had a rewrite rule change from being beneficial to being detrimental after upgrading versions? If so, how did you d…

To check if rewrite rules are still helpful, yea - we just read release notes and test manually. We've never had a rule change from being beneficial to detrimental. For most of them, I don't think that would be possible, because they just involve giving Sql less irrelevant things to chew on. For a few of them, like the manual scalar function inlining, I could see that being possible, so we will just need to keep chec…

> We've never had a rule change from being beneficial to detrimental

Whatever you're doing is really confusing me (see my prev post). A multi-thousand line piece of the SQL I wrote was tested on mssql 2019 and there was a blatant perf. fuckup from it's previous home on mssql 2016 (or was it 2014). Poss. down to the new cardinality estimator, I dunno.

> For example, when we trim joins from inner queries, we first trim their selections (depending on what is actually used in the outer queries).

If you looked at the query plans you will see this happens automatically. And very reliably because it is easy (indeed, quite straightforward) to do automatically.

Also... 'we just read release notes' - these optimisations are not documented (except maybe in one place which they carefully undocumented after the 1st release) because these are trade secrets. The optimiser is one of the most important and carefully guarded parts of mssql - it's not in the notes and never will be.

No offence but... seriously, wat???

Re: Open Source SQL Parsers

#46

Post by m-novikov »

There are also multiple ongoing projects attempting to implement incremental parsers(1, 2, 3) e.g. for tree-sitter. To make editing SQL code more convenient.

[1] https://github.com/m-novikov/tree-sitter-sql

[2] https://github.com/DerekStride/tree-sitter-sql

[3] https://github.com/Snowflake-Labs/lezer-snowsql

Re: Open Source SQL Parsers

#47

Post by ako »

tehlike wrote: post #5

Not related to parsers, but i find sql syntax so backwards. Listing columns first Then table Then join Then filter Then group bys Then limit The order of operations are out of whack and makes pipeline ing a little hard. I found this to be closer to LINQ way https://github.com/prql/prql I hope in near future databases will come with better query languages...

Nice example of: always begin with the end in mind - first specify what data you want to see and then specify where it should come from.

I think it helps you focus on the outcome required, and makes you more efficient deciding how to implement it.

I use the same approach for methods in normal code: first decide on the signature of the method, and potentially a unit test to test it, and then think about the implementation.

Re: Open Source SQL Parsers

#48

Post by password4321 »

newusertoday wrote: post #32
anitil wrote: post #6

I'd be interested to hear people's use cases for parsing SQL. The link talks about exploring sql history, but has anyone else got some interesting uses? A couple of times where I've needed to parse SQL I would typically write a module for sqlite3 and get it to do the parsing for me. But annoyingly I can't remember _why_ I did this or what I was trying to achieve.

auto generating api for frontend and backend that uses sql query and metadata.

Yes! Crossing the boundary between code + DB allows things like showing "this url could read/write these table columns", down to "hover over data to see the source" levels. This can track what has been accessed in the past year vs. no longer used, etc.

You can also reverse engineer missing foreign key relationships by parsing all of an apps' stored procedures (for relationship diagrams, etc.). Or just what external entities are referenced from "other" databases to get an idea of minimal schemas needed for automated testing.

Re: Open Source SQL Parsers

#49

Post by pbowyer »

anitil wrote: post #6

I'd be interested to hear people's use cases for parsing SQL. The link talks about exploring sql history, but has anyone else got some interesting uses? A couple of times where I've needed to parse SQL I would typically write a module for sqlite3 and get it to do the parsing for me. But annoyingly I can't remember _why_ I did this or what I was trying to achieve.

A couple of years ago I inherited an old, badly written web app doing up to 800 queries per page. It was slow and hammering the server.

Being read-heavy the app was an ideal candidate for output caching. But - there were no hooks in the admin code to add cache invalidation. And I wasn't going to crawl through 10s of thousands of badly written lines and add cache invalidation calls manually, because I would miss some.

So I hooked into the database layer, parsed the SQL queries and extracted the table names. The read-heavy pages on the frontend were tagged in the cache with the names of the tables they read data from. In the backend, I'd collect the table names in all the write SQL queries and then clear the cache that was tagged with these table names.

Working at the table level rather than row level it cleared more data than was needed, but it was simple and effective.

It worked really well but never went into production - in the end we forced a rewrite of the app. One day I'd like to revisit the idea.

Re: Open Source SQL Parsers

#50

Post by madebyherzblut »

anitil wrote: post #6

I'd be interested to hear people's use cases for parsing SQL. The link talks about exploring sql history, but has anyone else got some interesting uses? A couple of times where I've needed to parse SQL I would typically write a module for sqlite3 and get it to do the parsing for me. But annoyingly I can't remember _why_ I did this or what I was trying to achieve.

Slightly off-topic, but I appreciate any pointers: In a project, I want to offer an SQL interface for data analysts similar to Stripe Sigma[1]. The tricky thing is not to parse the query but to map the public schema to the private schema, add authorization, and distribute the query across data stores. So, I am looking for a customizable query parser, planner, and execution engine.

I briefly looked into Apache Calcite and DataFusion[2], but I am unsure if I am on the right track. If someone has any ideas about where to look, please let me know.

[1] https://stripe.com/en-gb/sigma [2] https://github.com/apache/arrow-datafusion

Post reply on HN