Query Inn

Calculate Grand Total in SQL Query

2010-07-21T10:09:00.000-07:00

To calculate a grand total within an SQL query, the keyword available for this purpose is COMPUTE Keyword which is used with out a BY clause

SELECT itemid, Qty, actualPrice, subtotal
FROM SalesOrderDetail s
WHERE  actualPrice< 1000
COMPUTE SUM(Qty), SUM(subtotal)

Date wise SQL Query

2010-06-23T08:54:00.000-07:00

Column Level SQL Query to extract data with date as condition

Select employee_id, employee_name
(Select count(*) from employee_leave el where e.employee_id=el.employee_id
and el.leave_date between e.hire_Date and '2010-06-20 23:59:59')
from tbl_employee e
where e.hire_date between '2010-06-01 00:00:00' and '2010-06-01 23:59:59'

here in the above query for extracting the total leaves the final date has been set from the employee hire date.

Generate Mapping against target Step

2010-05-25T11:20:00.000-07:00

In cases where you have a fixed target table, you will want to map the fields from the stream to their corresponding fields in the target output table. This is normally accomplished using a Select Values step in your transformation. The 'Generate mapping against target' option provides you with an easy to use dialog for defining these mappings that will automatically create the resulting Select Values step that can be dropped into your transformation flow prior to the table output step. To access the 'Generate mapping against target' option is accessed by right clicking on the table output step.

After defining your mappings, select OK and the Select Values step containing your mappings will appear on the workspace. Simply, attach the mapping step into your transformation immediately Attach the mapping step into your transformation just before the table output step.

Pentaho Spoon Installation

2010-05-25T10:58:00.000-07:00

The first step is the installation of Sun Microsystems Java Runtime Environment version 1.4 or higher. You can download a JRE

After this, you can simply unzip the zipfile: Kettle3.0.0. zip in a directory of your choice. In the Kettle directory where you unzipped the file, you will find a number of files. Under Unixlike environments (Solaris, Linux, MacOS, …) you will need to make the shell scripts executable. Execute these commands to make all shell scripts in the Kettle directory executable:

cd Kettle
chmod +x *.sh

Starting Spoon:.

To launch Spoon on the different platforms these are the scripts that are provided:
Spoon.bat: launch Spoon on the Windows platform.
sh spoon.sh: launch Spoon on a Unixlike
platform: Linux, Apple OSX, Solaris, ...
If you want to make a shortcut under the Windows platform an icon is provided: “spoon.ico” to set the correct icon. Simply point the shortcut to the Spoon.bat file.

Supported platforms

The Spoon GUI is supported on the following platforms:
Microsoft Windows: all platforms since Windows 95, including Vista
Linux GTK: on i386 and x86_64 processors
Apple's OSX: works both on PowerPC and Intel machines
Solaris: using a Motif interface (GTK optional)
AIX: using a Motif interface
HPUX:

Group by SQL Query

2010-05-21T09:36:00.000-07:00

To extract Country wise sales count for the period MAY-2010 use below code.

Select c.country_name, count(s.id)
from tbl_sales s inner join tbl_country c on s.country_id=c.country_id
where s.saels_date between '2010-05-01 00:00:00' and '2010-05-31 23:59:59'
group by c.coutnry_name

How to Insert Extracted Values in Pentaho Spoon

2010-05-16T11:47:00.001-07:00

To insert the extracted records from the transactional source database into the destination or data warehouse following is simple way to do it.

Open the Pentaho Spoon go to file menu and open new Transformation.

in the design mode drill down the Input Folder and drag & drop the Table Input Step, double click the Table Input and set the source database (How to Set Connection Parameters in Pentaho Spoon)connection parameters this will extract all required fields

Again go to the design mode and this time drag & drop the INERT / UPDATE Step lying under the OUT PUT Folder now hold the Shift key and place your mouse icon over the TABLE INPUT and drag upto the INERT / UPDATE Step this will generate a Green color Arrow called a hope with its tail at the TABLE INPUT and head towards INERT / UPDATE Step.

You can enable, disable or even delete the hope by placing your mouse over it and with right click. now double click the INERT / UPDATE Step

and set target Database Connection by clicking the New Button for the connection and Edit if you have it already.

For the first grid Click the Get Fields Button this will fill grid with all values you have queried in the previous Table Input Step here in this grid keep only those fields on the basis of which you will insert record.

Loading XML Data Using Pentaho ETL Tool

2010-05-16T06:47:00.001-07:00

To load the data from a XML file using pentaho kettle do the followings.
Go to the design mode, drill down the input folder and drag & drop the XML Input Step.

Double click the XML Input Step as shown in the image below

