Showing posts with label Pentaho. Show all posts
Showing posts with label Pentaho. Show all posts

Friday, 17 February 2023

FILE READ - REGULAR EXPRESSION

 (j_dataload)_[0-9]{8}_[0-9]{14}\.log

SHELL SCRIPT TO EXECUTE PENTAHO JOB

 #!/bin/bash -l

sh /home/abcserver/data-integration/kitchen.sh -file="/home/abcserver/etl/j_dataload.kjb" –level:Basic -logfile:/home/abcserver/etl/Logfiles/j_dataload_$(date +"%Y%m%d_%H%M%s").log

Wednesday, 18 January 2023

CRONTAB

 To edit the crontab

    crontab -e


To view the crontab

    crontab -l


After change, for save

    ctrl+c, :wq!


After change, not to save (wrongly given, back to original)

    ctrl+c, :q!

Penatho - Expressions for FILE READ

The file name is, 'j_dataload_20230117_18031673958817.log'


SCENARIO: If a directory contains more number of files and you want to read the 'j_dateload_' files alone, then you have to use the below expression.


EXPRESSION:  (j_dataload)_[0-9]{8}_[0-9]{14}\.log

Sample shell script - To call the pentaho job

 #!/bin/bash -l

sh /home/abc/data-integration/kitchen.sh -file="/home/abc/etl/j_dataload.kjb" –level:Basic -logfile:/home/abc/etl/Logfiles/j_dataload_$(date +"%Y%m%d_%H%M%s").log

sh /home/abc/data-integration/kitchen.sh -file="/home/abc/etl/j_logfile_archive.kjb" –level:Basic -logfile:/home/abc/etl/Logfiles/j_logfile_archive_$(date +"%Y%m%d_%H%M%s").log


CRON Schedule

 Sample cron schedule,


0 14 * * * /home/abc/etl/ExecutJob.sh

Tuesday, 3 January 2023

Pentaho - Read file and store content in the memory

 Step Name : Load file content in memory


Returns two columns, 

1. File Content

2. File Size

Penatho - Expressions

Compare two columns:

Source:


Comparison:


Pentaho Steps:




Use 'User Defined Java Expression' step in the pentho to achieve this, some sample expressions are below,

Count.equals(Tgt_Count) ? "TRUE" : "FALSE"

Value Type : String


Count<=Tgt_Count ? "OK" : "NOTOK"

Value Type : String

Wednesday, 21 December 2022

Pentaho - Regex to find a word in a line and replace the full line

 ^.*welcome.*$

Pentaho - Java Script example for if..else

 var out_record_count;

 if (start_pos == -1)

{

out_record_count=0

}

else if (end_pos == -1 )

{

out_record_count=0

}

else 

{

out_record_count=text.substring(start_pos+3, end_pos)

}

Pentaho Spoon - Unable to create the database cache

Couldn't read the database cache

org.pentaho.di.core.exception.KettleFileException 


The solution was to delete the db.cache* File in the user home dir


Linux: 

/home/abc/.kettle

db.cache-8.0.1-stable


Windows:

C:\Users\abc\.kettle

db.cache-8.3.0.0-371

Tuesday, 20 December 2022

Pentaho - JavaScript to find string position & find substring

var text = Field_000;

var start_pos = text.indexOf(" W=");

var end_pos = text.indexOf(", U=");

var out_record_count = text.substring(start_pos+3, end_pos);

PENATHO - JOB EXECUTION SHELL COMMAND & LOG FILE WRITE ENABLE, LOCATION

 /home/abc/data-integration_8_1/kitchen.sh -file="/home/abc/myfolder/j_sample.kjb" –level:Basic -logfile:/home/abc/myfolder/Logfiles/j_sample_$(date +"%Y%m%d_%H%M%s").log

Sunday, 11 December 2022

Pentaho job execution shell command & with log file enable option

/home/dev/data-integration_8_1/kitchen.sh -file="/home/dev/abc/j_dataload.kjb" –level:Basic -logfile:/home/dev/abc/Logfiles/j_dataload_$(date +"%Y%m%d_%H%M%s").log

Monday, 5 September 2022

SQL Error : Multiple ResultSets were returned by the query

Upon using the custom query in the source object, the mapping fails with the following error:


The select query for read operation failed because of the following error: [org.postgresql.util.PSQLException: Multiple ResultSets were returned by the query​


However, the same query works as expected in PostGresDB.​


If the custom query has more than two query statements, one which is commented and separated by a semicolon, then multiple ResultSets were returned by the query. 


This is expected behavior as the JDBC driver verifies both the statement and returns a null value for the blank query which is a commented line.



Solution:


To resolve the issue, either remove the commented lines from the query or keep the commented lines at the top of the query, so it can be disregarded while running the original query

Tuesday, 19 July 2022

JAVA SCRIPT - TO GET THE CHARACTER POSITION & SLICE(STRING CUT)

var out_filename = short_filename.slice(0, short_filename.indexOf("."))

CRON Schedule


0 5 * * * /home/pentaho/pentaho-pdi/data-integration/pan.sh -file="/home/pentaho/test/tr_test.ktr" –level: Basic –logfile="/home/pentaho/test/test.log"

Kiro - Core Features

What is Kiro Kiro is an innovative AI-powered IDE that revolutionizes software development through intelligent assistance and structured wor...