Convertir NDJSON en SQL

La correspondance est exactement aussi directe qu’elle en a l’air. Chaque ligne non vide de la source produit une instruction INSERT, donc la sortie a autant d’instructions que l’entrée a d’enregistrements, et `wc -l` sur l’un ou l’autre fichier donne le même nombre. Il n’y a pas d’en-tête à soustraire et rien dans les données ne peut produire de saut de ligne, parce qu’un saut de ligne à l’intérieur d’une chaîne est échappé dans le JSON.

Cette prévisibilité est la raison pour laquelle cette paire est agréable à utiliser. Si un chargement indique 412 000 lignes insérées et que le fichier avait 412 000 lignes, le chargement est complet — pas de réconciliation, pas de question sur des lignes partielles, pas à se demander si un champ entre guillemets a avalé une limite de ligne comme cela peut arriver dans un fichier délimité.

C’est l’endroit où un format source en flux n’achète pas une conversion en flux. La liste des colonnes doit être identique dans chaque instruction, et elle est construite à partir de l’union des clés sur toutes les lignes — donc un champ qui n’apparaît que sur le dernier enregistrement devient quand même une colonne, et rien ne peut être écrit avant que le lecteur l’ait vu.

La conséquence est un plafond de mémoire plutôt qu’un palier de taille : tout le fichier existe dans l’onglet pendant la conversion. Quelques dizaines de mégaoctets est sans histoire, quelques centaines de mégaoctets est le point où un navigateur commence à peiner, et au-delà la réponse est de scinder le fichier. NDJSON se scinde sans risque à toute limite de ligne, donc `split -l 200000` produit des fichiers valides sans traitement particulier, et chacun se convertit et se charge de son côté.

Un flux de journal qui émet des requêtes, des erreurs et des fins de tâches dans un seul fichier a trois jeux de clés différents. La réconciliation gère cela sans rien écarter — chaque clé devient une colonne, et une ligne qui ne l’a pas fournit NULL — et la table que vous finissez par déclarer est l’union de trois schémas.

Pour un après-midi d’analyse c’est souvent acceptable. Pour tout ce qui vivra plus longtemps, filtrer le fichier par type d’événement et charger trois tables produit un schéma sur lequel on peut raisonnablement indexer et des requêtes qui ne commencent pas par un filtre sur une colonne discriminante. Scinder d’abord est une expression `jq` par type, et les tables qui en sortent sont celles que vous auriez dessinées.

La sortie ne contient aucun DDL, et c’est délibéré. JSON vous dit qu’une valeur est un nombre et non si la colonne est un entier ou un numérique à deux décimales, si elle accepte le null, quelle est la clé primaire ou combien de temps le texte peut être — ce qui représente la plus grande partie de ce à quoi sert un schéma.

La boucle pratique est : convertir, lire la liste des colonnes sur la première instruction, écrire la table pour qu’elle corresponde, puis exécuter. Un avertissement qui compte ici plus que sur des fichiers plus petits : dérivez les colonnes d’une conversion du fichier entier, jamais d’un échantillon. Un champ rare qui apparaît dans une ligne sur un demi-million devient quand même une colonne dans chaque instruction, et une table déclarée à partir des mille premiers enregistrements rejettera tout le chargement sur l’instruction qui l’utilise.

Les enregistrements de journal structuré imbriquent — un objet `request` avec une méthode et un chemin, un bloc `context` avec un identifiant de trace — et une table relationnelle ne le fait pas. Le chemin est replié dans le nom de la colonne avec des soulignés, donc `request.method` devient `request_method` et chaque valeur feuille obtient sa propre colonne.

Un ou deux niveaux produisent une table que vous seriez content de déclarer, ce qui couvre la plupart des bibliothèques de journalisation. Un enregistrement portant toute une charge utile sérialisée produit une colonne par champ à l’intérieur, et à ce moment la question utile est quel sous-arbre vous avez l’intention d’interroger. L’extraire avant la conversion donne une table plus étroite et un CREATE TABLE bien plus court.

Les valeurs sont écrites en littéraux SQL : nombres à nu, booléens en `TRUE` et `FALSE`, nulls en `NULL`, et texte entre guillemets simples avec les apostrophes internes doublées. Le doublement est la forme portable et tout moteur la lit, donc un message contenant une apostrophe se charge correctement partout.

Les barres obliques sont écrites telles quelles, ce qui est correct selon le standard SQL et qui n’est pas la manière dont MySQL lit une chaîne avec ses réglages par défaut, où une barre oblique commence une séquence d’échappement. Les données de journal en sont inhabituellement pleines — chemins Windows, expressions régulières, JSON échappé dans un champ de message — donc cela a plus de chances de mordre ici que sur un export bien rangé. Réglez `NO_BACKSLASH_ESCAPES` pour la session, ou chargez dans Postgres, et vérifiez une ligne touchée plutôt que de supposer.

Les instructions arrivent une par ligne sans transaction autour. Exécutez cela tel quel contre une base de données et chaque instruction est sa propre transaction avec son propre commit et son propre aller-retour, ce qui est la manière la plus lente d’insérer un demi-million de lignes.

Deux modifications changent l’arithmétique. Entourer le fichier de `BEGIN` et `COMMIT` est une ligne à chaque bout et en général la plus grande amélioration disponible. Déposer les index avant le chargement et les recréer après est la deuxième, et sur une table à trois index cela divise souvent le temps par deux encore. Aucune des deux n’est spécifique à ce convertisseur ; les deux sont le conseil standard pour un INSERT en lot, et les deux comptent d’autant plus que le fichier est gros.

Il y a une taille au-delà de laquelle les instructions sont le mauvais instrument quelle que soit la manière dont on les regroupe. Chaque moteur a un chemin en lot — COPY dans Postgres, LOAD DATA dans MySQL, un import dans un outil client — qui lit un fichier délimité directement et saute entièrement l’analyse d’instructions, et pour un million de lignes la différence se compte en minutes contre heures.

Convertir le même NDJSON en CSV ou TSV et utiliser ce chemin est le meilleur plan au-dessus d’à peu près cent mille lignes. Les instructions gardent deux avantages qui valent la peine d’être soupesés : elles s’exécutent partout où un client peut se connecter, y compris une base de données gérée où vous ne pouvez pas mettre de fichier sur le serveur, et elles peuvent être relues et versées dans un dépôt comme fixture. Sous le seuil, ou là où le fichier doit passer par une revue de code, les INSERT restent la bonne réponse.

Si une ligne n’est pas un JSON valide, la conversion échoue et le message désigne le numéro de ligne. Rien de partiel n’est produit, ce qui est le comportement que vous voulez ici : un fichier d’instructions à moitié écrit chargé dans une table est considérablement pire que pas de fichier du tout.

Une ligne malformée dans un journal signifie en général une écriture tronquée plutôt qu’une coquille — un processus tué en plein vidage, un fichier rotaté coupé à une limite. Savoir quelle ligne signifie que vous pouvez tailler la queue, compter ce que vous écartez et charger le reste délibérément, au lieu de découvrir un trou dans les données trois requêtes plus tard.

Les instructions sont générées par du JavaScript dans cet onglet de navigateur. Le fichier n’est pas téléversé, il n’y a ni compte ni file d’attente, et le niveau gratuit accepte jusqu’à 100 Mo, avec la mémoire comme contrainte pratique pour la raison décrite plus haut.

Cette propriété fait un vrai travail pour cette paire. Les journaux d’événements sont les fichiers ordinaires les plus sensibles que la plupart des ingénieurs manipulent — adresses IP, identifiants de session, chemins de requête, parfois un jeton dans une chaîne de requête — et la raison de convertir l’un d’eux est en général un incident, qui est le pire moment possible pour l’envoyer à un tiers.