3. Manage a CFI BDomain Loader¶
Streams from different sources (Bdomains) which go into the same PSA target table may have similar but not completely identical structure Columns may be missing in some BDomains or datatypes may differ. Therefore, the loader specification may differ between BDomains. These specific settings are collected in the table CFI_LOADER_BD. A complete overview can be found in CFI_LOADER_DETAILS.
Before you continue make sure that the BDomain and all related CFI settings exist in addition the base loader itself:
3.1. Add BDomain specifc metadata to a Loader¶
To add bdomain specific metadata to a CFI loader use API_CFIADM.ADD_LOADER_BD.
The name of the loader and bdomain are the key values.
Furthermore, the import format, csv, parquet or json, has to be chosen.
File name and BDate pattern and format of the source file have to be defined as well as the name of the target schema and table.
In this example a regular expression is used to define the file name pattern starting with ‘TEST_person_’ followed by the BDate and timestamp when the file has been created.
Use the active flag in order to activate or deactivate the loader for this very BDomain.
One also has to provide the file-encoding format as well as the file row separator used by the source file.
Other values that can be set are the column separator and delimiter as well as if a trim function was used if the input file is of the format CSV.
The variable p_file_reject_limit can be used to set a limit of how many errors are tolerated before a file cannot be loaded while the variable p_part_number_pattern can be used to set the amount of parts a file can be split into if it is too big.
p_numeric_characters is used to specify how numeric numbers should be interpreted, for example if a thousand should be depicted as “1.000,00” or “1,000.00”.
This step does not yet load any data, the script is only used to define how the data should then be loaded and the necessary metadata is created.
Example
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 ,'CFITSA' -- p_tsa_schema
9 ,'TEST_PERSON' -- p_tsa_table
10 ,false -- p_active
11 ,null -- p_bdate_epoch
12 ,0 -- p_status_finished_offset
13 ,'WINDOWS-1252' -- p_file_encoding
14 ,null -- p_file_row_separator
15 ,null -- p_file_csv_null
16 ,1 -- p_file_skip_rows
17 ,null -- p_file_csv_trim
18 ,';' -- p_file_csv_column_separator
19 ,'"' -- p_file_csv_column_delimiter
20 ,null -- p_file_fbv_row_size
21 ,0 -- p_file_reject_limit
22 ,null -- p_part_number_pattern
23 ,false -- p_bdate_ffdl_prev_day
24 ,',.' -- p_numeric_characters
25);
Execution of the script above will result in a new entry in the table CFI_LOADER_BD.
LDR_NAME |
BDOMAIN |
IMP_TYPE |
FILE_NAME_PATTERN |
BDATE_PATTERN |
BDATE_FORMAT |
TSA_SCHEMA |
TSA_TABLE |
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 |
([1-2][0-9][0-9][0-9])([0-1][0-9][0-3][0-9]) |
YYYYMMDD |
CFITSA |
TEST_PERSON |
false |
WINDOWS-1252 |
LF |
; |
0 |
,. |
3.2. Modify a CFI BDomain Loader¶
Use API_CFIADM.MODIFY_LOADER_BD for modification of a BDomain loader. While the name of the loader which should be modified as well as the name of the corresponding bdomain have to be provided and therefore cannot be changed, one can change the rest of the parameters. Providing NULL leaves the parameters as is, while a blank (’ ‘) restores the default value. In the following example the loader will be set to active and the file row separator will be changed to ‘CRLF’. The rest of the parameters should stay the same and therefore a null value is used for all of them.
Example
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
9 ,null -- p_tsa_table
10 ,true -- p_active
11 ,null -- p_bdate_epoch
12 ,null -- p_status_finished_offset
13 ,null -- p_file_encoding
14 ,'CRLF' -- p_file_row_separator
15 ,null -- p_file_csv_null
16 ,null -- p_file_skip_rows
17 ,null -- p_file_csv_trim
18 ,null -- p_file_csv_column_separator
19 ,null -- p_file_csv_column_delimiter
20 ,null -- p_file_fbv_row_size
21 ,null -- p_file_reject_limit
22 ,null -- p_part_number_pattern
23 ,null -- p_bdate_ffdl_prev_day
24 ,null -- p_numeric_characters
25);
After running the command above the entry for the loader ‘PERSON’ with the BDomain ‘TESTING’ in the table CFI_LOADER_BD will have changed as can be seen below.
LDR_NAME |
BDOMAIN |
IMP_TYPE |
FILE_NAME_PATTERN |
BDATE_PATTERN |
BDATE_FORMAT |
TSA_SCHEMA |
TSA_TABLE |
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 |
([1-2][0-9][0-9][0-9])([0-1][0-9][0-3][0-9]) |
YYYYMMDD |
CFITSA |
TEST_PERSON |
true |
WINDOWS-1252 |
CRLF |
; |
0 |
,. |
3.3. Remove BDomain specific metadata from a Loader¶
To remove bdomain specific metadata from a loader use API_CFIADM.REMOVE_LOADER_BD.
Example
1EXECUTE SCRIPT API_CFIADM.REMOVE_LOADER_BD(
2 'PERSON' -- p_ldr_name
3 ,'TESTING' -- p_bdomain
4);
After issuing the command above the entry belonging to the loader ‘PERSON’ with the bdomain ‘TESTING’ will have been removed from the table CFI_LOADER_BD.