...
You can use this Snap to insert data parallelly across multi-threaded tasks. This Compared to the Oracle Bulk Load Snap, this Snap executes a high-performance data load compared to the Oracle - Bulk Load Snap.
The Parallel Load Snap currently always auto-commits its database operations. The Auto commit setting in the Oracle Thin Account or Oracle Thin Dynamic Account configured for the Parallel Load snap does not affect the Snap’s behavior.
...
Snap Type
The Oracle - Parallel Load Snap is a Write-type Snap that writes the specified data in the target table.
...
Type | Format | Number of Views | Examples of Upstream and Downstream Snaps | Description |
---|---|---|---|---|
Input | Document |
|
| Requires the EDI data and internal ID as a JSON documentSchema name and Table name to process a certain number of input documents into a prepared insert query and execute all of the prepared input document values on that query as a batch. |
Output | Document |
|
| Snap outputs the number of documents successfully inserted into the target table in the output view document. |
Error | Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter when running the pipeline by choosing one of the following options from the When errors occur list under the Views tab:
Learn more about Error handling in Pipelines. |
...
Field Name | Field Type | Description | ||
---|---|---|---|---|
Label* Default Value: Oracle - Parallel Load | String | Specify the name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline. | ||
Schema name Default Value: N/A | String/Expression | Specify the database schema name. You can pass the values as expressions using the pipeline parameters but cannot use values from the upstream Snap. | ||
Table name* Default Value: N/A | String/Expression | Specify the table on which to execute the load operation. You can pass the values as expressions using the pipeline parameters but cannot use values from the upstream Snap. | ||
Create table if not present Default Value: Deselected | Checkbox | Select this checkbox to automatically create the target table if it does not exist.
The newly created table is not visible to subsequent database Snaps during runtime validation because of implementation details. If you want to immediately use the newly updated data, you must use a child pipeline invoked through the Pipeline Execute Snap. | ||
Maximum thread count*
| Integer/Expression | Specify the maximum number of threads allowed to perform data insertions simultaneously. If the value is 0, the snap automatically sets the number of threads equal to the number of available processors on the system.
| ||
Batch capacity*
| Integer/Expression | Specify the maximum number of records allowed in a single batch for an insertion task.
| ||
Snap memory usage: To gracefully handle failed record insertions, the Snap must retain some input document data in memory during the Snap execution. To calculate the memory usage follow this formula: | ||||
Maximum error count*
| Integer/Expression | Specify the maximum number of record insertion errors allowed before execution stops on the next error. For example, if you specify a value of 10, the pipeline execution continues until the number of failed records exceeds 10. A value of -1 means no limit. At least one more error document than the specified maximum value is expected, but task concurrency may result in additional error documents. This applies only when the error behavior is set to Route Error Data to Error View. | ||
Truncate table before load
| Checkbox/Expression | Select this checkbox to truncate the target table before loading data. | ||
Snap Execution Default Value: | Dropdown list | Select one of the following three modes in which the Snap executes:
|
Troubleshooting
Error | Reason | Resolution |
---|---|---|
| The target table may not exist or a database access error may have occurred when trying to execute the TRUNCATE statement. | Verify your database credentials and ensure the target table exists, then try again. |
|
The connection used to retrieve table metadata displayed an exception while closing. | Verify the target table column names and try again. |
Behavior of Parallel Load Execution with Open Error Views
The following table describes the expected behavior for the general outcomes of Parallel Load execution when the Snap has an open error view (that is, when you select Route Error Data to Error View for When error occurs).
Committed database insertions | Error view | Output preview | Snap outcome | Final Snap state |
---|---|---|---|---|
All insertions committed. | N/A | One document containing | All input records are successfully inserted to the target database. | Success |
One error document for each record that failed to insert. | One document containing | Some input records individually fail to insert into the target database, but do not exceed maximum error count. | Success | |
Insertions made successfully | Multiple error documents, one for each record that failed to insert. | One document containing | Enough input records individually fail to insert to exceed the Maximum error count and cause the Snap to end execution early. | Success |
Insertions made | One error document for | One document containing | An error occurs beyond the scope of any individual record. | Failure |
Individual record insertion failures occur when the database encounters an error during the insertion of a record. These failures are recoverable and specific to one input document. For example, an error might occur if the value of a document field is incompatible with the data type of the target column. |
Examples
...
Parallel Loading for Document Insertion
The following example pipeline demonstrates the use of Parallel Load to insert 1000 documents. It showcases Parallel Load's efficiency and capability in managing large-scale data insertion tasks.
Configure the Mapper Snap as follows.
Configure the Oracle - Parallel Load Snap with the following settings:
Maximum thread count: 4
Batch capacity: 100
...
On validation, the pipeline inserts a maximum of 400 records simultaneously, across 4 threads handling 100 records each. The image below displays only 50 records because the Preview document count is set to 50.
...
Download this Pipeline.
Handle Database Insertion Failures with Open Error View
This example demonstrates a Mapper Snap configuration designed to intentionally cause a small number of records to fail during database insertion. For every failed insertion, an error document is generated. Specifically, out of 1000 records, 7 are expected to fail, resulting in 7 error documents, while the remaining 993 records are successfully inserted. The count of successful insertions is also recorded in the output document. This pipeline demonstrates how to manage and log insertion errors efficiently.
...
Configure the Mapper Snap with settings so that a small number of records fail during database insertion, and route the error documents to the error view.
Configure the Oracle - Parallel Load Snap as follows to insert a total of 1000 documents.
...
On validation, 993 records are successfully inserted and written to the output document, while 7 error documents are written on the error view. The image below displays only 47 records because the Preview document count is set to 50.
Inserted records | Insertion failed records |
---|---|
Download this Pipeline.
Downloads
...
Expand | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
|
...
Related Content
...