Pipe query syntax is an extension to GoogleSQL that supports a linearquery structure designed to make your queries easier to read, write, andmaintain. You can use pipe syntax anywhere you write GoogleSQL.

Pipe syntax supports the same operations as existingGoogleSQL querysyntax, orstandard syntax—for instance, selection,aggregation and grouping, joining, and filtering—but the operations can beapplied in any order, any number of times. The linear structure of pipe syntaxlets you write queries so that the order of the query syntax matches the orderof logical steps taken to build the result table.

Queries that use pipe syntax are priced, executed, and optimized the same wayas their equivalent standard syntax queries. When you write queries with pipesyntax, follow the guidelines toestimate costs andoptimize query computation.

Standard syntax suffers from issues that can make itdifficult to read, write, and maintain. The following table shows how pipesyntax addresses these issues:

To build up a complex query step by step in pipe syntax, seeAnalyze data using pipe syntax.For full syntax details, see thePipe query syntax referencedocumentation.

Basic syntax

In pipe syntax, queries start with a standard SQL query or aFROM clause. Forexample, a standaloneFROM clause, such asFROM MyTable, is validpipe syntax. The result of the standard SQL query or the table from theFROMclause can then be passed as input to a pipe symbol,|>, followed by a pipeoperator name and any arguments to that operator. The pipe operator transformsthe table in some way, and the result of that transformation can be passed toanother pipe operator.

You can use any number of pipe operators in your query to do things such asselect, order, filter, join, or aggregate columns. The names of pipe operatorsmatch their standard syntax counterparts and generally have the same behavior.The main difference between standard syntax and pipe syntax is the way youstructure your query. As the logic expressed by your query becomes more complex,the query can still be expressed as a linear sequence of pipe operators, withoutusing deeply nested subqueries, making it easier to read and understand.

Pipe syntax has the following key characteristics:

• Each pipe operator in pipe syntax consists of the pipe symbol,|>, anoperator name, and any arguments:
  |> operator_name argument_list
• Pipe operators can be added to the end of any valid query.
• Pipe operators can be applied in any order, any number of times.
• Pipe syntax works anywhere standard syntax is supported: in queries, views,table-valued functions, and other contexts.
• Pipe syntax can be mixed with standard syntax in the same query. Forexample, subqueries can use different syntax from the parent query.
• A pipe operator can see every alias that exists in the table preceding thepipe.
• A query canstart with aFROM clause, and pipe operatorscan optionally be added after theFROM clause.

Consider the following table:

CREATEORREPLACETABLEmydataset.ProduceAS(SELECT'apples'ASitem,2ASsales,'fruit'AScategoryUNIONALLSELECT'apples'ASitem,7ASsales,'fruit'AScategoryUNIONALLSELECT'carrots'ASitem,0ASsales,'vegetable'AScategoryUNIONALLSELECT'bananas'ASitem,15ASsales,'fruit'AScategory);

The following queries each contain valid pipe syntax that shows how you canbuild a query sequentially.

Queries canstart with aFROM clauseand don't need to contain a pipe symbol:

-- View the table.FROMmydataset.Produce;/*---------+-------+-----------+ | item    | sales | category  | +---------+-------+-----------+ | apples  | 7     | fruit     | | apples  | 2     | fruit     | | carrots | 0     | vegetable | | bananas | 15    | fruit     | +---------+-------+-----------*/

You can filter with aWHERE pipe operator:

-- Filter items with no sales.FROMmydataset.Produce|>WHEREsales >0;/*---------+-------+-----------+ | item    | sales | category  | +---------+-------+-----------+ | apples  | 7     | fruit     | | apples  | 2     | fruit     | | bananas | 15    | fruit     | +---------+-------+-----------*/

To perform aggregation, use theAGGREGATE pipeoperator, followed by any number of aggregatefunctions, followed by aGROUP BY clause. TheGROUP BY clause is part of theAGGREGATE pipe operator and isn't separated by a pipe symbol (|>).

-- Compute total sales by item.FROMmydataset.Produce|>WHEREsales >0|>AGGREGATESUM(sales)AStotal_sales,COUNT(*)ASnum_salesGROUPBYitem;/*---------+-------------+-----------+ | item    | total_sales | num_sales | +---------+-------------+-----------+ | apples  | 9           | 2         | | bananas | 15          | 1         | +---------+-------------+-----------*/

Now suppose you have the following table that contains an ID for each item:

CREATEORREPLACETABLEmydataset.ItemDataAS(SELECT'apples'ASitem,'123'ASidUNIONALLSELECT'bananas'ASitem,'456'ASidUNIONALLSELECT'carrots'ASitem,'789'ASid);

You can use theJOIN pipe operator to join the resultsof the previous query with this table to include each item's ID:

FROMmydataset.Produce|>WHEREsales >0|>AGGREGATESUM(sales)AStotal_sales,COUNT(*)ASnum_salesGROUPBYitem|>JOINmydataset.ItemDataUSING(item);/*---------+-------------+-----------+-----+ | item    | total_sales | num_sales | id  | +---------+-------------+-----------+-----+ | apples  | 9           | 2         | 123 | | bananas | 15          | 1         | 456 | +---------+-------------+-----------+-----*/

Key differences from standard syntax

Pipe syntax differs from standard syntax in the following ways:

• Queries canstart with aFROM clause.
• TheSELECT pipe operator doesn't perform aggregation. You must use theAGGREGATE pipe operator instead.
• Filtering is always done with theWHERE pipeoperator, which can be applied anywhere. TheWHEREpipe operator, which replacesHAVING andQUALIFY, can filter the results of aggregation or window functions.

For more details, see the complete list ofpipe operators.

Use cases

Common use cases for pipe syntax include the following:

• Ad-hoc analysis and incremental query building:The logical order of operationsmakes it easier to write and debug queries. The prefix of anyquery up to a pipe symbol|> is a valid query, which helps you viewintermediate results in a long query. The productivity gains canspeed up the development process across your organization.
• Log analytics: There exist other types of pipe-like syntax that arepopular among log analytics users. Pipe syntax provides a familiar structurethat simplifies onboarding for those users toLog Analytics andBigQuery.

Additional features in pipe syntax

With few exceptions, pipe syntax supports all operators that standard syntaxdoes with the same syntax. In addition, pipe syntax introduces additional pipeoperators and uses a modified syntax for aggregations and joins. The followingsections explain some of these operators. For all supported operators, see thecomplete list ofpipe operators.

EXTEND pipe operator

TheEXTEND pipe operator lets you append computedcolumns to the current table. TheEXTEND pipe operator is similar to theSELECT *, new_column statement, but it gives you more flexibility inreferencing column aliases.

Consider the following table that contains two test scores for each person:

CREATEORREPLACETABLEmydataset.ScoresAS(SELECT'Alex'ASstudent,9ASscore1,10ASscore2,10ASpoints_possibleUNIONALLSELECT'Dana'ASstudent,5ASscore1,7ASscore2,10ASpoints_possible);/*---------+--------+--------+-----------------+ | student | score1 | score2 | points_possible | +---------+--------+--------+-----------------+ | Alex    | 9      | 10     | 10              | | Dana    | 5      | 7      | 10              | +---------+--------+--------+-----------------*/

Suppose you want to compute the average raw score and average percentage scorethat each student received on the test. In standard syntax, later columns inaSELECT statement don't have visibility to earlier aliases. To avoid asubquery, you have to repeat the expression for the average:

SELECTstudent,(score1+score2)/2ASaverage_score,(score1+score2)/2/points_possibleASaverage_percentFROMmydataset.Scores;

TheEXTEND pipe operator can reference previously used aliases, making thequery easier to read and less error prone:

FROMmydataset.Scores|>EXTEND(score1+score2)/2ASaverage_score|>EXTENDaverage_score/points_possibleASaverage_percent|>SELECTstudent,average_score,average_percent;/*---------+---------------+-----------------+ | student | average_score | average_percent | +---------+---------------+-----------------+ | Alex    | 9.5           | .95             | | Dana    | 6.0           | 0.6             | +---------+---------------+-----------------*/

SET pipe operator

TheSET pipe operator lets you replace the value ofcolumns in the current table. TheSET pipe operator is similar to theSELECT* REPLACE (expression AS column) statement. You can reference the originalvalue by qualifying the column name with a table alias.

FROM(SELECT3ASx,5ASy)|>SETx=2*x;/*---+---+ | x | y | +---+---+ | 6 | 5 | +---+---*/

DROP pipe operator

TheDROP pipe operator lets you remove columns from thecurrent table. TheDROP pipe operator is similar to theSELECT *EXCEPT(column) statement. After a column is dropped you can still reference theoriginal value by qualifying the column name with a table alias.

FROM(SELECT1ASx,2ASy)ASt|>DROPx;/*---+ | y | +---+ | 2 | +---*/

RENAME pipe operator