in the first tab of file click the Brows Button and add your desire XML File by clicking Add Button this will add your file(s) inside the selected files Grid under the File /Directory Grid Label.
Below this Grid there is a Show File(s) options that allow you to view your complete files addresses.
Nnow go to the next tab of Fields followed by Content tab.

here in this Fields tab click the Get Fields Button lying below the Grid clicking this will extract all the contents of the XML file along with their data types and positions.
To view all these contents of your XML file you can use the XML OUTPUT Step available under the Out put folder.

SQL Query For Monthly Calculation

2010-05-16T06:29:00.000-07:00

To get individual library member's monthly status report use the following column level query that includes how many books have been issued and returned in the month of may 2010 for those members who have their registration date in the same month here is the query

select m.member_id as "Member ID",m.member_name as "Member Name"

,(select count(book_id)

from tbl_book_issue b

where m.member_id=b.member_id

and b.status=1)as "Total Issued Books"

, (select count(book_id)

from tbl_book_issue b

where m.member_id=b.member_id

and b.status=2)as "Total Returned Books" #status=1 for issued and status=2 for return

from tbl_member m

where m.registration_date between '2010-05-01 00:00:00' and '2010-05-31 23:59:59'

Concatenation in Pentaho Kettle

2010-05-15T23:19:00.001-07:00

To get the concatenated resulted values of the two variables do the followings.

in the design mode drill down the Scripting folder drag and drop the Modified Java Script Value step

Double click the icon and go the java script value Text area, copy the below code and past it into the Script Value Text Area

var fullName = FirstName+ LastName;

here in this code of java “var” is the reserved word for declaring String variables while “fullName is the name of variable that will hold the resulted or concatenated value, you can view the “fullName” variable contains the resulted value showing in the grid by clicking get Variable button at bottom of the step.

How to Split a String in pentaho Kettle

2010-05-15T22:54:00.001-07:00

To get values from a comma separated string we can spilt it in following way.

In the design mode drill down the Scripting folder drag and drop the Modified Java Script Value step

Double click the the icon and go the java script value Text area, copy the below code and past it into the Script Value Text Area

var employee = EmployeeInfo.getString().split("\\,");
var ID = employee [0];
var Name= employee [1];
var Address = employee [2];

here in this code of java “var” is the reserved word for declaring String variables while “ID”,”name”,”address” are the names of variables that will hold the values after splitting and the EmployeeInfo is the String that contains the ID, Name and Address in the form i.e. 1234,Ali,pakistan

Date to String Conversion in Pentaho Kettle

2010-05-13T09:04:00.001-07:00

Here is the simple conversion from Date to String in the spoon

Go the design mode select Modified Java Script Step lying under the Scripting folder drag & drop it.

Double click the Java Modified Script Step and write the following code in the scriptVale

var dateToStringConversion = date2str(datefield_name, "yyyyMMdd");

where dateToStringConversion is the name of the variable and datefield_name is the field name to whom you want to convert which is coming from the previous step

How to get Distinct Count in Pentaho

2010-05-13T08:35:00.001-07:00

In order to get Distinct Count in the Kettel spoon

Go to the design mode drill down the Transformation folder and Drag & drop the Unique Rows Step

this step remove the double and repeated Rows and give only unique occurrences but this works only on sorted input so you need to set a Sorted Rows Step available under the Transformation Folder jus drag & drop it and make the hope between the two steps

If the input is not sorted, then only double consecutive rows are handled correctly.

How to Write ETL Script in Pentaho

2010-05-13T08:12:00.001-07:00

In this posting i m going to exemplify for the beginners that how to write a simple script in the Pentaho Kettle Spoon let say extracting the data from the source or transactional database or from the one data source to destination or target or another database, here is the example steps.

Extract Customer information from Customer_database from MYSQL

Transform and Load The extracted data in SQL SERVER Customer_database Table

Open the Pentaho Spoon

Go to the file menu and select Transformation or press Ctr+N

Go to the design mode and select Table Input from the Input folder and the Table Output Steps from the Output folder respectively

Press Shift button and drag from Table input to Table Out Put called a hope having green color that showing all the values shifting to another step.

Extracting required fields from the source table representing here in this example as Table Input from MYSQL and loading all the fields in the destination database table representing here as Table output in SQLSERVER.

Double click the each step to set source and destination connections as shown below name the connection, if working on the local system name local host else IP

select MYSQL from the connection type set ,user id and password followed by the database name and test the connection.

after defining the connection write the query in the SQL text area and double click the Table Output set the connection same as defined above here you also have to define the target table that will hold the extracted data as can view in the below image.

Check Table Column Existence in Pentaho

2010-05-12T08:52:00.001-07:00

In order to find out the existence of a specific column in a database use the Check if Column Exists Step available under the Lookup Folder in the design mode

