Fiche 2 : conception des clés de tri (ORDER BY)

Déduisez la clause ORDER BY de chaque table NYC Taxi à partir de ses requêtes et recevez une correction immédiate pour chaque réponse.

Durée estimée : 20 à 25 minutes Référence : Moteurs MergeTree — section Conception de ORDER BY

Concept

Dans ClickHouse, la clause ORDER BY n’est pas décorative. Elle définit l’index primaire — un index clairsemé au niveau des blocs qui permet à ClickHouse d’ignorer les blocs de données non pertinents lors de l’évaluation des clauses WHERE. Elle détermine également l’ordre physique des données dans les parties, ce qui influe sur la compression.

Une mauvaise ORDER BY entraîne des requêtes lentes et du stockage gaspillé. Une ORDER BY commençant par un UUID n’élimine aucun bloc dans les requêtes analytiques (les UUID sont aléatoires et n’ont pas de préfixe triable). Une ORDER BY commençant par une date permet aux requêtes filtrées sur la date d’ignorer l’essentiel de la table.

Trois règles pour concevoir ORDER BY

Règle 1 : partez des filtres des requêtes, pas du schéma source. Examinez les colonnes employées dans WHERE, GROUP BY et JOIN parmi vos requêtes les plus fréquentes. Les colonnes les plus filtrées devraient probablement figurer dans ORDER BY, à condition que leur cardinalité ne soit pas trop élevée. La clé primaire de la table source, si elle existe, est généralement sans importance.

Règle 2 : faible cardinalité d’abord, forte cardinalité ensuite. L’index primaire de ClickHouse comporte une entrée pour environ 8192 lignes (un granule). Les colonnes à faible cardinalité (par exemple toStartOfMonth(date) = environ 48 valeurs distinctes sur 4 ans) regroupent de nombreuses lignes : l’index peut ignorer des granules entiers. Les colonnes à forte cardinalité (par exemple trip_id = 50 millions de valeurs) sont uniques par ligne ; les placer en tête empêche l’index d’ignorer quoi que ce soit. Cet ordre est une valeur par défaut, pas une exception à la règle 1 : une colonne filtrée par plage qui élimine la majorité des lignes peut mériter la première place devant une colonne de cardinalité plus faible, uniquement filtrée par égalité.

Règle 3 : avec ReplacingMergeTree, terminez par l’identifiant unique de la ligne. La clé de déduplication est le tuple ORDER BY complet. Si trip_id manque dans ORDER BY, deux courses distinctes ayant le même pickup_at et aucune colonne supplémentaire seraient considérées comme des doublons. Placez trip_id en dernier pour garantir l’unicité sans nuire aux performances de l’index.

Exercice : analyse des requêtes

Avant de concevoir les clés de tri, déterminez les colonnes réellement filtrées par les requêtes. L’atelier NYC Taxi comprend 7 requêtes représentatives ; pour chacune, choisissez la colonne de filtre qui élimine le plus de lignes.

Exercice : estimation de la cardinalité

Pour chaque colonne candidate à ORDER BY, estimez sa cardinalité dans le jeu de 50 millions de lignes sur 4 ans. La majeure partie du tableau ci-dessous est fournie à titre de référence ; les deux cellules à compléter concernent le nombre de valeurs distinctes estimé de pickup_at et sa classe de cardinalité. Quatre ans représentent environ 126 millions de secondes (et seulement 2,1 millions de minutes), le producteur horodate chaque course selon l’horloge réelle et PICKUP_AT est stocké sous la forme DateTime64(3, 'UTC') : calculez combien de ces emplacements 50 millions de courses peuvent occuper avant de choisir une tranche.

Exercice : conception des clés de tri

À partir de l’analyse des requêtes et des estimations de cardinalité, proposez une ORDER BY pour trips_raw, fact_trips et agg_hourly_zone_trips. Rappels :

Faible cardinalité d’abord → élimination maximale des blocs.
Colonnes présentes dans les clauses WHERE/GROUP BY de plusieurs requêtes → à inclure.
Tables ReplacingMergeTree → terminer par l’identifiant unique de la ligne.
Ne pas inclure les colonnes qui ne sont jamais filtrées.

Répondez ensuite aux questions de raisonnement et de réflexion une fois toutes les tables remplies.

Loading worksheet...

Report dans migration-plan.md

Après avoir rempli cette fiche, copiez vos choix de ORDER BY dans la section 4 de migration-plan.md et cochez :

- [ ] Sort key design: completed