Home | Table of Contents | Partitioning Output into Different Output Files | CloverETL 4.6.1
--- | --- | ---
Prev | Common Properties of Writers | Next

### Partitioning Output into Different Output Files

Some Writers let you part the incoming data flow and distribute the records among different output files. The components are:

CloverDataWriter
DBFDataWriter
FlatFileWriter
JSONWriter
SpreadsheetDataWriter
StructuredDataWriter
XMLWriter

#### Partitioning Criteria

You can part data according to number of records or classified according to values of specified fields.

#### Partitioning by Number of Records

Partitioning by number of records saves at most `N` records into one file. The other records are saved into another file until the limit is reached and so forth. Use Records per file attribute to set up the limit `N`.

Example: part 450 record into output files. Each output file has at most 100 record.

Solution: File URL value should contain $ sign(s). The $ signs will be replaced with digits.

Attribute | Value
--- | ---
File URL | ${DATAOUT_DIR}/output_$$.txt
Records per file | 100

#### Partitioning according to Data Field Value

Records can be parted into multiple output files according to a data field value. The field is specified with Partition key attribute.

The placeholder `#` in output file name can be replaced with a field value or with integer. If Partition file tag is set to Number file tag, the placeholder is replaced with integer. If Partition file tag is set to Key file tag, the placeholder is replaced with a field value. The default value is Number file tag.

The partition key consists of list of fields forming the partition key. The list has the form of a sequence of incoming record field names separated by semicolon.

Example: part data according to the `field1` field. Use the field value as part of output file name.

Attribute | Value
--- | ---
File URL | ${DATAOUT_DIR}/output_#.txt
Partition key | field1
Partition file tag | Key file tag

If you use two or more fields for partitioning, use the placeholder `#` on one place in the file URL: `${DATAOUT_DIR}/output_#.txt`. Do not use the placeholder for each key field.

#### Partitioning using Lookup Table

Partitioning using lookup table lets you part records using input field values. The values of Partition key serve as a key to be looked up in the lookup table. A value corresponding to the key defines a group.

Group can form its name with number or with value from lookup table.

Each group is written to its own output file.

The difference between partitioning according to data field value and partitioning using lookup table is that in the first case, one unique Partition key value creates one group whereas in the latter one, a single group can correspond to multiple differrent Partition key values.

Example: input data contain field `city` as well as other fields. Lookup table contains `city` and `country`. Part data into files: each file should contain records corresponding to one country. Records with unmatched cities should have `unmatched` instead of country.

Attribute | Value
--- | ---
File URL | ${DATAOUT_DIR}/output_#.txt
Partition key | field1
Partition lookup table | TheLookupTable
Partition file tag | Key file tag
Partition output fields | country
Partition unassigned file name | unmatched

Remember that if all incoming records are assigned to the values of lookup table, the file for unassigned records will be empty (even if it is defined).

#### Filtering Records using Lookup Table

You can use partitioning using lookup table to write a subset of input records. E.g. you can only write records corresponding to some countries (from previous example). To constrain the records, define values of desired fields in lookup table (key fields) and let Partition unassigned file name blank.

#### Combining of Ways of Partitioning

You can combine partitioning by number of records and partitioning according to data field value.

Example, part data according to the `field1` field. Use the field value as part of output file name. Write at most 100 records into one file.

Attribute | Value
--- | ---
File URL | ${DATAOUT_DIR}/output_#_$.txt
Records per file | 100
Partition key | field1
Partition file tag | Key file tag

The `#` sign is replaced with `field1` value. The $ sign is replaced with integer according to number of record with same `field1` value.

#### Limits of Partitioning

Partitioning algorithm keeps all output files open at once. Which could lead to undesirable memory footprint for many output files (thousands). Moreover, for example unix-based OS usually have very strict limitation of number of simultaneously open files (1024) per process.

In case you run into one of these limitations, consider sorting the data according to partition key using one of our standard sorting components and set Sorted input attribute to true. The partitioning algorithm does not need to keep open all output files, just the last one is open at one time.

#### Name for Partitioned File

The File URL value only serves as a base name for the output file names. The base name should contain placeholders - dollar sign or hash sign.

The dollar sign is replaced with number. If you use more dollar signs, each `$` is replaced with one digit. This way leading zeros can be inserted. Use `$` if you part according to number of records

The hash sign is replaced with number, field value, or value from lookup table. Leading zeros can be created with more hash signs. Use `#` if you part according to field value or using lookup table.

#### Hash Sign versus Dollar Sign

| Important
You should differentiate between hash sign and dollar sign usage. Hash sign Hash sign should be used when each of multiple output files only contains records corresponding to the value of specified Partition key. Dollar sign Dollar sign should be used when each of multiple output files contains only a specified number of records based on the Records per file attribute.

The hash(es) can be placed in any place of this file part of File URL, even in middle. For example: `path/output#.xls` (in case of the output XLS file).

If Partition file tag is set to `Number file tag`, output files are numbered and the count of hashes used in File URL means the count of digits for these distinguishing numbers. This is the default value of Partition file tag. Thus, `###` can go from `000` to `999`.

If Partition file tag is set to `Key file tag`, single hash must be used in File URL at most. Distinguishing names are used.

These distinguishing names will be created as follows:

If the Partition key attribute (or the Partition output fields attribute) is of the following form: `field1;field2;...;fieldN` and the values of these fields are the following: `valueofthefield1`, `valueofthefield2`, ..., `valueofthefieldN`, all the values of the fields are converted to strings and concatenated. The resulting strings will have the following form: `valueofthefield1valueofthefield2...valueofthefieldN`. Such resulting strings are used as distinguishing names and each of them is inserted to the File URL into the place marked with hash. Or appended to the end of File URL if no hash is used in File URL.

For example, if `firstname;lastname` is the Partition key (or Partition output fields), you can have the output files as follows:

- `path/outjohnsmith.xls`, `path/outmarksmith.xls`, `path/outmichaelgordon.xls`, etc. (if File URL is `path/out#.xls` and Partition file tag is set to `Key file tag`).

- Or `path/out01.xls`, `path/out02.xls`. etc. (if File URL is `path/out##.xls` and Partition file tag is set to `Number file tag`).

Prev | Up | Next
Selecting Output Records | Home | Table of Contents | Excluding Fields