3. Manage a CFI BDomain Loader¶
Streams from different sources (BDomains) that are loaded into the same PSA table may have a similar but not identical structure:
columns may be missing in some BDomains, or data types may differ.
Therefore, the loader specification is defined per BDomain in the table CFIADM.CFI_LOADER_BD.
A complete overview, including the resulting TSA schema and table, is shown in CFIADM.CFI_LOADER_DETAILS.
Before you continue, make sure that the BDomain, its CFI settings, and the loader exist:
3.1. Add BDomain-Specific Metadata to a Loader¶
To add BDomain-specific metadata to a CFI loader, use API_CFIADM.ADD_LOADER_BD. Loader name and BDomain are the key. The most important parameters are:
- p_imp_type
File format:
CSVorFBV(fixed-width). JSON and Parquet files are converted to CSV by RED-PSS before the import (see the note below), so their loaders useCSVas well.- p_file_name_pattern
Regular expression for the names of the files to be loaded. File names are relative to the input directory; a leading directory part selects a sub-directory of any depth, e.g.
^east/hr/TEST_person_[\d]{8}_[\d]{14}\.csv$for files indata/input/east/hr/.- p_bdate_pattern and p_bdate_fmt
If the file name contains the business date: regular expression for the BDate part of the file name and its format.
- p_tsa_schema_suffix
Mandatory suffix of the target schema: the TSA table is created as
CFITSA_<SUFFIX>.<BDOMAIN>_<LDR_NAME>.- p_active
Activates or deactivates the loader for this BDomain.
- p_file_encoding, p_file_row_separator, p_file_skip_rows
Character encoding (default UTF-8), row separator (LF, CR, CRLF; default LF), and the number of header rows to skip (default 0).
- p_file_csv_column_separator, p_file_csv_column_delimiter, p_file_csv_null, p_file_csv_trim
CSV only: column separator (default ‘,’), column delimiter (default ‘”’), the text to be interpreted as NULL, and an optional trim (LTRIM, RTRIM, TRIM).
- p_file_reject_limit
CSV only: number of rows with format errors that are tolerated before the import fails (default 0). Rejected rows are written to the error table
<TSA_TABLE>_ERR. For FBV files the reject limit is always 0.- p_part_number_pattern
Regular expression for a part number in the file name, if a delivery is split into several files.
- p_bdate_ffdl_prev_day
If true, the first full load of a day (after midnight) uses the previous day as BDate (default true).
- p_numeric_characters
Decimal and thousands separator (two characters), e.g. ‘,.’ for “1.000,00”.
This step does not load any data yet; it only defines how the files are loaded.
Example: Add a CSV Loader for BDomain ‘TESTING’
1EXECUTE SCRIPT API_CFIADM.ADD_LOADER_BD(
2 'PERSON' -- p_ldr_name
3 ,'TESTING' -- p_bdomain
4 ,'CSV' -- p_imp_type
5 ,'^TEST_person_[\d]{8}_[\d]{14}\.csv$' -- p_file_name_pattern
6 ,'([1-2][0-9][0-9][0-9])([0-1][0-9][0-3][0-9])' -- p_bdate_pattern
7 ,'YYYYMMDD' -- p_bdate_fmt
8 ,'TEST' -- p_tsa_schema_suffix
9 ,false -- p_active
10 ,null -- p_bdate_epoch
11 ,0 -- p_status_finished_offset
12 ,'WINDOWS-1252' -- p_file_encoding
13 ,null -- p_file_row_separator
14 ,null -- p_file_csv_null
15 ,1 -- p_file_skip_rows
16 ,null -- p_file_csv_trim
17 ,';' -- p_file_csv_column_separator
18 ,'"' -- p_file_csv_column_delimiter
19 ,null -- p_file_fbv_row_size
20 ,0 -- p_file_reject_limit
21 ,null -- p_part_number_pattern
22 ,false -- p_bdate_ffdl_prev_day
23 ,',.' -- p_numeric_characters
24);
The file name pattern matches files like TEST_person_20240101_20240102061500.csv: the business date followed by the creation timestamp. The BDate pattern extracts the first eight digits (20240101).
The command creates a new entry in CFIADM.CFI_LOADER_BD:
LDR_NAME |
BDOMAIN |
IMP_TYPE |
FILE_NAME_PATTERN |
BDATE_FMT |
TSA_SCHEMA_SUFFIX |
ACTIVE |
FILE_ENCODING |
FILE_ROW_SEPARATOR |
FILE_CSV_COLUMN_SEPARATOR |
FILE_REJECT_LIMIT |
NUMERIC_CHARACTERS |
|---|---|---|---|---|---|---|---|---|---|---|---|
PERSON |
TESTING |
CSV |
^TEST_person_[\d]{8}_[\d]{14}.csv$ |
YYYYMMDD |
TEST |
false |
WINDOWS-1252 |
LF |
; |
0 |
,. |
Note
JSON and Parquet files are converted to CSV by RED-PSS (see the RED-PSS Guide), and the original file is moved to the archive directory:
A Parquet file becomes a CSV file without header. Its loader must use the column separator ‘,’ and the column delimiter ‘*’.
A JSON array becomes a file with one JSON document per row, i.e. a single column.
Define the file name pattern for the resulting .csv file.
3.2. Modify a CFI BDomain Loader¶
Use API_CFIADM.MODIFY_LOADER_BD to modify a BDomain loader. Loader name and BDomain identify the entry and cannot be changed; all other parameters can. NULL leaves a parameter unchanged, while a blank (’ ‘) restores its default value. In the following example the loader is activated and the row separator is changed to ‘CRLF’.
Example: Activate the Loader and Change the Row Separator
1EXECUTE SCRIPT API_CFIADM.MODIFY_LOADER_BD(
2 'PERSON' -- p_ldr_name
3 ,'TESTING' -- p_bdomain
4 ,null -- p_imp_type
5 ,null -- p_file_name_pattern
6 ,null -- p_bdate_pattern
7 ,null -- p_bdate_fmt
8 ,null -- p_tsa_schema_suffix
9 ,true -- p_active
10 ,null -- p_bdate_epoch
11 ,null -- p_status_finished_offset
12 ,null -- p_file_encoding
13 ,'CRLF' -- p_file_row_separator
14 ,null -- p_file_csv_null
15 ,null -- p_file_skip_rows
16 ,null -- p_file_csv_trim
17 ,null -- p_file_csv_column_separator
18 ,null -- p_file_csv_column_delimiter
19 ,null -- p_file_fbv_row_size
20 ,null -- p_file_reject_limit
21 ,null -- p_part_number_pattern
22 ,null -- p_bdate_ffdl_prev_day
23 ,null -- p_numeric_characters
24);
The entry for loader ‘PERSON’ and BDomain ‘TESTING’ in CFIADM.CFI_LOADER_BD has changed:
LDR_NAME |
BDOMAIN |
IMP_TYPE |
FILE_NAME_PATTERN |
BDATE_FMT |
TSA_SCHEMA_SUFFIX |
ACTIVE |
FILE_ENCODING |
FILE_ROW_SEPARATOR |
FILE_CSV_COLUMN_SEPARATOR |
FILE_REJECT_LIMIT |
NUMERIC_CHARACTERS |
|---|---|---|---|---|---|---|---|---|---|---|---|
PERSON |
TESTING |
CSV |
^TEST_person_[\d]{8}_[\d]{14}.csv$ |
YYYYMMDD |
TEST |
true |
WINDOWS-1252 |
CRLF |
; |
0 |
,. |
To switch a BDomain loader on or off without changing other settings, use API_CFIADM.TOGGLE_LOADER_BD.
3.3. Remove BDomain-Specific Metadata from a Loader¶
To remove the BDomain-specific metadata of a loader, use API_CFIADM.REMOVE_LOADER_BD.
Example: Remove the Loader for BDomain ‘TESTING’
1EXECUTE SCRIPT API_CFIADM.REMOVE_LOADER_BD(
2 'PERSON' -- p_ldr_name
3 ,'TESTING' -- p_bdomain
4);
The entry for loader ‘PERSON’ and BDomain ‘TESTING’ is removed from CFIADM.CFI_LOADER_BD.