Exclure et renommer des colonnes avec SELECT * sur Snowflake

Dans sa release de novembre 2022, Snowflake a discrètement annoncé une nouvelle syntaxe SQL intéressante : EXCLUDE et RENAME, qui permet de supprimer et de renommer des colonnes spécifiques au sein d'une requête de type SELECT . C'est particulièrement utile car, dans les workflows SQL hors production, il est très courant de lancer des requêtes exploratoires de type `SELECT ` comme :

select *
from table
where id = 5

On procède souvent ainsi pour examiner un enregistrement précis ou un sous-ensemble d'enregistrements. Mais que faire lorsque l'on souhaite exclure une colonne en particulier (par exemple une grande colonne de texte) ou en renommer une ? Il faut alors énumérer toutes les colonnes voulues, en appliquant les éventuels renommages via la syntaxe traditionnelle AS :

select
  column_1,
  column_2,
  column_3 as column_3_renamed,
  column_4,
  column_5,
  column_6,
  column_7,
  column_8,
  column_9, -- les colonnes 10 et 11 sont volontairement omises
  column_12
from table
where id = 5

Pas l'idéal quand on cherche à obtenir des réponses rapidement.

Plutôt que de saisir chaque colonne, une demande récurrente 1 des utilisateurs porte sur la possibilité de désigner un sous-ensemble de colonnes à exclure de la table. Dans bien des cas, c'est beaucoup plus efficace à écrire, car le nombre de colonnes à exclure est souvent bien inférieur à celui des colonnes à inclure. Depuis la version 6.37 de Snowflake, il est désormais possible d'ajouter une clause EXCLUDE dans les requêtes SQL de type SELECT *, en précisant une ou plusieurs colonnes à ne pas renvoyer :

select
  *
  exclude(column_10, column_11)
from table
where id = 5

Cette syntaxe fonctionne aussi pour exclure une seule colonne :

select
  *
  exclude (column_10)
from table
where id = 5

Les parenthèses sont facultatives lorsqu'on exclut une seule colonne. Le SQL ci-dessus peut donc se réécrire ainsi :

select
  *
  exclude column_10
from table
where id = 5

Utiliser EXCLUDE de Snowflake avec plusieurs tables

L'exclusion de colonnes s'avère également pratique lors de jointures entre plusieurs tables. Imaginez une requête de ce type 2 :

select
  orders.*,
  customers.* exclude customer_id,
  items.* exclude order_id
from orders
join customers
  on orders.customer_id=customers.customer_id
join items
  on orders.order_id=items.order_id

Dans cet exemple, le résultat contient deux colonnes order_id et deux colonnes customer_id, puisqu'elles existent dans plusieurs tables. On peut facilement les exclure en modifiant le SQL comme suit :

select
  orders.*,
  customers.* exclude customer_id,
  items.* exclude order_id
from orders
join customers
  on orders.customer_id=customers.customer_id
join items
  on orders.order_id=items.order_id

L'équivalent Snowflake d'EXCEPT chez BigQuery et Databricks

La syntaxe EXCLUDE de Snowflake est proche de la syntaxe EXCEPT de BigQuery et de Databricks :

-- Snowflake
select
  *
  exclude(column_10, column_11)
from table

-- BigQuery/Databricks
select
  *
  except(column_10, column_11)
from table

Pour ceux qui connaissent DuckDB, Snowflake reprend la même syntaxe EXCLUDE que ce dernier. Les deux bases ont vraisemblablement préféré éviter le mot-clé EXCEPT pour cette fonctionnalité, puisqu'il est déjà utilisé dans les opérations ensemblistes.

Faut-il utiliser EXCLUDE en SQL de production ?

Si cette nouvelle syntaxe est idéale pour accélérer les workflows ad hoc, elle est déconseillée en SQL de production. Mieux vaut rester explicite sur les colonnes sélectionnées. Lister tous les noms de colonnes améliore la lisibilité et facilite l'audit. Les personnes qui relisent votre code SQL savent immédiatement quelles colonnes vous utilisez, et de quelle table. Les erreurs sont aussi plus claires : si une colonne est subitement supprimée d'une table, votre code SQL échouera immédiatement. C'est bien préférable à une application en aval qui plante soudainement à cause d'une colonne manquante.

L'usage d'EXCLUDE dans Snowflake modifie-t-il le plan d'exécution ou les performances ?

Non. Utiliser le mot-clé EXCLUDE pour retirer quelques colonnes ne change ni le plan d'exécution ni les performances par rapport à l'écriture explicite de toutes les colonnes. Pour le vérifier, exécutez les deux types de requêtes sur un jeu de données dans votre compte Snowflake et examinez le query profile de chacune.

Avant l'arrivée de la fonctionnalité RENAME, il fallait écrire chaque nom de colonne dès que l'on devait en renommer ne serait-ce qu'une seule dans une requête SELECT *. Désormais, on peut sélectionner toutes les colonnes tout en en renommant un sous-ensemble :

select
  *
  rename (column_3 as column_3_renamed, column_5 as column_5_renamed)
from table
where id = 5

Bien plus simple que de devoir lister tous les champs juste pour en renommer un ou deux :

-- ancienne méthode 👎
select
  column_1,
  column_2,
  column_3 as column_3_renamed,
  column_4,
  column_5 as column_5_renamed,
  column_6,
  ...
  column_12
from table
where id = 5

Comme pour EXCLUDE, l'opération fonctionne aussi pour une seule colonne, parenthèses facultatives :

select
  *
  rename (column_3 as column_3_renamed)
from table
where id = 5

Combiner EXCLUDE et RENAME

EXCLUDE et RENAME se combinent facilement, comme le montre l'exemple ci-dessous :

select
  *
  exclude(column_10, column_11)
  rename column_3 as column_3_renamed
from table
where id = 5

Bien plus lisible que le code du début de l'article :

select
  column_1,
  column_2,
  column_3 as column_3_renamed,
  column_4,
  column_5,
  column_6,
  column_7,
  column_8,
  column_9, -- les colonnes 10 et 11 sont volontairement omises
  column_12
from table
where id = 5

1. Pour donner une idée de la demande, cette question Stack Overflow, posée il y a près de 14 ans, totalise 1,3 million de vues et plus de 1 000 votes positifs.

2. Merci à Nate Sooter d'avoir inspiré cet exemple avec son récent tweet !

Ian Whitestone·Co-founder & CEO of SELECT

Ian est cofondateur et CEO de SELECT, une plateforme SaaS de gestion et d'optimisation des coûts Snowflake. Avant de lancer SELECT, Ian a passé six ans à diriger des équipes full stack data science et engineering chez Shopify et Capital One. Chez Shopify, il a piloté les chantiers d'optimisation du data warehouse et de mise en visibilité des coûts.