Drag & Drop the above icon.
Double click the step and name it , defined the connection for MYSQL or SQL SERVER etc.
Table Name: The name of the column to whom the column belongs.
Table name in Field: Enabled it if you want to read the name of the table from the input field.
Tablename Field:Set fields here that have parameters and its type
Columnname Field:Set the name of the column field reading from the input field.
Result fieldname:Resulting Boolean flag that shows weather it exist or not.

Performing Calculations in Pentaho Spoon

2010-05-12T08:21:00.001-07:00

To perform simple calculations and get new fields on different output fields extracting from the database table on constant values.

Go to Transformation in the design mode
Drill Down the Transformation folder and drag & drop the Calculator
Double Click the Calculator Step a pop window same as below will appear
In the New Field column name your new Field
Select the type of calculation from the list box on the fields you want to perform calculations.
Field A, Field B and Field C combos are available to select your fields that are need to be performed calculations
Select the value type from the combo box and set decimal precision.

Aggregate Functions in Pentaho Spoon

2010-05-12T07:57:00.001-07:00

To Calculate the Aggregate Functions likewise Sum(), Average(), Count(), MIn(), Max(), First() and Last() usually on the data coming and reading from text files do the followings

Drill Down the Input in the design mode and select Text Input File
Double Click the Text File Input in the File tab and brows your file add it to the selected path
In the Content tab select File Type
Go to Fields tab and click the Get Fields , it will extract all the fields along with data their data types, reading each coma separated field
Go to the design mode again find Deprecated the 3rd last folder drill down it and drag & drop the Aggregate Rows Step as the same icon mentioned below.
Double click the Aggregate Rows a pop window will open click Get Fields it will extract all the fields as it has read from the Text Input File, set the name for each field in the New Name column and finally select the aggregate function from the combo box.

What is Checksum in Pentaho Spoon

2010-05-12T07:23:00.001-07:00

By definition checksum is a way to determine the integrity of data in which both the sender and receiver can test the data integrity weather it is correct or not, the sender calculates the checksum value by taking the sum of binary data transmitted on the other end the receiver can perform the same calculation on the data and compare it with the checksum value provided by the sender. If the two values match, the receiver has surety that the data was received correctly.

Pentaho Spoon provide the Add a Checksum Step lying under the Transformation Tab.

Go to Design
Drill Down Transformation
Drag & Drop Add a Checksum
Name the Step
Define the Checksum Algorithm most used Algorithm for Checksum is MD-5
Click the Get Fields Button, this will extract and show all the fields coming from the previous step at which your going to apply checksum might be extracting using Input Step from the database table or Stored Procedure

Hex to integer Conversion in Kettle

2010-05-11T12:10:00.001-07:00

To convert your Hex value to integer in spoon do the followings

Go to the design mode.
Drill Down the Scripting step.
Drag and drop the Modified Java Script Value.

Double Click the Modified Java Script Value and drill down the Transformation Script available at the left hand side of the Script Mod, double click the ScriptValue

in the Script Value paste the following lines

java;
var nummerke = Packages.java.lang.Integer.valueOf(Hex.getString(), 16).intValue();

here Hex is your input value coming from another step, at the bottom of a Script Mod click the Get fields Button now you have get the converted variable with its data type showing in a grid, finally test the script.

How to Set Environment Variables in Kettle

2010-05-11T09:05:00.001-07:00

To set Environment Variables go to Edit menu and click Set Environment Variables or press CTRL+ALT+J, in the name column name your variable and assigned it to any value by default the variable is of String type. You can later in your job or transformation access these variable using GET VARIABLE STEP but it is recommended not to use Environment Variables if your working in multiple or more than two Transformations or jobs as these variable have global scope and Changes to the environment variables would conflict in Transformations or jobs for this reason Kettle variables were introduced to provide a way to define variables that are local to the job in which the variable is set. The Set Variable step in a transformation allows you to specify in which job you want to set the variable's scope (i.e. parent job, grandparent job or the root job).

in the same tab of Edit menu you can see your all Environment Variables using Show Environment Variables option, here you can also up and down your Variable on clicking name column header.

What is Get Variable in Pentaho Kettle

2010-05-10T12:16:00.001-07:00

The Get variable step is used to get access your declared variable’s values, whenever you need a variable value you have declared earlier in your script using Set Variable step what you need to do is drag and drop a Get Variable step and do the followings.

Go to the Job Folder drag & drop a Get Variable Step
Nam the value of accessing variable that you can use in other steps
in the second column give the same name of the required variable as you have declared it in Set Variable Step in below format
${variable} or %%variable%%

Example:

variable value ${my_variable as declared in Set Varible}

What is Set Variable in Pentaho Kettle

2010-05-10T11:56:00.001-07:00

