Mastering BigQuery’s Pipe Syntax: Streamlining Your SQL Queries

Introduction

Google BigQuery is a powerful data warehouse that allows you to analyze massive datasets using SQL. However, as datasets and business logic grow in complexity, SQL queries can become lengthy, nested, and difficult to read and maintain. To address these challenges, BigQuery introduces Pipe Syntax, a modern enhancement to SQL that simplifies query writing, improves readability, and maintains all the strengths of traditional SQL.

In this blog post, we’ll explore how you can leverage Pipe Syntax in BigQuery to streamline your SQL queries. We’ll use the BigQuery public dataset bigquery-public-data.thelook_ecommerce.order_items as our example.

Understanding the Challenges with Traditional SQL

SQL has been the de facto language for managing and analyzing relational databases for decades. While powerful, traditional SQL syntax can sometimes be:

• Verbose and Complex: Especially when dealing with nested subqueries, aggregations, and complex joins.
• Hard to Read and Maintain: The order of operations in SQL doesn’t always match the logical flow of data transformations, making queries difficult to follow.
• Challenging to Extend: Modifying existing queries can require significant restructuring, often leading to errors.

For example, performing multi-level aggregations or applying transformations across different stages often results in deeply nested queries that are hard to interpret.

The Inside-Out Data Flow Problem

In traditional SQL, the syntactic order of clauses (SELECT, FROM, WHERE, GROUP BY, ORDER BY) doesn't always align with the semantic order in which data is processed. This mismatch can make it challenging to understand the flow of data, especially in complex queries where subqueries are nested multiple levels deep.

Introducing BigQuery’s Pipe Syntax

Pipe Syntax is a feature in BigQuery that simplifies SQL queries by allowing you to chain operations using the pipe operator (|>). Inspired by functional programming and Unix pipelines, Pipe Syntax lets you express data transformations sequentially, making your queries more intuitive and easier to read.

Here’s a simple analogy: Imagine you’re assembling a lego-toy. Instead of following a complex, all-in-one instruction, you break it down into steps — connecting piece A to B, then B to C, and so on. Pipe syntax lets you do just that with your SQL queries.

How Does Pipe Syntax Work?

Instead of nesting subqueries or dealing with deeply structured SQL, you start with a base dataset and apply transformations step by step, using the |> operator to chain operations. Each operation takes the output of the previous step as its input.

Traditional SQL Example:

SELECT ...
FROM (
  SELECT ...
  FROM (
    SELECT ...
    FROM table
    WHERE ...
  )
  WHERE ...
)

Equivalent Pipe Syntax:

FROM table
|> WHERE ...
|> SELECT ...
|> WHERE ...
|> SELECT ...

In Pipe Syntax, each operation is composed in the order that it’s written, mirroring the logical flow of data transformations.

3. Benefits of Using Pipe Syntax

• Improved Readability: Queries are more straightforward to read since transformations are applied sequentially.
• Ease of Maintenance: Modifying queries becomes simpler. You can easily add, remove, or rearrange steps without restructuring the entire query.
• Simplified Debugging: Analyzing intermediate results is more manageable, as you can inspect the output after each step.
• Enhanced Extensibility: Extending SQL with new operations or functions becomes more intuitive and less prone to syntax conflicts.
• Alignment with Logical Data Flow: The order of operations in your query matches the actual data processing sequence.

Getting Started with Pipe Syntax in BigQuery

To use Pipe Syntax, you begin your query with a FROM clause and then chain subsequent operations using the |> operator. Each pipe operator represents a specific transformation.

Basic Structure:

FROM table_name
|> operation1
|> operation2
|> ...

Available Pipe Operators:

• SELECT: Select specific columns.
• WHERE: Filter rows based on conditions.
• JOIN: Join with another table.
• AGGREGATE: Perform aggregations.
• ORDER BY: Sort the results.
• LIMIT: Limit the number of rows.
• EXTEND: Add computed columns.
• DROP: Remove columns.
• SET: Update column values.
• CALL: Invoke table-valued functions.
• And more…

Key Points:

• Flexible Ordering: Operators can be applied in any order, any number of times.
• Independent Operations: Each operator works independently, making it easy to understand and modify.
• Consistent Scoping: Operators only have access to the immediate input table and their arguments, ensuring clear and predictable behavior.

Real-World Use Case: Analyzing E-commerce Orders

Let’s apply Pipe Syntax to a practical example using the bigquery-public-data.thelook_ecommerce.order_items dataset.

Objective:

We want to analyze the total sales and number of orders per day for ‘Completed’ orders and find the top 5 days with the highest sales.

Traditional SQL Query:

