Part 3 of this blog series focuses on the concept of SQL query transformation so that it can be executed on a modern, cloud database. (To learn more about translating queries, check out the previous post in this series: Moving Database Applications to the Cloud: SQL Query Translation.)
To set the stage for the SQL query transformation, here is the definition of transformation from mathematics and logic:
“A process by which one figure, expression, or function is converted into another that is equivalent in some important respect but is differently expressed or represented.”
A SQL query transformation is the process of changing the grammar of a SQL statement from the source SQL dialect to a target database SQL dialect. The equivalent statement must produce the same result set. Because of the complexity of the SELECT statement, detailed syntax elements and arguments are shown by clause.
Below, you can see a side-by-side comparison of the SQL syntax of two different Relational Database Management Systems (RDBMS).
Notice the difference in the number and name of the clauses. In some cases, the clause order could also be different.
As discussed in the previous blog post in this series, the task is to move your current on-premise database to the cloud. And, as you made progress in modernizing the existing applications, you started encountering more and more complex SQL statements. These statements require a SQL transformation and, in some cases, also translation. Let’s walkthrough the SQL statement below using the same process as before. This time Microsoft Azure SQL DW (SQL DW) is used as the target database.
Here is the Python code to execute this query directly on SQL DW.
As you might have already expected, a syntax error is returned. A transformation and translation of the query is required to successfully execute the query in SQL DW.
The figure below shows the changes needed to run this query directly in SQL DW.
The right-hand side shows the transformation and translation needed to get the original SQL (left-hand side) working. Changes to the query are numbered and explained below:
Executing the transformed query will produce the result shown below.
Using Datometry Hyper-Q enables your applications to continue working using the original SQL. No changes are needed to the application only the connection string to the target database needs to be modified.
Taking the original query without any transformations or translations and executing it through Hyper-Q will produce the expected result using a SQL DW database as shown below.
Hyper-Q guarantees the original query is converted to its equivalent in SQL DW and produce the same result.
Even though it might appear that the above mentioned changes are pure textual differences, it is not easy or practical to deal with them using pure text manipulation tools. In some cases, it is not even possible since the queries are generated on the fly by the application.
Statements in the transformation category often require elaborate rewrites. They use powerful and often non-standard constructions that require a full and detailed understanding of the grammar and meaning of the query (that is, what is the question that the query is answering). Hyper-Q will convert the query to relational algebra to understand the meaning and then reconstruct the query using best practices and SQL constructs of the target database. All this is accomplished in real-time thus eliminating the risk of introducing errors to the exiting applications and eliminating the cost of application rewrites.
SQL in a Nutshell, 3rd Edition: A Desktop Quick Reference Guide, Kevin Kline,
O’Reilly Media (November 2008)
Rapid Adoption of Cloud Data Warehouse Technology Using Datometry Hyper-Q, L. Antova, D. Bryant, T. Cao, M. Duller, M. A. Soliman, F. M. Waas
Datometry, Inc. (December 2017)
Replatforming Business Applications in the Cloud
Datometry, Inc. Case Study (2018)