Aller au contenu principal

Intake SQL Export

Category: Data
Node id: intake-sql-export
Maturity: beta
Risk tier: B

Export every table of a restored Oracle dump delivery from the read-only staging database into Parquet, reconciling COUNT(*) against the manifest and the exported rows, and hand the generated delivery to intake-verify.

What it does​

  • phi
  • database

Use cases​

  • Turn an Oracle Data Pump delivery restored on the hospital staging container into a verifiable Parquet delivery
  • Prove every restored table holds exactly the rows the hospital manifest declared

Runtime​

  • Performance tier: slow

Input ports​

NameTypeRequiredDescription
triggeranyNoOptional upstream signal; its content is not read.

Output ports​

NameTypeRequiredDescription
generated_delivery_idtextNoId of the generated delivery now waiting in incoming/: <delivery_id>-export.
tablesjsonNoPer table: staging COUNT(*), exported rows and the manifest row count.
successbooleanNoTrue when every table reconciled and the delivery reached incoming/.

Settings​

Basic​

SettingNameTypeRequiredDefaultAllowed valuesDescription
Staging Connectorconnector_idstringYesSaved oracle, mssql or postgres connector for the hospital-side staging database, connecting as the read-only user.
Source Delivery IDdelivery_idstringYesThe accepted dump delivery whose tables were restored by the runbook.
Intake Rootintake_rootstringYesAbsolute path of the intake volume holding incoming/, accepted/ and quarantine/ (pilot: /srv/hgugm/intake).
Intake ProfileprofilestringNohgugm-cardiology-v1Intake profile whose table contracts give the columns to export and their declared types.

Advanced​

SettingNameTypeRequiredDefaultAllowed valuesDescription
Expected DB Userexpected_userstringNoHYDRA_ROThe staging read-only user. The export refuses to run when the connection is any other user.
Row Group Sizebatch_sizenumberNo5000min 100; max 100000Rows per Parquet row group.

Errors​

CodeDescription
COUNT_MISMATCHStaging COUNT(*), exported rows and the manifest row_count are not all equal
IDENTIFIER_INVALIDA manifest or profile identifier failed the allowlist; no SQL was executed
NOT_READ_ONLYThe connector is not an oracle/mssql/postgres connector configured read_only and connected as expected_user
DELIVERY_NOT_ACCEPTEDThe source dump delivery is not accepted
EXPORT_EXISTSThe generated delivery id is already in incoming/, accepted/ or quarantine/
EXPORT_LOCKEDAnother run is exporting the same dump delivery; retry after it finishes
STORENo verdict store or connector resolver is configured

Runtime notes​

  • Reads only: COUNT(*) goes through the connector's guarded sql_query service, rows stream through a read-only transaction, and the connector must be configured read_only and connect as expected_user.
  • Every identifier is checked against the allowlist before any SQL is built; nothing runs when one fails.
  • Output is written under staging-export/ and renamed into incoming/ only when every table reconciled; on failure nothing appears in incoming/.
  • Values are exported as text in the profile's declared layouts; intake-verify then parses every one against the declared type.
  • The dump itself is restored by scripts/hgugm/restore_oracle_dump.sh, never by Hydra.

See Build any workflow for how to put nodes together and Ports, types and data flow for how port types connect.

Quelque chose n'est pas clair ? Envoyez vos commentaires sur la documentation par courriel.