Data Analytics

Accelerating BigQuery migrations with automated SQL translation

Google’s data cloud enables customers to drive limitless innovation and unlock the value of their data via its robust offerings under a single, unified interface. By migrating their data ecosystems to Google Cloud, organizations are able to break down their data silos and harness the full potential of their data. However, historically, migrating data warehouses has not been an easy task. One of the hardest pieces of a warehouse migration is modernizing legacy business logic, such as SQL queries and stored procedures. This process normally involves substantial manual query rewrites and verifications, which is time consuming and error prone.

Today, Google Cloud is making data warehouse migrations even easier with automated SQL translation as part of the BigQuery Migration Service (BQMS). Customers can now get semantically correct, human readable translations of their legacy SQL queries, across a wide breadth of data warehouses, with just a push of a button. BQMS is available for free and significantly reduces the time, cost and risk of data warehouse migrations to BigQuery.

1 Automated SQL translation.jpg
Automated SQL translation: Reduce risk and accelerate migrations to BigQuery

Industry leading customers like PayPal, Bed Bath & Beyond and many others are already trusting BQMS with their migration journeys. We are thrilled to make the power of automated SQL translation available to all of our customers and partners. BQMS now supports the ability to batch-translate SQL from 10+ dialects to BigQuery: 

  1. Amazon Redshift SQL and Teradata SQL in GA

  2. Apache HiveQL, Apache Spark SQL, Azure Synapse T-SQL, Basic Teradata Query (BTEQ), Teradata SPL, IBM Netezza SQL/NZPLSQL, Oracle SQL / Exadata, PL/SQL, Snowflake SQL and Vertica SQL in Preview

Furthermore, we are making these translation capabilities available via a Google Translate-like experience called interactive SQL translation, providing users with a live, real time SQL translation tool which allows self-serve translation of queries. This not only reduces the time and effort for analysts to migrate their queries, but also increases how quickly they learn to leverage the modern capabilities of BigQuery.

2 Interactive SQL translation.jpg
Interactive SQL translation: Live, real time SQL translation across multiple SQL dialects

To make using SQL translation easier and more accurate, we are including a suite of open and flexible client-side tools to help with common tasks like extracting SQL and schema from source warehouses and remapping source schema naming to BigQuery’s destination schema. These tools help further automate the end-to-end translation experience, reducing manual intervention and post-translation refactoring. When manual intervention is required, BQMS clearly delineates which parts need to be reviewed for accuracy and which couldn’t be automatically translated and need to be handled outside BQMS.

3 Translation results.jpg
Translation results: Simple, clear reporting to help focus post-translation effort

The BigQuery Migration Service accelerates a significant portion of your end-to-end migration with open and powerful tools. We are pleased to make SQL translation for a wide breadth of data warehouses available to you and your partners free of charge. We hope it speeds up and lowers the cost of your migrations to BigQuery. 

If you would like to leverage BQMS for an upcoming proof-of-concept or migration, reach out to your GCP partner,  your GCP sales rep or check out our documentation to try it out yourself. We look forward to partnering with you on your data warehouse migration journey.