You can use partitions to optimize performance for mapping tasks.
If a mapping task processes large data sets or includes transformations that perform complicated calculations, the task can take a long time to process. When you use multiple partitions, the mapping task divides data into partitions and processes the partitions concurrently, which can optimize performance. Not all source types support partitioning.
Enable partitioning when you configure the Source transformation in the Mapping Designer. When you configure partitions in the Source transformation, partitioning occurs throughout the mapping.
Each Source transformation across all data flows in the mapping must contain the same number of partitions.
To enable partitioning for a source, select a partitioning method on the Partitions tab. The partitioning methods that you can select vary based on the source type. For more information about partitioning different types of sources, see the help for the appropriate connector.
You can select one of the following partitioning methods based on the source type:
None
The mapping task processes all data in a single partition. This is the default option.
Fixed
The mapping task distributes rows of data based on the number of partitions that you specify. You can specify up to 64 partitions.
Use this method for a source type that does not allow key range partitioning such as a flat file source, or when the mapping includes a transformation that does not support key range partitioning.
Consider the number of records to be passed in the mapping to determine an appropriate number of partitions for the mapping. For a small number of records, partitioning might not be advantageous.
If the mapping includes multiple sources, specify the same number of partitions for each source.
Key range
The mapping task distributes rows of data based on a field that you define as a partition key. You select one field in the source as the partition key, and then you define a range of values for the partition key.
You can use this method for tabular sources such as relational, Google BigQuery, and JDBC V2 sources.
Key ranges can be of the following data types:
- String
- Any type of number data type. However, you cannot use decimals in key range values.
- Date/time type. Use the following format: MM/DD/YYYY HH24:MI:SS
If the mapping includes multiple sources, use the same number of key ranges for each source.
Pass through
The mapping task processes data without redistributing rows among partitions. All rows in a single partition stay in the partition. Choose pass-through partitioning when you want to create additional partitions to improve performance, but do not want to change the distribution of data across partitions.
You can use this method for sources such as Amazon S3, Netezza, and Teradata.
Dynamic
The mapping task determines the optimal number of partitions to create at runtime based on the source size.
You cannot partition a mapping in the following situations:
•The mapping includes a Web Services or Hierarchy Parser transformation.
•The mapping includes multiple sources that use custom relationships or advanced relationships.
•The mapping is a mapping in SQL ELT mode.
When you configure partitions, be sure to save and run the mapping in the Mapping Designer to validate the partition settings.
Partitioning rules and guidelines
Before you partition a mapping, note the following rules and guidelines:
•Consider the types of transformations in the mapping and the order in which transformations appear so that you do not get unexpected results. You can partition a mapping if the mapping task can maintain data consistency when it processes the partitioned data.
•For flat file partitioning, session performance is optimal with large source files. The load may be unbalanced if the amount of input data is small.
•When a Sequence Generator transformation is in a mapping with partitioning enabled, ensure that you set up caching in the Sequence Generator transformation. Otherwise, the sequence numbers the task generates for each partition are not consecutive.
•Sequence numbers generated by Normalizer and Sequence Generator transformations might not be sequential for a partitioned source, but they are unique.
•When a Sorter transformation is in a mapping with partitioning enabled, the task sorts data in each partition separately.
•A Sorter transformation must be placed before any Joiner transformation or Aggregator transformation that is configured to use sorted data.
•You cannot use in-out parameters for key range values.
•If your mapping has more than eight partitions, mapping task performance might degrade. You can configure the Buffer Block Size and DTM Buffer Size advanced properties in the mapping task to improve performance.
•On Linux, if a target table name includes a unicode character, you need to set the default locale to UTF-8 to support multibyte data. To set the default locale to UTF-8, see the following examples:
- For bash and related UNIX shells:
export LC_ALL=en_US.UTF-8
- For csh and related UNIX shells:
setenv LC_ALL en_US.UTF-8
•If you use key range partitioning on a source in a mapping in advanced mode, the mapping fails if all of the following conditions are true:
- The Data Integration Server processes the source.
- An advanced cluster processes a midstream transformation.
- The advanced cluster runs in a Google Cloud or Microsoft Azure environment.
Partitioning examples
The following examples show how you can configure partitioning in a mapping.
Partitioning with a Flat File Source
You have a mapping task that uses a large, 1GB flat file source. You want to specify two partitions in the Source transformation to optimize performance.
On the Partitions tab for the Source transformation, you select fixed partitioning and enter the number of partitions, as shown in the following image:
Key Range Partitioning with a Relational Database Source
You have customer names, addresses, and purchasing history in a relational database source. You decide to partition the source data into three partitions based on postal codes, using the following ranges:
•First partition: Minimum value to 30000
•Second partition: 30000 to 50000
•Third partition: 50000 to maximum value
On the Partitions tab for the Source transformation, you select key range partitioning and choose the BILLINGPOSTALCODE field as the partition key. You add three key ranges to create three partitions, as shown in the following image:
Note that for the first partition, you leave the start value blank for the minimum value. In the last partition, you leave the end value blank for the maximum value.
Using these values, records with a postal code of 0 up to 30000 are processed in partition #1, records with a postal code of 30000 to 50000 are processed in partition #2, and records with a postal code of 50000 or higher are processed in partition #3.
After you configure the mapping, you save and run the mapping to validate the partitions.
