7 ms·

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 coup

• eatonphil 5y ago
  Building a SQL IDE and want to do introspection on queries. Building a SQL frontend for some API or CLI. Building a SQL-aware proxy. Just a few ideas. :)
  • anitil 4y ago
    Interesting. Out of interest, what would an SQL-aware proxy give you? On-the-fly rewrites? Automatic caching?
    • eatonphil 4y ago
      Have you heard of Vitess? Or the company Planet Scale? Vitess is a SQL-aware proxy that helps you scale MySQL. https://vitess.io/docs/13.0/overview/whatisvitess/ https://vitess.io/docs/13.0/overview/whatisvitess/
  • achanda358 5y ago
    Some more: SQL linter, alternate DB implementations (Cockroach DB, TiDB etc), SQL optimisers
• cgio 5y ago
  Harvesting lineage information could be a good use case.
  • anitil 4y ago
    This would be so useful to me right now. So often I'm flying blind making edits and just praying I don't ruin someone else's day
  • crorella 5y ago
    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"
    • anitil 4y ago
      Where can I sign up?
• lmwnshn 5y ago
  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 https://15799.courses.cs.cmu.edu/spring2022/project1.html
  • crorella 5y ago
    haha, we did just that in my company, but aimed at distributed tables (like HDFS) for their partition and bucketing schemata.
• dmfay 5y ago
  I'm using a tree-sitter grammar^1, which category the OP doesn't mention, to index database object references in data access code and process schema migrations^2. The idea is early detection of potentially-dangerous database changes that modify or drop tables/views still used elsewhere, across the entire organization's code. It's already saved my bacon a few times. ^1 specifically https://github.com/DerekStride/tree-sitter-sql https://github.com/DerekStride/tree-sitter-sql , but there are a few others around too ^2 https://ectomigo.com https://ectomigo.com
• i_s 5y ago
  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 web request to executing in a few hundred milliseconds. A few examples: - Inlining scalar function calls (less impactful now with Sql Server 2019) - Removing joins from a query when we know it won't impact the number of records - Deepening where conditions against derived tables - Killing "branches" of union queries when they can be determined to not matter statically Yes, we could write the queries that way in the first place, but it would make them harder to compose, more verbose, and harder for the programmer to communicate intent. Not to mention, often the user can impact what the query will be, making it less feasible.
  • lmwnshn 5y ago
    > 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 detect that? Thanks! Edit: Also, what considerations do you have for rewrite rule order? Do you find that it makes a significant difference in practice?
    • i_s 5y ago
      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).
• crorella 5y ago
  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.
  • lmwnshn 5y ago
    Would you be able to provide examples of interesting facts, and is this tool (or anything similar) open-source or described publicly? Thanks!
    • crorella 5y ago
      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.
• newusertoday 5y ago
  auto generating api for frontend and backend that uses sql query and metadata.
  • password4321 5y ago
    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.
• matteote 5y ago
  I use ANTLR4 and its rewriter to translate between two similar SQL dialects, for migration purposes.
• technion 5y ago
  I'm still waiting for a very simple proxy where I can write rules like "DELETE statements must come with a WHERE".
  • orangepanda 5y ago
    Oh this doesnt need a condition, let me just WHERE 1=1
    • anitil 4y ago
      Maybe you could add that the where condition needs the name of the authorising user? Start with "WHERE stmt AND AUTHORIZED_BY='Anitil'" then strip the AUTHORIZED_BY. If they're smart enough to do "WHERE 1=1" they might be smart enough to realise that signing off on it is a bad idea
    • technion 5y ago
      Yes, but deliberate malfeasance isn't the usual problem when this occurs, as much as some variant of "the BI person hit run before the query was finished".
• gunnarmorling 5y ago
  For Debezium (a change data capture solution) we parse SQL for some of the connectors in order to keep track of the schema of captured tables as DDL statements are executed. Events we receive e.g. from MySQL aren't fully self-descriptive, you need to know the schema they adhere to in order to interpret them. As table schemas can change over time and as -- due to the async nature of streaming the transaction log -- we may receive change events produced from before a table's schema was changed, we cannot simply query the current table schema. Parsing incoming DDL events is the only way for keeping the connector's view on the schema in sync.
• pbowyer 5y ago
  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.
  • anitil 4y ago
    That is a fantastic use case! I'd have never have thought of doing this
• madebyherzblut 5y ago
  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 https://stripe.com/en-gb/sigma [2] https://github.com/apache/arrow-datafusion https://github.com/apache/arrow-datafusion
  • zasdffaa 5y ago
    > So, I am looking for a customizable query parser, planner, and execution engine That looks like an entire DB engine, isn't it? > map the public schema to the private schema sounds like view. It seems like a pretty major project as you describe it.
    • ErwinSmout 5y ago
      And if you're set on building an "entire DB engine" then you can just as well go the full mile and go for relational. As opposed to SQL.
      • zasdffaa 5y ago
        I know about TTM etc, but what business value would that bring to be fully relational? Specifically, thanks.
        • ErwinSmout 5y ago
          See the "business case for SIRA_PRISE". Imagine how many years of codeshitter-hours you [or, if you're in the DBMS market, your customers] would no longer have to pay for if you could have *ALL* of your [strictly data-related] business rules enforced by a mere *declaration* made by the business analyst c.q. the data administrator (note the absence of 'base' in that term), as opposed to having to rely on those very same codeshitters because there simply ain't no other way for you to enforce same set of said business rules other than to pay that mob for coding all that application-enforced integrity (which they are bound to get wrong because it is fundamentally beyond their ability) or the stored procedures that achieve the same but are still procedural (and is still bound to fail for essentially the same reason). If you know about TTM, you might also know about "Applied Mathematics for Database Professionals". What I'm referring to is their "execution model 6" becoming possible *WITHOUT* any [procedural form of] coding [by mere programmers]. Another way of saying this is "You can have CREATE ASSERTION if you want to".
• cryptonector 5y ago
  Here's a few: - syntax highlighting - algebraic manipulations - optimization analysis of queries seen - analysis of equivalence of queries - query rewriting (for style, performance, etc.) - RDBMS portability layer (implement a common subset of SQL, port queries to different engines; see previous item) I can probably think of more.