Microsoft SQL Server to Google BigQuery Migration. Converting the code !
This page provides you with instructions on how to extract the schema of tables, views and SQL Queries from SQL Server and convert it into Google Bigquery. (If this manual process sounds onerous, check out Roboquery, which can do all the heavy lifting for you in just a few clicks)
Before we dive in, for those who’s new to Data warehousing,
What is SQL Server?
SQL Server is a relational database management system, or RDBMS, developed and marketed by Microsoft. The 4 decade old company is powering the operational systems of several fortune 100 companies.
There are other SQL Products from Microsoft, you are use this tutorial to migrate those systems as well
Azure SQL Database
Azure Synapse Analytics (SQL DW)
Parallel Data Warehouse
What is Google Bigquery?
Google BigQuery is a serverless data warehouse that delivers super-fast results from SQL queries. With BigQuery, there’s no spinning up (and down) clusters of machines as you work with your data. With that said, it’s clear why some claim that BigQuery prioritizes querying over administration. It’s super fast, and that’s the reason for its popularity.
Planning the migration
There are two steps in the migration process,
- Creating the Database objects (Tables, Views, other SQL code conversion)
- Moving the data from SQL Server to BigQuery
While there are several tools and utilities available to load data from SQL Server to BigQuery, the tedious process of converting the database objects is highly underrated. We will cover that in this article
1. Converting SQL Server table DDL to BigQuery
Lets export the table definition to a text file. Get the DDL for all the tables in your database
From management studio
- Right click on the table you want
- script table as
- create to
- new query editor window
Now that we have the SQL Server DDLs, its time to convert them. Use the free online tool to convert SQL Server code to BigQuery
Once you visit the page, paste the SQL Server table DDLs and click on the convert button. Viola! Now you have the code converted to Bigquery
2. Converting SQL Server Views to BigQuery
Lets follow the same steps as we did in step-1 and extract the view definitions:
Export the view definitions to a text file and copy paste the source view definitions in to the online converter and click the convert button to convert the view SQL to BigQuery
3. Converting SQL Server Queries to BigQuery
You can follow the same steps to convert the SQL Queries as well. Roboquery converts all the datatypes, functions and also rewrites the structure that’s optimized for BigQuery data warehouse
Avoid Manual work!
You don’t want to manually convert the code to BigQuery, even If you have all the skills necessary to go through this process, It still isn’t a very high-leverage use of your time.
Thankfully, products like Roboquery were built to convert schema from SQL Server to BigQuery automatically. With just a few clicks, Roboquery starts converting your SQL Server code, structuring it in a way that’s optimized for BigQuery data warehouse.
