Query SQL sources

A source with ingest.mode: 'query' exposes declared remote tables through a StarRocks JDBC catalog. An optional source-level ingest.intervalSec of at least 60 seconds also schedules a managed mirror of each declared table. Without an interval, existing sources remain live-query-only. The installed source must have a current active registration and usable connection credentials before it appears in the Analytics catalog explorer.

Use the full catalog, schema and table coordinates shown in Remote SQL catalogs. For example, SELECT * FROM query_example.public.ports LIMIT 20. A catalog belongs to one organization, workspace, environment and source. A name from another scope cannot be used in this query. Only tables declared by the source are available; a remote table with the same name is insufficient.

Each referenced table is checked, including joins, CTE bodies and nested queries. Supported reads are single SELECT statements. Writes, table functions, locking reads and file exports are refused. Managed table references resolve through the selected environment's managed-table registry and governed views. Arbitrary physical databases, unresolved relation names and unsupported grouped FROM shapes are refused. Reads involving managed tables require native governance to be enabled; pure registered remote catalog reads use central table classification authorization. Notebook tables currently execute separately from managed and remote catalog queries. Analyst deep-dive query plans support notebook tables only.

Table classifications use the organization's active classification scheme. An unlabelled declared table uses the organization's default unlabelled-data rank; it does not grant unrestricted access. Unknown or ambiguous labels are refused. A caller needs actual organization membership and workspace access (or organization administrator authority), plus sufficient effective clearance. These checks run in the central authorization service; administrator authority does not bypass clearance.

Organization administrators can set or clear a table classification override through PATCH /query-catalogs/{catalogName}/classification, with workspaceEnvironmentId, schemaName, tableName, nullable classification and optional clearOverride. Both the existing and proposed effective label must be within the administrator's clearance. Clearing restores the manifest label. The change is audited.

Pause, source removal, descriptor changes, credential rotation and stale generations revoke new reads until the current registration is active again. Changes do not promise immediate cancellation of a query already running.

Results have a maximum of 1,000 rows and an 8 MiB serialized row payload. A larger explicit LIMIT cannot bypass the row maximum. Each query has the configured API Table deadline and an engine deadline. Request cancellation closes the query's owned driver connection; remote database work may take longer to stop. Concurrency limits apply per API Table process. Oversized results and unavailable authorization produce errors rather than an empty successful result.

Endpoint from a connection

ingest.endpoint either writes the host, port, database and trustedCaRef in the manifest, or names the connection fields that hold them: endpoint: { endpointRefs: { host: 'dbHost', port: 'dbPort', database: 'dbName' }, trustedCaRef: 'partner-ca' }. Each named field is a type: 'text' config field the extension declares, and each value is held to the same rule as a literal one. The trust anchor may come from a field too (endpointRefs.trustedCaRef), but is named exactly once; TLS always verifies the server's identity. The source's credentialRef names a basicAuth credential of the extension. See Endpoints from a connection.

Tables chosen at install

Set ingest.selectable: true to let the person adding the source choose its tables instead of listing them in the manifest. tables may then be empty (or hold a suggested first choice). Each installed source is its own copy with its own tables: an extra copy of the source starts with none, and re-releasing the extension keeps every installed copy's choice as long as the release is still selectable and still admits those tables.

ingest: {
  mode: 'query',
  engine: 'postgres',
  intervalSec: 3600,
  selectable: true,
  endpoint: { endpointRefs: { host: 'dbHost', port: 'dbPort', database: 'dbName' }, trustedCaRef: 'warehouse-ca' },
  tables: [],
}

While choosing, a person browses the database — its schemas, then a schema's tables, then a table's column types — through the source's own catalog, which already holds the connection's read-only login. Browsing needs Use on the connection the source is set up with; without it, browsing is refused. A table list shows no row-count estimate or primary key: PostgreSQL and MySQL keep them where the catalog cannot read them, so an upsert table's key is a column the person names. A column preview shows names and types only, never a row. Tables already chosen show the classification marking their reads carry. Only lowercase identifiers can be chosen; other names are listed but not selectable.

Each chosen table is an ordinary entry of tables with six optional settings:

A table with no tablePK and no timestampColumn cannot be chosen: every run would copy all of it again. A table installed before this rule that appends without a timestampColumn stops with mirror_increment_missing instead of adding its rows again; naming a timestampColumn resumes it, and its first run copies every row that has a value once more before it copies only new ones.

A destination's name, key, timestamp column and history are fixed once it has been written to: a selection that would change them is refused when it is chosen (data_source_table_destination_exists, naming each table), and the table is never rewritten. An append that already copies by a timestampColumn keeps it. Its columns only grow, as newColumns lets them: a column the remote table drops or retypes stops the mirror with mirror_destination_changed, whatever the setting. Changing the chosen tables re-registers the source's catalog, so browsing and mirroring wait until it is active again.

