Today i will discuss about the json input and json output component in Pentaho. So , in first scenario, i will load the text file to json output . Below is the image for the same. Here, i am using order_Details.txt as input file which has data related to orders. In JSON output component, few things should be kept in mind . Json bloc name :- Name of the bloc, it should be as per the input file. so , in my cases i have kept “ORDER” as Json bloc…
Year: 2017
Microsoft Access input Component in Pentaho
Today, I will discuss about the Microsoft Access input component in Pentaho. There are many times we as developers comes across the scenario where we need to write the Sql to join the multiple tables in Access db. So, we tend to use either table input or Sql component to achieve the same by creating database connection for Access db. I tried this multiple times but it didn’t worked for me. Second way to handle the multiple table joins in Access db is to write the Sql query in Access…
create table dynamically based on Metadata file and load the source file into that table in pentaho
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)…
Logging in Pentaho Jobs
Today , i will discuss about the logging in Pentaho jobs which actually helps the production support team to analyze/identify the issue in less time post . First thing , in order to create the logs for the ETL jobs, right click on job and go to Edit and go to 3rd tab (logging settings). Check the check box “Specify log file” If you want to append the log file, Check the box “Append logfile”. Then comes the “Name of the logfile”, specify the variables and place their values in…
Shell Script to Merge multiple files into one file and Archive the source files.
Today i will discuss about the shell script which will merge the multiple files into one file and post merging , archive the source files. Below is the code for the same. Here, i am passing four arguments namely source file location,Archive file location,merged file name pattern,source file name pattern. Using date_1 variable which converts the date into format YYYYMMDD_HHMMSS. Using “cd” command to go source file location. Count number of files in that location. If count is equal to 1 , that means there is no need to merge.…
internal.job.filename.directory and internal.transformation.filename.directory in Pentaho
We will discuss about two built-in variables of Pentaho which most of the developers are not aware of or they don’t use these variables so often in their coding. These variables are Internal.Job.Filename.Directory and Internal.Transformation.Filename.Directory. Designed one Job which has further sub-jobs. See the SS for the same. Here, you can see there is parent-job which has sub jobs (start-job.kjb and job 2). The parent job is present at location C:\AS2\ as seen in below image. Now, right click on start-job and go to Edit, as it is clearly seen in…