Sintaxe de pipe

Visualização

Esse recurso está sujeito aos "Termos de ofertas pré-GA" na seção "Termos de Serviço gerais" dos Termos específicos de serviço. Os recursos pré-GA estão disponíveis "no estado em que se encontram" e podem ter suporte limitado. Para mais informações, consulte as descrições da fase de lançamento.

Observação: para pedir feedback ou suporte sobre esse recurso, envie um e-mail para [e-mail].

A sintaxe de pipe é uma extensão do GoogleSQL que oferece suporte a uma estrutura de consulta linear projetada para facilitar a leitura, a gravação e a manutenção das consultas.

Para inscrever um projeto na prévia da sintaxe de pipe, preencha o formulário de inscrição na sintaxe de pipe do BigQuery.

Visão geral

É possível usar a sintaxe de pipe em qualquer lugar em que você escreve o GoogleSQL. A sintaxe de pipe oferece suporte às mesmas operações da sintaxe do GoogleSQL, ou sintaxe padrão, por exemplo, seleção, agregação e agrupamento, mesclagem e filtragem, mas as operações podem ser aplicadas em qualquer ordem e quantas vezes forem necessárias. A estrutura linear da sintaxe de pipe permite que você escreva consultas para que a ordem da sintaxe da consulta corresponda à ordem das etapas lógicas realizadas para criar a tabela de resultados.

As consultas que usam a sintaxe de pipe são precificadas, executadas e otimizadas da mesma forma que as consultas de sintaxe padrão equivalentes. Ao escrever consultas com a sintaxe de pipe, siga as diretrizes para estimar custos e otimizar a computação de consultas.

A sintaxe padrão tem problemas que podem dificultar a leitura, a escrita e a manutenção. A tabela a seguir mostra como a sintaxe de pipe resolve esses problemas:

Sintaxe padrão	Sintaxe de pipe
As cláusulas precisam aparecer em uma ordem específica.	Os operadores de pipe podem ser aplicados em qualquer ordem.
Consultas mais complexas, como consultas com agregação de vários níveis, geralmente exigem CTEs ou subconsultas aninhadas.	As consultas mais complexas geralmente são expressas adicionando operadores de pipe ao final da consulta.
Durante a agregação, as colunas são repetidas nas cláusulas SELECT, GROUP BY e ORDER BY.	As colunas só podem ser listadas uma vez por agregação.

Sintaxe básica

Na sintaxe de pipe, as consultas começam com uma consulta SQL padrão ou uma cláusula FROM. Por exemplo, uma cláusula FROM autônoma, como FROM mydataset.mytable, é uma sintaxe de pipe válida. O resultado da consulta SQL padrão ou da tabela da cláusula FROM pode ser transmitido como entrada para um símbolo de pipe, |>, seguido por um nome de operador de pipe e quaisquer argumentos para esse operador. O operador de barra vertical transforma a tabela de alguma forma, e o resultado dessa transformação pode ser transmitido a outro operador de barra vertical.

Você pode usar qualquer número de operadores de pipe na consulta para fazer coisas como selecionar, ordenar, filtrar, mesclar ou agregar colunas. Os nomes dos operadores de pipe correspondem às respectivas contrapartes de sintaxe padrão e geralmente têm o mesmo comportamento. A principal diferença entre a sintaxe padrão e a sintaxe de pipe é a maneira como você estrutura a consulta. À medida que a lógica expressa pela consulta se torna mais complexa, ela ainda pode ser expressa como uma sequência linear de operadores de pipe, sem usar subconsultas profundamente aninhadas, facilitando a leitura e a compreensão.

Considere a tabela a seguir:

CREATE TABLE mydataset.produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

As consultas a seguir contêm uma sintaxe de pipe válida que mostra como criar uma consulta sequencialmente.

As consultas podem começar com uma cláusula FROM e não precisam conter um símbolo de pipe:

-- View the table
FROM mydataset.produce;

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | carrots | 0     | vegetable |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

É possível filtrar com um operador de pipe WHERE:

-- Filter items with no sales
FROM mydataset.produce
|> WHERE sales > 0;

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

Para realizar a agregação, use o operador de pipe AGGREGATE, seguido por qualquer número de funções de agregação, seguido por uma cláusula GROUP BY. A cláusula GROUP BY faz parte do operador de barra vertical AGGREGATE e não é separada por uma barra vertical (|>).

-- Compute total sales by item
FROM mydataset.produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item;

/*---------+-------------+-----------+
 | item    | total_sales | num_sales |
 +---------+-------------+-----------+
 | apples  | 9           | 2         |
 | bananas | 15          | 1         |
 +---------+-------------+-----------*/

Agora suponha que você tenha a tabela a seguir, que contém um ID para cada item:

