Google BigQuery Structs: Syntax and Queries Simplified 101

BigQuery is Google’s Data Warehousing solution. It is serverless, i.e., it allocates compute resources on the fly, as per the requirements, so that you need not worry about resource allocation. It performs Parallel Query Execution, thanks to the organization of data in columns rather than rows, and is well suited for spiky workloads, i.e. querying large chunks of data in a short duration.

Often, the data you are dealing with in your analysis does not belong to the conventional data types like int, float, boolean, string, etc. You often deal with collections. One of the common ways of representing data collections is through key-value pairs. BigQuery Structs allow the storage of key-value pair collections in your tables. Google BigQuery defines a struct as follows:

Container of ordered fields each with a type (required) and field name (optional).

If you have worked with JSON files in the past, or with dictionaries in Python, you will feel at home with structs in BigQuery.

In this article, you will learn how to create BigQuery Structs, how to use them in queries, and how to perform operations on these Structs. In the end, you’ll also briefly touch upon the concept of Nested Structs.

Table of Contents

• Prerequisites
• Introduction to Google BigQuery
• Working with Google BigQuery Structs
  • Creating Google BigQuery Structs
  • Querying Google BigQuery Structs
  • Performing Operations on Google BigQuery Structs
• Understanding Nested Structs in Google BigQuery
• Conclusion

Prerequisites

It is assumed that you have worked with Google BigQuery in the past and know how to create datasets and tables in Google BigQuery. If you are not familiar with these concepts, it will be worthwhile to look at these helper articles:

• Creating Datasets
• Creating and using Tables

Introduction to Google BigQuery

Google BigQuery is a fully-managed Cloud Data Warehouse that lets you use SQL to manage terabytes of data. It enables businesses to evaluate their data more quickly and generate insights using normal SQL queries. Since Google BigQuery is part of the Google Cloud Platform (GCP), it can take advantage of Google Cloud Functions and other Google products to help you save time and get better results. To process read-only data, Google BigQuery is built on Google’s Dremel engine. Users can scale up or down both the Storage and Compute power on their own, depending on their needs.

Separating the Processing Engine from the storage space gives Google BigQuery additional flexibility. This capability allows Google BigQuery to Store, Analyze and Evaluate data from any location. Google BigQuery also features advanced Data Analysis and Visualization capabilities, such as the Google BigQuery ML (Machine Learning) and BI (Business Intelligence) Engine.

Also, you can utilize Google BigQuery in three different ways:

• Data Loading and Exporting: You may easily and rapidly import your data into BigQuery. After BigQuery has processed your data, you can export it for further study.
• Interactive Queries: You may also use data to conduct Batch queries and construct Virtual tables.
• Manage Data: Google BigQuery allows you to list jobs, datasets, projects, and tables, as well as update them. Any data you upload to Google BigQuery can be managed.

Working with Google BigQuery Structs

• Creating Google BigQuery Structs
• Querying Google BigQuery Structs
• Performing Operations on Google BigQuery Structs

Creating Google BigQuery Structs

Let’s get started by creating a table with a Struct column. Let’s create a student_records table, containing rollNo as one integer column and info as a Struct column. The info column is itself composed of 3 attributes: name, age and department, with age being an integer, and the other two columns being strings.

You can set an expiration time for your table as shown. Also, replace the ‘my_first_dataset’ with your dataset name.

CREATE TABLE my_first_dataset.student_records
 (
   rollNo INT OPTIONS(description="The identifier for each student"),
   info STRUCT<
     name STRING,
     age INT,
     department STRING
   >
 )
 OPTIONS(
   expiration_timestamp=TIMESTAMP "2021-10-07 00:00:00 UTC",
   description="Table with a struct column"
 )

Now that the table is created, let’s populate it with values. You can run a query like the one below (with more values as required).

INSERT INTO my_first_data.student_records (rollNo, info) VALUES (1,STRUCT("Yash",26,"Mechanical Engineering"))

Now, in case you are using the Google BigQuery Sandbox, then the above query won’t execute, because DML (Data Manipulation Language) queries like INSERT, UPDATE, or DELETE are not supported in Sandbox and you will have to provide billing information. However, that doesn’t mean you can’t have a table populated with data. Run the following query. It will not only create the table but also populate it with data. 

CREATE OR REPLACE TABLE my_first_dataset.student_records AS (
SELECT 1 AS rollNo, STRUCT("Isha" as name,22 as age,"Pharmacy" as department) AS info
UNION ALL
SELECT 2 AS rollNo, STRUCT("Yash" as name,22 as age,"Mechanical Engineering" as department) AS info
UNION ALL 
SELECT 3 AS rollNo, STRUCT("Siddhi" as name,18 as age,"Metallurgy" as department) AS info
UNION ALL 
SELECT 4 AS rollNo, STRUCT("Kushal" as name,21 as age,"Electrical Engineering" as department) AS info
);

