Understanding Google BigQuery Subquery Simplified 101

on Data Warehouse, Google BigQuery, SQL, Tutorials • January 13th, 2022 • Write for Hevo

Venturing into Data Science and deciding on a tool to use to solve a given problem can be challenging at times especially when you have a wide array of choices. In this age of data transformation where organizations are constantly seeking out ways to improve the day to day handling of data being produced and looking for methods to minimize the cost of having these operations, it has become imperative to handle such data transformations in the Cloud as it is a lot easier to manage and is also cost-efficient.

Data Warehousing architectures have rapidly changed over the years and most of the notable service providers are now Cloud-based. Therefore, companies are increasingly on the move to align with such offerings on the Cloud as it provides them with a lower upfront cost, enhances scalability, and performance as opposed to traditional On-premise Data Warehousing systems. Google BigQuery is among one of the well-known and widely accepted Cloud-based Data Warehouse Applications.

In this article, you will gain information about Google BigQuery Subquery. You will also gain a holistic understanding of Google BigQuery, its key features, SQL, Subqueries, and the different types of Subqueries supported by Google BigQuery. Read along to find out in-depth information about undergoing Google BigQuery Subquery.

Table of Contents

What is Google BigQuery?

BigQuery Subquery - Google BigQuery
Image Source

Google BigQuery is a Cloud-based Data Warehouse that provides a Big Data Analytic Web Service for processing petabytes of data. It is intended for analyzing data on a large scale. It consists of two distinct components: Storage and Query Processing. It employs the Dremel Query Engine to process queries and is built on the Colossus File System for storage. These two components are decoupled and can be scaled independently and on-demand.

Google BigQuery is fully managed by Cloud service providers. We don’t need to deploy any resources, such as discs or virtual machines. It is designed to process read-only data. Dremel and Google BigQuery use Columnar Storage for quick data scanning, as well as a tree architecture for executing queries using ANSI SQL and aggregating results across massive computer clusters. Furthermore, owing to its short deployment cycle and on-demand pricing, Google BigQuery is serverless and designed to be extremely scalable.

For further information about Google Bigquery, follow the Official Documentation.

Key Features of Google BigQuery

BigQuery Subquery - Key Features
Image Source

Some of the key features of Google BigQuery are as follows:

1) Scalable Architecture

BigQuery has a scalable architecture and offers a petabyte scalable system that users can scale up and down as per load.

2) Faster Processing

Being a scalable architecture, BigQuery executes petabytes of data within the stipulated time and is more rapid than many conventional systems. BigQuery allows users to run analysis over millions of rows without worrying about scalability.

3) Fully-Managed

BigQuery is a product of Google Cloud Platform, and thus it offers fully managed and serverless systems.

4) Security

BigQuery has the utmost security level that protects the data at rest and in flight. 

5) Real-time Data Ingestion

BigQuery can perform real-time data analysis, thereby making it famous across all the IoT and Transaction platforms.

6) Fault Tolerance

BigQuery offers replication that replicates data across multiple zones or regions. It ensures consistent data availability when the region/zones go down.

7) Pricing Models

The Google BigQuery platform is available in both on-demand and flat-rate subscription models. Although data storage and querying will be charged, exporting, loading, and copying data is free. It has separated computational resources from storage resources. You are only charged when you run queries. The quantity of data processed during searches is billed.

What is SQL?

BigQuery Subquery -SQL
Image Source

SQL stands for Structured Query Language and executes queries against the database for data analytics. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. 

With the help of SQL, you can:

  • Create and Delete Database
  • Create, Delete and Update Table
  • Load/UnLoad data into Tables
  • Set and Manage permissions on tables, procedures, and views
  • And many more.

What are Subqueries?

A Subquery is an inner or nested query that users can use inside a SQL query or in a WHERE clause. The subquery specified will return the data used in the main query as a filter condition to retrieve the data from the main query.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements and the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

To write the subquery, the user must follow some rules which are as follows:

  1. The Subqueries must be enclosed within parenthesis.
  2. A Subquery can have only one column in select.
  3. A subquery cannot use the ORDER BY command. However, a GROUP BY function can be used instead of the ORDER BY function. 
  4. Subqueries that return more than one row can only be used with IN operator.

What is the Need to Use Subqueries?

The instances where Subqueries are used are as follows: 

  • There is a need to filter a table based on data from another table
  • There is a need to refer a column from another table into the current table.

Simplify Google BigQuery ETL and Analysis with Hevo’s No-code Data Pipeline

A fully managed No-code Data Pipeline platform like Hevo Data helps you integrate and load data from 100+ different sources (including 40+ free sources) to a Data Warehouse such as Google BigQuery or Destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. Its strong integration with umpteenth sources allows users to bring in data of different kinds in a smooth fashion without having to code a single line. 

Get Started with Hevo for Free

