AngoraTek EN
← Retour au blog
Tutoriels 7 min de lecture

Excel s'arrête à 1 048 576 lignes. Votre CSV, non.

Cette page s'adresse à la personne dont le tableur est devenu une barre de chargement : un export d'horaire de plusieurs millions de lignes, ou deux fichiers qui réclament une jointure qu'aucune fenêtre de tableur ne peut contenir. Le premier chiffre à connaître est 1 048 576, le compte exact de lignes qu'une feuille accepte, après lequel l'import s'arrête à ce qui rentre et le reste n'arrive jamais, avec un avertissement qu'on clique sans le lire. L'échappatoire qui suit tient en une commande, et elle s'appelle DuckDB.

La thèse de ce tutoriel : pas besoin d'entrepôt, de serveur ni de compte pour poser des questions utiles à des fichiers de cette taille. DuckDB est une base en process qui lit les fichiers CSV, Parquet et même .xlsx là où ils sont, les lit en flux au lieu de les charger, et répond en SQL sur un ordinateur portable ordinaire. Installez-la depuis duckdb.org ou avec brew install duckdb, puis ouvrez le CLI. Décompressez un flux GTFS public dans un dossier et lancez les requêtes depuis ce dossier. stops.txt et stop_times.txt sont les deux fichiers de ce tutoriel. Notre exemple fil rouge est l'horaire d'une agence de transport, parce que les flux GTFS sont publics, volumineux à dessein, et façonnés comme les fichiers qui cassent les tableurs. Vos comptages différeront à chaque requête selon le flux, alors lancez-les plutôt que de nous croire.

Une commande lit le fichier là où il se trouve

Pas d'assistant d'import, pas de sélecteur de schéma non plus. Pointez DuckDB vers le chemin :

SELECT count(*) AS rows_in_feed
FROM read_csv_auto('stop_times.txt');

Le moteur devine le séparateur, l'en-tête et les types de colonnes, lit le fichier en flux, calcule et ne garde rien sur le disque tant qu'on ne le demande pas. La réponse à ce comptage est souvent le moment de la conversion : toute l'opération est un processus qui tourne sur la machine où vit déjà le fichier.

Le détecteur est bon, mais il ne devine pas, et c'est une qualité à rencontrer tôt. Dans un flux GTFS, un departure_time de véhicule peut légitimement valoir 24:15:00, car la spécification GTFS Schedule laisse les heures courir après minuit pour couvrir le service tardif. Aucun type TIME n'accepte cette valeur ailleurs, donc DuckDB laisse la colonne en texte et vous prenez l'heure vous-même avec split_part. Un tableur, face à la même conversion, transformera 24:15:00 en heure du mauvais jour, ou refusera selon la version. La base décline de deviner, vous le dit, et continue. Quand le rapport du fournisseur arrive en .xlsx, le chemin garde la même forme :

SELECT * FROM read_xlsx('vendor-report.xlsx');

L'extension excel se charge au premier usage, lit la première feuille par défaut (nommez-en une autre avec sheet = '...'), et sait aussi écrire du .xlsx, ce qui compte à la fin du tutoriel.

La jointure, la chose que le tableur ne peut pas contenir

La partie dure, pour Excel, est rarement l'arithmétique sur une feuille ; ce sont deux fichiers au-delà du million de lignes qui ont besoin l'un de l'autre. stops.txt, le fichier léger, nomme chaque arrêt. stop_times.txt, le mastodonte, enregistre chaque départ à chaque arrêt, et c'est lui que l'assistant d'import étouffe. La jointure demande quels arrêts sont les plus denses, et en SQL c'est une seule requête qui lit tout en flux :

SELECT s.stop_name, count(*) AS departures
FROM read_csv_auto('stops.txt') AS s
JOIN read_csv_auto('stop_times.txt') AS st USING (stop_id)
GROUP BY s.stop_name
ORDER BY departures DESC
LIMIT 20;

La même astuce répond à la question pour laquelle on ouvre vraiment le tableur, la forme de la journée. Quand la demande se concentre-t-elle ? Groupez les départs par heure et regardez :

SELECT split_part(departure_time, ':', 1)::INTEGER AS departure_hour,
       count(*) AS departures
FROM read_csv_auto('stop_times.txt')
GROUP BY 1
ORDER BY 1;

Les heures 24 et plus apparaissent dans la sortie parce que les heures GTFS franchissent minuit, et ce sont des données réelles, pas un problème. Dans le tableur, ces lignes étaient soit abîmées, soit jetées ; ici elles s'alignent sagement au bout de l'histogramme et disent quelle part du réseau continue de rouler après le changement de jour.

Le problème du second fichier se règle de la même façon. La réponse classique d'Excel à une question entre fichiers, c'est VLOOKUP, qui exige les deux fichiers ouverts et exige que le géant tienne, ou Power Query, qui tente de l'amener. DuckDB lit le flux et joint sans avoir besoin que le fichier géant tienne dans une feuille de tableur. La jointure qui valait deux jours d'attente de barres de chargement devient une ligne dont le plan s'imprime avec EXPLAIN, si l'on veut la voir élaguer.

La réponse retourne dans le tableur, pas dans les données

Vingt lignes d'arrêts classés tiennent dans une feuille, un courriel ou une diapositive, et des humains doivent encore y toucher. Exportez la petite réponse, pas la source géante :

LOAD excel;
COPY (
    SELECT s.stop_name, count(*) AS departures
    FROM read_csv_auto('stops.txt') AS s
    JOIN read_csv_auto('stop_times.txt') AS st USING (stop_id)
    GROUP BY s.stop_name
    ORDER BY departures DESC
    LIMIT 20
) TO 'busiest-stops.xlsx' WITH (FORMAT xlsx, HEADER true);

Deux détails font de ce bout de code un aller-retour honnête. L'outil xlsx livré par DuckDB porte un sheet_row_limit dont la valeur par défaut est 1 048 576, le plafond d'Excel lui-même, puisque une feuille écrite au-delà deviendrait illisible pour le logiciel visé. Nos vingt lignes ne frôlent jamais ce plafond. Et le fichier source reste où il est, intact, puisque rien n'a jamais été chargé. Le tableur garde les réponses pour les humains, et SQL garde les questions. Cette division du travail est l'argument entier en une phrase.

La répétition change un détail : le format. Quand le même fichier est interrogé chaque semaine, une copie unique en Parquet rend chaque exécution suivante moins chère, car Parquet conserve des statistiques de colonnes et un vrai schéma au lieu de re-deviner du texte :

COPY (SELECT * FROM read_csv_auto('stop_times.txt'))
TO 'stop_times.parquet' (FORMAT PARQUET);

Ensuite, les requêtes lisent FROM 'stop_times.parquet', sautent l'analyse du texte et, pour les questions à l'échelle de l'heure, la majeure partie du fichier, et le script d'un soir devient un petit pipeline reproductible.

Ce que nous avons essayé d'abord, et ce que cet outil ne sait pas

Notre premier réflexe devant une requête comme les arrêts les plus denses était d'assembler de l'infrastructure : une instance Postgres, un schéma, une tâche de chargement, parce qu'une jointure implique une base. La bascule est venue de ce constat : la question était posée une fois, et le fichier dormait déjà sur la machine. L'entrepôt a rendu service uniquement le jour où les questions sont devenues planifiées et les résultats un produit. Jusque-là, c'était une semaine de mise en place empaquetée autour de la même requête. La contrainte d'abord, l'outillage ensuite, et rarement dans l'ordre attendu.

Les limites honnêtes concernent le partage, pas la capacité. DuckDB est en process et n'admet qu'un seul scripteur : deux collègues qui interrogent en même temps le même fichier de base, c'est une affaire en lecture seule ou une dispute en préparation, donc une équipe qui a besoin d'écritures concurrentes est une équipe pour Postgres, pas pour ceci. Une cellule de tableur qui se recalcule quand sa source change n'a pas d'équivalent ici. La requête se recalcule en la relançant, planifiée ou non. Et rien de tout cela n'est une base de service : une application face aux clients n'a pas plus d'affaire à lire un CSV par requête HTTP. DuckDB vous répond ; elle ne répond pas à vos utilisateurs.

La décision se lit donc comme un passage de relais. Si un fichier sature la feuille, ou si deux fichiers réclament une jointure qu'aucun des deux ne supporte, lancez une requête avant de demander qui que ce soit de l'infrastructure. Gardez le tableur pour les vingt lignes qu'un humain lit, gardez les fichiers énormes comme des fichiers, et laissez l'échappatoire lire au-delà du plafond au million de lignes dont on ne lui a jamais parlé.

AngoraTek met des questions comme celles-là sur un terrain réel, fichiers et ordinateurs portables compris. [Montrez-nous le tableur qui tourne en rond](/fr/#contact) et nous lancerons la première requête avec vous.

Merci d'avoir lu jusqu'ici. Si cela ressemble au problème sur lequel vous butez, parlez-nous-en. L'équipe lit chaque message.

Engager la conversation