Zum Hauptinhalt springen

SQL Database

Category: Database
Node id: sql-connector
Maturity: beta
Risk tier: B

Read, write and run SQL against any supported engine through one node: a saved connector of any SQL engine, or an inline connection with an engine selector. Reads, keyed record writes, DDL/DML statements and transactions; writes need a connection with read_only=false.

What it does​

  • One node for every SQL engine the build links (plus any ODBC driver in odbc builds), selected by connector or dialect
  • Read-only queries, schema exploration and table previews
  • Keyed insert/upsert/update/delete with per-record or atomic error policy
  • DDL/DML statements and multi-statement transactions with structured engine errors

Use cases​

  • Load a hospital extract from Oracle or SQL Server into the analytics database
  • Upsert rows produced upstream into a staging table keyed by identifier
  • Run a verification query after a bulk load and branch on the result
  • Explore an unknown database's schema before mapping it

Runtime​

  • Effect: write

Input ports​

NameTypeRequiredDescription
recordsjsonNoRecords to write when operation=write
service_paramsjsonNoParameters for operation=service; merged over the service_params config

Output ports​

NameTypeRequiredDescription
recordsjsonNoRows returned by operation=read, one object per row
countnumberNoRows read (operation=read)
writtennumberNoRecords written (operation=write)
errorsjsonNoPer-record failures (operation=write with on_error=continue)
service_idtextNoService invoked (operation=service)
resultjsonNoResult of operation=service: rows/columns for sql_query, rows_affected for sql_execute, per-statement results for sql_transaction

Settings​

Set at least one of: connector_id, dialect.

Basic​

SettingNameTypeRequiredDefaultAllowed valuesDescription
Connectorconnector_idconnector-selectNoA saved SQL connector of any engine (PostgreSQL, MySQL, SQL Server, Oracle, SAP HANA, Snowflake, …). The engine comes from the connector. Leave empty to configure the connection inline below.
OperationoperationenumYesread, write, serviceread runs the SELECT in sql (read-only transaction, capped by limit); write applies the records input to table (insert, upsert, update, delete by key_columns); service calls sql_query, schema_explorer, table_preview, sql_execute or sql_transaction.
SQL (read)sqltextNoSELECT to run when operation=read.
Row limit (read)limitnumberNo1000Maximum rows for operation=read (engine cap 1000).
Write operationwrite_operationenumNoinsertinsert, upsert, update, deleteWhat operation=write does with the records input.
Table (write)tablestringNoTarget table for operation=write.
Schema (write)schemastringNoSchema of the target table, if any.
Key columns (write)key_columnsstringNoComma-separated key columns for upsert, update and delete.
On error (write)on_errorenumNorollbackrollback, continuerollback: one transaction, a bad record undoes the batch. continue: commit record by record and list the failures in errors.
Serviceservice_idstringNoService to call when operation=service: sql_query, schema_explorer, table_preview (read-only); sql_execute, sql_transaction (need Allow Non-Read-Only Service and a connection with read_only=false).
Service Parametersservice_paramsjsonNoParameters for the service, as JSON. May also be supplied on the service_params input port.

Connection​

SettingNameTypeRequiredDefaultAllowed valuesDescription
EnginedialectenumNoDatabase engine for an inline connection (ignored when a connector is selected). Ignored when connector_id is set.
HosthoststringNoDatabase host for an inline connection. Ignored when connector_id is set.
PortportnumberNoDatabase port; the engine's default when empty. Ignored when connector_id is set.
DatabasedatabasestringNoDatabase (or service) name. Ignored when connector_id is set.
UsernameusernamestringNoLogin user for the inline connection. Ignored when connector_id is set.
PasswordpasswordsecretNoLogin password for the inline connection. Prefer a saved connector so it is stored encrypted. Ignored when connector_id is set.
File path (sqlite, duckdb)pathstringNoDatabase file for the file-based engines. Ignored when connector_id is set.
Read-onlyread_onlybooleanNotrueDefault true. Set false to allow operation=write, sql_execute and sql_transaction on an inline connection. Ignored when connector_id is set.
Engine optionsconnection_optionsjsonNoAny other field of the engine's connector, as JSON: e.g. {"sslmode":"verify-full","sslrootcert":"/etc/hydra/ca.pem"} for PostgreSQL, {"service_name":"HISPROD"} for Oracle, {"encrypt":"true"} for SQL Server. Ignored when connector_id is set.

Advanced​

SettingNameTypeRequiredDefaultAllowed valuesDescription
Allow Non-Read-Only Serviceallow_write_servicebooleanNofalseRequired to run sql_execute or sql_transaction. Off by default so a step added to look at a database cannot change it.

Runtime notes​

  • With a connector the engine is the connector's; the dialect and connection fields are ignored.
  • Placeholders follow the engine: $1 (PostgreSQL), @p1 (SQL Server), :1 (Oracle), ? (MySQL, SQLite, others).
  • A connection saved or configured with the default read_only=true refuses writes with an error naming read_only.
  • Reads are capped at 1000 rows per step; bulk and incremental loads run through the estate transform engine.

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

Etwas unklar? Senden Sie Feedback zur Dokumentation per E-Mail..