dboutput

Converts input data into SQL queries and loads them into an external database server. The JDBC profile must be configured in advance.

Command properties

Syntax

Options

type={insert|update}

SQL query type to execute. Default: insert

• insert: Executes INSERT statements.
• update: Runs a SELECT query based on key fields. If the record exists, executes UPDATE; if not, executes INSERT.

database=STR

Name of the database or schema to switch to after connecting.

table=STR

Name of the target table to load data into.

batchsize=INT

Transaction batch size. A larger batch size is more efficient because more records are committed at once, but the number of records rolled back on transaction failure also increases. A value of 2,000 is recommended. Without specifying a batch size, each record is auto-committed individually, which can be very slow. Default: 1

rowretry=BOOL

Whether to retry on a per-row basis when a batch transaction fails. When set to t, the failed batch is broken into individual rows for retry. This may reduce performance but minimizes data loss. Default: f

stoponfail=BOOL

Whether to cancel the query on commit failure. When set to t, the entire query is cancelled when a commit fails. If stoponfail=t, the rowretry option is ignored. Default: f

Target

PROFILE

JDBC connection profile name. Connection profiles can be configured in the web console.

[+]FIELD, ...

List of fields to load into the database. Separate multiple fields with commas (,). Prefix a field name with + to designate it as a key field. Field names must match the column names of the target table.

Input fields

Error codes

Parse errors

Runtime errors

N/A

Description

The dboutput command converts all records passed from the previous command into SQL queries and loads them into an external database.

With type=insert (default), each record is converted to an INSERT statement and executed. When key fields are specified, records with duplicate key values within the same batch are ignored.

With type=update, a SELECT COUNT(*) query is run first based on key fields. If the matching record exists, an UPDATE statement is executed; otherwise, an INSERT statement is executed. When only key fields are specified with no non-key fields, records that already exist are ignored.

When a batch transaction fails, behavior depends on the stoponfail and rowretry options:

• stoponfail=t: The entire query is cancelled.
• rowretry=t: The failed batch is broken into individual rows for retry. If a row fails, _error_msg, _error_code, and _sql_state fields are added to that record.
• Both options are f: All records in the failed batch receive the error fields.

The connection is automatically re-established if it is lost.

In a distributed environment, execution runs on the Control Node.

Examples

1. Loading data in INSERT mode

   json "[{'name': 'Alice', 'age': 30}, {'name': 'Bob', 'age': 25}]"
   | dboutput mydb table=employees name, age
   

   Inserts name and age fields into the employees table in the database connected via the mydb profile.

2. Loading with a specified batch size

   table duration=1d web_logs
   | dboutput mydb batchsize=2000 table=access_logs src_ip, method, status, bytes
   

   Loads large amounts of data efficiently with a batch size of 2,000.

3. Loading data in UPDATE mode

   json "[{'id': 1, 'name': 'Alice', 'score': 95}, {'id': 2, 'name': 'Bob', 'score': 88}]"
   | dboutput mydb type=update table=students +id, name, score
   

   Uses the id field as the key to UPDATE if the record exists, or INSERT if it does not.

4. Enabling per-row retry

   table duration=1h web_logs
   | dboutput mydb batchsize=2000 rowretry=t table=access_logs src_ip, method, status, bytes
   

   Retries on a per-row basis when a batch transaction fails to minimize data loss.

5. Switching the database schema

   json "[{'item': 'widget', 'qty': 100}]"
   | dboutput mydb database=inventory table=items item, qty
   

   Switches to the inventory database after connecting and loads the data.