A New Era for SQL: Google’s Pipe Syntax Makes Querying Faster and Easier
Simplified Syntax and Powerful Performance in SQL
Have you ever wished SQL could be easier to work with while keeping its full potential?
SQL has been the standard language for querying data for decades, but it can be very complex from time to time. Google’s new pipe syntax, now in BigQuery and Cloud Logging, simplifies SQL, making queries faster and more efficient.
In this post, we will explore how this new approach transforms SQL, making it easier to write, faster to execute, and more intuitive for everyone.
What is Pipe Syntax in SQL? 🤔
Pipe syntax is an extension of GoogleSQL that makes working with data more intuitive. Instead of following SQL’s traditional rigid structure, pipe syntax allows queries to flow in a top-down, logical order. This new approach uses a simple pipe symbol (|>) to link different stages of a query, such as filtering, aggregating, or joining tables.
The key advantage of pipe syntax is its flexibility.
With pipe syntax, queries start with the FROM clause, followed by operations like SELECT, WHERE, and GROUP BY in a logical sequence. Each step is separate, making the query easier to read and adjust as needed.
Example 1: This query shows how pipe syntax streamlines identifying users impacted by frequent errors within a set date range.
-- Pipe Syntax
FROM log_table
|> WHERE datetime BETWEEN DATETIME '2024-01-01' AND '2024-01-31'
|> AGGREGATE COUNT(log_id) AS error_count GROUP BY user_id, error_type
|> WHERE error_count>100
|> AGGREGATE COUNT(user_id) AS user_count GROUP BY error_typeHere’s a breakdown of each step:
Data Filtering: Selects records from
log_tablewithin January 1–31, 2024.Initial Aggregation: Counts errors per
user_idanderror_type.Filtering by Error: Keeps entries where
error_countexceeds 100.Final Aggregation: Counts unique users per
error_type.
This linear, top-down approach clarifies each transformation step, highlighting the readability of pipe syntax.
Example 2: Another example here is demonstrating the same query in both pipe syntax and standard syntax side-by-side to emphasise the differences.
Both queries retrieve TV series with an average rating above 8.5, more than 100,000 votes, a start year after 2010, and a genre containing “drama.” While both queries achieve the same result, pipe syntax provides a cleaner, more intuitive structure that enhances readability, flexibility, and ease of debugging. What do you think? 🤔 We’d love to hear your thoughts too!
Key Features of Pipe Syntax
1. Simplified, Linear Query Structure
Traditional SQL can feel disjointed, as operations are written out of logical order. However, pipe syntax uses a top-down approach. You start with the FROM clause and then apply filters, joins, or aggregations in any sequence. This structure makes queries easier to understand and follow.
2. New Operators for Added Functionality
Pipe syntax introduces new operators that simplify data manipulation:
EXTEND: Adds new computed columns without replacing existing ones.
SET: Replaces the values of existing columns in the result.
DROP: Removes specified columns from the output.
AGGREGATE: Enables cleaner aggregations.
Example:
FROM sales_data
|> EXTEND ROUND(total_price) AS rounded_price
|> SET status = LOWER(status)
|> DROP item_code
|> AGGREGATE SUM(rounded_price) AS total_sales GROUP BY region;This query demonstrates:
Adding a Column: Rounds
total_priceto createrounded_price.Updating Values: Converts
statusto lowercase.Removing a Column: Drops
item_code.Aggregating Data: Calculates
total_salesby summingrounded_price, grouped byregion.
3. Easy Debugging and Query Building
Pipe syntax is ideal for debugging, as any part of the query up to a pipe (|>) is a valid, runnable query. This lets you test intermediate steps to verify results, making it simpler to identify errors and refine the query as you go. For example, operators like LOG can log intermediate results, and ASSERT can check conditions within the query.
4. Compatibility with Existing SQL
Pipe syntax can be used alongside standard SQL, making it easy to adopt without disrupting current workflows. Existing SQL queries work as usual, and you can gradually integrate pipe syntax in new or modified queries where it adds value.
5. No Performance Impact
Google confirms that pipe syntax operates as efficiently as standard SQL. The SQL optimizer ensures fast query execution, allowing users to benefit from cleaner syntax without sacrificing performance.
6. Optimised for Log Analytics
In addition to its strength in exploratory analysis, pipe syntax is particularly effective for log analytics, which often requires fast, flexible querying of large, semi-structured data. With pipe syntax, log queries become clearer and easier to adjust, allowing analysts to quickly filter, aggregate, and transform data as they investigate specific events or patterns.
Future Potential and AI Integration
SQL queries, particularly complex ones, can be challenging for AI models to generate or understand. Google’s research suggests that pipe syntax could improve AI integration by simplifying complex SQL queries into clear, independent steps. This modularity aligns better with how AI models process data, opening the door for advanced AI tools such as code suggestions, auto-completion, and query generation.
Conclusion
Google’s pipe syntax changes how we write and use SQL queries. It simplifies the structure and improves readability, tackling many of SQL's long-standing issues. With its flexibility for building complex queries, optimization for semi-structured data like JSON, and potential for future AI integration, pipe syntax is a powerful tool.
If you haven’t tried it yet, now is a great time to explore how pipe syntax can enhance your data workflows.
Join The Conversation!
If you're interested in more posts like this, be sure to subscribe for upcoming news, tutorials, deep dives, and insights. Let's learn and grow together in the exciting world of data engineering!
We’d be happy to hear your thoughts, experiences, and any comments you may have about the pipe syntax.
Resources
If you’re interested in exploring Google’s pipe syntax in more detail, here are some valuable resources directly from Google:
Google Cloud Blog: Write Better Log Queries, Faster: Introducing Pipe Syntax in BigQuery and Cloud Logging [Blog Link]
Google Cloud Blog: Revolutionizing SQL with Pipe Syntax, Now Available in BigQuery and Cloud Logging [Blog Link]
Google Cloud Documentation: Pipe syntax [Link]
Research Paper: SQL Has Problems. We Can Fix Them: Pipe Syntax In SQL1 [Link]
Shute, J., Bales, S., Brown, M., Browne, J. D., Dolphin, B., Kudtarkar, R., ... & Yu, L. (2024). SQL Has Problems. We Can Fix Them: Pipe Syntax In SQL. Proceedings of the VLDB Endowment. VLDB Endowment, 4051-4063.