The Set Variable is used to set or declare variables to hold values usually database fields likewise you want to set start_date value which you have extracted from your time_dimension table using INPUT Step so that this start_date field will be accessible through out your script.

in the upper text filed named the step and then click the get fields button to have all desired table fields in the field name column that you have extracted using input step, in the second step name the variable to each field name and in the last column define its scope. In order to avoid null values that might come in your queries using INPUT for setting different variable the last column here to set default value that will be very useful let say if there is null value it will set it to default value.

Storing Transformations and Jobs in local Files System

2010-05-09T05:48:00.000-07:00

Spoon provides you with the ability to store transformation and job files to the local file system or in the Kettle repository. The Kettle repository can be housed in any common relational database. This means that in order to load a transformation from a database repository, you need to connect to this repository. To do this, you need to define a database connection to this repository. You can do this using the repositories dialog you are presented with when you start up Spoon bat file you will come across a welcome window in order to work for storing your transformations and jobs files in local file system and just click no repository.

What is Pentaho

2010-05-09T05:33:00.000-07:00

The Pentaho BI Project is open source application software for enterprise reporting, analysis, dashboard, data mining, workflow and ETL capabilities for business intelligence needs.

What is Spoon

Kettle is an acronym for “Kettle E.T.T.L. Environment”. This means it has been designed to help you with your ETTL needs: the Extraction, Transformation, Transportation and Loading of data. Spoon is a graphical user interface that allows you to design transformations and jobs that can be run with the Kettle tools Pan and Kitchen. Pan is a data transformation engine that is capable of performing a multitude of functions such as reading, manipulating and writing data to and from various data
sources.Kitchen is a program that can execute jobs designed by Spoon in XML or in a database repository. Usually

Transformations and Jobs can describe themselves using an XML file or can be put in a Kettle database

repository. This information can then be read by Pan or Kitchen to execute the described steps in the
transformation or run the job.

ETL Scripting with Open Source ETL Tools

2010-05-09T02:55:00.000-07:00

Extract Transform Load (ETL)

Extracting, transforming, and loading (ETL) is a process used in database especially in data warehousing and have followin steps

Extracting data from sources (The transactinal Database)

Transforming it to fit operational needs (which can include quality levels)

Loading in destination (database or data warehouse or staging where you practice about data)

Extraction:

The first part of an ETL process involves extracting the data from the source systems usually called a transactional database where actual transactions are perforemd. Most data warehousing projects consolidate data from different source systems like Relational Databases.

Extraction converts the data into a format for transformation processing. An intrinsic part of the extraction involves the parsing of extracted data, resulting in a check if the data meets an expected pattern or structure. If not, the data may be rejected entirely or in part. This step invloves extracting all the data as mentioned in the written SQL queries and procedure from the source system.

Transformation:

The transform stage applies a series of rules or functions to the extracted data from the source to derive the data for loading into the end target. Some data sources will require very little or even no manipulation of data

Loading

This step involves process lodaing data which has extracted from one source database say MYSQL to load in SQLSERVER The load phase loads the data into the end target, usually the data warehouse (DW). Depending on the requirements of the organization, this process varies widely. Some data warehouses may overwrite existing information with cumulative, frequently updating extract data is done on daily, weekly or monthly. while other DW (or even other parts of the same DW) may add new data in a historicized form, for example, hourly

Example:

It might be your requirement that you need to accumulate data that is residing in different database like wise Customer Sales information on MYUSQL and its support and maintenance information on SQL SERVER so here you need to gather the information in order to present it the ETL script is the best solution for you.

BEST Practices:

Four-layered approach for ETL architecture design

Functional layer: Core functional ETL processing (extract, transform, and load).
Operational management layer: Job-stream definition and management, parameters, scheduling, monitoring, communication and alerting.
Audit, balance and control (ABC) layer: Job-execution statistics, balancing and controls, rejects- and error-handling, codes management.
Utility layer: Common components supporting all other layers.

Open Source ETL Tools:

Apatar
CloverETL
Flat File Checker
Jitterbit 2.0
Pentaho Data Integration (now[update] included in OpenOffice Base)
RapidMiner
Scriptella
Talend Open Studio

SELECT AS CLAUSE

2010-05-08T22:58:00.000-07:00

In SQL Query you can define column heading jus write heading near to field name with out any space but if you want to make space in column heading then use AS clause like wise
Column Heading with out AS Clause
SELECT LASTNAME, SALARY TotalSalary
FROM EMP
WHERE WORKDEPT = 'C01'
ORDER BY 2 DESC
Column Heading with space
SELECT LASTNAME, SALARY + BONUS + COMM AS TOTAL EANINGS
FROM EMP
WHERE WORKDEPT = 'C01'
ORDER BY TOTAL_EANINGS DESC