CSV, JSON lines, Arrow

Vectro reads CSV, JSON lines and Arrow into tables, and writes Arrow back out. None of it needs a dependency. CSV is parsed straight from bytes in engine memory, on every core, without making a JavaScript string per row.

CSV

engine.readCSV(path, options) loads a file (Node only). engine.parseCSV(textOrBytes, options) takes a string, a Uint8Array or a Buffer, and works in every runtime.

import { Engine } from 'vectrojs';
import { mkdtempSync, writeFileSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';

const dir = mkdtempSync(join(tmpdir(), 'vectro-csv-'));
const file = join(dir, 'orders.csv');
writeFileSync(file, [
  'ts,region,price,note',
  '2024-03-01T10:00:00Z,EU,12.5,"hello, world"',
  '2024-03-02T11:00:00Z,US,,"she said ""hi"""',
  '2024-03-03T12:00:00Z,EU,8,x',
].join('\n'));

const engine = new Engine();
const orders = engine.readCSV(file);
console.log(orders.columnNames, orders.length);
console.log(orders.toRows());
console.log(orders.filter("region == 'EU'").sum('price'));

rmSync(dir, { recursive: true });
await engine.close();

ts was recognised as a date column, price as numbers (the blank one is missing, NaN), and region and note as text. Quoted fields may hold commas, newlines and doubled quotes.

How it works

The file’s bytes go into engine memory once. They are cut into chunks at row boundaries (with quotes in the file, a parallel pass counts quote characters per chunk so a cut never lands inside a quoted field), rows are counted per chunk in parallel, then every chunk is parsed in parallel straight into the final columns. Text columns get a dictionary per chunk, merged at the end.

readCSVAsync(path, options) reads the file with async I/O straight into engine memory and parses on the worker threads; parseCSVAsync is the same for text or bytes.

Options

Option	Meaning	Default
delimiter	one character, e.g. ';' or '\t'	','
header	the first line has column names; with false columns are col0, col1, …	true
types	{ name: type } to override the guess (see below)	guessed
columns	names of the columns to load; the others are skipped	all
type	number storage, Float64Array or Float32Array	Float64Array
sampleRows	rows read to guess the column types	1000
compress	pack the columns after loading (see compressed columns)	false

A UTF-8 byte-order mark and Windows line endings are handled. Column names that aren’t plain identifiers are cleaned up for use in expressions: Order Date becomes Order_Date.

Column types

Each column is guessed from the first sampleRows rows:

Type	Chosen when	Can be
number	every sampled value is a number (blanks allowed)	filtered, grouped, summed
date	every sampled value looks like an ISO date or timestamp	filtered, grouped; held as ms since 1970, UTC
category	text that repeats (regions, statuses)	filtered, grouped, joined, sorted
string	free text: 50 or more sampled values, more than half of them different (emails, notes)	returned in results only

Set the type yourself with types: 'number', 'date', 'category', 'string', or 'text' (category or string, from the sample). A common case is a code with leading zeros, such as a postcode, which should stay text.

import { Engine } from 'vectrojs';

const engine = new Engine();
let csv = 'id;zip;email;region\n';
for (let i = 0; i < 100; i++) csv += `${i};0${1000 + i};user${i}@example.com;${i % 2 ? 'EU' : 'US'}\n`;

const guessed = engine.parseCSV(csv, { delimiter: ';' });
console.log(guessed.toRows(1));          // zip lost its 0; email is free text

const typed = engine.parseCSV(csv, {
  delimiter: ';',
  types: { zip: 'category', email: 'category' },
  columns: ['zip', 'email', 'region'],
});
console.log(typed.toRows(1));
console.log(typed.filter("email == '[email protected]'").toRows());
await engine.close();

Filtering or grouping on a free-text column gives an error that names the fix: load it with types: { email: 'category' }.

Files bigger than memory

readCSV loads the whole file. For a CSV larger than memory (or than the 4 GB an engine holds), convert it to a dataset with engine.csvToDataset(csvPath, datasetPath), which reads it in pieces. See datasets.

JSON lines

JSON lines (.jsonl, .ndjson) is one flat JSON object per line. Vectro’s parser reads them straight into columns, without making an object per row: only the wanted keys are read, numbers and numeric strings go into number columns, strings into category columns, and null, absent keys and nested values become missing. A line that isn’t a JSON object is skipped.

There are three ways in:

• A data folder. openTables({ root }) from vectrojs/serve loads every .csv, .tsv, .jsonl, .ndjson, .arrow, .feather and .vectro file in a folder, each as a table named after its file. The servers and npx vectro serve use the same loader. JSON lines whose objects carry an array of numbers get a vector index.
• A stream. A split engine’s streamAsync({ format: 'ndjson', columns }) takes JSON lines pushed to it or sent over TCP. See streams.
• Objects you already have. engine.tableFromRows(rows) takes an array of objects, with column types from the first row. null in a number column loads as missing, and Date values make a date column.

import { openTables } from 'vectrojs/serve';
import { mkdtempSync, writeFileSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';

const dir = mkdtempSync(join(tmpdir(), 'vectro-jsonl-'));
writeFileSync(join(dir, 'events.jsonl'), [
  '{"user":"a","ms":12,"page":"/home"}',
  '{"user":"b","ms":null,"page":"/cart"}',
  'not json',
  '{"user":"a","ms":30,"page":"/cart","extra":{"x":1}}',
].join('\n'));

const tables = await openTables({ root: dir });
const { events } = tables.named();
console.log(events.toRows());
console.log(events.groupBy('page').agg({ hits: 'count()', avgMs: 'mean(ms)' }).toRows());

await tables.close();   // frees the tables and closes the engine it made
rmSync(dir, { recursive: true });

import { SplitEngine } from 'vectrojs';

const engine = new SplitEngine({ workers: 2 });
const live = await engine.streamAsync({ format: 'ndjson', columns: { user: 'category', ms: 'number' } });

live.push('{"user":"a","ms":12}\n{"user":"b","ms":null}\n{"user":"a","ms":30}\n');
await live.flushAsync();
console.log(await live.aggAsync({ events: 'count()', avgMs: 'mean(ms)' }));
await engine.close();

Apache Arrow

Arrow IPC is the binary table format of pandas, Polars, DuckDB, pyarrow and Arrow Flight. Vectro reads and writes both the stream format and the file format (.arrow / .feather), including LZ4-compressed files.

Call	Does
engine.fromArrow(bytes, { columns, type })	a table from Arrow bytes; columns picks which to read
table.toArrow({ file })	the table’s rows (after any filter) as Arrow bytes; file: true for the file format
table.toArrowAsync({ file })	the same, without blocking
readArrow(bytes, { columns })	{ rows, columns, dates }: columns ready for engine.table, and the names of date columns
writeArrow(columns, { file, dates })	Arrow bytes from plain columns; dates names columns of ms to write as timestamps
splitEngine.fromArrowAsync(bytes)	a split table from Arrow bytes

import { Engine, readArrow, writeArrow } from 'vectrojs';
import { mkdtempSync, writeFileSync, readFileSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';

const engine = new Engine();
const sales = engine.table({
  ts:     [new Date('2024-01-01'), new Date('2024-01-02'), new Date('2024-01-03')],
  region: ['EU', 'US', 'EU'],
  price:  [12.5, NaN, 8],
});

// write a Feather file pandas can open with read_feather
const dir = mkdtempSync(join(tmpdir(), 'vectro-arrow-'));
const file = join(dir, 'eu.feather');
writeFileSync(file, sales.filter("region == 'EU'").toArrow({ file: true }));

// read it back, only two columns
const back = engine.fromArrow(readFileSync(file), { columns: ['ts', 'price'] });
console.log(back.toRows());

// the lower-level pair: plain columns in, plain columns out
const bytes = writeArrow({ id: [1, 2], name: ['Ada', 'Bo'] });
const { rows, columns } = readArrow(bytes);
console.log(rows, columns.name);

rmSync(dir, { recursive: true });
await engine.close();

Types

• Read: ints, floats (16, 32 and 64-bit), bools, dates, times, timestamps and durations become number columns (times in milliseconds); dates and timestamps are marked as date columns. Strings and dictionary-encoded strings become category columns. Nulls load as missing: NaN for numbers, '' for text. 64-bit integers are exact up to 2^53.
• Refused: lists, structs, decimals and binary give an error, unless you leave them out with columns. ZSTD-compressed files aren’t supported yet.
• Written: numbers as float64 (float32 tables as float32), dates as Timestamp(ms, UTC), categories as dictionary-encoded strings, which pandas opens as Categorical.

Files written by pyarrow (every type above, nulls, delta and replaced dictionaries, LZ4, pandas to_feather) are checked to read back exactly, and pyarrow and pandas read Vectro’s files back exactly.

Speed

From the README (M1 Pro): a 10M-row, 5-column Feather file loads in about 0.43 s uncompressed and 1.1 s with LZ4. pyarrow, with native threads, takes 0.16 s and 0.07 s. Arrow streams sent from Vectro’s Emitter arrive at 71 to 75M rows per second into 7 workers.

For Arrow over a network, ArrowReader and ArrowEncoder read and write it batch by batch; streams take format: 'arrow'. See the Arrow API and streams.