Overview This lab describes how to use the batch SQL translator in BigQuery to translate scripts written in other SQL dialects into GoogleSQL queries.
Migrating a Data Warehouse to the cloud or from another cloud provider is a tedious task. This task requires even more effort when the different SQL dialects are used, as one has to re-implement the entire logic.
Google Cloud Platform makes it easier with SQL translator, which is part of the BigQuery Migration Service. SQL translators are available in two modes: batch and interactive, that translates scripts and queries written in other SQL dialects into GoogleSQL queries.
The SQL translator can translate the following SQL dialects into GoogleSQL: Amazon Redshift SQL Teradata SQL except for SPL
Additionally, translation of the following SQL dialects is supported in preview:
Apache HiveQL
Apache Spark SQL
Azure Synapse T-SQL
Basic Teradata Query (BTEQ)
IBM Netezza SQL/NZPLSQL
MySQL SQL
Oracle SQL, PL/SQL, Exadata
PostgreSQL SQL
Presto SQL
Snowflake SQL
SQL Server T-SQL
Teradata SPL
Vertica SQL
What you'll learn
In this lab, you will learn:
Setup Qwiklabs setup
For each lab, you get a new Google Cloud project and set of resources for a fixed time at no cost.
Make sure you signed into Qwiklabs using an incognito window .
Note the lab's access time (for example, and make sure you can finish in that time block.
There is no pause feature. You can restart if needed, but you have to start at the beginning.
When ready, click .
Note your lab credentials. You will use them to sign in to the Google Cloud Console.
Click Open Google Console .
Click Use another account and copy/paste credentials for this lab into the prompts.
If you use other credentials, you
7. Accept the terms and skip the recovery resource page.
Do not click End Lab unless you are finished with the lab or want to restart it. This clears your work and removes the project.
Task 1. Enable the API In the Google Cloud Console,
Go to the Navigation menu > APIs & Services > Library .
Search for BigQuery Migration API .
Click on the BigQuery Migration API and if the API is not Enabled, click Enable.
Task 2. Translate scripts with batch SQL translator Collect source files
Collect the source files containing the Teradata scripts and queries to be translated.
In Cloud Shell, create a file named source_teradata.txt.
nano source_teradata.txt
Add the following script to the file.
-- Create a new table named "Customers"
CREATE TABLE Customers (
CustomerID INTEGER PRIMARY KEY,
FirstName VARCHAR(255 ),
LastName VARCHAR(255 ),
Email VARCHAR(255 )
);
-- Insert some data into the "Customers" table
INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES (1 , 'John', 'Doe', 'johndoe@example.com');
INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES (2 , 'Jane', 'Smith', 'janesmith@example.com');
INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES (3 , 'Bob', 'Johnson', 'bobjohnson@example.com');
-- Select all data from the "Customers" table
SELECT * FROM Customers;
-- Add a new column to the "Customers" table
ALTER TABLE Customers ADD Address VARCHAR(255 );
-- Update the email address for a specific customer
UPDATE Customers SET Email = 'johndoe2 @example.com' WHERE CustomerID = 1 ;
-- Delete a customer record from the "Customers" table
DELETE FROM Customers WHERE CustomerID = 3 ;
-- Select customers whose first name starts with 'J'
SELECT * FROM Customers WHERE FirstName LIKE 'J%';
Save and close the nano editor by entering Ctrl+x to exit, then Y to save the contents, and then enter to write the saved contents to the file.
Create Cloud Storage bucket and upload source files
In Cloud Shell, run the following command to create a Cloud Storage bucket.
gsutil mb gs://qwiklabs-gcp-00 -4002 e2694721
Upload the source file to the bucket.
gsutil cp source_teradata.txt gs://qwiklabs-gcp-00 -4002 e2694721 /source/source_teradata.txt
Click Check my progress to verify the objective.
Collect source files
Check my progress
Submit the translation job
In the Google Cloud console, go to the BigQuery page.
In the Migration section of the navigation panel, click SQL translation .
Click + Start Translation .
Fill in the translation configuration dialog.
For Display name, type a name for the translation job. The name can contain letters, numbers or underscores.
For Processing location, select the location as US . The translation job performs best when you choose the same location as your source file bucket.
For Source dialect, select Teradata .
For Target dialect, select GoogleSQL .
Click Next.
For Source location, specify the path to the Cloud Storage folder containing the files to translate. Use the Browse option to browse to the source path as qwiklabs-gcp-00-4002e2694721/source.
Click Next.
For Target location, specify the path as qwiklabs-gcp-00-4002e2694721/target.
Leave the optional settings to its default values, and click Create to start the translation job.
Once the translation job is created, you can see its status in the translation jobs list.
Explore the translation output
After running the translation job, you can see information about the job in the Google Cloud console. You can see job results in the destination Cloud Storage bucket named qwiklabs-gcp-00-4002e2694721.
To see translation job details, follow these steps:
In the Google Cloud console, go to the BigQuery page.
In the Migration section of the navigation panel, click SQL translation.
In the list of translation jobs, locate and click the translation job name you have created.
In the Results section, you can see the overall translation success rate, the number of statements processed, and the job duration.
Select the Actions tab to see translation issues and how often they occurred. Each action listed has child rows that show the issue category, the specific issue message, and a link to the file in which the issue occurred.
Select the Translation Configuration tab to see the translation job configuration details.
In the Translation report section, click translation_report.csv .
On the Object details page, click the value in the Authenticated URL row to see the file in your browser.
For each source file, a corresponding output file is generated in the Cloud Storage bucket named qwiklabs-gcp-00-4002e2694721, under target directory. The output file is named similarly to the source file and contains the translated script.
Click Check my progress to verify the objective.
Create the translation job
Check my progress
Task 3. Create Dataset and translate queries with the interactive SQL translator The BigQuery interactive SQL translator is an interactive SQL translation tool that helps speed up and simplify the migration of SQL workloads to BigQuery.
Create a BigQuery dataset
Create a BigQuery dataset named cymbal_warehouse, by running the below command in the cloud shell.
bq mk -d --data_location=US cymbal_warehouse
Translate queries
Translate a Teradata query into GoogleSQL
On the BigQuery page, in the Editor pane, click More, and then select Translation settings.
For Source dialect, select the Teradata.
For Processing location, leave the field to its default value.
Click Save .
In the Editor pane, click More, and then select Enable SQL translation. For any prompt regarding enabling translation mode, click Confirm . The Editor pane splits into two panes. In the left pane, enter the following query.
-- Create a new table named "Orders"
CREATE TABLE cymbal_warehouse.Orders (
OrderID INTEGER PRIMARY KEY,
CustomerID INTEGER,
OrderDate DATE,
TotalAmount NUMERIC(10 ,2 )
);
-- Insert data into the "Orders" table
INSERT INTO cymbal_warehouse.Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES (1 , 1 , '2022 -01 -01 ', 100 .00 );
INSERT INTO cymbal_warehouse.Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES (2 , 2 , '2022 -01 -02 ', 200 .00 );
INSERT INTO cymbal_warehouse.Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES (3 , 1 , '2022 -01 -03 ', 150 .00 );
INSERT INTO cymbal_warehouse.Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES (4 , 3 , '2022 -01 -04 ', 75 .00 );
Click Translate.
BigQuery translates your query into GoogleSQL and displays it in the right pane.
Solution of Lab
curl -LO raw.githubusercontent.com/quiccklabs/Labs_solutions/refs/heads/master/Introduction%20 to%20 BigQuery%20 SQL%20 translation/quicklab.sh
sudo chmod +x quicklab.sh
./quicklab .sh
