Today , i will discuss about creating the table based on metadata file and then loading the source file into that table.Below is the image of ETL job.
First, loading the metadata file into a table, means loading the metadata file which has details like column name ,column type and column length.
In this ktr (loading_metadata_file), loading the metadata file to a table TBL_METADATA_STORE.
Below is the DDL of this table.I am using mysql as database.
CREATE TABLE `tbl_metadata_store` (
`column_name` varchar(100) DEFAULT NULL,
`data_type` varchar(100) DEFAULT NULL,
`col_len` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Once the metadata is loaded into table, now i will create the dynamic query using the data which is present in the TBL_METADATA_STORE and eventually when i execute the dynamic query, table gets created as per the metadata defined.
Name of the ktr is call-dynamic proc. Below is the image of this code.
You can alter the procedure(used to create the table dynamically) as per your metadata file and DB used.
Now, simplest task is left which is loading the source file to that table.Below is the image for the same.
ETL code, procedure, DDL , sample metadata file and source file checked in to git. you can use this code as reference for your use-case development.
https://github.com/Karan-Arora-13/technicalstuff/
ETL_CODE_PENTAHO_DYNAMIC_TABLE_CREATION.zip
