SPEAKER 1: The process of writing and running SQL queries
doesn't always follow a straight line.
A particular query can be in constant iteration
while you use it to explore and clean up your data,
or as you fine tune it to optimize its performance.
In today's episode, we highlight the ways to save and share
queries in BigQuery to help you navigate
these bends in the road.
[THEME MUSIC]
So you ran a query last Friday for your colleague
and sent them the results.
All done, right?
Wrong.
It's Monday morning, and now there's
another request for additional data fields on the same query.
The new request requires you to join data from another table--
a join which you actually did in a separate query
a few weeks ago.
But you can't quite remember how you did it.
Do you really want to write a new SQL query from scratch?
No.
I mean, it's great practice, but it's not always
the most practical.
Luckily, BigQuery has features built
in that allow you to view your query history,
proactively save specific queries,
and share queries with others.
Let's take a look at each of these features a bit further.
Your first line of defense for revisiting past queries
in BigQuery is your query history.
This feature is automatic and includes the text
of all queries you have run.
You can view your query history in the console
by selecting Query History in the top left-hand nav.
Click on a specific query and you'll
see the text of the query and be able to open it in the editor
so you can modify and rerun it.
Just note, this history is limited to 1,000 queries
and looks back up to six months.
Going a step further, you can decide to proactively name
and save a specific query.
Just enter the query, valid or not, in the editor.
Then click Save Query, located just
to the right of the Run button.
Give the query a name and then click Save.
You can find a list of your saved queries
in the left-hand nav.
Similar to the functionality in your query history,
you can now click to open the query in the editor,
modify it, and run it.
If you want to share one of your queries with a colleague,
you can create shared queries.
You can create a shared query by first saving a query,
turning on link sharing, and then sharing the unique URL.
A shared query provides the query text only,
so users that open your shared query
will still need to have appropriate access
to the tables referenced in the query to be able to run it.
For more information on how to share access to data
sets using Identity and Access Management, or IAM, watch
our last episode, "Managing Access with Cloud IAM,"
linked in the description below.
Other users will be restricted from editing
your personal saved queries.
If you're looking to make saved queries editable
by your team members, you'll need
to change the saved query visibility to Project
and then grant your team members the permissions to get,
list, and update saved queries on your project.
So now that you have the text of the query covered,
what about the query results?
Just as BigQuery automatically saves your query history,
it also by default caches the results
of your successfully run queries in temporary tables.
These tables expire after 24 hours.
And within this time, you can browse the results
through your query history.
Just select the query and scroll to find
a link to the temporary table.
Here you can preview the data and also export the full table
to cloud storage.
If you rerun the query, BigQuery will use the cached results
if it can, meaning if you are still within the 24-hour period
and the underlying data has not had any changes.
By using cached results, you won't incur any query costs.
Temporary tables are available only to the specific user
that runs the query.
So if you're looking to share the query results,
you should consider saving them to a permanent table.
You can make the switch from saving in a temporary table
to a permanent table by opening the query settings
and setting a destination table for the results.
Click Run.
And after the query is complete, you
will see the new table in the left-hand nav.
You can then use IAM to share the destination data
set or table with your collaborators.
It's important to note, permanent tables will
incur storage costs, while temporary tables do not.
Another method for sharing results
is to use the option to save them immediately
after running your query.
Click the Save Results button and choose
between several options for saving results--
locally, in Google Drive, or directly to a Google Sheet.
We've now reviewed how to save and share query text and query
results.
But there is one more tool to cover, which is called views.
A view is a virtual table defined by a query.
A view can be queried just like you query a table.
Views are listed next to tables in a data set,
denoted with a different icon.
When running a query that references a view,
BigQuery will also run the query that defines that view.
So why would views be helpful?
Views can help hide the complexity of data
by allowing you to join multiple tables
into a single virtual table ready to query.
Views can be a good alternative to sharing query text,
because then you can take advantage of the finer grain
controls available in Cloud IAM to share
the view and the underlying tables.
Views do not require storage.
Since they're virtual tables, there's
not a storage cost associated with them.
To create a view, write the query
that defines the view in the Query Editor
and then click Save View.
Choose the project and data set where
you'd like the view to live, and then give the view a name.
Click Save.
The view will populate under the destination data set
and can be queried just like you query a table.
Now that you know how to save and share queries,
it's time for you to try.
If you haven't created your BigQuery sandbox,
do so by following the link below.
Once you're in the console, click Add Data, Explore Public
Data Sets, and then pick out your favorite one.
Write a query and share it with a friend and in the comments
below, if you like.
If you want to learn more about BigQuery,
check out the rest of our playlist.
And don't forget to subscribe to our channel
on YouTube for other great content.
Look out for our next episode of "BigQuery Spotlight."
And remember, stay curious.
[THEME MUSIC]