BigQuery Case Expressions Simplified 101: Syntax & Example Queries

on Data Warehouse, Google BigQuery, Tutorials • September 8th, 2021 • Write for Hevo

Business Organizations fetch data from various resources, they generate petabytes of data every day. Using traditional databases, it is difficult to maintain a track record of this data and even access it flexibly. 

To query a dataset that holds petabytes of data is time-consuming, and even slows down the performance. Hence, the enterprises are looking for some flexible solutions that can produce optimized results with better performance and availability. 

In this article, you will get a glance at Google BigQuery, a fundamental introduction, and key features of Google BigQuery. You will gain in-depth knowledge of Google BigQuery Case and Conditional Expressions. Read along to learn about BigQuery Case and Conditional Expressions.

Table of Contents

Introduction to Google BigQuery

BigQuery Case and conditional expressions - BigQuery logo
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

Image Source

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

1) Performance

Partitioning is supported by BigQuery, which improves query performance. The data may be readily queried using SQL or Open Database Connectivity (ODBC)

2) Scalability

Being quite elastic, BigQuery separates computation and storage, allowing customers to scale processing and memory resources according to their needs. The tool has significant vertical and horizontal scalability and runs real-time queries on petabytes of data in a very short period.

3) Security

When a third-party authorization exists, users can utilize OAuth as a standard approach to get the cluster. By default, all data is encrypted and in transit. Cloud Identity and Access Management (IAM) allows for fine-tuning administration.

4) Usability

Google BigQuery is a highly user-friendly platform that requires a basic understanding of SQL commands, ETL tools, etc.

5) Data Types

 It supports JSON and XML file formats.

6) Data Loading

It employs the conventional ELT/ETL Batch Data Loading techniques by employing standard SQL dialect, as well as Data Streaming to load data row by row using Streaming APIs.

7) Integrations

In addition to operational databases, the system supports integration with a wide range of data integration tools, business intelligence (BI), and artificial intelligence (AI) solutions. It also works with Google Workspace and Cloud Platform.

8) Data Recovery

Data backup and disaster recovery are among the services provided by Google BigQuery. Users can query point-in-time snapshots of data changes from the last seven days.

9) Pricing Models

The Google BigQuery platform is available in both on-demand and flat-rate subscription models. Although data storage and querying will be chargedexporting, 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.

Introduction to SQL Conditional Expressions

SQL Conditional Expressions are similar to If else statements that are used in other programming languages. Conditional Expressions are usually used to get an optimal result based on a valid expression. In Conditional expressions, a condition is specified, and depending upon that condition, SQL Compiler returns a Boolean result. 

BigQuery Case & Conditional Expressions - SQL Conditional Expressions
Image Source

Simplify 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 30+ 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 30+ 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

  • Working knowledge of Google BigQuery.
  • A Google BigQuery account and project.
  • Working knowledge of SQL.

6 BigQuery Case & Conditional Expressions 

BigQuery Case & Conditional Expressions impose constraints as per the evaluation order of the inputs. The BigQuery Case & Conditional expressions follow the procedure from left to right before short-circuiting and then evaluate the output for the given condition. 

The 6 BigQuery Case & Conditional Expressions are as follows:

1) IF Condition

Syntax:

IF(expr, true_result, else_result)

Description: 

In the syntax of IF Condition, if the expr expression holds the true value then it will return true_result. If the expr expression holds the true value but if the true_result evaluates false value then else_result returns False value or Null. 

For example:

SELECT
  C, D
  IF( C<D, 'true', 'false') as result
FROM Num

2) CASE Condition

Syntax: 

CASE
  WHEN condition THEN result
  [ ... ]
  [ ELSE else_result ]
END

Description: 

The above syntax returns a true value only if the WHEN clause evaluates true results. If all the conditions evaluated indicate the wrong result then else_result returns a false value. 

For example:

SELECT A, B,
  CASE
    WHEN A > 70 THEN 'green'
    WHEN A > 30 THEN 'red'
    ELSE 'red'
  END
  AS result
FROM Num

3) CASE_Expr Condition

Syntax

CASE expr
  WHEN expr_to_match THEN result
  [ ... ]
  [ ELSE else_result ]
END

Description

For each condition, expr is compared to expr_to_match. WHEN clause returns true only on comparison. But if the condition expr is equal to expr_to_match then for all conditions it returns false results. 

For example:

SELECT C, D,
  CASE C
    WHEN 70 THEN 'orange'
    WHEN 30 THEN 'green'
    ELSE 'orange'
  END
  AS result
FROM Num

4) Coalesce Condition

Syntax:

COALESCE(expr[, ...])

Description

The above-mentioned syntax returns only the first not null expression on evaluation.

For example:

SELECT COALESCE('F', 'H', 'G') as result

5) NULLIF Condition

Syntax:

IFNULL(expr, null_result)

Description :

In the above-mentioned syntax, If the expr holds NULL, then only null_result returns NULL. But if expr doesn’t hold NULL value then there’s no value as null_results displays nothing. 

For example:

SELECT IFNULL(NULL, 0) as result

6) IFNULL Condition

Syntax:

NULLIF(expr, expr_to_match)

Description:

The above-mentioned syntax states that if the expression is equal to expr_to_match then it returns a true value. 

For example:

SELECT NULLIF(0, 0) as result

Conclusion

The article summarizes some essential concepts about BigQuery and particularly focuses on SQL conditional expressions. This article also provided in-depth knowledge on the BigQuery Case & Conditional Expressions along with proper syntax, description, and real-time examples.

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 such as Google BigQuery, with a few clicks.

Visit our Website to Explore Hevo

Hevo Data with its strong integration with 100+ sources (including 30+ 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 so that you can focus on your key business needs and perform insightful analysis using BI tools.

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 the BigQuery Case and Conditional Expressions in the comment section below! We would love to hear your thoughts.

No-code Data Pipeline for Google BigQuery