Utiliser la syntaxe des requêtes pipe

La syntaxe de requête pipe est une extension de GoogleSQL qui prend en charge une structure de requête linéaire conçue pour faciliter la lecture, l'écriture et la maintenance de vos requêtes. Vous pouvez utiliser la syntaxe pipe partout où vous écrivez du code GoogleSQL.

La syntaxe pipe est compatible avec les mêmes opérations que la syntaxe de requête GoogleSQL existante, ou syntaxe standard (par exemple, sélection, agrégation et regroupement, jointure et filtrage), mais les opérations peuvent être appliquées dans n'importe quel ordre et n'importe quel nombre de fois. La structure linéaire de la syntaxe pipe vous permet d'écrire des requêtes de sorte que l'ordre de la syntaxe de requête corresponde à l'ordre des étapes logiques effectuées pour créer la table de résultats.

Les requêtes qui utilisent la syntaxe pipe sont tarifées, exécutées et optimisées de la même manière que leurs requêtes de syntaxe standard équivalentes. Lorsque vous écrivez des requêtes avec la syntaxe pipe, suivez les consignes pour estimer les coûts et optimiser le calcul des requêtes.

La syntaxe standard présente des problèmes qui peuvent rendre sa lecture, son écriture et sa maintenance difficiles. Le tableau suivant montre comment la syntaxe pipe résout ces problèmes :

Syntaxe standard	Syntaxe pipe
Les clauses doivent apparaître dans un ordre particulier.	Les opérateurs pipe peuvent être appliqués dans n'importe quel ordre.
Les requêtes plus complexes, telles que celles avec une agrégation à plusieurs niveaux, nécessitent généralement des CTE ou des sous-requêtes imbriquées.	Les requêtes plus complexes sont généralement exprimées en ajoutant des opérateurs pipe à la fin de la requête.
Lors de l'agrégation, les colonnes sont répétées dans les SELECT, GROUP BY, et ORDER BY clauses.	Les colonnes ne peuvent être listées qu'une seule fois par agrégation.

Pour créer une requête complexe étape par étape dans la syntaxe pipe, consultez Analyser des données à l'aide de la syntaxe pipe. Pour obtenir des informations complètes sur la syntaxe, consultez la documentation de référence sur la syntaxe de requête pipe.

Syntaxe de base

Dans la syntaxe pipe, les requêtes commencent par une requête SQL standard ou une clause FROM. Par exemple, une clause FROM autonome, telle que FROM MyTable, est une syntaxe pipe valide. Le résultat de la requête SQL standard ou de la table de la FROM clause peut ensuite être transmis en entrée à un symbole pipe, |>, suivi d'un pipe nom d'opérateur et de tous les arguments de cet opérateur. L'opérateur pipe transforme la table d'une manière ou d'une autre, et le résultat de cette transformation peut être transmis à un autre opérateur pipe.

Vous pouvez utiliser n'importe quel nombre d'opérateurs pipe dans votre requête pour effectuer des opérations telles que la sélection, le tri, le filtrage, la jointure ou l'agrégation de colonnes. Les noms des opérateurs pipe correspondent à leurs équivalents de syntaxe standard et ont généralement le même comportement. La principale différence entre la syntaxe standard et la syntaxe pipe réside dans la façon dont vous structurez votre requête. À mesure que la logique exprimée par votre requête devient plus complexe, la requête peut toujours être exprimée sous la forme d'une séquence linéaire d'opérateurs pipe, sans utiliser de sous-requêtes profondément imbriquées, ce qui la rend plus facile à lire et à comprendre.

La syntaxe pipe présente les caractéristiques clés suivantes :

• Chaque opérateur pipe de la syntaxe pipe se compose du symbole pipe, |>, d'un nom d'opérateur et de tous les arguments :
  |> operator_name argument_list
• Les opérateurs pipe peuvent être ajoutés à la fin de n'importe quelle requête valide.
• Les opérateurs pipe peuvent être appliqués dans n'importe quel ordre et n'importe quel nombre de fois.
• La syntaxe pipe fonctionne partout où la syntaxe standard est acceptée : dans les requêtes, les vues, les fonctions de table et d'autres contextes.
• La syntaxe pipe peut être combinée à la syntaxe standard dans la même requête. Par exemple, les sous-requêtes peuvent utiliser une syntaxe différente de celle de la requête parente.
• Un opérateur pipe peut voir tous les alias qui existent dans la table précédant le pipe.
• Une requête peut commencer par une clause FROM, et des opérateurs pipe peuvent éventuellement être ajoutés après la clause FROM.

Prenons l'exemple de table suivant :

CREATE OR REPLACE 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);

Les requêtes suivantes contiennent chacune une syntaxe pipe valide qui montre comment créer une requête de manière séquentielle.

Les requêtes peuvent commencer par une FROM clause et ne doivent pas nécessairement contenir de symbole pipe :

-- View the table.
FROM mydataset.Produce;

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

