...

{scrollbar}

Prerequisites

In order follow along with this how-to guide you will need the following:

• MapR
• Pentaho Data Integration
• Hive

Sample Files

The source data for this guide will reside in a Hive table called weblogs. If you have previously completed the "Loading Data into MapR Hive" guide, then you can skip to "Create #Create a Database Connection to Hive". If not, then you will need the following datafile and perform the " Create a Hive Table" ] instructions before proceeding.
The sample data file needed for the "Create #Create a Hive Table" instructions is:

NOTE: If you have previously completed the "Using Pentaho MapReduce to Parse Weblog Data " in MapR guide, then the necessary files will already be in the proper Anchor_GoBack_GoBack location.
This file should be placed in the /weblogs/parse directory of the CLDB using the following commands.

hadoop fs -mkdir /weblogs

...

hadoop fs -mkdir /weblogs/parse

...

hadoop fs -put weblogs_parse.txt /weblogs/parse/part-00000

Step-By-Step Instructions

Setup

Start MapR if it is not already running.
Start Hive Server if it is not already running.

NOTE: This task may be skipped if you have completed the "Loading Data into Hive" guide.

...

...

...

create table weblogs (
client_ip string,
full_request_date string,
day string,
month string,
month_num int,
year string,
hour string,
minute string,
second string,
timezone string,
http_verb string,
uri string,
http_status_code string,
bytes_returned string,
referrer string,
user_agent string)
row format delimited
fields terminated by '\t';

...

...

...

hadoop fs -put part-00000.txt /user/hive/warehouse/weblogs/

Create a Database Connection to Hive

If you already have a shared Hive Database Connection defined within PDI then this task may be skipped.

1. Start PDI on your desktop. Once it is running choose 'File' -> 'New' -> 'Job' from the menu system or click on the 'New file' icon on the toolbar and choose the 'Job' option.
2. Create a New Connection: In the View Palette right click on 'Database connections' and select 'New'.

Image Removed

1. Configure the Connection: In the Database Connections window enter the following:
   1. Connection Name: Enter 'Hive'
   2. Connection Type: Select 'Hadoop Hive'
   3. Host Name and Port Number: Your connection information. For local single node clusters use 'localhost' and port '10000'.
   4. Database Name: Enter 'Default'

...

Create a Job to Aggregate Web Log Data into a Hive Table

In this task you will create a job that runs a Hive script to build an aggregate table, weblogs_agg, using the detailed data found in the Hive weblogs table. The new Hive weblogs_agg table will contain a count of page views for each IP address by month and year.

1. Start PDI on your desktop. Once it is running choose 'File' -> 'New' -> 'Job' from the menu system or click on the 'New file' icon on the toolbar and choose the 'Job' option.
2. Add a Start Job Entry: You need to tell PDI where to start the job, so expand the 'General' section of the Design palette and drag a 'Start' node onto the job canvas. Your canvas should look like:

Image Removed

1. Add a SQL Job Entry: You are going to run a HiveQL script to create the aggregate table, so expand the 'Scripting' section of the Design palette and drag a 'SQL' node onto the job canvas. Your canvas should look like:

Image Removed

1. Connect the Start and SQL Job Entries: Hover the mouse over the 'Start' node and a tooltip will appear. Image Removed Click on the output connector (the green arrow pointing to the right) and drag a connector arrow to the 'SQL' node. Your canvas should look like this:

Image Removed

1. Edit the SQL Job Entry: Double-click on the 'SQL' node to edit its properties. Enter this information:
2. Connection: Select 'Hive'
3. SQL Script: Enter the following

create table weblogs_agg
as
select
client_ip
, year
, month
, month_num
, count as pageviews
from weblogs
group by client_ip, year, month, month_num
When you are done your window should look like:
Image Removed
Click 'OK' to close the window.

1. Save the Job: Choose 'File' -> 'Save as...' from the menu system. Save the transformation as 'aggregate_hive.kjb' into a folder of your choice.
2. Run the Job: Choose 'Action' -> 'Run' from the menu system or click on the green run button on the job toolbar. A 'Execute a job' window will open. Click on the 'Launch' button. An 'Execution Results' panel will open at the bottom of the PDI window and it will show you the progress of the job as it runs. After a few seconds the job should finish successfully: Image Removed

If any errors occurred the job step that failed will be highlighted in red and you can use the 'Logging' tab to view error messages.

Check Hive

1. Open the Hive Shell: Open the Hive shell so you can manually create a Hive table by entering 'hive' at the command line.
2. Query Hive for Data: Verify the data has been loaded to Hive by querying the weblogs table.

select * from weblogs_agg limit 10;

1. Close the Hive Shell: You are done with the Hive Shell for now, so close it by entering 'quit;' in the Hive Shell.

Summary

During this guide you learned how to transform data within Hive within a PDI job flow.

{scrollbar}