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

21–30 of 103 posts

Re: Open Source SQL Parsers

#21

Post by i_s »

lmwnshn wrote: post #19
i_s wrote: post #18

Earlier quoted context omitted.

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…

> 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 checking.

For rewrite rule order, I guess we just do the ones that can enable other optimizations first. So far, that has been pretty simple to determine. For example, when we trim joins from inner queries, we first trim their selections (depending on what is actually used in the outer queries).

Re: Open Source SQL Parsers

#22

Post by crorella »

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.

We created a custom purpose parser to get some interesting facts about the SQL statements running in our warehouse, a couple of millions of queries per day. With that information, we created a tool that finds the best partition and bucketing schema for each table, based on how the query patterns of downstream pipelines access them.

Since some of the tables are multi-petabyte with thousand of downstream consumers, the savings have been in the millions of USD, because of CPU savings mostly, but also there is the benefit of improved wall time and data arriving much earlier to dashboards and reports.

Re: Open Source SQL Parsers

#23

Post by lmwnshn »

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…

Thanks! Super interesting.

Re: Open Source SQL Parsers

#24

Post by bogomipz »

>"Parsing SQL queries provides superpowers for monitoring data health"

Can someone say how this works exactly? What does parsing offer that things like logs and other metrics don't in terms of monitoring?

Re: Open Source SQL Parsers

#25

Post by crorella »

lmwnshn wrote: post #12
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.

In Andy Pavlo's current seminar class 15-799 [0], everyone parsed SQL to extract which columns were being accessed in a workload's queries. This was used to build an automatic index tuning tool. [0] https://15799.courses.cs.cmu.edu/spring2022/project1.html

haha, we did just that in my company, but aimed at distributed tables (like HDFS) for their partition and bucketing schemata.

Re: Open Source SQL Parsers

#26

Post by lmwnshn »

crorella wrote: post #22
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.

We created a custom purpose parser to get some interesting facts about the SQL statements running in our warehouse, a couple of millions of queries per day. With that information, we created a tool that finds the best partition and bucketing schema for each table, based on how the query patterns of downstream pipelines access them. Since some of the tables are multi-petabyte with thousand of downstream consumers, the…

Would you be able to provide examples of interesting facts, and is this tool (or anything similar) open-source or described publicly? Thanks!

Re: Open Source SQL Parsers

#27

Post by crorella »

cgio wrote: post #11
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.

Harvesting lineage information could be a good use case.

Yes! you can then put a UI on top of that and answer stuff like "who will get affected by my change on this table?" or.. "there is a bug in my pipeline and now I need to notify downstream consumers"

Re: Open Source SQL Parsers

#28

Post by crorella »

lmwnshn wrote: post #26
crorella wrote: post #22

Earlier quoted context omitted.

We created a custom purpose parser to get some interesting facts about the SQL statements running in our warehouse, a couple of millions of queries per day. With that information, we created a tool that finds the best partition and bucketing schema for each table, based on how the query patterns of downstream pipelines access them. Since some of the tables are multi-petabyte with thousand of downstream consumers, the…

Would you be able to provide examples of interesting facts, and is this tool (or anything similar) open-source or described publicly? Thanks!

Interesting facts about the tool, we created the parser with speed and efficiency in mind, it is able to process ~5M queries in less than an hour, since many of the SQL statements are associated with scheduled pipelines, I came up with a way of normalizing them and generating a signature we use to skip them if they have not changed, that saved a lot of processing when parsing.

We also created some other datasets that tell you how the tables are normally joined and another team created a ML model that now we use in an internal tool that automatically recommends the best join keys when you join 2 or more tables (since we have lots of historical info on that already stored).

We also had to create a very efficient table profiler to evaluate the candidates, because in some cases there are columns that are widely used in equi-where conditions, but their cardinality is very high, making them bad partition columns.

One guy created a parser that actually gets the most common values used to filter each column, I guess we could use that in the future to materialize some views; my original vision of the project was to continue with partial aggregations for common computations. The thing is there are several pieces of code that are pretty much copy/pasted and reused in many pipelines, so why not materialize those and rewrite the SQL of the subsequent pipelines to leverage the materialized version? huge savings there.

We are planning to present it in VLDB or a similar forum, there are some aspects of it that we need to 'clean' if we want to open source it.

Other parts of the system include the candidate evaluation and the module that computes the expected savings for the best candidate selected during evaluation; this system in particular has a lot of specific Presto and Spark logic that might need to get more general if we want to open source it.

Re: Open Source SQL Parsers

#29

Post by lmwnshn »

crorella wrote: post #28
lmwnshn wrote: post #26

Earlier quoted context omitted.

Would you be able to provide examples of interesting facts, and is this tool (or anything similar) open-source or described publicly? Thanks!

Interesting facts about the tool, we created the parser with speed and efficiency in mind, it is able to process ~5M queries in less than an hour, since many of the SQL statements are associated with scheduled pipelines, I came up with a way of normalizing them and generating a signature we use to skip them if they have not changed, that saved a lot of processing when parsing. We also created some other datasets that…

Thanks for the detailed response, looking forward to the VLDB paper when it happens! Your vision sounds cool. I wonder if you could get most of the way there by exposing the workload (across different pipeline stages) to a materialized view recommender.

In the class project mentioned elsewhere, I found normalizing queries to be pretty slow in practice (naive standardized formatting + query templatization, tried various Python libraries, settled on pglast). I didn't think about trying "skip if fingerprint matches", which may help considerably. Fast normalization is nice! :)

Re: Open Source SQL Parsers

#30

Post by crorella »

lmwnshn wrote: post #29
crorella wrote: post #28

Earlier quoted context omitted.

Interesting facts about the tool, we created the parser with speed and efficiency in mind, it is able to process ~5M queries in less than an hour, since many of the SQL statements are associated with scheduled pipelines, I came up with a way of normalizing them and generating a signature we use to skip them if they have not changed, that saved a lot of processing when parsing. We also created some other datasets that…

Thanks for the detailed response, looking forward to the VLDB paper when it happens! Your vision sounds cool. I wonder if you could get most of the way there by exposing the workload (across different pipeline stages) to a materialized view recommender. In the class project mentioned elsewhere, I found normalizing queries to be pretty slow in practice (naive standardized formatting + query templatization, tried vario…

> I wonder if you could get most of the way there by exposing the workload (across different pipeline stages) to a materialized view recommender

yes! that's something we are trying to do, since we have a way to create signatures for SQL statements and subqueries are just SQL statements then we can get all the "signatures" a query use and compare if other queries are using the same signatures. Then just sort those queries by number of times used and put some other perf metrics like IO/CPU needed to compute it and you get a good starting point.

Microsoft did something similar with Azure, using bipartite graphs, their solution was more advanced as they also baked in constraints like "the materialized view can't be more than X GB in size" but the end result is the same. (https://www.microsoft.com/en-us/research/uploads/prod/2018/0...)

Post reply on HN