10 ms·
Queryparser, an Open Source Tool for Parsing and Analyzing SQL
- sixdimensional 9y agoThis is an awesome utility and great that it was open sourced. With even more SQL dialects, this can get even more interesting as one could try to understand usage across heterogeneous databases. I'm going to go all the way with this thought, as it is something I've been wanting to do for a long time. I've been wanting to build an open source query proxy server. The idea is that, it would sit in front of any database that accepts any type of query expression (first use case, SQL, of course). You would register database drivers with the platform and then clients wouldn't need them anymore. The proxy would accept queries destined for targets via a service call. Traffic to the service can be secured and encrypted. It would be able to be deployed in a cluster, so there are multiple endpoints to hit to load balance traffic. It would parse all queries, recording the metadata about the queries and destination servers / tables / objects using something like Queryparser, so you'd have the benefit of all this metadata, but now you'd have it across data sources too. This kind of data can also help power a data catalog, which could be a web app on top of the metadata / query repository, to help users find and/or annotate the data flowing around their organization (e.g. data democracy). It could also have caching for result sets, additional security measures (RBAC), or even be linked up with a processing layer for additional processing (like Apache Spark). There are commercial solutions for this, but I want to see an open source one. Especially because, I'd like to see all dialects and query type expressions supported, and I think only an open source project could get this kind of support across industry players. One way I have thought about this type of query proxy is, it's a bit like a GraphQL back-end web service endpoint but with SQL instead of GraphQL as the interface. Using the native SQL/query expressions of the destination platform would simplify the work, since one would not need to create a generic query expression abstraction. We just need a way to extract the relevant information from the dialect/query that we are receiving. We can let data platforms have different query semantics/dialects but still get metadata. For example, there are ways to map concepts for MongoDb to relational ones (collections are like tables, fields are like columns, etc.) so even a MongoDb query expression could go through the query proxy, and have metadata recorded in a consistent format, as long as you could express it to the query proxy's web service interface. Maybe I'm just daydreaming.. but I would love to work on that and let it be open source.
- dllthomas 9y agoI really wanted to see it used for type checking (of domain-relevant types, not just the sql representational types), but I left before that got anywhere.
- contingencies 9y agoIIRC 10+ years ago MySQL already had one of these and it was Lua based, the primary intent was for transparent sharding.
- rzzzt 9y agoYou might be thinking of this project: https://github.com/mysql/mysql-proxy https://github.com/mysql/mysql-proxy
- contingencies 9y agoYep.
- elserj 9y agoSince someone has already mentioned Apache Calcite once... Take a look at https://calcite.apache.org/avatica/ https://calcite.apache.org/avatica/ -- it does some of what you're mentioning.
- d33 9y agoIt's a pity that the thing has to get reimplemented all over so many times. For example, you'd think you could reuse PostgreSQL's parser, but its code is autogenerated and riddled with side effects: https://github.com/postgres/postgres/blob/master/src/backend/parser/gram.y https://github.com/postgres/postgres/blob/master/src/backend... We've a lot of got languages and usually their parsers are tightly coupled, basically forcing people to reimplement them if you want a flexible parser. I'd say it would be great if we had one parsing library for each language, ideally used at its core. I think that would greatly simplify implementing linters, static analysers and maybe even tools that generate code. Also, perhaps this would enable even more cooler hacks?
- sixdimensional 9y agoI agree, I wish every vendor would publish an official open source parser for their SQL dialects, or that the community would create a repository of standard parsers or grammars for them all. We are almost there with ANTLR grammars... https://github.com/antlr/grammars-v4 https://github.com/antlr/grammars-v4
- dllthomas 9y agoThe trickiest bit isn't parsing per-se, but disambiguating references, so grammar alone isn't enough. And unfortunately, disambiguating references doesn't work the same way everywhere - for instance, tables on Hive will shadow a CTE.
- manigandham 9y agoDefinitely agreed on the amount of duplicate effort. Apache Calcite seems to be a good solution but usually gets ignored: https://calcite.apache.org https://calcite.apache.org Interestingly, Uber does use Calcite themselves for AthenaX: https://github.com/uber/AthenaX/ https://github.com/uber/AthenaX/
- dllthomas 9y agoWhen I looked at Calcite, the branding was very focused on query optimization, which was sufficiently unrelated to what I was doing that I didn't dig in. Had I, it might've been the starting point instead of my personal project, although at the time it didn't have Vertica support (I think that was ultimately added by a different group at Uber).
- i_s 9y agoIf you are using SQL Server, you may be interested to hear this already exists for .NET: https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.transactsql.scriptdom.aspx https://msdn.microsoft.com/en-us/library/microsoft.sqlserver... It can both parse and generate SQL scripts. I've been leaning on it heavily for some of our non-trival query APIs.
- dllthomas 9y agoScanning the docs, it's unclear whether that's just the parsing or also analysis.
- i_s 9y agoIt is 'just' the parsing, but the analysis is fairly easy. Here is an example of: 1. Taking a database 2. Parsing every view and stored procedure 3. Tracking how tables are joined 4. Creating a graph visualization of the tables and their relationships: https://github.com/isaksky/FsSqlDom/blob/master/examples/FsSqlDomGallery/FsSqlDomGallery/GraphTableRelationships.fs https://github.com/isaksky/FsSqlDom/blob/master/examples/FsS... (Not perfect, but does a pretty good job on databases I have tested.)
- intrasight 9y agoI've long thought that a graph visual based query client would be really neat
- weavie 9y agoDidn't ms access have something like this?
- intrasight 9y agoYes - "something like". I'm envisioning more interact with the graph rather than the graph only showing relationships.
- fuzzieozzie 9y agoA more general/robust implementation of this functionality (and a whole lot more) has been developed by http://www.compilerworks.com http://www.compilerworks.com Covers many SQL dialects: Proprietary: Teradata, Netezza, Vertica, Oracle PL/SQL Open Source:Hive, Presto, Postgre, MySQL, Derby, Splice Machine Cloud: RedShift, Snowflake, BigQuery
- dllthomas 9y agoAlso of note, https://github.com/uber/queryparser/blob/master/FUTURE.md https://github.com/uber/queryparser/blob/master/FUTURE.md got cut from earlier drafts.
- chx 9y agoIs it just me who cringes heavily reading this? A single Haskell enthusiast decides to code up something in Haskell despite it doesn't fit the infra and most people don't know it. And they do this while Python already has SQL parsers. WAT.
- johnhenry 9y agoNot cringing, but I think I understand your point... However; I feel like with modern infrastructures, projects don't really "fit" can be packaged into a container, and used virtually anywhere. (Oversimplification acknowledged!)
- mikekchar 9y agoContainers aren't the problem. Even without containers, it's not that hard to automate the build/deployment infrastructure. The problem is when "Joe" is the only person who knows how to do anything with X. "Joe" leaves for a high flying company that uses X. You have new requirements for the thing "Joe" was working on. "Anybody fancy working on an X project?" <crickets> "I know. Why don't we rewrite this in Y. I hear it's the new X anyway.", "Great idea!" Don't get me wrong, I'm a believer in using the right tool for the job. It's just that there's more to it than just making sure it's easy to install.
- johnhenry 9y agoTrue -- getting it to work within your infrastructure is only part of the issue. Updating and maintenance would still be an issue.
- yen223 9y agoUse the right tool for the right job. Haskell is extremely well-suited for building language parsers.
- whateveracct 9y agoAnybody that manages to use corporate money for community/societal gain* deserves to be lauded. * In this case, furthering the use of Haskell in industry
- tabtab 9y agoIt's time for a new relational query language(s) to compete with SQL. We need something that's more API-based rather than using custom key-words in a COBOL-esque way. With the API-ish approach, Vendors (and DBA's) may still add proprietary functions/methods, but the syntax itself could be consistent across vendors. It's roughly comparable to using XML to define a domain-specific sub-language: a standard XML parser can be used to parse it even though specific tags or attributes are added by the vendor/domain. The syntax charts for SQL are pasta-city. My favorite SQL-replacement candidate is SMEQL (formerly TQL). One of its features is using tables, real or virtual, to describe the attributes of schemas and RDBMS settings rather than language text. This also makes it easier to "query" commands, giving one meta abilities/abstraction.
- manigandham 9y agoSQL is fine, most of the quirky way of stating things is because it's declarative, which actually makes it very powerful. The biggest problem is that the standards move so slowly that many of the database implementers came up with their own dialects. The latest official SQL:2016 is actually pretty great and it would be better if all the various databases converged back to that, however it's probably too late for that now, let alone a whole new language...
- tabtab 9y agoThe API/functional-versus-keyword issue is not about declarative versus imperative. Those are mostly independent issues. Therefore, I'm not following you. Perhaps there's a miscommunication.
- manigandham 9y agoSQL already has UDFs and even custom types. Those can be namespaced and customized with built-in implementations by all databases, which they all already do to some extent (eg: system data queries) so it seems the only real issue is the fact that the SQL dialects are different. If the SQL dialect was the same across all databases, then what would be the advantage of an "API" approach as you say?
- jmiserez 9y ago>To achieve this, our Data Warehouse team was tasked with identifying every foreign-key relationship between every table in the data warehouse to backfill all the ID columns with corresponding UUIDs. Given the decentralized ownership of our tables, this was not a simple endeavor. The most promising solution was to crowdsource the information by scraping all the SQL queries submitted to the warehouse and observing which columns were joined together. Am I missing something, or does scraping SQL queries to effectively get an ER diagram sound a bit strange? If you have SQL and a relational database, don’t you already have a formal model of the relations encoded somewhere? E.g. in the table definitions, annotations in your code, or at least as a Word document? The tool sounds great, but isn’t it a failure having to write it in the first-place?
- bastawhiz 9y ago(former Uber engineer) Data lived in many databases across the company. Vertica, hive, schemaless, Cassandra, etc. The value in a column might refer to an id in an entirely different system. When you have (tens of?) thousands of tables owned by hundreds of teams, documentation is neither homogeneous nor centralized.
- jmiserez 9y agoI see. Very cool approach to have a tool replace people reading documentation, and certainly more accurate and up-to-date. Also I re-read only now that this is for a DWH, in which case it makes even more sense.
- fusiongyro 9y agoSo, why does it make sense in this situation ("you can all do what you please with your data") to mandate something technical ("but starting today you all must use UUIDs from now on") across all groups? What's the value of this change if using UUIDs is now literally the only thing they all have in common?
- jhoechtl 9y agoProcess mining to identify yet unknown relationships. This is more sort of a big data "what relates to each-other yet we don't know it" analysis
- batbomb 9y agoFor those who don't know, the Presto parser is easy to use and analyze if you are programming in Java/JVM. It's close to the grammar and the IR is really good.
- tkyjonathan 9y agoYou know.. any Data Architect would have done this sort of data governance task for you. You wanted to know which tables where join'd on and which group by's were the most popular? welcome to a concept called query digest from >11 years ago. Sorry, but all I am hearing is 'how can we burn money on a pet haskell project using a team of devs that will forever need to maintain it'. Have the right expert do the right job..
- cube00 9y agoI really wish I could get over my irrational fear that using an UUID will one day result in a collision and the associated data integrity issue when an update silently overwrites an unrelated record, where as the old sequential sequence never has this risk.
- dansingerman 9y agoDo you worry about the irrational fear that one day all the oxygen molecules in the room will gather in an upper corner, and you'll suffocate?
- shamaku 9y agolol
- eklavya 9y agoHaving witnessed a uuid collision in only millions records, I can assure you it's not a laughing matter.
- dansingerman 9y agoThat is likely due to a fault in the UUID generator, rather than the concept of using UUIDs. http://www.h2database.com/html/advanced.html#uuid http://www.h2database.com/html/advanced.html#uuid
- eklavya 9y agoWell than the java implementation of UUID is flawed!
- dansingerman 9y agoYou say 'only millions records'. Even if you had 2^36 records (that's over 68 billion) the chances of a collision should be 0.0000000000000004 That really is vanishingly small. So, rationally, I'd guess it is more likely there is a problem with the implementation of UUID you are using, or just perhaps there is some other cause of the collision...like a bug?
- maslam 9y agoSomeone's trying to be GDPR compliant :D
- iamleppert 9y agoHow does it handle tables that aren't joined anywhere?