Google BigQuery: Cloud Data Warehouse

Table of contents

Reading Time: 6 minutes

BigQuery is a data warehouse to work with large amounts of data. With BigQuery, one can collect data from various sources, store the data, analyze the data, and eventually; be able to visualize the analysis in multiple ways. This blog talks about BigQuery, its various features, and use cases.

This blog will go through the following topics:

Introduction

Ever since the dawn of computing, and subsequently the internet, the amount of data in the world has been increasing continuously. However, in recent years, the rate of increase has shot up. According to estimates, the total data in the world today is in zettabytes, with 2.5 quintillion bytes being added to it every day!
Taking that into consideration, we need something to handle this massive amount of data. Google’s offering BigQuery does just that – giving us a tool to help us work with this huge amount of data. BigQuery can be used to run very large-scale SQL queries – spanning up to billions of rows – and generate results in almost real-time.

BigQuery Working

The working of BigQuery can be divided into three main parts – ingestion, storage and preparation, and analysis. Here we will elaborate upon these parts

Pre-requisite: Creating a Table

To start working with BigQuery, one needs to have a table. BigQuery tables can be of three types – Native tables, External tables, and Views. Once you have a table, you can run the various BQ operations on it, including:

Listing all tables in a dataset
Getting information about the table(s)
Controlling access to the table data
Getting the table metadata

These tasks can be performed by using Console, bq command-line-tool, using client libraries, or by using the API method.

Once a table is build, we can get to the further steps to make use of BQ’s functionalities.

1. Ingestion (Loading Data)

Once a table has been build, the next step is to load data into the table. There are several ways to ingest data into BigQuery:

Batch loading a set of data records
Streaming individual records
Using queries to generate new data to append into table
Using a third-party application or service

Ingestion can be done manually, as explained above, or automatically, by using Data Transfer Service.

2. Preparation & Storage

In this process, the data is stored in BigQuery, and prepared for analysis. BigQuery takes the raw data gotten in the previous step, and refines it so that it is ready for further analysis.

3. Analysis (Exporting Data)

Finally, once the data is ready in the table, it can be spread. Data can be exported from BigQuery in several ways, with a maximum of 1GB per file. The available options here are to either manually export the data, or to use a service like Dataflow to automate the process. Once exported, various services like BigQuery ML and Google Data Studio can be used to analyze the data.

Features of BigQuery

BigQuery is a fast, serverless data warehouse that is designed for organizations dealing with a high amount of data. From getting detailed insights from the data using its built-in Machine Learning, to analyzing petabytes of data using ANSI SQL, BigQuery offers a wide variety of features in a cost-effective pricing model. Here we look at some of those features.

1. Multicloud Functionality (BQ Omni)

BigQuery is an analytics solution that allows for data analysis across multiple cloud platforms. The USP of BigQuery is providing a novel way of analyzing data present in more than one cloud, without it costing an arm and a leg. This is in contrast to other solutions, where this always involved high egress costs to migrate data from the source. BigQuery achieves this by separating Compute and Storage components. This means that BigQuery can run the computation on the data right where it is located, without the need for moving it to a different zone for processing.

BigQuery Omni runs on Anthos clusters which is govern by Google Cloud. This allows for the secure execution of queries, even on foreign cloud platforms. BigQuery Omni comes with the following functionalities:

Break down silos and gain insights into the data
Get consistent data experience across clouds
Enable flexibility, courtesy of Anthos

2. Built-in ML Integration (BQ ML)

BigQuery ML is treat for creating and executing Machine Learning models in BigQuery using simple SQL queries. Prior to the introduction of BigQuery ML, Machine Learning on large datasets required ML-specific knowledge and programming skills. BigQuery Ml eliminated the need for that, by allowing SQL practitioners to build ML models using their existing skills.
BigQuery ML can be access in the following four manners:

Google Cloud Console
bq command-line-tool
BigQuery REST API
An external tool (for example Jupyter)

Machine Learning in BigQuery works on models, which are representations of what the ML system has learned from the data. Some of the models used in BigQuery ML include Linear regression, Binary and Multiclass Logistic regression, Matrix Factorization, Time Series, and Deep Neural Network models.

No need for data export leading to a higher speed of model development
No need to program ML solution using Python or Java (as shown in the image above)
Ability to build and run ML models using existing BI tools & spreadsheets

3. Foundation for BI (BQ BI Engine)

BigQuery BI engine is an in-memory analysis solution. It is operate to analyze the data stored in BigQuery with high concurrency, and response times of under a second. Being a part of the BigQuery family, it is no surprise that it comes with an SQL Interface as well. This helps it to interact with other BI tools like Looker, Tableau, Power BI, etc. It can also integrate with custom applications, and help with data exploration and analysis.

BigQuery BI Engine has the following advantages:

Speed
Very fast response times and load times help with data analysis over streaming data
Simplicity
Performs in-place analysis within BigQuery which eliminates the need for ETL pipelines
Ease of use
The smart tuning design ensures that there are very few configuration settings from the user’s end

BigQuery BI Engine comes with a free tier that allows up to 1GB of free capacity for Data Studio users, and on-demand capacity at $0.0416 per GB afterward. Organizations with higher needs can choose to opt for flat-rate capacity pricing, which comes with monthly charges.

4. Geospatial Analysis (BQ GIS)

BigQuery Geographic Information Systems (GIS) provides information about location and mapping, which is important in a data-warehouse like BigQuery.

BigQuery GIS functions by converting latitudes and longitudes columns into geographical points. There are four types of objects when working with geospatial data:

geometry
This represents an area on the surface of the earth
spatial feature
This represents a logical spatial object
spatial feature collection
This is a set of spatial features

The final visualization of the BigQuery data can be done by using one of the following:

BigQuery Geo Viz
Google Earth Engine
Jupyter notebooks (using extension)

5. Automated Data Transfer (BQ Data Transfer Service)

BigQuery Data Transfer is a service that automates the movement of data into BigQuery on a regular basis. This schedule can be manage by the analytics team in a simple manner, without the need for any coding. One can also add data backfills to make up for any gaps or outages during ingestion.

The BigQuery Data Transfer Service can be access in three ways:

Cloud Console
bq command-line-tool
BigQuery Data Transfer Service API

As of now, BigQuery Data Transfer allows for data that imported from a variety of Google-owned services like Cloud Storage, Google Play, Google Ads, and YouTube Channel reports. One can also import the data from external cloud storage services, like Amazon S3. Data warehouses like Teradata and Amazon Redshift can also be use to load data into BigQuery using the Data Transfer service.

6. Free Access (BQ Sandbox)

While all the features of BigQuery mentioned above are very useful, sometimes a user might want more experience with a said feature before investing in it. For that purpose, Google has the BigQuery Sandbox – a place for experiencing BigQuery and the Cloud Console without any commitment. This means no need to create a billing account or a project, or even provide credit card details. All the applications run in a separate environment, which emulates the one provided by BigQuery in Google Cloud Platform. Once the user tries the features and is satisfy, they can easily upgrade to the full BigQuery experience.

BigQuery Sandbox also comes with a set of limitations, that are applicable until the upgrade. These are: