Convert NDJSON to SQL

The mapping is exactly as direct as it sounds. Every non-empty line of the source produces one INSERT statement, so the output has as many statements as the input has records, and wc -l on either file tells you the same number. There is no header to account for and nothing in the data can produce a line break, because a newline inside a string is escaped inside the JSON.

That predictability is the reason this pair is pleasant to work with. If a load reports 412,000 rows inserted and the file had 412,000 lines, the load was complete — no reconciliation, no partial-row question, no wondering whether a quoted field swallowed a row boundary the way it can in a delimited file.

This is the one place where a streaming source format does not buy you a streaming conversion. The column list has to be identical in every statement, and it is built from the union of keys across every line — so a field that appears only on the last record still becomes a column, and nothing can be written until the reader has seen it.

The consequence is a memory ceiling rather than a size tier: the whole file exists in the tab while it is being converted. Tens of megabytes is unremarkable, several hundred is where a browser starts to labour, and beyond that the answer is to split the file. NDJSON splits safely at any line boundary, so split -l 200000 produces valid files with no special handling, and each one converts and loads on its own.

A log stream that emits requests, errors and job completions into one file has three different key sets in it. The reconciliation handles that without dropping anything — every key becomes a column, and a line without it supplies NULL — and the table you end up declaring is the union of three schemas.

For an afternoon of analysis that is often acceptable. For anything that will live longer, filtering the file per event type and loading three tables produces a schema you can index sensibly and queries that do not begin with a filter on a discriminator column. Splitting first is one jq expression per type, and the tables that come out are the ones you would have designed.

The output has no DDL in it, deliberately. JSON tells you a value is a number and not whether the column is an integer or a numeric with two decimal places, whether it is nullable, what the primary key is or how long the text can be — which is most of what a schema is for.

The practical loop is to convert, read the column list off the first statement, write the table to match, then run. One warning that matters here more than on smaller files: derive the columns from a conversion of the whole file, never from a sample. A rare field that appears in one line of half a million still becomes a column in every statement, and a table declared from the first thousand records will reject the entire load on the statement that uses it.

Structured log records nest — a request object with a method and a path, a context block with a trace identifier — and a relational table does not. The path is folded into the column name with underscores, so request.method becomes request_method and every leaf value gets its own column.

One or two levels produce a table you would be happy to declare, which covers most logging libraries. A record carrying a whole serialised payload produces a column per field inside it, and at that point the useful question is which subtree you actually intend to query. Extracting it before converting gives a narrower table and a much shorter CREATE TABLE.

Values are written as SQL literals: numbers bare, booleans as TRUE and FALSE, nulls as NULL, and text single-quoted with internal single quotes doubled. Doubling is the portable form and every engine reads it, so a message containing an apostrophe loads correctly everywhere.

Backslashes are written through untouched, which is correct under the SQL standard and is not how MySQL reads a string with its default settings, where a backslash begins an escape sequence. Log data is unusually full of them — Windows paths, regular expressions, escaped JSON inside a message field — so this is more likely to bite here than on a tidy export. Set NO_BACKSLASH_ESCAPES for the session, or load into Postgres, and check one affected row rather than assuming.

The statements arrive one per line with no transaction around them. Run that as-is against a database and every statement is its own transaction with its own commit and its own round trip, which is the slowest possible way to insert half a million rows.

Two edits change the arithmetic. Wrapping the file in BEGIN and COMMIT is one line at each end and usually the single largest improvement available. Dropping the indexes before the load and recreating them afterwards is the second, and on a table with three indexes it often halves the time again. Neither is specific to this converter; both are the standard advice for a bulk insert, and both matter more the larger the file is.

There is a size past which statements are the wrong instrument regardless of how they are batched. Every engine has a bulk path — COPY in Postgres, LOAD DATA in MySQL, an import in a client tool — that reads a delimited file directly and skips the statement parsing entirely, and for a million rows the difference is minutes against hours.

Converting the same NDJSON to CSV or TSV and using that path is the better plan above roughly a hundred thousand rows. The statements keep two advantages worth weighing: they run anywhere a client can connect, including a managed database where you cannot put a file on the server, and they can be reviewed and committed to a repository as a fixture. Below the threshold, or where the file has to travel through a code review, INSERTs are still the right answer.

If any line is not valid JSON, the conversion fails and the message names the line number. Nothing partial is produced, which is the behaviour you want here: a half-written statement file loaded into a table is considerably worse than no file at all.

A malformed line in a log usually means a truncated write rather than a typo — a process killed mid-flush, a rotated file cut at a boundary. Knowing which line means you can trim the tail, count what you are discarding and load the rest deliberately, instead of discovering a gap in the data three queries later.

The statements are generated by JavaScript in this browser tab. The file is not uploaded, there is no account and no queue, and the free tier accepts up to 100 MB, with memory as the practical constraint for the reason described above.

That property is doing real work for this pair. Event logs are the most sensitive ordinary file most engineers handle — IP addresses, session identifiers, request paths, sometimes a token in a query string — and the reason to convert one is usually an incident, which is the worst possible moment to be sending it to a third party.