CREATE TABLE mydataset.item_data AS (
  SELECT "apples" AS item, "123" AS id
  UNION ALL
  SELECT "bananas" AS item, "456" AS id
  UNION ALL
  SELECT "carrots" AS item, "789" AS id
);

Use o operador de pipe JOIN para agrupar os resultados da consulta anterior com essa tabela e incluir o ID de cada item:

FROM mydataset.produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item
|> JOIN mydataset.item_data USING(item);

/*---------+-------------+-----------+-----+
 | item    | total_sales | num_sales | id  |
 +---------+-------------+-----------+-----+
 | apples  | 9           | 2         | 123 |
 | bananas | 15          | 1         | 456 |
 +---------+-------------+-----------+-----*/

A sintaxe de pipe tem as seguintes características principais:

• Os operadores de pipe podem ser aplicados em qualquer ordem e quantas vezes você quiser.
• A sintaxe de pipe funciona em qualquer lugar em que a sintaxe padrão é aceita: consultas, visualizações, funções com valor de tabela e outros contextos.
• A sintaxe de pipe pode ser misturada com a sintaxe padrão na mesma consulta. Por exemplo, as subconsultas podem usar uma sintaxe diferente da consulta mãe.
• Um operador de pipe pode ver todos os alias que existem na tabela antes do pipe.

Principais diferenças em relação à sintaxe padrão

A sintaxe de pipe difere da sintaxe padrão das seguintes maneiras:

• As consultas podem começar com uma cláusula FROM.
• O operador de pipe SELECT não realiza agregação. Use o operador de pipe AGGREGATE.
• A filtragem é sempre feita com o operador de pipe WHERE, que pode ser aplicado em qualquer lugar. O operador de pipe WHERE, que substitui HAVING e QUALIFY, pode filtrar os resultados de funções de agregação ou de janela.

Para mais informações e uma lista completa de operadores de pipe, consulte Sintaxe de consulta de pipe.

Casos de uso

Os casos de uso comuns para a sintaxe de pipe incluem:

• Análise ad hoc e criação de consultas incrementais: a ordem lógica das operações facilita a criação e a depuração de consultas. O prefixo de qualquer consulta até um símbolo de pipe |> é uma consulta válida, que ajuda a visualizar resultados intermediários em uma consulta longa. Os ganhos de produtividade podem acelerar o processo de desenvolvimento em toda a organização.
• Análise de registros: existem outros tipos de sintaxe semelhante a pipe que são populares entre os usuários de análise de registros. A sintaxe de pipe fornece uma estrutura familiar que simplifica a integração desses usuários ao Log Analytics e ao BigQuery.

Outros recursos na sintaxe de pipe

Com poucas exceções, a sintaxe de pipe aceita todos os operadores que a sintaxe padrão aceita com a mesma sintaxe. Além disso, a sintaxe de pipe apresenta os operadores de pipe a seguir.

Operador de pipe EXTEND

O operador de canalização EXTEND, que só pode ser usado imediatamente após um símbolo de canalização, permite anexar colunas computadas à tabela atual. O operador de pipe EXTEND é semelhante à instrução SELECT *, new_column, mas oferece mais flexibilidade na referência de aliases de coluna.

Considere a tabela a seguir, que contém duas notas de cada pessoa:

CREATE TABLE mydataset.scores AS (
  SELECT 'Alex' AS student, 9 AS score1, 10 AS score2, 10 AS points_possible
  UNION ALL
  SELECT 'Dana' AS student, 5 AS score1, 7 AS score2, 10 AS points_possible);

/*---------+--------+--------+-----------------+
 | student | score1 | score2 | points_possible |
 +---------+--------+--------+-----------------+
 | Alex    | 9      | 10     | 10              |
 | Dana    | 5      | 7      | 10              |
 +---------+--------+--------+-----------------*/

Suponha que você queira calcular a pontuação média bruta e a pontuação média percentual que cada aluno recebeu no teste. Na sintaxe padrão, as colunas posteriores em uma instrução SELECT não têm visibilidade para os aliases anteriores. Para evitar uma subconsulta, repita a expressão para a média:

SELECT student,
  (score1 + score2) / 2 AS average_score,
  (score1 + score2) / 2 / points_possible AS average_percent
FROM mydataset.scores;

O operador de pipe EXTEND pode se referir a aliases usados anteriormente, facilitando a leitura da consulta e reduzindo a probabilidade de erros:

FROM mydataset.scores
|> EXTEND (score1 + score2) / 2 AS average_score
|> EXTEND average_score / points_possible AS average_percent
|> SELECT student, average_score, average_percent;

/*---------+---------------+-----------------+
 | student | average_score | average_percent |
 +---------+---------------+-----------------+
 | Alex    | 9.5           | .95             |
 | Dana    | 6.0           | 0.6             |
 +---------+---------------+-----------------*/

Operador de pipe SET

