Moving Database Applications to the Cloud: SQL Query Transformation (Part 3)

10.31.18

The Cloud Brings New and Exciting Opportunities for IT Professionals

11.07.18

The Past, Present, and Future of the Cloud

10.24.18

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.

Executing the Query in Microsoft Azure 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:

  1. Translation of the date functions – the EXTRACT, INTERVAL and CURRENT_DATE functions need to be translated to their equivalent name DATEPART, DATEADD, CONVERT and DATGETUTCDATE in SQL DW.
  2. Translation of the CHARS function to LEN – this function is used to filter product names longer than five characters.
  3. Transformation of the QUALIFY statement – The QUALIFY clause does not exist in SQL DW. QUALIFY filters on OLAP functions, similar to the HAVING clause in aggregate functions. Notice the partition is performed in the sub query and the filter (>1500) in the outer most SELECT.
  4. Transformation and ordering of the ORDER BY clause – In ANSI SQL, the ORDER BY clause goes after the WHERE clause. In this case the clause appears before. The SQL DW parser will return a syntax error. Also, NULLS LAST is transformed to a CASE statement to produce the same ordering.
  5. Projection – Finally the results are projected.

Executing the transformed query will produce the result shown below.

Virtualizing the Query: A New Approach

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.

Key Takeaways on SQL Query Transformation

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.

Request a demo and an assessment of your environment today! Also, if you haven’t already, don’t forget to read Part 1 and Part 2.

References

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)

The cloud revolution is already underway.
Download Ebook

About Alex Infanzon

Alex Infanzon holds a Master of Science in New Generation Computing from The University of Exeter in the United Kingdom and a B. A. in Computer Science from The Anahuac University in Mexico. He has over 30 years of professional experience in consulting, pre-sales and management roles at Westinghouse, Informix Software, Sun Microsystems, Composite Software, Dun and Bradstreet, EMC Greenplum and most recently the SAS Institute. He has worked with fortune 500 companies in different vertical industries, both in a selling and consulting capacity. Alex has extensive experience in architecting and implementing Business Intelligence, Data Management, Enterprise Information Integration, Fuzzy Matching and High Performance Computing solutions.

Your email address will not be published. Required fields are marked *

All comments are moderated

By checking this box, I acknowledge that this form collects your name and email so that Datometry can keep track of comments placed on this website. For more information, view privacy policy.

Continue Reading