Syntaxe du pipe

Preview

Cette fonctionnalité est soumise aux "Conditions des offres de pré-DG" dans la section "Conditions d'utilisation générales" des Conditions spécifiques au service. Les fonctionnalités pré-DG sont disponibles "en l'état" et peuvent avoir une assistance limitée. Pour en savoir plus, consultez les descriptions des étapes de lancement.

Remarque :Pour demander des commentaires ou obtenir de l'aide concernant cette fonctionnalité, envoyez un e-mail à l'adresse [e-mail].

La syntaxe des pipes 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.

Pour inscrire un projet à la version preview de la syntaxe des pipes, remplissez le formulaire d'inscription à la syntaxe des pipes BigQuery.

Présentation

Vous pouvez utiliser la syntaxe des pipes partout où vous écrivez GoogleSQL. La syntaxe avec pipe accepte les mêmes opérations que la syntaxe 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 un nombre illimité de fois. La structure linéaire de la syntaxe des pipes vous permet d'écrire des requêtes de sorte que l'ordre de la syntaxe de la requête corresponde à l'ordre des étapes logiques suivies pour créer le tableau des résultats.

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

La syntaxe standard souffre de problèmes qui peuvent rendre la lecture, l'écriture et la maintenance difficiles. Le tableau suivant montre comment la syntaxe de pipe résout ces problèmes:

Syntaxe standard	Syntaxe du pipe
Les clauses doivent apparaître dans un ordre particulier.	Les opérateurs de pipe peuvent être appliqués dans n'importe quel ordre.
Les requêtes plus complexes, telles que les requêtes avec agrégation multiniveau, 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.

Syntaxe de base

Dans la syntaxe de pipe, les requêtes commencent par une requête SQL standard ou une clause FROM. Par exemple, une clause FROM autonome, telle que FROM mydataset.mytable, est une syntaxe de pipe 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 de pipe, |>, suivi d'un nom d'opérateur de pipe et de tous les arguments de cet opérateur. L'opérateur de 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 de pipe.

Vous pouvez utiliser autant d'opérateurs pipe que vous le souhaitez dans votre requête pour effectuer des opérations telles que sélectionner, trier, filtrer, joindre ou agréger des colonnes. Les noms des opérateurs de pipe correspondent à leurs homologues de syntaxe standard et ont généralement le même comportement. La principale différence entre la syntaxe standard et la syntaxe avec 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, elle peut toujours être exprimée sous la forme d'une séquence linéaire d'opérateurs de pipe, sans utiliser de sous-requêtes profondément imbriquées, ce qui la rend plus facile à lire et à comprendre.

Prenons l'exemple du tableau suivant:

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

Les requêtes suivantes contiennent chacune une syntaxe de pipe 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 de 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 à l'aide d'un opérateur 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     |
 +---------+-------+-----------*/

Pour effectuer une agrégation, utilisez l'opérateur de 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 pipe AGGREGATE et n'est pas séparée par un 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 que vous disposiez du tableau suivant, qui contient un ID pour chaque élément:

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

Vous pouvez utiliser l'opérateur de 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.item_data USING(item);

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

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

• Les opérateurs de pipe peuvent être appliqués dans n'importe quel ordre et autant de fois que nécessaire.
• La syntaxe des pipes fonctionne partout où la syntaxe standard est prise en charge: requêtes, vues, fonctions à valeurs de table et autres contextes.
• La syntaxe des pipes peut être mélangé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 de pipe peut voir tous les alias qui existent dans le tableau précédant le pipe.

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 pipe SELECT n'effectue pas d'agrégation. Vous devez utiliser l'opérateur pipe AGGREGATE à la place.
• Le filtrage est toujours effectué à l'aide de l'opérateur de pipe WHERE, qui peut être appliqué n'importe où. L'opérateur de 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 et obtenir la liste complète des opérateurs de pipe, consultez la page Syntaxe des requêtes avec pipe.

Cas d'utilisation

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

• Analyse ponctuelle 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 n'importe quelle requête jusqu'à un symbole de pipe |> est une requête valide, ce qui vous permet d'afficher les résultats intermédiaires dans une requête longue. Les gains de productivité peuvent accélérer le processus de développement au sein de votre organisation.
• Analyse des journaux: il existe d'autres types de syntaxe semblables à des pipes 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 de la syntaxe Pipe

À quelques exceptions près, la syntaxe du pipe accepte tous les opérateurs que la syntaxe standard accepte avec la même syntaxe. De plus, la syntaxe de pipe introduit les opérateurs de pipe suivants.

Opérateur pipe EXTEND

L'opérateur de pipe EXTEND, qui ne peut être utilisé qu'immédiatement après un symbole de pipe, vous permet d'ajouter des colonnes calculées à la table actuelle. L'opérateur de pipe EXTEND est semblable à l'instruction SELECT *, new_column, mais il vous offre plus de flexibilité pour référencer des alias de colonnes.

Prenons l'exemple du tableau suivant, qui contient deux notes de test pour chaque personne:

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

Supposons que vous souhaitiez calculer la note brute moyenne et le pourcentage moyen obtenu par chaque élève à l'examen. 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 pour 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 pipe EXTEND peut faire référence à 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 de barre verticale SET, qui ne peut être utilisé qu'immédiatement après un symbole de barre verticale, vous permet de remplacer la valeur des colonnes du 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 pipe DROP, qui ne peut être utilisé qu'immédiatement après un symbole de pipe, vous permet de supprimer des colonnes de la table actuelle. L'opérateur de pipe DROP est semblable à l'instruction SELECT * EXCEPT(column). Une fois qu'une colonne a été 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 pipe RENAME, qui ne peut être utilisé qu'immédiatement après un symbole de pipe, vous permet de renommer des colonnes de la table actuelle. L'opérateur de 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 pipe, utilisez l'opérateur de pipe 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 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 clause GROUP BY par une clause GROUP AND ORDER BY, 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 besoin de répéter les noms de colonnes à deux endroits.

Pour effectuer une agrégation complète de la table, utilisez GROUP BY() ou omettez complètement 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 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 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
);

L'exemple suivant utilise une clause USING et évite l'ambiguïté des colonnes:

FROM `mydataset.produce`
|> JOIN `mydataset.item_data` 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 éliminer les ambiguïtés dans une clause ON, vous devez attribuer un alias à la table actuelle à l'aide de l'opérateur de pipe AS. Vous pouvez éventuellement attribuer un alias à la table jointe. Vous pouvez faire référence aux deux alias après les opérateurs de pipe suivants:

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

Le côté droit de la jointure n'est pas visible sur le côté gauche de la jointure, ce qui signifie que vous ne pouvez pas joindre le tableau actuel à lui-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 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 contenant des informations sur les commandes des clients:

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

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 comme suit:

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;

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

Avec la syntaxe de pipe, vous pouvez écrire la requête pour suivre les étapes logiques que vous pourriez envisager de suivre 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 des clients.
• 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.
• Déterminez le montant moyen que les clients réguliers dépensent pour chaque État et chaque type d'article.

Limites

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

Étape suivante

• En savoir plus sur la syntaxe des requêtes avec pipe
• En savoir plus sur la syntaxe de requête standard