Get the Row Count in PDI Dynamically

Get the Row Count in PDI Dynamically

Often people use the data input component in pentaho with count(*) select query to get the row counts. And pass the row count value from the source query to the variable and use it in further transformations.The more optimised way to do so can be through the built in number of options available in the pentaho. One of such component available in the penatho is ‘Memory Group By’.

In this blog, I’ll discuss how we can use the ‘Memory Group By’ component available in the pentaho to count the rows.

Here is an example, Suppose you have a ready query to pull records from the database then what you can do is pass the record value coming from the input component to the memory group by component as shown below:

Memory_GRoup_by1

Get the ‘Memory Group by’ component from: Design->Statistics-> Memory Group by

Memory_GRoup_by2

After that pass the count records from ‘Memory Group by’ component to the ‘Set Variables’.
Click on the ‘Memory Group by’ and in ‘Aggregates’, write the name as ‘CR_count’
In subject put the column name on which the records should be grouped and give the final count.
In type, from the drop down list select the option shown below as ‘Number of rows (without field argument)’ to get the records count.

Memory_GRoup_by3

Pass the output from memory group by option to the Get Variables, there configure the variable with the same name as you wrote in ‘Memory Group by’ i.e ‘CR_count’ overhere we have used.

Memory_GRoup_by4

Finally, we can use the records count via variable throughout the job by accessing the variable.

Thanks,
Nisha Sahu

Groovy

Groovy Language

  • Groovy can run on JVM
  • Groovy scripts can be executed using groovy jar file.
  • Annotations are supported in groovy.

Groovy installation.

  • Binary download link http://www.groovy-lang.org/download.html
  • The binary can be run from command prompt
  • Set GROOVY_HOME, GROOVY_HOME\bin variable as enviroment variable.
  • After installing and running in the command line you get the groovy shell(groovysh).
  • groovy> println ” hello helical”

Source code in groovy.

  • The extension for groovy source file is .groovy
  • We can write either script or we may also encapsulate the code as class definition inside this file.
  • By default groovy class is subclassed by java.lang.Object.
  • Java class and its objects can access groovy script class and object and vice versa.
  • Using groovyc we can compile the source code.
  • Plain Old Groovy Objects (POGO).

Intersting facts about groovy.

  • Groovy doesnot require ; at the end of statement.
  • Last expression of the method is returned by default. So we may not explicitly use return keyword.
  • Maps, list and regular expression readily available in groovy.
  • The following package are readily available. (groovy.lang.*, groovy.util.* , java.lang.*, j ava.util.*, java.net.*, java.io.*, java.math.BigInteger, java.math.BigDecimal)
  • Its classes and methods are by-default public
  • Automatic getter and setter is created for the fields in groovy class.
  • Unlike java == operator in groovy checks for contents.
  • The is() may be used to check if two variables/object refer the same.
  • The parameters inside a function are optional.
  • Gpath is expression language to evaluate nested structure.

Data types in Groovy.

    • The keyword def is used to define variable. We can also use specific type for variable declaration. Example int marks, String company. etc
    • Range data type is a Collection. Ranges consists of two values separated by two dots.
for (i in 0..9) {
  println ("Hello $i")
}

assert 'L'..'P' == ['L', 'M', 'N', 'O','P']

String & Gstring.

  • ‘Strings may be enclosed inside single quote ‘.
  • “Mehod() calls and ${variable} substitution inside a string is known as Gstring”.
  • ”’Tripple single quotes can be used for multi line string”’
  • “”” Multi line Gstring can be enclosed in tripple quotes”””
  • /regularexpression/

Methods in Groovy.

  • Groovy supports operator overloading. – operator can be used to substract string, << can be used for string concatination.
  • Groovy string has .toUrl() method that can be directly used to convert a string to Url encoding string.

 

 

package packageName
class ClassName {
  static void main(def args){
    def hetroList= [1,2,"hi","45"]
    hetroList.each{ println it }
  }
} 

Expression Language of Spring (SpEL)

Spring Expression Lanaguage (SpEL)

 

There are many expression languages available such as JSP EL, OGNL, MVEL and JBoss EL. SpEL provides some additional features such as method invocation and string templating functionality.

 

The Spring Expression Language (SpEL) supports querying and manipulating an object graph at runtime. This expression language is not directly tied to Spring and can be used independently.

 

The following functionality is supported

Literal expressions , Boolean and relational operators , Regular expressions , Class expressions , Accessing properties, arrays, lists, maps , Method invocation ,Relational operators , Assignment , Calling constructors , Ternary operator , Variables , User defined functions , Collection projection , Collection selection ,
 Templated expressions.

 

In Spring Expression Language, we can reference a bean and its nested properties using a ‘dot (.)’

for example #{ T(java.lang.Math).random() * 100.0 } 

Using toUpperCase() method with String

ExpressionParser parser = new SpelExpressionParser();
Expression exp = parser.parseExpression("'Welcome'.toUpperCase()");
String message = (String) exp.getValue();
System.out.println(message);
o/p WELCOME

 

Evaluating a boolean operation

Expression exp = parser.parseExpression("5==3 and 'text'=='test' ");
boolean result = exp.getValue( Boolean.class);
System.out.println(result);
o/p false

 

We can use many operators in SpEL such as arithmetic, relational, logical etc.
Spring EL supports most of the standard mathematical, logical or relational operators.

Relational operators – equal (==, eq), 
not equal (!=, ne), 
less than (<, lt),
 less than or equal (<= , le), 
greater than (>, gt), 
and greater than or equal (>=, ge).
Logical operators:– and, or, and not (!).
Mathematical operators:- 
addition (+),
Subtraction (-),
Multiplication (*), 
division (/),
modulus (%) and
 exponential power (^).

 

In SpEL, we can store a value in the variable and use the variable in the method and call the method. To work on variable, we need to use StandardEvaluationContext class.

 

Example of Using variable in SPEL

public class Rectangle {
private int length;
private int width;
public int getLength() {
return length;
}
public int getWidth() {
return width;
}
public void setLength(int length) { this.length = length; } 
public void setWidth(int width) { this.width = width; } public int area(){ return length*width; } }

The above class is a bean which is used in the Expression Language in the below class

import org.springframework.expression.ExpressionParser;
import org.springframework.expression.spel.standard.SpelExpressionParser;
import org.springframework.expression.spel.support.StandardEvaluationContext;

public class Test {
public static void main(String[] args) {
Rectangle rectangle= new Rectangle();
StandardEvaluationContext context=new StandardEvaluationContext(rectangle);

ExpressionParser parser = new SpelExpressionParser();
parser.parseExpression("length").setValue(context,"5");
parser.parseExpression("width").setValue(context,"15");

System.out.println(rectangle.area());
}
}

Spring expression language efficiently sets the length and width property of the bean to 5 and 15 then the area method is invoked in the bean it gives the appropriate result.

Sources: internet