TheRENAME pipe operator lets you rename columns fromthe current table. TheRENAME pipe operator is similar to theSELECT *EXCEPT(old_column), old_column AS new_column statement.

FROM(SELECT1ASx,2ASy,3ASz)ASt|>RENAMEyASw;/*---+---+---+ | x | w | z | +---+---+---+ | 1 | 2 | 3 | +---+---+---*/

AGGREGATE pipe operator

To perform aggregation in pipe syntax, use theAGGREGATE pipeoperator, followed by any number of aggregatefunctions, followed by aGROUP BY clause. You don't need to repeat columns inaSELECT clause.

The examples in this section use theProduce table:

CREATEORREPLACETABLEmydataset.ProduceAS(SELECT'apples'ASitem,2ASsales,'fruit'AScategoryUNIONALLSELECT'apples'ASitem,7ASsales,'fruit'AScategoryUNIONALLSELECT'carrots'ASitem,0ASsales,'vegetable'AScategoryUNIONALLSELECT'bananas'ASitem,15ASsales,'fruit'AScategory);/*---------+-------+-----------+ | item    | sales | category  | +---------+-------+-----------+ | apples  | 7     | fruit     | | apples  | 2     | fruit     | | carrots | 0     | vegetable | | bananas | 15    | fruit     | +---------+-------+-----------*/

FROMmydataset.Produce|>AGGREGATESUM(sales)AStotal,COUNT(*)ASnum_recordsGROUPBYitem,category;/*---------+-----------+-------+-------------+ | item    | category  | total | num_records | +---------+-----------+-------+-------------+ | apples  | fruit     | 9     | 2           | | carrots | vegetable | 0     | 1           | | bananas | fruit     | 15    | 1           | +---------+-----------+-------+-------------*/

If you are ready to order your results immediately following aggregation, youcan mark the columns in theGROUP BY clause that you want to order withASC orDESC. Unmarked columns aren't ordered.

If you want to order all columns, then you can replace theGROUP BY clausewith aGROUP AND ORDER BY clause, which ordersevery column in ascending order by default. You can specifyDESC following thecolumns that you want to order in descending order. For example, the followingthree queries are equivalent:

-- Use a separate ORDER BY clause.FROMmydataset.Produce|>AGGREGATESUM(sales)AStotal,COUNT(*)ASnum_recordsGROUPBYcategory,item|>ORDERBYcategoryDESC,item;

-- Explicitly mark how to order columns in the GROUP BY clause.FROMmydataset.Produce|>AGGREGATESUM(sales)AStotal,COUNT(*)ASnum_recordsGROUPBYcategoryDESC,itemASC;

-- Only mark descending columns in the GROUP AND ORDER BY clause.FROMmydataset.Produce|>AGGREGATESUM(sales)AStotal,COUNT(*)ASnum_recordsGROUPANDORDERBYcategoryDESC,item;

The advantage of using aGROUP AND ORDER BY clause is that you don't have torepeat column names in two places.

To perform full table aggregation, useGROUP BY() or omit theGROUP BYclause entirely:

FROMmydataset.Produce|>AGGREGATESUM(sales)AStotal,COUNT(*)ASnum_records;/*-------+-------------+ | total | num_records | +-------+-------------+ | 24    | 4           | +-------+-------------*/

JOIN pipe operator

TheJOIN pipe operator lets you join the current tablewith another table and supports the standardjoin operations,includingCROSS,INNER,LEFT,RIGHT, andFULL.

The following examples reference theProduce andItemData tables:

CREATEORREPLACETABLEmydataset.ProduceAS(SELECT'apples'ASitem,2ASsales,'fruit'AScategoryUNIONALLSELECT'apples'ASitem,7ASsales,'fruit'AScategoryUNIONALLSELECT'carrots'ASitem,0ASsales,'vegetable'AScategoryUNIONALLSELECT'bananas'ASitem,15ASsales,'fruit'AScategory);

CREATEORREPLACETABLEmydataset.ItemDataAS(SELECT'apples'ASitem,'123'ASidUNIONALLSELECT'bananas'ASitem,'456'ASidUNIONALLSELECT'carrots'ASitem,'789'ASid);

The following example uses aUSING clause and avoids column ambiguity:

FROMmydataset.Produce|>JOINmydataset.ItemDataUSING(item)|>WHEREitem='apples';/*--------+-------+----------+-----+ | item   | sales | category | id  | +--------+-------+----------+-----+ | apples | 2     | fruit    | 123 | | apples | 7     | fruit    | 123 | +--------+-------+----------+-----*/

