Local H2 datasets

AS H2 writes a result into a table of a local H2 database: a working copy you can query with SQL, refresh later, and keep longer than the source keeps its own rows. The syntax common to every destination is in Exporting data.

Writing to a local H2 database (AS H2)

<name> (before the dot): the target H2 database. If it already names a connection, that connection is reused (it must be H2; DUMP refuses to touch a connection of any other type). Otherwise a new H2 database is created automatically and registered under that name. <table> (after the dot): the table to write into that database.

A newly created connection is registered as a standalone connection (no Database Group) in the built-in LOCAL environment. Both can be changed afterward from CONFIG.

Times and timestamps keep every fractional digit, and a time-zone-aware timestamp keeps its original offset in a TIMESTAMP WITH TIME ZONE column.

Example: a local working dataset

Run an expensive or remote query once, keep the result locally, then work on the copy.

-- 1. A useful query on the remote system
SELECT ID, NAME, COUNTRYCODE, POPULATION FROM CITY WHERE POPULATION > 1000000;

-- 2. Keep the displayed result as a local H2 table (WORKCOPY is created on first use)
DUMP / TO WORKCOPY.BIGCITIES AS H2;

-- 3. Query the local copy
CONNECT WORKCOPY;
SELECT COUNTRYCODE, COUNT(*) AS CITIES, SUM(POPULATION) AS TOTAL FROM BIGCITIES GROUP BY COUNTRYCODE ORDER BY TOTAL DESC;

The copy is a snapshot: run a DUMP (<query>) TO ... AS H2 again (with MODE OVERWRITE or MODE APPEND KEY(...)) to refresh it.

Provenance (BROADSQL_PULL_AUDIT)

Every successful AS H2 export also records one row in BROADSQL_PULL_AUDIT, a table BroadSQL maintains inside the destination database: where the table came from, when it was last refreshed, from what query and connection, and how many rows. One row per execution, never overwritten:

SELECT * FROM BROADSQL_PULL_AUDIT
WHERE TARGET_TABLE = 'CITY'
ORDER BY EXECUTED_AT DESC;

BROADSQL_PULL_AUDIT is a reserved name: it cannot itself be used as a destination table.

MODE OVERWRITE

MODE APPEND KEY(<column>) [FORCE]

MODE APPEND needs a SQL source (a table or a query in parentheses): it filters that query itself.

How it behaves:

• First run (the target table does not exist yet): every row the query returns is loaded.
• Later runs: BroadSQL reads the current maximum value of <column> in the target table, then only fetches and inserts source rows whose <column> is strictly greater. No row already in the target is ever updated or deleted, and running again with no new source rows inserts nothing.
• <column> must be a single column of a numeric or date/time type.
• The source's own WHERE condition keeps its meaning: the key filter is added to the whole condition, WHERE (<condition>) AND <column> > <maximum>, so a condition with OR never brings back rows already in the target.
• <column> may name a column alias of the select list (O.ID AS ORDER_ID, then KEY(ORDER_ID)): the filter then uses the aliased expression (O.ID), since a WHERE clause cannot refer to an alias. In a JOIN, an unaliased key such as C.ID is filtered as C.ID, never as an ambiguous ID.
• The source query may use JOIN (any kind), but not a comma-joined or derived (subquery) FROM, nor GROUP BY, HAVING, ORDER BY, UNION, LIMIT, OFFSET or FETCH.
• Before inserting, BroadSQL checks whether <column> could ever be NULL for this query, since a NULL key would be skipped by every later run. A key confirmed as nullable is always refused. When BroadSQL cannot tell (a computed key such as COALESCE(...)), it refuses too; add FORCE after KEY(<column>) once you have checked yourself that the key can never be NULL.
• An outer join always prints a caution note: depending on the database, BroadSQL's check may not detect a NULL key produced by an unmatched row.

Typical use: mirror a table whose old rows are purged at the source into a local H2 database with a longer retention window, kept current by running the same command again:

DUMP CITY TO ARCHIVE.CITY AS H2 MODE APPEND KEY(ID);

Example: refresh a working copy every morning

The first run creates WORKCOPY and the table; each later run adds only the new rows:

DUMP (SELECT ID, CUSTOMER, AMOUNT, ORDER_DATE FROM ORDERS) TO WORKCOPY.ORDERS AS H2 MODE APPEND KEY(ID);
CONNECT WORKCOPY;
SELECT TARGET_TABLE, EXECUTED_AT FROM BROADSQL_PULL_AUDIT ORDER BY EXECUTED_AT DESC;

The last query lists every refresh recorded in BROADSQL_PULL_AUDIT.

Related pages

• Import / Export overview
• Exporting data: syntax, sources, unsupported forms.
• Connections, Database Groups and Environments: the LOCAL environment and standalone connections.
• Command reference: DUMP, CONNECT.