Check out some of the cool features of Hevo:

  • Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
  • Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
  • Connectors: Hevo supports 100+ integrations to SaaS platforms, files, Databases, analytics, and BI tools. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; and MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL Databases to name a few.  
  • Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
  • 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
  • Scalable Infrastructure: Hevo has in-built integrations for 100+ sources (including 40+ free sources) that can help you scale your data infrastructure as required.
  • 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

Prerequisites

  • An active Google account with a subscription to Google Cloud Platform (GCP).
  • Basic understanding of Google BigQuery.
  • Understanding of basic SQL queries.
  • Consider the following tables given below for the example purposes:

Products

product_idproduct_nameproduct_owner
P01PS4Sony
P02XBoxMicrosoft
P03NintendoNintendo

Orders

order_idorder_dateprodcut_idorder_qty
O12022-01-02 01:12:23P011
O12022-01-02 01:12:23P021
O22022-01-04 02:12:23P012
O32022-01-06 06:12:23P032

Transactions

transaction_idorder_idtransaction_datetransaction_amt_usd
T01O12022-01-02 01:12:23800
T02O22022-01-04 02:12:23500
T03O32022-01-06 06:12:231000

How to use Google BigQuery Subquery?

Google BigQuery uses ANSI SQL to query the data against the database, and hence it supports Subqueries within the main query. Let’s discuss the different types of subqueries that can be used in BigQuery:

1) Expression Subqueries

Expression Subqueries are those queries that return a single value and are used against expression. Expression subqueries can be correlated queries.

There are different types of Expression subqueries to look around:

A) Scalar Subqueries

The subqueries resulting in a single column and single row are called scalar subqueries. Scalar subqueries are often used with SELECT or WHERE clauses.

Using multiple columns in Scalar queries results in analysis error, and also, if subquery results into multiple rows, that also results in a runtime error. To understand more about the scalar subqueries, consider the following example –

Consider the table Products and Orders, below query that represent a scalar subquery example:

SELECT order_id, (SELECT product_name 
                  FROM products 
                  WHERE orders.product_id = product.product_id) 
                  AS product_name
FROM orders;

Output:

+---------------------------+
| order_id   | product_name |
+---------------------------+
| O1         | PS4          |
| O1         | XBox         |
| O2         | PS4          |
| O3         | Nintendo     |
+---------------------------+

B) EXISTS-based Subqueries

EXISTS-based subqueries return TRUE if the subquery returns one or more rows and FALSE if it produces zero rows. In Exists-based subqueries, users can use any number of columns as required, which will not affect the query result.

SELECT EXISTS(SELECT product_id 
              FROM products 
              WHERE product_owner = 'PS4') AS result;

Output:

+--------+
| result |
+--------+
| TRUE   |
+--------+

C) IN-based Subqueries

IN-based subqueries return TRUE if the subquery returns the row and FALSE if it doesn’t return any value.

The value returned by IN-based subqueries must have a single column, and the data type of the returned value must be the same as the comparable value. Otherwise, it will throw an error.

SELECT "Nintendo" IN (SELECT product_name 
                      FROM products) as result;

Output:

+--------+
| result |
+--------+
| TRUE   |
+--------+

D) ARRAY Subqueries

ARRAY subqueries are the special case of expression subquery, and it returns ARRAY of values combined from different rows. If no rows are returned from the query, it returns an empty array.

SELECT ARRAY(SELECT product_id 
             FROM orders 
             WHERE order_qty = '1') as product_names
FROM orders LIMIT 1;             

Output:

+-----------------+
| product_names   |
+-----------------+
| [P01, P02]      |
+-----------------+

2) Table Subqueries

The Table subqueries are the types where the main query treats the result of the subquery as a temporary table and uses that to fetch the value. This query can only be used with FROM clause.

SELECT product_name FROM (SELECT product_name 
                          FROM products 
                          WHERE product_owner IN ('Sony'))

Output:

+----------------+
| product_name   |
+----------------+
| PS4            |
+----------------+

3) Correlated Subqueries

A Correlated subquery is another type of subquery that references a column from another table outside that subquery. Correlation prevents reusing of the subquery result. 

SELECT product_name, (SELECT order_id 
                      FROM orders 
                      WHERE orders.product_id = 'P02') AS order_id
FROM products;

Output:

+---------------------------+
| product_name   | order_id |
+---------------------------+
| XBOX           | O1       |
+---------------------------+

Conclusion

In this article, you have learned about BigQuery SubQuery. This article also provided information on Google BigQuery, its key features, SQL, Subqueries, and the different types of subqueries used in Google BigQuery in detail. For further information on BigQuery JSON Extract, BigQuery Create View Command, BigQuery Partition Tables, you can visit the former links.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Visit our Website to Explore Hevo

Hevo Data with its strong integration with 100+ data sources (including 40+ Free Sources) allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready. Hevo also allows integrating data from non-native sources using Hevo’s in-built Webhooks Connector. You can then focus on your key business needs and perform insightful analysis using BI tools. 

Want to give Hevo a try?

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

Share your experience of understanding Google BigQuery Subquery in the comment section below! We would love to hear your thoughts.

No-code Data Pipeline for Google BigQuery