#sqlpipesyntax

The revolutionary SQL pipe syntax is now accessible in Cloud Logging and BigQuery.

SQL has emerged as the industry standard language for database development. Its well-known syntax and established community have made data access genuinely accessible to everyone. However, SQL isn’t flawless, let’s face it. Several problems with SQL’s syntax make it more difficult to read and write:

Rigid structure: Subqueries or other intricate patterns are needed to accomplish anything else, and a query must adhere to a specific order (SELECT … FROM … WHERE … GROUP BY).

Awkward inside-out data flow: FROM clauses included in subqueries or common table expressions (CTE) are the first step in a query, after which logic is built outward.

Verbose, repetitive syntax: Are you sick of seeing the same columns in every subquery and repeatedly in SELECT, GROUP BY, and ORDER BY?

For novice users, these problems may make SQL more challenging. Reading or writing SQL requires more effort than should be required, even for experienced users. Everyone would benefit from a more practical syntax.

Numerous alternative languages and APIs have been put forth over time, some of which have shown considerable promise in specific applications. Many of these, such as Python DataFrames and Apache Beam, leverage piped data flow, which facilitates the creation of arbitrary queries. Compared to SQL, many users find this syntax to be more understandable and practical.

Presenting SQL pipe syntax

Google Cloud is to simplify and improve the usability of data analysis. It is therefore excited to provide pipe syntax, a ground-breaking invention that enhances SQL in BigQuery and Cloud Logging with the beauty of piped data flow.

Pipe syntax: what is it?

In summary, pipe syntax is an addition to normal SQL syntax that increases the flexibility, conciseness, and simplicity of SQL. Although it permits applying operators in any sequence and in any number of times, it provides the same underlying operators as normal SQL, with the same semantics and essentially the same syntax.

How it operates:

FROM can be used to begin a query.

The |> pipe sign is used to write operators in a consecutive fashion.

Every operator creates an output table after consuming its input table.

Standard SQL syntax is used by the majority of pipe operators:

LIMIT, ORDER BY, JOIN, WHERE, SELECT, and so forth.

It is possible to blend standard and pipe syntax at will, even in the same query.

Impact in the real world at HSBC

After experimenting with a preliminary version in BigQuery and seeing remarkable benefits, the multinational financial behemoth HSBC has already adopted pipe syntax. They observed notable gains in code readability and productivity, particularly when working with sizable JSON collections.

Benefits of integrating SQL pipe syntax

SQL developers benefit from the addition of pipe syntax in several ways. Here are several examples:

Simple to understand

It can be difficult to learn and accept new languages, especially in large organizations where it is preferable for everyone to utilize the same tools and languages. Pipe syntax is a new feature of the already-existing SQL language, not a new language. Because pipe syntax uses many of the same operators and largely uses the same syntax, it is relatively easy for users who are already familiar with SQL to learn.

Learning pipe syntax initially is simpler for users who are new to SQL. They can utilize those operators to express their intended queries directly, avoiding some of the complexities and workarounds needed when writing queries in normal SQL, but they still need to master the operators and some semantics (such as inner and outer joins).

Simple to gradually implement without requiring migrations

As everyone knows, switching to a new language or system may be costly, time-consuming, and prone to mistakes. You don’t need to migrate anything in order to begin using pipe syntax because it is a part of GoogleSQL. All current queries still function, and the new syntax can be used sparingly where it is useful. Existing SQL code is completely compatible with any new SQL. For instance, standard views defined in standard syntax can be called by queries using pipe syntax, and vice versa. Any current SQL does not become outdated or unusable when pipe syntax is used in new SQL code.

No impact on cost or performance

Without any additional layers (such translation proxies), which might increase latency, cost, or reliability issues and make debugging or tweaking more challenging, pipe syntax functions on well-known platforms like BigQuery.

Additionally, there is no extra charge. SQL’s declarative semantics still apply to queries utilizing pipe syntax, therefore the SQL query optimizer will still reorganize the query to run more quickly. Stated otherwise, the performance of queries written in standard or pipe syntax is usually identical.

For what purposes can pipe syntax be used?

Pipe syntax enables you to construct SQL queries that are easier to understand, more effective, and easier to maintain, whether you’re examining data, establishing data pipelines, making dashboards, or examining logs. Additionally, you may use pipe syntax anytime you create queries because it supports the majority of typical SQL operators. A few apps to get you started are as follows:

Debugging queries and ad hoc analysis

When conducting data exploration, you usually begin by examining a table’s rows (beginning with a FROM clause) to determine what is there. After that, you apply filters, aggregations, joins, ordering, and other operations. Because you can begin with a FROM clause and work your way up from there, pipe syntax makes this type of research really simple. You can view the current results at each stage, add a pipe operator, and then rerun the query to view the updated results.

Debugging queries is another benefit of using pipe syntax. It is possible to highlight a query prefix and execute it, displaying the intermediate result up to that point. This is a good feature of queries in pipe syntax: every query prefix up to a pipe symbol is also a legitimate query.

Lifecycle of data engineering

Data processing and transformation become increasingly difficult and time-consuming as data volume increases. Building, modifying, and maintaining a data pipeline typically requires a significant technical effort in contexts with a lot of data. Pipe syntax simplifies data engineering with its more user-friendly syntax and linear query structure. Bid farewell to the CTEs and highly nested queries that tend to appear whenever standard SQL is used. This latest version of GoogleSQL simplifies the process of building and managing data pipelines by reimagining how to parse, extract, and convert data.

Using plain language and LLMs with SQL

For the same reasons that SQL can be difficult for people to read and write, research indicates that it can also be difficult for large language models (LLMs) to comprehend or produce. Pipe syntax, on the other hand, divides inquiries into separate phases that closely match the intended logical data flow. A desired data flow may be expressed more easily by the LLM using pipe syntax, and the generated queries can be made more simpler and easier for humans to understand. This also makes it much easier for humans to validate the created queries.

Because it’s much simpler to comprehend what’s happening and what’s feasible, pipe syntax also enables improved code assistants and auto-completion. Additionally, it allows for suggestions for local modifications to a single pipe operator rather than global edits to an entire query. More natural language-based operators in a query and more intelligent AI-generated code suggestions are excellent ways to increase user productivity.

Discover the potential of pipe syntax right now

Because SQL is so effective, it has been the worldwide language of data for 50 years. When it comes to expressing queries as declarative combinations of relational operators, SQL excels in many things.

However, that does not preclude SQL from being improved. By resolving SQL’s primary usability issues and opening up new possibilities for interacting with and expanding SQL, pipe syntax propels SQL into the future. This has nothing to do with creating a new language or replacing SQL. Although SQL with pipe syntax is still SQL, it is a better version of the language that is more expressive, versatile, and easy to use.