Table Output
The Table Output step loads data into a database table. Table Output is equivalent to the SQL INSERT operator.
If you only need to update rows, use the Update step.
To perform both
INSERTandUPDATE, use Insert/Update.
This step provides configuration options for a target table and performance-related options such as Commit size and Use batch update for inserts.
If you insert a record into a database table that has identity columns, the JDBC driver might return the auto-generated key. This is not supported on all database types.
General
Step name
Specifies the unique name of the Table Output step on the canvas. You can customize the name or leave it as the default.
Connection
Select the database connection where data will be written.
If you do not have an existing connection, select New or Wizard. To modify an existing connection, select Edit.
Target schema
Database schema name.
Target table
Table name where data is written.
Commit size
Number of INSERT statements to run before sending a COMMIT command. Transactions are not supported on all database platforms.
Truncate table
Select to truncate the table before the first row is inserted. If you run multiple copies or clusters, truncate before running the transformation.
Ignore insert errors
Select to ignore insert errors such as primary key violations. A maximum of 20 warnings are logged. Not available for batch inserts.
Specify database fields
Select to define fields in the Database fields section. Required to use Get fields and Enter field mapping.
SQL (button)
Opens the Simple SQL Editor, which can generate SQL to create the output table.
Main options
Partition data over tables
Split data over multiple tables based on a date field specified in Partitioning field. Target table names follow <target-table>_<date-format>. You must create these tables before running the transformation.
Partitioning field
Date field used to generate dated table names when partitioning.
Partition data per month / Partition data per day
Table name date format: yyyyMM (month) or yyyyMMdd (day).
Use batch update for inserts
Select to group INSERT statements into batches to reduce round trips.
Batch mode is used only when:
Use batch update for inserts is selected
Commit size is greater than 0
Return auto-generated key is cleared
The transformation does not use unique connections
The database supports batch updates
Is the name of the table defined in a field?
Select to take the target table name from a field (specified in Field that contains name of table).
Field that contains name of table
Field that contains the target table name for the current row.
Store the table name field
Select to store the table name in the output. The table must contain a column with the name defined by Field that contains name of table.
Return auto-generated key
Select to return the key generated by inserting a row into the table.
Name of auto-generated key field
Output field name for the auto-generated key.
Database fields
The Database fields section specifies which stream fields are inserted into which database columns.
Table field
Database column that receives the data.
Stream field
Incoming stream field whose value is inserted.
Get fields (button)
Imports incoming fields into the table (requires Specify database fields).
Enter field mapping (button)
Opens the field mapping window (requires Specify database fields).
Enter field mapping
Source fields
Field names from the incoming stream.
Target fields
Fields in the output table.
When using this step with Spark on AEL, output field types are converted for processing.
Add (button)
Adds the selected source and target pair to Mappings.
Delete (button)
Removes the selected mapping.
Mappings
Field-to-column mappings.
Auto target selection?
Automatically select a target.
Hide assigned source fields?
Hide a source field after it is mapped.
Auto source selection
Automatically select a source field when a target column is selected.
Hide assigned target fields?
Hide a target column after it is mapped.
Guess (button)
Automatically matches fields and populates Mappings.
Metadata injection support
You can use ETL metadata injection to pass metadata at runtime.
The following fields support metadata injection.
Options
Target schema
Target table
Commit size
Partitioning field
Store the table name field
Name of auto-generated key field
Truncate table
Specify database fields
Ignore insert errors
Use batch update for inserts
Partition data over tables
Partition data per
Is the name of table defined in a field
Return auto-generated key
Connection
Database fields
Table field
Stream field
Last updated
Was this helpful?