Database targets include relational sources such as Oracle, MySQL, and Microsoft SQL Server.
When you configure a Target transformation for a database target, you can write data to a single target table. You can select an existing table or create the table at run time.
Ensure that the table and column names do not exceed 74 characters.
Database target properties
You configure database target properties on the Target tab of the Properties panel.
The following table describes the database target properties:
Property
Description
Connection
Name of the target connection.
Alternatively, you can define a parameter, and then specify the connection in the mapping task.
Target Type
Target type, either single object or parameter.
Object
Name of the target object.
If you select a single object, you can also preview the data.
Operation
Target operation, either insert, update, upsert, delete, or data driven.
Truncate Target
Truncates the target object before inserting new rows.
Applies to insert and data driven operations.
Enable Target Bulk Load
Uses the database bulk API to perform an insert operation.
Use the bulk API to write large amounts of data to the database with a minimal number of API calls. Loading in bulk mode can improve performance, but it limits the ability to recover because no database logging occurs.
Applies to insert operations.
Update Columns
The fields to use as temporary primary key columns when you update, upsert, or delete target data. When you select more than one update column, the mapping task uses the AND operator with the update columns to identify matching rows.
Applies to update, upsert, delete and data driven operations.
Data Driven Condition
Enables you to define expressions that flag rows for an insert, update, delete, or reject operation.
For example, the following IIF statement flags a row for reject if the ID field is null. Otherwise, it flags the row for update:
IIF (ISNULL(ID), DD_REJECT, DD_UPDATE )
Applies to the data driven operation.
Forward Rejected Rows
Causes the mapping task to forward rejected rows to the reject file.
If you do not forward rejected rows, the mapping task drops rejected rows and writes them to the session log.
If you enable row error handling, the mapping task writes the rejected rows and the dropped rows to the row error logs instead of generating a reject file. If you want to write the dropped rows to the session log in addition to the row error logs, you can enable verbose data tracing.
Pre SQL
SQL command to run against the target before reading data from the source.
You can enter a command of up to 5000 characters.
Post SQL
SQL command to run against the target after writing data to the target.
You can enter a command of up to 5000 characters.
Update Override
Overrides the default UPDATE statement for the target.
Enter the update statement. Alternatively, click Configure to generate the default UPDATE statement, and then modify the default statement.
The UPDATE statement that you enter overrides the default UPDATE statement that Data Integration uses to update targets based on key columns. You can define an override UPDATE statement to update target tables based on non-key columns.
Reject File Directory
Directory path to write the reject file. By default, the mapping task writes all reject files to the following service process variable directory:
$PMBadFileDir/<federated task ID>
If you specify both the directory and file name in the Reject File Name field, clear this field. The mapping task concatenates this field with the Reject File Name field when it runs the task.
Reject File Name
File name, or file name and path of the reject file. By default, the mapping task names the reject file after the target object name: <target name>.bad.
The mapping task concatenates this field with the Reject File Directory field when it runs the task. For example, if you have C:\reject_file\ in the Reject File Directory field, and enter filename.bad in the Reject File Name field, the mapping task writes rejected rows to C:\reject_file\filename.bad.
For more information about database target properties, see the help for the appropriate connector.
Vector database target properties
In advanced mode, you can write vectors to a vector database such as Pinecone. You can configure the target properties, advanced properties, and target fields.
Target properties
The following table describes the vector database target properties:
Property
Description
Connection
Name of the target connection.
Alternatively, you can define a parameter, and then specify the connection in the mapping task.
Target Type
Target type, either single object or parameter.
Object
Name of the target object.
Operation
Target operation. You can use only upsert.
Update Columns
The fields to use as temporary primary key columns when you upsert target data. When you select more than one update column, the mapping task uses the AND operator with the update columns to identify matching rows.
If you generate an ID within the mapping, the ID is dynamic and might not be consistent with existing IDs in the vector database. If a generated ID matches an existing ID in the vector database, the Target transformation replaces the row in the vector database, but the vector might not correspond to the same data.
For more information about the target properties, see the help for the appropriate connector.
Advanced properties
The following table describes the advanced properties:
Property
Description
Batch Size
Number of operations for the vector database to process at the same time.
Namespace
Namespace in the vector database where you want to store the vectors.
Target fields
The following table describes the target fields that a Target transformation might write to a vector database:
Property
Description
Vector ID
Vector identifier to store in the vector database. The ID helps to quickly access the vector representation for efficient storage, indexing, and retrieval operations.
To create vector IDs, you can use the UUID_STRING function with no arguments in an Expression transformation or you can use a Sequence Generator transformation that uses a shared sequence across all mappings that load data to the same index in the vector database.
Vector
Array of doubles that represents the vector embedding.
You can use a Vector Embedding transformation to create vector embeddings.
Metadata
Metadata to write to the vector database as a struct. Includes all incoming fields except for the vector and vector ID fields. The metadata field contains a list of key-value pairs in JSON format.
The target fields can differ based on the vector database. For more information about target fields, see the help for the appropriate connector.
Database targets created at run time
If a mapping includes a database target, you can select an existing target table or create the target table at run time. When you create a database target at run time, Data Integration automatically discovers the target object metadata for data type, precision, and scale, based on the data source.
If you need to edit target object metadata, you can edit it in the Source transformation.
You cannot link the target fields to the upstream transformation. If you want to reduce the number of unused fields in the target, configure field rules in the Target transformation or in the upstream transformations.
When you create a database target at run time, the mapping task creates the database table the first time the mapping runs based on the fields from the upstream transformation.
In subsequent runs, the mapping task replaces the data in the target table that was created in the initial run. Consequently, if you change the mapping after the initial run, in subsequent runs the target will not reflect changes to the number of target fields and its metadata. To see the changes, you can either delete the existing target before you run the mapping or change the name of the target.
If you create a relational target at run time, the target operation is always insert. You can choose to truncate the target.
Note: In mappings created before the Spring 2020 September release, Data Integration converts Bigint data from a parameterized source to Int data in database targets created at runtime. To write Bigint data to the target without conversion, edit the mapping in the Mapping Designer and enable the option in the mapping advanced properties.
Data Integration does not convert Bigint data in mappings created after the Spring 2020 September release.
Creating a database target at run time
To create a database target at run time, select Create New at Runtime in the Target Object dialog box and enter the target table name.
1On the Target tab of the Target transformation, select a database connection.
2Set the target type to Single Object.
3Click Select to select the target object.
4In the Target Object dialog box, select Create New at Runtime.
5 Enter the target table name.
6Click OK.
Update columns for relational targets
You can configure one or more fields as update columns in relational targets. Update columns are columns that uniquely identify rows in the target table. The mapping task uses them to update, upsert, or delete data in the target.
Configure update columns when the target table does not contain a primary key and the mapping uses an update, upsert, or delete operation. When you select more than one update column, the mapping uses the AND operator with the update columns to identify matching rows.
When you run the mapping, it uses the field mapping to match rows in the upstream transformations to the target table. If the mapping task matches an incoming row to multiple target rows, it performs the specified task operation on all matched target rows.
When you use a parameter for the target connection or target object, you can configure update columns in the mapping task.
Configuring update columns
You can configure update columns when you use the update or upsert operation to update data in a relational target.
1In the Properties panel, click the Target tab.
2Select a relational connection.
You can also use a connection parameter for a relational database connection type.
3Select the target type that you want to use.
4Select a target object.
5Select the update or upsert operation.
6To select update columns, click Add.
The Update Columns window displays all target columns.
7Move the fields that you want to use from the Target Columns list to the Update Columns list.
8Click OK.
Target update override
By default, Data Integration updates target tables based on key values. However, you can override the default UPDATE statement for each target in a mapping. You might want to update the target based on non-key columns.
You can enter a target update override for relational and ODBC connections. For information on the specific steps to override the default target UPDATE statement, see the help for the appropriate connector.
Override the UPDATE statement in the Target transformation advanced properties. Enter the target UPDATE statement in the Update Override field. In some relational connections, you can generate the default UPDATE statement. Click Configure to generate the default UPDATE statement and then modify the statement.
Because the target fields must match the target column names, the update statement includes the keyword :TU to specify the fields in the target transformation. If you modify the UPDATE portion of the statement, you must use :TU to specify fields.
When you override the default UPDATE statement, you must enter an SQL statement that is valid for the database. Data Integration does not validate the syntax.
Example
A mapping passes the total sales for each salesperson to the T_SALES table.
Data Integration generates the following default UPDATE statement for the target T_SALES:
UPDATE T_SALES SET EMP_NAME = :TU.EMP_NAME, DATE_SHIPPED = :TU.DATE_SHIPPED, TOTAL_SALES = :TU.TOTAL_SALES WHERE EMP_ID = :TU.EMP_ID
You want to override the WHERE clause to update records for employees named Mike Smith only. To do this, you edit the WHERE clause as follows:
UPDATE T_SALES SET DATE_SHIPPED = :TU.DATE_SHIPPED, TOTAL_SALES = :TU.TOTAL_SALES WHERE :TU.EMP_NAME = EMP_NAME AND EMP_NAME = 'MIKE SMITH'
Guidelines for configuring the target update override
Use the following guidelines when you enter target update queries:
•If you use target update override, you must manually put all database reserved words in quotes.
•You cannot override the default UPDATE statement if a target column name contains any of the following characters:
' , ( ) < > = + - * / \ t \ n \ 0 <space>
•If you update an individual row in the target table more than once, the database only has data from the last update. If the mapping does not define an order for the result data, different runs of the mapping on identical input data may result in different data in the target table.
• A WHERE clause that does not contain any column references updates all rows in the target table, or no rows in the target table, depending on the WHERE clause and the data from the mapping. For example, the following query sets the EMP_NAME to "MIKE SMITH" for all rows in the target table if any row of the transformation has EMP_ID > 100:
UPDATE T_SALES SET EMP_NAME = 'MIKE SMITH' WHERE :TU.EMP_ID > 100
• If the WHERE clause contains no field references, the mapping updates the same set of rows for each row of the mapping. For example, the following query updates all employees with EMP_ID > 100 to have the EMP_NAME from the last row in the mapping:
UPDATE T_SALES SET EMP_NAME = :TU.EMP_NAME WHERE EMP_ID > 100
•If you set the target operation to update or upsert, configure the mapping task to treat source rows as update in the advanced session properties.