O operador de barra vertical SET, que só pode ser usado imediatamente após um símbolo de barra vertical, permite substituir o valor das colunas na tabela atual. O operador de pipe SET é semelhante à instrução SELECT * REPLACE (expression AS column). É possível fazer referência ao valor original qualificando o nome da coluna com um alias de tabela.

FROM (SELECT 3 AS x, 5 AS y)
|> SET x = 2 * x;

/*---+---+
 | x | y |
 +---+---+
 | 6 | 5 |
 +---+---*/

Operador de pipe DROP

O operador de canalização DROP, que só pode ser usado imediatamente após um símbolo de canalização, permite remover colunas da tabela atual. O operador de pipe DROP é semelhante à instrução SELECT * EXCEPT(column). Depois que uma coluna é excluída, ainda é possível referenciar o valor original qualificando o nome da coluna com um alias de tabela.

FROM (SELECT 1 AS x, 2 AS y) AS t
|> DROP x;

/*---+
 | y |
 +---+
 | 2 |
 +---*/

Operador de pipe RENAME

O operador de canalização RENAME, que só pode ser usado imediatamente após um símbolo de canalização, permite renomear colunas da tabela atual. O operador de pipe RENAME é semelhante à instrução SELECT * EXCEPT(old_column), old_column AS new_column.

FROM (SELECT 1 AS x, 2 AS y, 3 AS z) AS t
|> RENAME y AS w;

/*---+---+---+
 | x | w | z |
 +---+---+---+
 | 1 | 2 | 3 |
 +---+---+---*/

Operador de pipe AGGREGATE

Para realizar a agregação na sintaxe de pipe, use o operador de pipe AGGREGATE, seguido por qualquer número de funções de agregação, seguido por uma cláusula GROUP BY. Não é necessário repetir colunas em uma cláusula SELECT.

Os exemplos nesta seção usam a tabela produce:

CREATE TABLE mydataset.produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | carrots | 0     | vegetable |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

FROM mydataset.produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY item, category;

/*---------+-----------+-------+-------------+
 | item    | category  | total | num_records |
 +---------+-----------+-------+-------------+
 | apples  | fruit     | 9     | 2           |
 | carrots | vegetable | 0     | 1           |
 | bananas | fruit     | 15    | 1           |
 +---------+-----------+-------+-------------*/

Se você quiser ordenar os resultados imediatamente após a agregação, marque as colunas na cláusula GROUP BY que você quer ordenar com ASC ou DESC. As colunas não marcadas não são ordenadas.

Se você quiser ordenar todas as colunas, substitua a cláusula GROUP BY por uma cláusula GROUP AND ORDER BY, que ordena cada coluna em ordem crescente por padrão. É possível especificar DESC seguindo as colunas que você quer ordenar em ordem decrescente. Por exemplo, as três consultas a seguir são equivalentes:

-- Use a separate ORDER BY clause
FROM mydataset.produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY category, item
|> ORDER BY category DESC, item;

-- Explicitly mark how to order columns in the GROUP BY clause
FROM mydataset.produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY category DESC, item ASC;

-- Only mark descending columns in the GROUP AND ORDER BY clause
FROM mydataset.produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP AND ORDER BY category DESC, item;

A vantagem de usar uma cláusula GROUP AND ORDER BY é que você não precisa repetir os nomes das colunas em dois lugares.

Para realizar a agregação completa da tabela, use GROUP BY() ou omita a cláusula GROUP BY inteiramente:

FROM mydataset.produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records;

/*-------+-------------+
 | total | num_records |
 +-------+-------------+
 | 24    | 4           |
 +-------+-------------*/

Operador de pipe JOIN

O operador de pipe JOIN permite mesclar a tabela atual com outra e oferece suporte às operações de mesclagem padrão, incluindo CROSS, INNER, LEFT, RIGHT e FULL.

Os exemplos a seguir fazem referência às tabelas produce e item_data:

CREATE TABLE mydataset.produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

CREATE TABLE mydataset.item_data AS (
  SELECT "apples" AS item, "123" AS id
  UNION ALL
  SELECT "bananas" AS item, "456" AS id
  UNION ALL
  SELECT "carrots" AS item, "789" AS id
);

O exemplo a seguir usa uma cláusula USING e evita a ambiguidade de colunas:

FROM `mydataset.produce`
|> JOIN `mydataset.item_data` USING(item)
|> WHERE item = "apples";

/*--------+-------+----------+-----+
 | item   | sales | category | id  |
 +--------+-------+----------+-----+
 | apples | 2     | fruit    | 123 |
 | apples | 7     | fruit    | 123 |
 +--------+-------+----------+-----*/

Para fazer referência a colunas na tabela atual, como para eliminar a ambiguidade em uma cláusula ON, é necessário definir um alias para a tabela atual usando o operador de pipe AS. Você pode usar um alias para a tabela combinada. É possível fazer referência aos dois aliases seguindo operadores de pipe posteriores:

