Versions Compared

Old Version 13

changes.mady.by.user Former user

Saved on

compared with

New Version Current

changes.mady.by.user Virginia Agnew

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Panel
title:Warning
title:Warning
borderColorblack
bgColor#ffff00
borderStylesolid

(warning) PLEASE NOTE: This documentation applies to Pentaho 8.1 and an earlier version. For Pentaho 8.2 and later, see Table Output on the most recent documentation, visit the Pentaho Enterprise Edition documentation site.

Description

The Table Output step allows you to load data into a database table. Table Output is equivalent to the DML operator INSERT. This step provides configuration options for target table and a lot of housekeeping and/or performance-related options such as Commit Size and Use batch update for inserts.

...

Option

Description

Step name

Name of the step; this name has to be unique in a single transformation.

Connection

The database connection to which data is written

Target Schema

The name of the Schema for the table to write data to. This is important for data sources that allow for table names with periods in them.

Target table

The name of the table to which data is written.

Commit size

Use transactions to insert rows in the database table. Commit the connection every N rows if N is larger than zero (0); otherwise, don't use transactions. (Slower)

Note: Transactions are not supported on all database platforms.

Truncate table

Select if you want the table to be truncated before the first row is inserted into the table

Note: Don't use this option when you are running the transformation clustered or multiple step copies! In this case, truncate the table before the transformation starts, for example in a job.


Ignore insert errors

Makes Kettle ignore all insert errors such as violated primary keys. A maximum of 20 warnings will be logged however. This option is not available for batch inserts.

Specify database fields

Enable this option to specify the fields in the Database fields tab. Otherwise all fields are taken into account by default.

Partition data over tables

Use to split the data over multiple tables. For example instead of inserting all data into table SALES, put the data into tables SALES_200510, SALES_200511, SALES_200512, ... Use this on systems that don't have partitioned tables and/or don't allow inserts into UNION ALL views or the master of inherited tables. The view SALES allows you to report on the complete sales:

Code Block
CREATE OR REPLACE VIEW SALES AS
SELECT * FROM SALES_200501
UNION ALL
SELECT * FROM SALES_200502
UNION ALL
SELECT * FROM SALES_200503
UNION ALL
SELECT * FROM SALES_200504
...

Use batch update for inserts

Enable if you want to use batch inserts. This feature groups inserts statements to limit round trips to the database. This is the fastest option and is enabled by default.

Note: There are limiting factors depending on the used database type and further step options. The batch mode is only used when this check box is selected

  1. and the Commit Size is greater than 0
  2. and the Return auto-generated key option is not enabled
  3. and the transformation is not enabled to use unique connections (Transformation settings / Misc / Make the transformation transactional)
  4. and the following rule is false: the database type supports safe points and step error handling is enabled (see the database feature list to check if the database supports safe points)
  5. and the database type supports batch updates (see the database feature list to check this)                         

Is the name of the table defined in a field?

Use these options to split the data over one or more tables; the name of the target table is defined in the field you specify. For example if you store customer data in the field gender, the data might end up in tables M and F (Male and Female). There is an option to exclude the field containing the tablename from being inserted into the tables.

Field that contains name of table

When the option "Is the name of the table defined in a field?" is enabled, enter the field name to use here.

Store the table name field

When the option "Is the name of the table defined in a field?" is enabled, you can chose is this field should we written to the table or not.

Return auto-generated key

Enable if you want to get back the key that was generated by inserting a row into the table

Name of auto-generated key field

Specifies the name of the new field in the output rows that contains the auto-generated key

SQL

Generates the SQL to create the output table automatically

...