To reference columns in the current table, such as to disambiguate columns in anON clause, you need to alias the current table by using theAS pipeoperator. You can optionally alias the joined table. You canreference both aliases following subsequent pipe operators:

FROMmydataset.Produce|>ASproduce_table|>JOINmydataset.ItemDataASitem_tableONproduce_table.item=item_table.item|>WHEREproduce_table.item='bananas'|>SELECTitem_table.item,sales,id;/*---------+-------+-----+ | item    | sales | id  | +---------+-------+-----+ | bananas | 15    | 123 | +---------+-------+-----*/

The right-hand side of the join doesn't have visibility to the left-hand sideof the join, which means you can't join the current table with itself. Forexample, the following query fails:

-- This query doesn't work.FROMmydataset.Produce|>ASproduce_table|>JOINproduce_tableASproduce_table_2USING(item);

To perform a self-join with a modified table, you can use a common tableexpression (CTE) inside of aWITH clause.

WITHcte_tableAS(FROMmydataset.Produce|>WHEREitem='carrots')FROMcte_table|>JOINcte_tableAScte_table_2USING(item);

Example

Consider the following table with information about customer orders:

CREATEORREPLACETABLEmydataset.CustomerOrdersAS(SELECT1AScustomer_id,100ASorder_id,'WA'ASstate,5AScost,'clothing'ASitem_typeUNIONALLSELECT1AScustomer_id,101ASorder_id,'WA'ASstate,20AScost,'clothing'ASitem_typeUNIONALLSELECT1AScustomer_id,102ASorder_id,'WA'ASstate,3AScost,'food'ASitem_typeUNIONALLSELECT2AScustomer_id,103ASorder_id,'NY'ASstate,16AScost,'clothing'ASitem_typeUNIONALLSELECT2AScustomer_id,104ASorder_id,'NY'ASstate,22AScost,'housewares'ASitem_typeUNIONALLSELECT2AScustomer_id,104ASorder_id,'WA'ASstate,45AScost,'clothing'ASitem_typeUNIONALLSELECT3AScustomer_id,105ASorder_id,'MI'ASstate,29AScost,'clothing'ASitem_type);

Suppose you want to know, for each state and item type, the average amount spentby repeat customers. You could write the query in the following way:

SELECTstate,item_type,AVG(total_cost)ASaverageFROM(SELECTSUM(cost)AStotal_cost,customer_id,state,item_type,COUNT(*)OVER(PARTITIONBYcustomer_id)ASnum_ordersFROMmydataset.CustomerOrdersGROUPBYcustomer_id,state,item_typeQUALIFYnum_orders >1)GROUPBYstate,item_typeORDERBYstateDESC,item_typeASC;

If you read the query from top to bottom, you encounter the columntotal_costbefore it has been defined. Even within the subquery, you read the names ofcolumns before you see which table they come from.

To make sense of this query, it needs to beread from the inside out. The columnsstate anditem_type are repeatednumerous times in theSELECT andGROUP BY clauses, then againin theORDER BY clause.

The following equivalent query is written usingpipe syntax:

FROMmydataset.CustomerOrders|>AGGREGATESUM(cost)AStotal_cost,GROUPBYcustomer_id,state,item_type|>EXTENDCOUNT(*)OVER(PARTITIONBYcustomer_id)ASnum_orders|>WHEREnum_orders >1|>AGGREGATEAVG(total_cost)ASaverageGROUPBYstateDESC,item_typeASC;/*-------+------------+---------+ | state | item_type  | average | +-------+------------+---------+ | WA    | clothing   | 35.0    | | WA    | food       | 3.0     | | NY    | clothing   | 16.0    | | NY    | housewares | 22.0    | +-------+------------+---------*/

With pipe syntax, you can write the query to follow the logical steps you mightthink through to solve the original problem. The lines of syntax in the querycorrespond to the following logical steps:

• Start with the table of customer orders.
• Find out how much each customer spent on each type of item by state.
• Count the number of orders for each customer.
• Restrict the results to repeat customers.
• Find the average amount that repeat customers spend for each state anditem type.

Limitations

• You can't include adifferential privacy clause in aSELECTstatement following a pipe operator. Instead, use a differential privacyclause in standard syntax and apply pipe operators following the query.

What's next

• Analyze data using pipe syntax
• Pipe query syntax reference
• Standard query syntax reference
• VLDB 2024 conference paper on pipe syntax