Utiliser la syntaxe de requête avec pipe

La syntaxe des requêtes avec des pipes est une extension de GoogleSQL qui accepte une structure de requête linéaire conçue pour rendre vos requêtes plus faciles à lire, à écrire et à gérer. Vous pouvez utiliser la syntaxe de canalisation partout où vous écrivez du code GoogleSQL.

La syntaxe de canalisation est compatible avec les mêmes opérations que la syntaxe de requête GoogleSQL ou la syntaxe standard existantes (par exemple, la sélection, l'agrégation et le regroupement, la jointure et le 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 de canalisation vous permet d'écrire des requêtes de sorte que l'ordre de la syntaxe de requête corresponde à l'ordre des étapes logiques suivies pour créer la table de résultats.

Les requêtes qui utilisent la syntaxe de canalisation 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 de canalisation, 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 la lecture, l'écriture et la maintenance difficiles. Le tableau suivant montre comment la syntaxe avec barre verticale résout ces problèmes :

Syntaxe standard Syntaxe pipe
Les clauses doivent apparaître dans un ordre précis. Les opérateurs de canalisation 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 clauses SELECT, GROUP BY et ORDER BY. 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 du canal, consultez Analyser les données à l'aide de la syntaxe du canal. Pour en savoir plus sur la syntaxe, consultez la documentation de référence sur la syntaxe des requêtes avec des pipes.

Syntaxe de base

Dans la syntaxe de canalisation, 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 de canalisation valide. Le résultat de la requête SQL standard ou de la table de la clause FROM peut ensuite être transmis en entrée à un symbole pipe, |>, suivi d'un nom d'opérateur pipe et de tous les arguments de cet opérateur. L'opérateur de barre verticale transforme la table d'une certaine manière, et le résultat de cette transformation peut être transmis à un autre opérateur de barre verticale.

Vous pouvez utiliser autant d'opérateurs pipe que vous le souhaitez dans votre requête pour effectuer des actions telles que sélectionner, trier, filtrer, joindre ou agréger des colonnes. Les noms des opérateurs de canalisation 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 avec des pipes réside dans la façon dont vous structurez votre requête. À mesure que la logique exprimée par votre requête se complexifie, la requête peut toujours être exprimée sous la forme d'une séquence linéaire d'opérateurs de canalisation, sans utiliser de sous-requêtes profondément imbriquées, ce qui la rend plus facile à lire et à comprendre.

La syntaxe de canalisation 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 d'arguments :
    |> operator_name argument_list
  • Vous pouvez ajouter des opérateurs pipe à la fin de n'importe quelle requête valide.
  • Les opérateurs de canalisations peuvent être appliqués dans n'importe quel ordre et autant de fois que nécessaire.
  • La syntaxe pipe fonctionne partout où la syntaxe standard est acceptée : dans les requêtes, les vues, les fonctions table et d'autres contextes.
  • Vous pouvez combiner la syntaxe pipe et 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 de canalisation peut voir tous les alias qui existent dans la table précédant la canalisation.
  • Une requête peut commencer par une clause FROM, et des opérateurs de canalisation 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 de canalisation valide qui montre comment créer une requête de manière séquentielle.

Les requêtes peuvent commencer par une clause FROM et ne doivent pas contenir de symbole | :

-- 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 opérateur 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     |
 +---------+-------+-----------*/

Pour effectuer une agrégation, utilisez l'opérateur pipe AGGREGATE, 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 de barre verticale AGGREGATE et n'est pas séparée par un symbole de barre verticale (|>).

-- 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 du tableau suivant, qui contient 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 de canalisation 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 clause FROM.
  • L'opérateur de canalisation SELECT n'effectue pas d'agrégation. Vous devez utiliser l'opérateur pipe AGGREGATE à la place.
  • Le filtrage s'effectue toujours avec l'opérateur pipe WHERE, qui peut être appliqué n'importe où. L'opérateur de canalisation 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 de canalisation.

Cas d'utilisation

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

  • Analyse ad hoc et création incrémentielle de requêtes : 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 | |> 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 toute votre organisation.
  • Analyse des journaux : il existe d'autres types de syntaxe de type canal qui sont populaires auprès des utilisateurs de l'analyse des journaux. La syntaxe des pipes offre une structure familière qui simplifie l'intégration de ces utilisateurs à Log Analytics et BigQuery.

Fonctionnalités supplémentaires dans la syntaxe pipe

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

Opérateur pipe EXTEND

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

Prenons l'exemple de la table suivante, qui contient deux notes 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 que chaque élève a obtenu 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 de canalisation 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 dans le tableau actuel. L'opérateur pipe SET est semblable à l'instruction SELECT * REPLACE (expression AS column). Vous pouvez faire référence à 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 de canal 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 faire référence à 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 de canalisation 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 de canalisation, utilisez l'opérateur de canalisation AGGREGATE, 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 souhaitez 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 clause GROUP BY par une clause GROUP AND ORDER BY, qui trie toutes les colonnes 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 entièrement la clause 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 de canalisation JOIN vous permet de joindre la table actuelle à une autre table. Il 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é concernant les colonnes :

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

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

Pour faire référence à des colonnes de 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 faire référence aux deux alias en suivant 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 du tableau suivant, qui contient des informations sur les commandes des clients :

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 de nombreuses 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 de canalisation :

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

La syntaxe de canalisation vous permet d'écrire la requête en suivant les étapes logiques que vous pourriez suivre pour résoudre le problème initial. Les lignes de syntaxe de la requête correspondent aux étapes logiques suivantes :

  • Commencez par le tableau des commandes client.
  • Découvrez combien chaque client a dépensé pour chaque type d'article par État.
  • Comptez le nombre de commandes pour chaque client.
  • Limitez les résultats aux clients réguliers.
  • Trouvez le montant moyen dépensé par les clients connus pour chaque État et chaque type d'article.

Limites

  • Vous ne pouvez pas inclure de clause de confidentialité différentielle dans une instruction SELECT 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.
  • Vous ne pouvez pas utiliser de fenêtre nommée dans la syntaxe de canalisation.

Étapes suivantes