Vous pouvez filtrer avec un WHERE opérateur pipe :

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

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

Pour effectuer une agrégation, utilisez l'AGGREGATEopérateur pipe, suivi d'un nombre quelconque de fonctions d'agrégation, puis d'une clause GROUP BY. La clause GROUP BY fait partie de l'opérateur pipe AGGREGATE et n'est pas séparée par un symbole pipe (|>).

-- 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         |
 +---------+-------------+-----------*/

Supposons maintenant que vous disposiez de la table suivante contenant un ID pour chaque élément :

CREATE OR REPLACE TABLE mydataset.ItemData 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
);

Vous pouvez utiliser l'opérateur pipe JOIN pour joindre les résultats de la requête précédente à cette table afin d'inclure l'ID de chaque élément :

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

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

Principales différences par rapport à la syntaxe standard

La syntaxe pipe diffère de la syntaxe standard sur les points suivants :

• Les requêtes peuvent commencer par une FROM clause.
• L'opérateur pipe SELECT n'effectue pas d'agrégation. Vous devez utiliser l' AGGREGATE opérateur pipe à la place.
• Le filtrage est toujours effectué avec l'WHEREopérateur pipe, qui peut être appliqué n'importe où. L'opérateur pipe WHERE, qui remplace HAVING et QUALIFY, peut filtrer les résultats des fonctions d'agrégation ou de fenêtrage.

Pour en savoir plus, consultez la liste complète des opérateurs pipe.

Cas d'utilisation

Voici quelques cas d'utilisation courants de la syntaxe pipe :

• Analyse ad hoc et création de requêtes incrémentales: l'ordre logique des opérations facilite l'écriture et le débogage des requêtes. Le préfixe de toute requête jusqu'à un symbole pipe |> est une requête valide, ce qui vous permet d'afficher les résultats intermédiaires dans une longue requête. Les gains de productivité peuvent accélérer le processus de développement dans votre organisation.
• Analyse des journaux : il existe d'autres types de syntaxe de type pipe qui sont populaires auprès des utilisateurs d'analyse des journaux. La syntaxe pipe fournit une structure familière qui simplifie l'intégration de ces utilisateurs à Observability Analytics et BigQuery.

Fonctionnalités supplémentaires de la syntaxe pipe

À quelques exceptions près, la syntaxe pipe est compatible avec tous les opérateurs de la syntaxe standard avec la même syntaxe. De plus, la syntaxe pipe introduit des opérateurs pipe supplémentaires et utilise une syntaxe modifiée pour les agrégations et les jointures. Les sections suivantes présentent certains de ces opérateurs. Pour obtenir la liste de tous les opérateurs compatibles, consultez la liste complète des opérateurs pipe.

Opérateur pipe EXTEND

L'opérateur pipe EXTEND vous permet d'ajouter des colonnes calculées à la table actuelle. L'opérateur pipe EXTEND est semblable à l'instruction SELECT *, new_column, mais il vous offre plus de flexibilité pour référencer les alias de colonne.

Prenons l'exemple de table suivant contenant deux résultats de test pour chaque personne :

CREATE OR REPLACE 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              |
 +---------+--------+--------+-----------------*/

Supposons que vous souhaitiez calculer le score brut moyen et le score moyen en pourcentage obtenus par chaque élève au test. Dans la syntaxe standard, les colonnes ultérieures d'une instruction SELECT n'ont pas de visibilité sur les alias précédents. Pour éviter une sous-requête, vous devez répéter l'expression de la moyenne :

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

L'opérateur pipe EXTEND peut référencer des alias précédemment utilisés, ce qui rend la requête plus facile à lire et moins sujette aux erreurs :

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             |
 +---------+---------------+-----------------*/

Opérateur pipe SET

L'opérateur pipe SET vous permet de remplacer la valeur des colonnes de la table actuelle. L'opérateur pipe SET est semblable à l'instruction SELECT * REPLACE (expression AS column). Vous pouvez référencer la valeur d'origine en qualifiant le nom de la colonne avec un alias de table.

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

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

Opérateur pipe DROP

L'opérateur pipe DROP vous permet de supprimer des colonnes de la table actuelle. L'opérateur pipe DROP est semblable à l'instruction SELECT * EXCEPT(column). Une fois qu'une colonne est supprimée, vous pouvez toujours référencer la valeur d'origine en qualifiant le nom de la colonne avec un alias de table.

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

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

Opérateur pipe RENAME

L'opérateur pipe RENAME vous permet de renommer les colonnes de la table actuelle. L'opérateur pipe RENAME est semblable à l'instruction 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 |
 +---+---+---*/

Opérateur pipe AGGREGATE

Pour effectuer une agrégation dans la syntaxe pipe, utilisez l'AGGREGATEopérateur pipe, suivi d'un nombre quelconque de fonctions d'agrégation, puis d'une clause GROUP BY. Vous n'avez pas besoin de répéter les colonnes dans une clause SELECT.