SELECT
  DATE(created_at) AS order_date,
  COUNT(*) AS total_orders,
  SUM(sale_price) AS total_sales
FROM
  `bigquery-public-data.thelook_ecommerce.order_items`
WHERE
  status = 'Completed'
GROUP BY
  order_date
ORDER BY
  total_sales DESC
LIMIT
  5;

Refactoring Using Pipe Syntax:

FROM `bigquery-public-data.thelook_ecommerce.order_items`
|> WHERE status = 'Completed'
|> EXTEND DATE(created_at) AS order_date
|> AGGREGATE
     COUNT(*) AS total_orders,
     SUM(sale_price) AS total_sales
   GROUP BY order_date
|> ORDER BY total_sales DESC
|> LIMIT 5;

Detailed Explanation of the Pipe Syntax Query

Let’s dissect each part of the Pipe Syntax query:

Starting Point:

FROM `bigquery-public-data.thelook_ecommerce.order_items`

• We begin by selecting the order_items table.

Filtering Rows:

|> WHERE status = 'Completed'

• Filters the dataset to include only rows where the status is 'Completed'.

Adding Computed Column:

|> EXTEND DATE(created_at) AS order_date

• Adds a new column order_date by extracting the date from the created_at timestamp.

Aggregating Data:

|> AGGREGATE
        COUNT(*) AS total_orders,
        SUM(sale_price) AS total_sales
      GROUP BY order_date

• Groups the data by order_date and calculates the total_orders and total_sales for each date.

Sorting Results:

|> ORDER BY total_sales DESC

• Orders the results in descending order of total_sales.

Limiting Output:

|> LIMIT 5;

• Limits the output to the top 5 records.

Benefits Highlighted:

• Sequential Flow: Each operation builds on the previous one in a logical sequence.
• Simplified Syntax: Eliminates the need for nested subqueries and repetitive clauses.
• Enhanced Clarity: Easier to read and understand compared to the traditional SQL version.

Additional Examples

Calculating Average Sale Price Per Status

Objective: Find the average sale price for each order status.

Traditional SQL:

SELECT
  status,
  AVG(sale_price) AS average_sale_price
FROM
  `bigquery-public-data.thelook_ecommerce.order_items`
GROUP BY
  status;

Pipe Syntax:

FROM `bigquery-public-data.thelook_ecommerce.order_items`
|> AGGREGATE
     AVG(sale_price) AS average_sale_price
   GROUP BY status;

Explanation:

• AGGREGATE Operator: Performs the aggregation and grouping in a single, concise operation.
• Clarity: Removes redundancy by eliminating the need to reference status in both SELECT and GROUP BY.

Identifying Delayed Shipments

Objective: List orders where the shipping date is more than 7 days after the order creation date.

Traditional SQL:

SELECT
  order_id,
  user_id,
  created_at,
  shipped_at,
  DATE_DIFF(shipped_at, created_at, DAY) AS days_to_ship
FROM
  `bigquery-public-data.thelook_ecommerce.order_items`
WHERE
  DATE_DIFF(shipped_at, created_at, DAY) > 7;

Pipe Syntax:

FROM `bigquery-public-data.thelook_ecommerce.order_items`
|> EXTEND
     DATE_DIFF(shipped_at, created_at, DAY) AS days_to_ship
|> WHERE days_to_ship > 7
|> SELECT
     order_id,
     user_id,
     created_at,
     shipped_at,
     days_to_ship;

Explanation:

• Compute New Column: EXTEND calculates days_to_ship and adds it to the dataset.
• Filter Rows: WHERE filters rows where days_to_ship is greater than 7.
• Select Desired Columns: SELECT picks specific columns for the final output.

Advantages:

• Intermediate Computation: Allows you to compute days_to_ship and use it immediately in the WHERE clause.
• Modularity: Each step is independent, making it easier to debug and modify.

Conclusion

BigQuery’s Pipe Syntax modernizes SQL by making it more intuitive and easier to work with, without sacrificing its strengths or deviating from its foundational principles. By adopting Pipe Syntax, you can write cleaner, more maintainable queries that align closely with the logical flow of data transformations.

Key Takeaways:

• Sequential Transformation: Pipe Syntax lets you write queries that mirror the step-by-step data processing steps.
• Enhanced Readability: Reduces complexity by eliminating deep nesting and repetitive clauses.
• Improved Maintainability: Simplifies the addition or modification of query operations.

References:

• BigQuery SQL Reference: BigQuery SQL
• Introduction to Pipe Syntax: Modernizing SQL with Pipe Syntax
• SQL Has Problems. We can Fix Them: Pipe Syntax in SQL