Scheduled mirrors

Declare ingest.intervalSec: 300 once on the query source. A table may declare tablePK: ['id'] and timestampColumn: 'updated_at'. Declared keys select UPSERT; remote deletions are not synchronized. Without keys, a table appends incrementally: each run reads only the rows whose timestampColumn is newer than the newest one the last completed run copied (WHERE updated_at > …), and appends those, each stamped with the run's __snapshotAt. The position moves only when every batch of a run has committed, so a run that fails or is retried reads the same rows again rather than skipping any. Remote __snapshotAt columns are refused.

An UPSERT run re-reads the table and writes only the rows whose values changed: a row whose values are what the destination already holds is not rewritten and keeps the _run_id and _loaded_at of the run that last changed it, and a run that changes nothing writes no rows. A keyed table without a declared timestamp keeps the time a row was read in __snapshotAt; that time is not compared, so an unchanged row keeps the time it was first read with its current values, and the table keeps no history. Schema comes from driver metadata, including empty results; unsupported types, unsafe precision and invalid keys fail the mirror before publication.

Older experimental per-table intervals enable mirroring only when all are identical and at least 60 seconds. Mixed or shorter intervals leave live queries available and show mirroring as disabled until an explicit source upgrade. Saved descriptors and their version digests are not silently rewritten.

A mirror is scheduled by the platform's ingestion service — the same scheduler that runs poll sources — which every default installation runs. One replica holds the source's lease and asks for a mirror run when it is due: at the source's own fixed point in its interval, never later than one interval after the last sync, or at once when someone chooses Sync now. The run itself stays in the table engine. The database is read through the catalog, never by the ingestion service, which never receives the database credential. A batch is written only while the run still holds the source's lease, so two replicas never write one mirror at once. Sync now on a source read only in place has nothing to run; on a paused source it is refused until the source is resumed.

Each remote table has a distinct managed destination. The platform uses narrow internal mirror authorization, exact current source/environment/catalog bindings and the organization's active classification scheme. The destination must have an equal or higher representable classification. If no managed classification can represent it, mirroring reports mirror_classification_unrepresentable; live query authorization remains independent. Mirror failures do not automatically pause live catalog access. An operator pause stops both new live reads and new mirror publication; resume enables the next scheduled attempt. Removal drops all source-owned mirror destinations.

Open a source's details in Analytics to see Live catalog, mirror cadence, destination links and per-table history. An incomplete save never means a completed snapshot. An empty table can complete with zero rows and no fabricated write. Previously confirmed batches may remain visible after a later commit fails; a run is complete only after full remote EOF and all canonical write acknowledgements.

A scan has a 30-second deadline and an 8 MiB durable spool limit. Batches contain at most 1,000 rows and 768 KiB. A mirror run stops scheduling further batches after five minutes and uses the existing query admission limits; an already authorized in-flight write may finish. These limits fail explicitly rather than truncating a successful snapshot. Retried saves use the same retained bytes, receipt IDs and snapshot timestamp. If a source change revokes an unconfirmed run whose batches may already be in its destination table, its bytes remain retained and mirror_recovery_required blocks a new run; operator recovery is needed before those uncertain publication records can be discarded. A run whose destination table was never created cannot have written anything, so a source change simply replaces it. Completed and failed scan payloads are cleaned, and history retains at most 50 runs per destination.

Each batch is written exactly once. The destination keeps the last batch it committed for the mirror — its run and its place in the run — so a batch saved again after a retry or a takeover is recognised as written however late it comes, and a mirror can run for as long as it is scheduled: there is no limit on how many batches a destination takes. A batch is bound to the rows the scan stored, not to the rows written, so a data-protection rule that masks a value in a batch does not stop it from being written.

Removal first pauses a query source. If scanning, destination provisioning or a save is still running, removal returns mirror_operation_in_progress and keeps the source paused; retry removal after that operation finishes. Resume, source upgrades and registry pruning cannot reopen a source once removal has started.

An interrupted scan, provisioning step or save keeps its operation record. The next mirror run of the source takes that record over only once it holds a newer fence of the source's lease; it then resumes the same batches under the same receipt IDs, so nothing is lost or written twice. A record left before this takeover existed, which carries no fence, still needs an operator. The record is never cleared by time, and there is still no force-clear API: deleting an uncertain record could permit a late create or save after its destination was removed. mirror_operation_recovery_required means an ambiguous external failure: an operator must establish the outcome of that exact operation before mirroring or removal can continue. A destination that StarRocks refuses to create — for example when no backend has the disk space or replicas the table asks for — is not ambiguous: nothing was created, the operation is released, and the next mirror run provisions it again. The source detail panel shows pending removal and recovery separately from ordinary mirror history.