Les exemples de cette section utilisent la table Produce :

CREATE OR REPLACE 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           |
 +---------+-----------+-------+-------------*/

Si vous êtes prêt à trier vos résultats immédiatement après l'agrégation, vous pouvez marquer les colonnes de la clause GROUP BY que vous souhaitez trier avec ASC ou DESC. Les colonnes non marquées ne sont pas triées.

Si vous souhaitez trier toutes les colonnes, vous pouvez remplacer la GROUP BY clause par une GROUP AND ORDER BY clause, qui trie chaque colonne par ordre croissant par défaut. Vous pouvez spécifier DESC après les colonnes que vous souhaitez trier par ordre décroissant. Par exemple, les trois requêtes suivantes sont équivalentes :

-- 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;

L'avantage d'utiliser une clause GROUP AND ORDER BY est que vous n'avez pas à répéter les noms de colonnes à deux endroits.

Pour effectuer une agrégation de table complète, utilisez GROUP BY() ou omettez complètement la GROUP BY :

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

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

Opérateur pipe JOIN

L'opérateur pipe JOIN vous permet de joindre la table actuelle à une autre table et est compatible avec les opérations de jointure standards, y compris CROSS, INNER, LEFT, RIGHT et FULL.

Les exemples suivants font référence aux tables Produce et ItemData :

CREATE OR REPLACE 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 OR REPLACE TABLE mydataset.ItemData 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
);

L'exemple suivant utilise une clause USING et évite toute ambiguïté de colonne :

FROM mydataset.Produce
|> JOIN mydataset.ItemData USING(item)
|> WHERE item = 'apples';

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

Pour référencer des colonnes dans la table actuelle, par exemple pour lever toute ambiguïté dans une clause ON, vous devez créer un alias pour la table actuelle à l'aide de l'opérateur pipe AS. Vous pouvez éventuellement créer un alias pour la table jointe. Vous pouvez référencer les deux alias après les opérateurs pipe suivants :

FROM mydataset.Produce
|> AS produce_table
|> JOIN mydataset.ItemData 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 |
 +---------+-------+-----*/

Le côté droit de la jointure n'a pas de visibilité sur le côté gauche de la jointure, ce qui signifie que vous ne pouvez pas joindre la table actuelle à elle-même. Par exemple, la requête suivante échoue :

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

Pour effectuer une auto-jointure avec une table modifiée, vous pouvez utiliser une expression de table courante (CTE) dans une clause WITH.

WITH cte_table AS (
  FROM mydataset.Produce
  |> WHERE item = 'carrots'
)
FROM cte_table
|> JOIN cte_table AS cte_table_2 USING(item);

Exemple

Prenons l'exemple de table suivant contenant des informations sur les commandes client :

CREATE OR REPLACE TABLE mydataset.CustomerOrders 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);

Supposons que vous souhaitiez connaître, pour chaque État et chaque type d'article, le montant moyen dépensé par les clients réguliers. Vous pouvez écrire la requête de la manière suivante :

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.CustomerOrders
    GROUP BY customer_id, state, item_type
    QUALIFY num_orders > 1
  )
GROUP BY state, item_type
ORDER BY state DESC, item_type ASC;

Si vous lisez la requête de haut en bas, vous rencontrez la colonne total_cost avant qu'elle n'ait été définie. Même dans la sous-requête, vous lisez les noms des colonnes avant de voir de quelle table elles proviennent.

Pour comprendre cette requête, vous devez la lire de l'intérieur vers l'extérieur. Les colonnes state et item_type sont répétées plusieurs fois dans les clauses SELECT et GROUP BY, puis à nouveau dans la clause ORDER BY.

La requête équivalente suivante est écrite à l'aide de la syntaxe pipe :

FROM mydataset.CustomerOrders
|> 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    |
 +-------+------------+---------*/

Avec la syntaxe pipe, vous pouvez écrire la requête pour suivre les étapes logiques que vous pourriez envisager pour résoudre le problème d'origine. Les lignes de syntaxe de la requête correspondent aux étapes logiques suivantes :

• Commencez par la table des commandes client.
• Déterminez le montant dépensé par chaque client pour chaque type d'article par État.
• Comptez le nombre de commandes pour chaque client.
• Limitez les résultats aux clients réguliers.
• Déterminez le montant moyen dépensé par les clients réguliers pour chaque État et chaque type d'article.

Limites

• Vous ne pouvez pas inclure de clause de confidentialité différentielle dans une SELECT instruction suivant un opérateur pipe. Utilisez plutôt une clause de confidentialité différentielle dans la syntaxe standard et appliquez des opérateurs pipe après la requête.

Étape suivante

• Analyser des données à l'aide de la syntaxe pipe
• Documentation de référence sur la syntaxe de requête pipe
• Documentation de référence sur la syntaxe de requête standard
• Article de la conférenceVLDB 2024 sur la syntaxe pipe