Connecting to Oracle Database Using Service Name.

Connecting to Oracle Database Using Service Name.

While creating a connection to Database we give SID as Database Name.

To give Service name instead of SID.

• Give a back slash before the Service name Eg: /Service name

If you are using JNDI connection give URL as shown below.

Service URL is:

jdbc:oracle:thin:@Host name:port/service name

Configuring File Repository in Pentaho Data Integration(Kettle).

Configuring File Repository in Pentaho Data Integration.

Repository is a place to store your work (jobs and transformations), This section will help you how to configure a File Repository in Pentaho Data Integration.

Step 1:

Open Spoon navigates to: Toolsà Repository à Connect

Step 2: 

Click on the + button as shown in figure

If you are using default instance give the default user name and password.

User Name: admin

Password: Password

Then click OK.

Step 3:

From the dialog box select Kettle file repository and click ok.

Step 4: Select a base directory for the Repository and give a name and description to the repository then click ok.

Step 5:

Then you can see your repository name then click OK, Now you are connected to the repository.

Step 6:

Explore the repository using

Toolsà Repository à Explore

Step 7:

Now you can see the window as shown below

Step 8:

Write click on the folder icon and select new folder and give a name to the folder.

Step 9:

Now folder is added to the repository

Now create a simple transformation and while saving select folder 1 from Directory and select OK.

Step 10:

Now transformation is added to the repository. You can check this by exploring your repository.

Spoon User Defined Java Class Transformation with example.

Welcome

Pentaho Data Integration Suit: Spoon User Defined Java Class Transformation.

This section provide you a basic idea about how to implement a java transformation in Spoon.

Requirement: Splitting effective start date and end date on  yearly basis

Source table:

Expected output:

Transformation:

We can use a java transformation to implement this logic in Spoon.

Java code used inside the User Defined Java Class


import java.util.Date;
import java.util.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
static RowMetaInterface inputRowMeta;

public boolean processRow(StepMetaInterface smi, StepDataInterface sdi)
{

try
{


    // First, get a row from the default input hop

    Object[] r = getRow();

    // If the row object is null, we are done processing.
    //
    if (r == null)
{
      setOutputDone();
      return false;
    }

    // It is always safest to call createOutputRow() to ensure that your output row's Object[] is large
    // enough to handle any new fields you are creating in this step.
    //
DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
Calendar cal1 = Calendar.getInstance();//create a empty calendar variable
Calendar cal2 = Calendar.getInstance();
    Calendar cal3 = Calendar.getInstance();
    int st_yr, ed_yr, Cu_yr, st_mon, ed_mon, st_date, ed_date, st_ldm, ed_ldm,stcount, count;
    String str,end,OUT_SVC_ST_DT,OUT_SVC_END_DT;

    Date st_dt = get(Fields.In, "Eligibilty_Begin_Date").getDate(r);//get the start and end date from the source
    Date ed_dt = get(Fields.In, "Eligibility_End_Date").getDate(r);
    cal1.clear();
    cal1.setTime(st_dt);//initialise the calendar variable acc to the source table
    cal2.clear();
    cal2.setTime(ed_dt);

st_yr = cal1.get(Calendar.YEAR);//get the year in int format
    ed_yr = cal2.get(Calendar.YEAR);
    Cu_yr = cal3.get(Calendar.YEAR);
count=(ed_yr-st_yr);

st_mon = cal1.get(Calendar.MONTH)+1;//get the month int format
    ed_mon = cal2.get(Calendar.MONTH)+1;

do
    {
 
        Object[] outputRow = RowDataUtil.createResizedCopy(r, data.outputRowMeta.size());
        OUT_SVC_ST_DT = formatter.format(st_dt);
get(Fields.Out, "Start_Date").setValue(outputRow, OUT_SVC_ST_DT);
        if(ed_yr != st_yr)
        {
            str = "31/12/" + st_yr;
            st_dt = (Date)formatter.parse(str);
            cal1.setTime(st_dt);
            OUT_SVC_END_DT = formatter.format(st_dt);

        }
        else
OUT_SVC_END_DT = formatter.format(ed_dt);
get(Fields.Out, "End_Date").setValue(outputRow, OUT_SVC_END_DT);
        putRow(data.outputRowMeta, outputRow);
     
        st_yr = st_yr + 1;
        str = "01/01/" + st_yr;
        st_dt = (Date)formatter.parse(str);
        cal1.setTime(st_dt);
        st_yr = cal1.get(Calendar.YEAR);

    }while(ed_yr >= st_yr);
}

catch (Exception e)
   {
throw new KettleException("An unexpected error occurred creating the new transformation", e);
    }
 return true;
}