LEIGHA JARETT: In our last episode
of "BigQuery Spotlight," we walked
through the structure and components of the BigQuery
resource model.
In this episode, we're drilling in deeper on tables,
covering each of the different table types
and when to use them.
Stay tuned.
[MUSIC PLAYING]
So what is a table?
A BigQuery table is a resource that
lives inside of a data set.
It contains individual records organized into rows,
with each record composed of columns or fields
that have an enforced data type.
BigQuery supports many different data types,
including decimal representations for precision
in financial use cases, geography for geospatial data,
plus structs and arrays for complex data.
Oh, and keep an eye out for more data
types that will support semi-structured data, coming
soon.
Aside from simply storing your data,
tables can also be used to control access
to information and store metadata, such as descriptions
and labels.
There are several different types of tables in BigQuery,
so let's go ahead and dig deeper into each one.
The first table type we'll talk about is a Managed Table.
Managed Tables are tables where data is saved
in BigQuery's native storage.
Let's jump into the console and create a new Managed Table.
When getting started with BigQuery,
you might already have some data that you
want to load into a table, such as a CSV file living
on your computer.
Here, you can specify a local file and leverage schema
auto detect so that BigQuery infers the name and data
type of each column.
Alternatively, you may want to create an empty table, where
new rows will be inserted through data pipelines.
In this case, you'll use the "create table" statement
in a SQL query to create a new Managed
Table with a specified schema and a set of configuration
options.
This is going to be a pretty big table,
so let's take it one step further
and include partition and cluster keys
so that BigQuery stores the data in a way that will make queries
much more performant.
Another great thing about Managed Tables
is that you can use time travel to access data that has been
changed in the past seven days.
So no need to panic if you accidentally
erase a row or a table.
You can still query data that was deleted or has expired.
Now, what if your data is already
stored somewhere else, like a data lake located
in a Cloud Storage Bucket?
Other systems might also be accessing this data,
say, Spark Workflows running on a Dataproc cluster.
If you took the approach of replicating the data
in a Managed Table, you would have two different versions,
one stored in Cloud Storage and one
stored in BigQuery, which could lead to mismatching analyses.
This situation is a good use case
for our second type of table.
External Tables give you the ability to query data
that's stored outside a BigQuery,
preserving a single source of truth.
BigQuery currently supports querying data
stored in Cloud Bigtable, Google Drive,
and Cloud Storage, which can include leveraging
Hive-based partitioning.
Let's create an External Table that points to a JSON file
living in our Storage bucket.
This time, you'll specify that it's an External Table
and also specify the format and file path for the data.
You can now join this data onto other tables in BigQuery,
so long as it's stored in the same region.
Aside from creating an External Table in BigQuery,
you can also create a Connection,
which is more like an external data set.
A Connection links BigQuery to a Cloud SQL database
and allows you to create external queries.
An external query executes the query
in Cloud SQL and returns results which
you can leverage in BigQuery.
Both Connections and External Tables
are examples of Federation, meaning
BigQuery will go and fetch the data from the external source
at the query's runtime.
This offers a great approach to ensure everyone
in your organization is working off of the same data sources.
However, you should keep in mind that query performance probably
won't be as high as querying data
in native BigQuery storage.
So far, we've talked about tables stored inside
of BigQuery and tables stored outside of BigQuery.
But let's imagine that you want to create an aggregate version
of an existing BigQuery table--
for example, a sum of sales each day.
You could create a whole new Managed Table
using a SQL query, but you would need
to rerun that statement each time the aggregate table needs
to be refreshed.
A better option is to use a View.
Views are virtual tables that are defined by a SQL query.
In BigQuery, you can create a Logical View, a View where
the defining query is simply executed at runtime,
or you can create a Materialized View,
which is re-computed in the background
when the base table changes.
Materialized Views are great for improving performance,
because the results are saved and reused, as opposed
to rerunning the query each time you're leveraging the View.
And better yet, BigQuery will actually
reroute queries against your base table
to the Materialized View in order
to make things more efficient.
However, there are some limitations on the SQL
you can use.
But these are in flux, so keep an eye out on future releases.
On the other hand, Logical Views are
less restrictive in the terms of SQL that you can use.
Logical Views are also really useful for granting users
access to results of a query, even if they don't have access
to the underlying data set.
This is known as an Authorized View.
For more details, check out our previous video, linked below.
Congrats!
Now you know all the different table types
in BigQuery and some best practices on when to use them.
For more guidance on getting started with BigQuery
or digging deeper into BigQuery resource concepts,
check out the links below.
Thanks for joining us today for "BigQuery Spotlight."
Watch out for the next episode, and remember, stay curious.
[MUSIC PLAYING]