Avoid Losing SQL Queries During BigQuery Studio Migration
Written on
Chapter 1: Introduction to the Migration
In the realm of corporate cybersecurity, effective teams not only safeguard vital assets but also instill a healthy skepticism regarding emails, particularly those that demand immediate attention. After successfully evading various phishing attempts, ranging from fictional CEOs requesting urgent wire transfers to dubious offers from "Starbucks," I approach any email marked with "Action Advised" with caution.
As I hovered my cursor over the block button, I quickly scanned the email for typical red flags. To my relief, it was a legitimate notification from Google Cloud about a significant update to BigQuery Studio.
Section 1.1: Understanding the Update
The email outlined changes regarding how users save, access, and manage their queries—an essential function for someone like me who frequently relies on them. While this isn't groundbreaking news, such as an announcement about BigQuery queries utilizing quantum computing, it does carry weight for users like myself who often craft queries and then forget about them.
2024 is gearing up to be pivotal for Google's data products. The recent announcement about the end of Google's Universal Analytics has overshadowed other updates. On a more granular level, Google Cloud has also phased out support for Python versions older than 3.8, which forced me to spend considerable time migrating nearly 50 cloud functions I manage.
With the transition to BigQuery Studio, Google is introducing a "Collaborative analytics workspace to accelerate data-to-AI workflows." As part of this transition, existing customers will see six new APIs enabled automatically by GCP, enhancing the functionality of this workspace:
- Dataform
- Dataplex
- BigQuery Data Policy
- BigQuery Connection
- Analytics Hub
- Reservations
Section 1.2: The Risk of Losing Access
The primary reason behind the email is to alert users about the potential loss of access to crucial resources, particularly saved queries. Fortunately, although the new APIs will be activated in March 2024, users have until then to migrate their saved queries to the new setup in BigQuery Studio.
If you're thinking that saving query drafts might not be worth the hassle, consider this: even the simplest code snippets can often serve as the foundation for future tasks. Personally, I save queries for a variety of reasons, such as:
- Quickly updating existing queries or views.
- Storing frequently used code snippets.
I often save queries during quality assurance, especially when comparing specific fields across multiple tables. This allows me to minimize repetition when writing CTEs and JOINs.
It's important to note that while I have access to sensitive data like revenue metrics, not all BigQuery users do. This highlights the significance of access control and permissions.
Chapter 2: New Permissions and Responsibilities
With the BigQuery Studio migration, users will require updated permissions to create and save queries. Specifically:
- BigQuery Admins will gain a Dataform Admin role, allowing for enhanced resource management at the project level.
Similar to any product migration, both users and the platform have responsibilities to facilitate a seamless transition:
- GCP will automatically enable six new APIs.
- User accounts will transition to the required Dataform permissions.
- Users can opt out of the automatic API enablement by March 3, 2024.
- Users should review and update their scripts to align with the new APIs.
This announcement provides ample notice—nearly a month for automatic API enablement and a year for migrating saved queries. It underscores the necessity for users to stay informed about new developments and to have a contingency plan for deprecation.
The earlier you start this process, the better prepared you will be, minimizing stress and unforeseen complications post-migration. I intend to migrate the queries saved in my personal instance soon, as the last thing I want is to lose access to them.
In this video, titled "Calculating Keyword Ranking in BigQuery SQL," you'll learn how to effectively manage your SQL queries within the BigQuery environment.
The second video, "Dynamic BigQuery SQL with Parameters," offers a tutorial on advanced techniques in Looker Studio, helping you navigate SQL with ease.