Migration from legacySQL to Standard

[krahel]

Hello there,

In my firm we have the data ingested from various streams all to Bigquery. 

The problem is that we have many views(hundreds) written in legacySQL, so when we interrogate the DB when using these views, the synthax must be written in legacy.

In order to fix this problem and be able to improve performance and compatibility for the future, we're thinking about migrating all the views in standardsql.
Could you please help me to understand how this should be managed?
My idea was to first understand the dependencies of the views and tables, in order to start doing tests on the "leaf" nodes, is there any way to do this using any tool in GCP or Vertex?
Then i thought to extract all the queries of the views and using maybe the Gemini API to get the query written in standard, but maybe there is some tool that does this natively.
About the process of changing step by step all the calls to the new views, what would be the best way to do it?

Thanks a lot in advance

[Joy_S · Accepted solution]

Hi @krahel,

Welcome to Google Cloud Community!

To answer your question, you can follow these steps if you are planning to migrate from legacySQL to standard SQL:

1. Find "Leaf" Views: You're right! Start with views that don't rely on other views. BigQuery has a special place called INFORMATION_SCHEMA where it keeps details about your tables and views. You can ask it to tell you what's inside each view (the actual code). You would then need to look at that code to see which tables or other views it uses. You may need to do some programmatic parsing or develop a script to build a dependency graph.
2. To translate the code you may use any of the following:
   1. Use Gemini API to rewrite the code: Once you have an old SQL query, you send it to the Gemini AI using its special connection point (the API). Create a prompt to instruct Gemini to convert the code to StandardSQL. Even though Gemini is smart, it might not get every single detail perfect on the first try, especially with very tricky parts of SQL. You'll need to test the new Standard SQL code generated by Gemini to make sure it works exactly as expected and gives the same results as your old views. You might need to adjust the prompt or make small manual fixes. 
   2. BigQuery Batch SQL Translator (BigQuery Migration Service): It is designed for mass migrations. You can upload a collection of Legacy SQL queries (e.g., extracted view definitions) to Cloud Storage, and the Batch SQL Translator will convert them to Standard SQL. It provides detailed reports on the success rate and any issues encountered during translation.
3.  Migrate LegacySQL to GoogleSQL:  You may also refer to this documentation on how to migrate  from LegacySQL to Google SQL.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.