Google Cloud Platform’s BigQuery service allows you to create, manage, distribute, and query data, as well as execute data warehousing, analytics, and machine learning. BigQuery enables users to analyze massive volumes of data while utilizing cloud-based parallel computing to meet your big data needs.
This Google solution, in contrast to most other cloud-based data warehouses, requires minimal to no maintenance. As a result, you may use Google’s Data Warehouse from any place that has connectivity to Google Cloud. The platform can also now gather and process more data from a larger number of sources after recently updating its data capabilities.
Types of Queries in BigQuery
Saved queries are classified into three kinds, and the IAM rights necessary to create, view, edit, share and remove saved queries are described below:
- Private Queries
Private stored queries are only available to the user who created them. No additional permissions are required to create private saved queries. A private query may be saved in any project, but only you can read, change, or remove it. - Public Queries
Creating public queries requires no special permissions. A public query may be saved in any project, but only you can change or delete it. A public stored query can be viewed by anybody who has the URL. - Project Level Queries
Only the project members or users with managed permissions can see project level inquiries. There are specific IAM permissions that give users the ability to create, view, delete, and update saved queries, which can be found on Google’s documentation.
How To View and Open Saved Queries in BigQuery
The saved query feature is only available in the Google Cloud console. To view a saved query, visit the BigQuery Google Cloud console, click your projects branch in the Explorer panel, and then select amongst the Saved queries used in your project.
Where to Find Saved Queries in BigQuery?
Additionally, if you want to create a quick way to access this query, you can generate a QR code using a QR code maker and save it for future use.
How To Create Saved Queries in BigQuery – SQL Editor
Follow the steps below to get started with creating saved queries.
- Login to your Google Cloud console, and open the BigQuery Page
- Click on Compose a New Query
- Write valid SQL in the Query Editor to create a saved query. Any previously created tables can be utilized as well.
- Once your SQL is written, click the Save button.
- Choose Save Query option from the drop down menu that appears when hovering over the save button.
You can set visibility to one of these three IAM options: Personal, Project, & Public - Click Save to store your query.
How To Share Saved Queries in BigQuery
Queries that have project or public visibility can be shared with other users. Principals with the necessary rights can read, amend, or remove the query when it is visible in the project. Anyone with the query link can see but not edit or modify the query since it is public.
To begin sharing saved queries, follow the steps below:
- Login to your Google Cloud console, and open the BigQuery Page.
- Navigate to your project from the Explorer panel on the left-hand side.
- Click on Saved Queries or Project Queries if your query is a project-level query.
- From the list of queries, click the three dots for a menu to get the sharable link. If you do not get the link, it is because your account does not have the required permissions.
- Share the link with Google users to grant them access to your queries.
Tip: If you decide to share a query, it is always helpful to provide a note in the query that indicates its purpose.
How To Update Saved Queries in BigQuery
To update saved queries, follow the steps below:
- Login to your Google Cloud console, and open the BigQuery Page.
- Navigate to your project from the Explorer panel on the left-hand side.
- Click on Saved Queries or Project Queries if your query is a project-level query.
- From the list of queries, select the one you want to modify. It will open on a right-hand panel known as the Query Editor.
- Modify the query using SQL to make necessary changes & updates.
- Choose Save Query option from the drop down menu that appears when hovering over the save button.
Change Table name in BigQuery
Tip: You cannot rename a query while editing, however you can save it with a new name and delete the old one.
How To Delete Saved Queries in BigQuery
To delete stored queries, follow the steps below:
- Login to your Google Cloud console, and open the BigQuery Page.
- Navigate to your project from the Explorer panel on the left-hand side.
- Click on Saved Queries or Project Queries if your query is a project-level query.
- From the list of queries, click on the three dots to open the query menu, and select Delete.
- Before full deletion, Google sends a ‘This operation cannot be undone’ warning. Type “delete” in the dialogue box to send confirmation.
- Click Delete to remove the selected query.
Conclusion
With so many services and pricing options, it can be overwhelming to keep track of it all. But having a good understanding of these models and services can help companies manage GCP spend and choose the best solutions for their specific needs.
This article provides an introductory, step-by-step tutorial for viewing, creating, sharing, updating, and deleting saved queries from BigQuery. Utilizing this service should be done with keeping BigQuery cost optimization practices in mind, to avoid expenditure on redundant data and processes. For a comprehensive estimate of your BigQuery charges, check out our BigQuery Pricing calculator. We hope you get the most out of BigQuery and enjoy the benefits of this contemporary data warehouse!
