IF Commons GitHub

sql/loads/load

Written to: 5 requirements: 5 proved, 0 partly, 0 failing, 0 with no test recorded. 0054-sql-loads FR-004, 0054-sql-loads FR-005, 0054-sql-loads FR-006, 0054-sql-loads FR-007, 0054-sql-loads FR-008

Loads (commons data 0054-sql-loads): a source into a typed table, in batches, in one transaction, and recorded. append, skip-existing and upsert say what happens where a key is there; replace empties the table first. With a gate, a load is ELT: the rows go to a staging table that has the target's columns and none of its constraints, SQL-DQ tests run on the staging table, and only a load that passes is merged into the target; one that does not leaves the target as it was and reports the failures. Every run is a row of the ledger of loads, with its counts and its high-water mark, so that the next run can read only what is new.

Source: commons/data/sql/loads/load.ts

3 of 6 exports are imported by a test of the run. A test that imports an export may not call it or check what it returns.

ensureLedgerfunction
function ensureLedger(on: Runner): Promise<void>

Makes the ledger of loads if it is not there.

Imported by 8 tests of the run (8 passed, 0 failed): the library's results

LoadModetypeno test imports it
type LoadMode = "append" | "skip-existing" | "upsert" | "replace";

What a load does where a row's key is already in the table.

LoadSpecinterfaceno test imports it
interface LoadSpec<R, I, N extends string>

What a load is told.

MemberTypeWhat it is
namestringThe name of the load, by which its runs are known.
sourceSource<I>
targetTable<R, I, N>
mode?LoadModeappend fails on a key that is there; the default.
keys?readonly (keyof R & string)[]The columns that a row is known by, for skip-existing and upsert: the primary key of the table where not given.
batchSize?numberThe rows in a batch, 500 where not given.
gate?((staging: { readonly name: string; readonly schema?: string | undefined; }) => readonly DqTest[])The tests of the rows that are staged; where there are any, the load goes through a staging table and only a load that passes is merged.
watermark?(keyof R & string)A column whose greatest value in the rows loaded is kept as the high-water mark.
maxRejected?numberFail the load, and take nothing, where more than this share of the lines were rejected (none where not given).
keepStaging?booleanKeep the staging table, to look at what was gated.
loadIdColumn?(keyof R & string)A column of the table that is given the run's id on every row this load writes, so that what a load wrote can be found and undone by it.
divertConflicts?booleanIn append mode, a row whose key is already there is not a failure: where its content is the same it is absorbed (counted, not written again), and where it differs it is diverted to the table <table>__conflict (the existing row and the incoming one, as JSON), and the original is left as it is. Without a gate.
loadRunconstno test imports it
const loadRun: Table<RunRow, RunRow, "loadRun">

The ledger of loads: one row for each run.

runLoadfunction
function runLoad<R, I, N extends string>(spec: LoadSpec<R, I, N>, on: Runner): Promise<LoadReport>

Runs a load.

ParameterWhat it is
specwhat to load, from where, into what
onthe database and its dialect

Returns: what it did; a refusal by the database is a report with status failed, not a throw

Imported by 13 tests of the run (13 passed, 0 failed): the library's results

watermarkOffunction
function watermarkOf(on: Runner, load: string): Promise<string | undefined>

The high-water mark of the last load of a name that was loaded, or none: what the next run reads from.

Imported by 8 tests of the run (8 passed, 0 failed): the library's results

Titles, headings, exports, tables, columns and requirements. Esc closes.