FROM `mydataset.produce`
|> AS produce_table
|> JOIN `mydataset.item_data` AS item_table
   ON produce_table.item = item_table.item
|> WHERE produce_table.item = "bananas"
|> SELECT item_table.item, sales, id;

/*---------+-------+-----+
 | item    | sales | id  |
 +---------+-------+-----+
 | bananas | 15    | 123 |
 +---------+-------+-----*/

O lado direito da mesclagem não tem visibilidade para o lado esquerdo da mesclagem, o que significa que você não pode mesclar a tabela atual com ela mesma. Por exemplo, a consulta a seguir falha:

-- This query doesn't work.
FROM `mydataset.produce`
|> AS produce_table
|> JOIN produce_table AS produce_table_2 USING(item);

Para realizar uma autofusão com uma tabela modificada, use uma expressão de tabela comum dentro de uma cláusula WITH.

WITH cte_table AS (
  FROM `mydataset.produce`
  |> WHERE item = "carrots"
)
FROM cte_table
|> JOIN cte_table AS cte_table_2 USING(item);

Exemplo

Considere a tabela a seguir com informações sobre os pedidos do cliente:

CREATE TABLE mydataset.customer_orders AS (
  SELECT 1 AS customer_id, 100 AS order_id, "WA" AS state, 5 AS cost, "clothing" AS item_type
  UNION ALL
  SELECT 1 AS customer_id, 101 AS order_id, "WA" AS state, 20 AS cost, "clothing" AS item_type
  UNION ALL
  SELECT 1 AS customer_id, 102 AS order_id, "WA" AS state, 3 AS cost, "food" AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 103 AS order_id, "NY" AS state, 16 AS cost, "clothing" AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 104 AS order_id, "NY" AS state, 22 AS cost, "housewares" AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 104 AS order_id, "WA" AS state, 45 AS cost, "clothing" AS item_type
  UNION ALL
  SELECT 3 AS customer_id, 105 AS order_id, "MI" AS state, 29 AS cost, "clothing" AS item_type);

Suponha que você queira saber, para cada estado e tipo de item, o valor médio gasto pelos clientes recorrentes. Você pode escrever a consulta da seguinte maneira:

SELECT state, item_type, AVG(total_cost) AS average
FROM
  (
    SELECT
      SUM(cost) AS total_cost,
      customer_id,
      state,
      item_type,
      COUNT(*) OVER (PARTITION BY customer_id) AS num_orders
    FROM mydataset.customer_orders
    GROUP BY customer_id, state, item_type
    QUALIFY num_orders > 1
  )
GROUP BY state, item_type
ORDER BY state DESC, item_type ASC;

Se você ler a consulta de cima para baixo, vai encontrar a coluna total_cost antes que ela seja definida. Mesmo dentro da subconsulta, você lê os nomes das colunas antes de saber de qual tabela elas vêm.

Para entender essa consulta, ela precisa ser lida de dentro para fora. As colunas state e item_type são repetidas várias vezes nas cláusulas SELECT e GROUP BY, e depois na cláusula ORDER BY.

A consulta equivalente a seguir é gravada usando a sintaxe de pipe:

FROM mydataset.customer_orders
|> AGGREGATE SUM(cost) AS total_cost, GROUP BY customer_id, state, item_type
|> EXTEND COUNT(*) OVER (PARTITION BY customer_id) AS num_orders
|> WHERE num_orders > 1
|> AGGREGATE AVG(total_cost) AS average GROUP BY state DESC, item_type ASC;

/*-------+------------+---------+
 | state | item_type  | average |
 +-------+------------+---------+
 | WA    | clothing   | 35.0    |
 | WA    | food       | 3.0     |
 | NY    | clothing   | 16.0    |
 | NY    | housewares | 22.0    |
 +-------+------------+---------*/

Com a sintaxe de pipe, você pode escrever a consulta para seguir as etapas lógicas que você pensa para resolver o problema original. As linhas de sintaxe na consulta correspondem às seguintes etapas lógicas:

• Comece com a tabela de pedidos do cliente.
• Descubra quanto cada cliente gastou em cada tipo de item por estado.
• Conte o número de pedidos de cada cliente.
• Restringir os resultados a clientes recorrentes.
• Encontre o valor médio gasto pelos clientes recorrentes em cada estado e tipo de item.

Limitações

• Não é possível incluir uma cláusula de privacidade diferencial em uma instrução SELECT após um operador de pipe. Em vez disso, use uma cláusula de privacidade diferencial na sintaxe padrão e aplique operadores de pipe após a consulta.
• Não é possível usar uma janela nomeada na sintaxe de pipe.

A seguir

• Saiba mais sobre a sintaxe de consulta de pipe.
• Saiba mais sobre a sintaxe de consulta padrão.