This article provides a details of ETL Framework to load any source file without having to creating new set of ETL jobs. The jobs are being designed using IBM Inforsphere DataStage v8.5 or later.
The process of adding a new source file follows a standard workflow.
To add a new source file:
- Import file metadata through DataStage Import Table Definition/Sequential File Definitions
- Create the OSH schema definition
- Create and execute the target table ddl
- Import table metadata through DataStage shared table definition
- Place source and schema files in server directories
Import file metadata through DataStage Import Table Definition/Sequential File Definitions
Use DataStage import table definition to get file metadata.
The process of adding a new source file follows a standard workflow.
To add a new source file:
- Import file metadata through DataStage Import Table Definition/Sequential File Definitions
- Create the OSH schema definition
- Create and execute the target table ddl
- Import table metadata through DataStage shared table definition
- Place source and schema files in server directories
Import file metadata through DataStage Import Table Definition/Sequential File Definitions
Use DataStage import table definition to get file metadata.
Define file column names, data types and nullability
Edit file specifications in the file definition properties.
In the columns tab, make sure to remove all field properties like null_field = ‘’
In the Parallel tab, edit the properties as required for the input data.
Create the OSH schema definition
In table definition properties for the file, open the Layout tab / Parallel. This is the OSH schema definition.
Copy-paste this definition into a file and save as “SourceFileName.osh”. The resulting definition may need to be further edited.
As a general rule, consider the following when creating an OSH schema definition file
- For delimited files, remove all “{prefix = 2}”
- Carefully specify timestamp and date format strings
- Although the length of file columns may be variable, a maximum column length should be specified as this will be used to define the target table structure. Another advantage of specifying column length is that the actual column lengths will be validated during file import, avoiding truncation of data
- Match schema data types to cater for Vertica database types (see target table creation section for more on this)
It is good practice to test schema file definitions before usage in the actual jobs (this can be tested through “view data” in the sequential file stage after the schema file creation).
Create and execute the target table ddl
For the target table, create the structure according to the source schema definition. Additionally, add the CDC control columns to the table structure as shown
OSH Schema Target Table
record
{final_delim=end, delim='\t', null_field='', quote=none, timestamp_format='%dd %mmm %yyyy %hh:%nn:%ss'}
(
RecType:string[1];
RDM_PUBLICATION_ID:int64;
RDM_GNRA_PUBLICATION_ID:nullable int64;
NM_RDM_PUBDIVNNAME:nullable string[max=50];
NM_RDM_PUBREGNNAME:nullable string[max=50];
NM_RDM_PUBOFFICENAME:nullable string[max=50];
NM_RDM_MASTHEADNAME:nullable string[max=50];
NM_RDM_GNRA_PUBNAME:nullable string[max=50];
FL_RDM_PUB_ACTIVE:nullable string[1];
DT_RDM_PUB_CREATEDDATE:nullable timestamp;
NM_RDM_PUB_PROPRIETOR:nullable string[max=50];
)
CREATE TABLE sdm_gnra_test.STG_RDMS_GNRA_PUBLICATION_TEST
(
RecType char(1) NOT NULL,
RDM_PUBLICATION_ID int NOT NULL,
NK varchar(255) NOT NULL,
RDM_GNRA_PUBLICATION_ID int,
NM_RDM_PUBDIVNNAME varchar(50),
NM_RDM_PUBREGNNAME varchar(50),
NM_RDM_PUBOFFICENAME varchar(50),
NM_RDM_MASTHEADNAME varchar(50),
NM_RDM_GNRA_PUBNAME varchar(50),
FL_RDM_PUB_ACTIVE char(1),
DT_RDM_PUB_CREATEDDATE timestamp(0),
NM_RDM_PUB_PROPRIETOR varchar(50),
EFF_TS timestamp,
END_TS timestamp,
EFF_RUN_ID int,
END_RUN_ID int
);
Matching common OSH schema data types to Vertica database data types
OSH data type Vertica data type
int64
INTEGER,INT,BIGINT,INT8,SMALLINT,TINYINT
string[max=length]
VARCHAR(length)
string[length]
CHAR(length)
timestamp / timestamp[precision]
TIMESTAMP[0] / TIMESTAMP[precision]
...
...
Import table metadata through DataStage shared table definition.