As you can see, you are creating a table using the result of a query and you are adding multiple rows by performing the UNION ALL operation. Of course, this approach is not scalable (you won’t do this to populate thousands of rows), but it will help you proceed further with this tutorial.

As things stand right now, you have a table student_records containing a column of type struct, populated with 4 rows of data. Now let’s explore further.

First of all, if you click on the student_records table in the explorer, and look at the schema, you will see that the type for the info column is RECORD and the mode is NULLABLE. This combination (RECORD + NULLABLE) identifies a Struct BigQuery. 

Note: If the type is RECORD and the mode is REPEATED, it means that the column contains an Array of Structs.

Querying Google BigQuery Structs

If you directly query a Struct column in Google BigQuery, the result will contain multiple columns, one for each of the attributes within the BigQuery Structs. Here’s an example:

SELECT info from my_first_dataset.student_records

The output contains 3 columns since the info column has 3 attributes.

In case you wish to query specific attributes of the Struct, you can use the Dot Notation. For instance, the following query fetches the roll no, name, and age for each student:

SELECT rollNo, info.name, info.age from my_first_dataset.student_records

Performing Operations on Google BigQuery Structs

Structs support limited operations: Equal (=), Not equal (!= or <>), IN, and NOT IN. An example with Equal is given below.

SELECT * from my_first_dataset.student_records
WHERE info = ("Yash",22,"Mechanical Engineering")

Note that you did not use the STRUCT keyword before (“Yash”,22, “Mechanical Engineering”) in the above query. This is because Google BigQuery constructs an anonymous Struct when it is given a tuple of values. See the tuple syntax of constructing a struct.

An example with NOT IN is given below:

SELECT * from my_first_dataset.student_records
WHERE info NOT IN unnest([("Siddhi",18,"Metallurgy"), ("Kushal",21,"Electrical Engineering")])

As you can see, we checked if the Struct belongs to an Array of Structs or not, by listing items of the array, using unnest.

Note that the limited set of operations listed above apply only to the Struct as a whole. Individual attributes within the Struct can support other operations (>, <, etc.) depending on the data type of that attribute. An example is given below showing the use of the ‘<’ operator on the age attribute.

SELECT * from my_first_dataset.student_records
WHERE info.age < 22

Understanding Nested Structs in Google BigQuery

A Struct having another Struct as one or more of its attributes is known as a Nested Struct. If you have understood the concept of Structs, then creating and querying Nested Structs should be straightforward for you.

Whether it’s a single record or repeated data, Google BigQuery allows nested records within tables. Unlike traditional denormalization methods, records in Google BigQuery are expressed using nested and repeated fields.

Google BigQuery’s inbuilt support for Nested and Repeated structures in JSON on the other hand is the preferred way for denormalizing data. This strategy, rather than flattening attributes into a table, localizes a record’s subattributes into a single table. Employing nested records during analysis eliminates the necessity for repeating data, generating new subtables or using joins in Google BigQuery Structs.

The following example shows the creation and population of a table containing the info column which is a Struct, which contains another BigQuery Struct (subjects) as one of its attributes.

CREATE OR REPLACE TABLE my_first_dataset.student_records_detailed AS (
SELECT 1 AS rollNo, STRUCT("Isha" as name,22 as age,"Pharmacy" as department, STRUCT("Chemistry" as sub1, "Bio" as sub2) as subjects) AS info
 
UNION ALL
 
SELECT 2 AS rollNo, STRUCT("Yash" as name,22 as age,"Mechanical Engineering" as department, STRUCT("Physics" as sub1, "Math" as sub2) as subjects) AS info);

When you query the Nested Struct column, the attributes within the Inner Struct also appear as columns.

SELECT info from my_first_dataset.student_records_detailed

As you would have expected, the dot notation can be extended to queries of Nested Structs:

SELECT info.subjects.sub1 from my_first_dataset.student_records_detailed

Finally, operations work on Nested Structs like on normal Google BigQuery Structs. An example is shown below for the equal operator:

SELECT * from my_first_dataset.student_records_detailed
WHERE info = ("Isha",22,"Pharmacy",("Chemistry","Bio"))

I hope you enjoyed reading this article on creating & working with Google BigQuery Structs and found it useful. You can refer to the official documentation for any further reading on structs.

Conclusion

This article introduced you to the concept of Google BigQuery Structs. It also provided you with an in-depth guide with proper syntax and examples of creating, querying, and managing Google BigQuery Structs. It also covered Nested Structs and how it is just an extension of the concept of Google BigQuery Structs.

Companies store valuable data from multiple data sources into Google BigQuery. The manual process to transfer data from source to destination is a tedious task but this is where Hevo saves the day!

Visit our Website to Explore Hevo

Hevo Data is a No-code Data Pipeline that can help you transfer data from any data source to Google BigQuery. It fully automates the process to load and transform data from 100+ Data Sources (Including 40+ Free Sources) to a destination of your choice such as Google BigQuery without writing a single line of code. 

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of learning about Google BigQuery Structs in the comments section below!