In this last section, we’ll learn about BigQuery, which is Google's fully managed,
petabyte-scale, low-cost analytics data warehouse.
BigQuery is a fully-managed, serverless data warehouse.
A data warehouse is a large store that contains terabytes and petabytes of data gathered from
a wide range of sources within an organization, and it’s used to guide management decisions.
Being fully managed means that BigQuery takes care of the underlying infrastructure, so
you can focus on using SQL queries to answer business questions without worrying about
deployment, scalability, and security.
Let’s look at some key features of BigQuery.
1.
BigQuery provides two services in one: storage plus analytics.
It’s a place to store petabytes of data.
For reference, 1 petabyte is equivalent to 11,000 movies at 4k quality.
BigQuery is also a place to analyze data, with built-in features like machine learning,
geospatial analysis, and business intelligence, which we’ll explore a bit later on.
2.
BigQuery is a fully managed serverless solution, which means that you use SQL queries to answer
your organization's biggest questions in the frontend without worrying about infrastructure
in the backend.
If you’ve never written SQL before, don’t worry.
This course provides resources and labs to help.
3.
BigQuery has a flexible pay-as-you-go pricing model where you pay for the number of bytes
of data your query processes and for any permanent table storage.
If you prefer to have a fixed bill every month, you can also subscribe to flat-rate pricing
where you have a reserved amount of resources for use.
4.
Data in BigQuery is encrypted at rest by default without any action required from a customer.
By encryption at rest, we mean that encryption is used to protect data that is stored on
a disk, including solid-state drives or backup media.
5.
BigQuery has built-in machine learning features, so you can write ML models directly in BigQuery
by using SQL.
Also, if you use other professional tools—such as Vertex AI from Google Cloud—to train
your ML models, you can export datasets from BigQuery directly into Vertex AI for a seamless
integration across the data-to-AI lifecycle.
So what does the typical architecture of a data warehouse solution look like?
The input data can be either real-time or batch data.
If it's streaming data, which can be either structured or unstructured, high speed, and
large volume, Pub/Sub is needed to digest the data.
If it’s batch data, it can be directly uploaded to Cloud Storage.
After that, both pipelines lead to Dataflow to process the data.
Dataflow is where we extract, transform, and load the data if needed.
BigQuery sits in the middle to link data processes by using Dataflow and data access through
analytics, AI, and ML tools.
The job of the analytics engine of BigQuery at the end of a data pipeline is to ingest
all the processed data after ETL, store and analyze it, and then possibly output it for
further use such as data visualization and machine learning.
BigQuery outputs usually feed into two buckets: business intelligence tools and AI/ML tools.
If you’re a business analyst or data analyst, you can connect to visualization tools like
Looker, Google Data Studio, Tableau, and other BI tools.
If you prefer to work in spreadsheets, you can query both small or large BigQuery datasets
directly from Google Sheets and even perform common operations like pivot tables.
Alternatively if you’re a data scientist or machine learning engineer, you can directly
call the data from BigQuery through AutoML or Vertex AI Workbench.
These AI/ML tools are part of Vertex AI, Google's unified ML platform.
BigQuery is like a common staging area for data analytics workloads.
When your data is there, business analysts, BI developers, data scientists, and machine
learning engineers can be granted access to your data for their own insights.
BigQuery can ingest datasets from various sources, including:
Internal data, which is data saved directly in BigQuery,
External data.
BigQuery also offers the option to query external data sources–like data stored in other Google
Cloud storage services such as Cloud Storage, or in other Google Cloud database services,
such as Spanner or Cloud SQL–and bypass BigQuery managed storage.
This means that a raw CSV file in Cloud Storage or a Google Sheet can be used to write a query
without being ingested by BigQuery first.
Multi-cloud data, which is data stored in multiple cloud services, such as AWS or Azure.
and Public datasets.
If you don't have data of your own, you can analyze any of the public datasets available
in the Cloud Marketplace.
After the data is stored in BigQuery, it’s fully managed and is automatically replicated,
backed up, and set up to autoscale.
You can use three basic patterns to load data into BigQuery:
The first is a batch load, where source data is loaded into a BigQuery table in a single
batch operation.
This can be a one-time operation or it can be automated to occur on a schedule.
A batch load operation can create a new table or append data into an existing table.
The second is streaming, where smaller batches of data are streamed continuously so that
the data is available for querying in near-real time.
And the third is generated data, where SQL statements are used to insert rows into an
existing table or to write the results of a query to a table.
Of course, the purpose of BigQuery is not to just store data; it’s for analyzing data
and helping to make business decisions.
BigQuery is optimized for running analytic queries over large datasets.
It can perform queries on terabytes of data in seconds and petabytes in minutes.
This performance lets you analyze large datasets efficiently and get insights in near real
time.
Google Data Studio, Looker, and many integrated partner tools can be used to draw analytics
from BigQuery and build sophisticated interactive data visualizations.
BigQuery also has built-in capabilities for building machine learning models.
An ML model lets you solve certain kinds of problems at scale by using data examples,
but without the need for custom code.
Machine learning on large datasets requires extensive programming and knowledge of ML
frameworks.
These requirements restrict solution development to a small set of people within each company,
and they exclude data analysts who understand the data but have limited machine learning
knowledge and programming expertise.
BigQuery ML empowers data analysts to use machine learning through existing SQL tools
and skills.
Analysts can use BigQuery ML to build and evaluate ML models in BigQuery.
Analysts no longer need to export small amounts of data to spreadsheets or other applications,
and they no longer need to wait for limited resources from a data science team.
BigQuery ML functionality is available by using:
The BigQuery web UI The bq command-line tool
The BigQuery REST API An external tool such as a Jupyter notebook
or business intelligence platform