SPEAKER: So far in this series you've
queried data stored within BigQuery
by following a common path of ingesting your data,
storing it, and then analyzing it.
But it's also possible to query data stored externally
or outside of BigQuery.
In today's episode of "BigQuery Spotlight"
we're diving in to these external data sources.
[MUSIC PLAYING]
Wait.
So the world exists outside of BigQuery?
Of course it does.
The good news is that it's possible to leave
your data in place and use BigQuery as your query engine.
We call these sources external or federated data sources.
This functionality is currently supported
for data residing in Google Drive, Cloud Storage, Cloud
SQL, and Bigtable.
Before we look closer at how to query these sources,
let's discuss a few notable differences you'll experience
with external data sources.
First to note is that the query performance for external data
sources may not equal the query performance of data
stored in BigQuery.
So if query speed is a priority in your case,
you may want to load the data directly into BigQuery.
Secondly, when querying an external source,
BigQuery cannot predict the amount of data to be processed.
So you will only know after you run your query.
Finally, the results are not cached
like they would be when querying data stored in BigQuery.
Caching is a great way to save costs and improve performance
on repeated queries when the underlying data hasn't changed.
So given these considerations, what
are some popular use cases for external data sources?
Overall, this feature is best for short term,
less frequently accessed data.
For example, you could use external data sources
to support loading and transforming your data
in just a single pass.
In this workflow, you query the external source,
transform the data as part of the query,
and then write the results as a permanent table
in BigQuery storage.
Another use case is for joining small amounts
of frequently changing data with data stored in BigQuery.
By keeping the frequently changing data
as an external data source, it does not
need to be reloaded into BigQuery every time
it is updated.
An example here is querying data that lives in a Google Sheet.
Even as the Sheet is edited in real time,
you can run queries over the data
and the results will reflect the live, up-to-date information.
OK.
So now let's look at the how for setting up external data
sources in BigQuery.
You can follow along in your own BigQuery sandbox.
If you don't have one yet, set one up
following the video link below.
In this example, you'll run a query
over a collection of JSON files located in a Cloud storage
bucket.
These files have the flight performance data
for all domestic flights in the United States from 2014.
Starting in the console, create a new data set.
Highlight your project name in the left hand nav
and click Create Data Set.
Name the data set flight_performance,
choose US for the location, and click Create Data Set.
Now highlight your new data set and click Create Table.
Under Source, choose Google Cloud Storage.
Quick note, if your data was located in Google Drive--
such as in a Google Sheet--
or in Bigtable, you would choose those alternate sources
in the dropdown.
The flight performance data is located
in a public bucket which any GCP user can
access with the URI address.
Paste the URI provided in the description
below into the GCS bucket field.
You'll see there's a wild card character in the URI, which
indicates to BigQuery to include all the JSON files that
adhere to the specific naming convention.
Next, under Destination, make sure you
set the table type to external.
Name the table 2014.
And you can auto detect the schema in this case.
Finally, click Create Table.
Since there is no data ingestion involved,
you'll see the table immediately populate under the data set.
Look at the table details where you
can see the external configuration that you just
set up and a table size of 0 bytes
because your external table does not use any BigQuery storage.
Now you can run a query that references the external table.
In our query we're selecting account of all flights
by carrier.
As we discussed earlier, you cannot see the amount of data
processed until after the query completes.
In this example, you created a permanent for the external data
source.
However, you can also query an external source
using a temporary table which is useful for one time ad hoc
queries or for ETL processes.
More information on how to create and query
temporary external tables is linked below.
Want to analyze data in Cloud SQL?
Creating the initial connection follows a different process
than you just saw for Cloud Storage in Drive.
But never fear, we've created a quick start video link
below that shows you end-to-end how
to connect to and query your MySQL or Postgres
instance in Cloud SQL.
Looking to data sources beyond the Google Cloud ecosystem,
we recently announced BigQuery Omni
which gives BigQuery users the ability
to analyze data stored in other public clouds.
Be sure to check out the announcement blog
post link below.
Thanks for joining us today for "BigQuery Spotlight."
Watch out for our next episode and remember, stay curious.
[MUSIC PLAYING]