Hitachi Vantara Pentaho Community Wiki
Child pages
  • Transforming Data within Hive in MapR

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Scrollbar

Excerpt

How to read data from a Hive table, transform it, and write it to a Hive table within the workflow of a PDI job.

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 Hive guide, then you can skip to #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 a Hive Table instructions is:

File Name

Content

How To's^weblogs_parse.txt

...

Tab-delimited,

...

parsed

...

weblog

...

data

...


NOTE:

...

If

...

you

...

have

...

previously

...

completed

...

the

...

Using

...

Pentaho

...

MapReduce

...

to

...

Parse

...

Weblog

...

Data

...

guide,

...

then

...

the

...

necessary

...

files

...

will

...

already

...

be

...

in

...

the

...

proper

...

location.

...


This

...

file

...

should

...

be

...

placed

...

in

...

the

...

/weblogs/parse

...

directory

...

of

...

the

...

CLDB

...

using

...

the

...

following

...

commands.

...

}
Code Block
hadoop fs –mkdir /weblogs
hadoop fs –mkdir /weblogs/parse
hadoop fs –put weblogs_parse.txt /weblogs/parse/part-00000
{code}
h1.

Step-By-Step

...

Instructions

...

Setup

...

Start

...

MapR

...

if

...

it

...

is

...

not

...

already

...

running.

...


Start

...

Hive

...

Server

...

if

...

it

...

is

...

not

...

already

...

running.

...


Anchor

...

Create

...

a

...

Hive

...

Table

...

Create

...

a

...

Hive

...

Table

Create a Hive Table

NOTE:

...

This

...

task

...

may

...

be

...

skipped

...

if

...

you

...

have

...

completed

...

the

...

Loading

...

Data

...

into

...

Hive

...

guide.

...



  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. Create the Table in Hive: You need a hive table to load the data to, so enter the following in the hive shell.
    Code Block
    
    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';
    

...

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

...

  1. in

...

  1. the

...

  1. Hive

...

  1. Shell.

...



  1. Load the Table: Load the Hive table by running the following commands:
    Code Block
    
    
    hadoop fs –put part-00000.txt /user/hive/warehouse/weblogs/
    
    

...

Anchor
Create a Database Connection to Hive
Create a Database Connection to Hive

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 Added

  3. 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'
      When you are done your window should look like:
      Image Added
      Click 'Test' to test the connection.
      If the test is successful click 'OK' to close the Database Connection window.

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.

Tip
titleSpeed Tip

You can download the Kettle Job aggregate_hive.kjb already completed

  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 Added

  3. 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 Added

  4. Connect the Start and SQL Job Entries: Hover the mouse over the 'Start' node and a tooltip will appear. Image Added 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 Added

  5. Edit the SQL Job Entry: Double-click on the 'SQL' node to edit its properties. Enter this information:
    1. Connection: Select 'Hive'
    2. SQL Script: Enter the following
      Code Block
      
      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
      

...

    1. When

...

    1. you

...

    1. are

...

    1. done

...

    1. your

...

    1. window

...

    1. should

...

    1. look

...

    1. like:
      Image Added
      Click 'OK'

...

    1. to

...

    1. close

...

    1. the

...

    1. window.

...



  1. Save the Job: Choose 'File'

...

  1. ->

...

  1. 'Save

...

  1. as...'

...

  1. from

...

  1. the

...

  1. menu

...

  1. system.

...

  1. Save

...

  1. the

...

  1. transformation

...

  1. as

...

  1. 'aggregate_hive.kjb'

...

  1. into

...

  1. a

...

  1. folder

...

  1. of

...

  1. your

...

  1. choice.

...



  1. Run the Job: Choose 'Action'

...

  1. ->

...

  1. 'Run'

...

  1. from

...

  1. the

...

  1. menu

...

  1. system

...

  1. or

...

  1. click

...

  1. on

...

  1. the

...

  1. green

...

  1. run

...

  1. button

...

  1. on

...

  1. the

...

  1. job

...

  1. toolbar.

...

  1. A

...

  1. 'Execute

...

  1. a

...

  1. job'

...

  1. window

...

  1. will

...

  1. open.

...

  1. Click

...

  1. on

...

  1. the

...

  1. 'Launch'

...

  1. button.

...

  1. An

...

  1. 'Execution

...

  1. Results'

...

  1. panel

...

  1. will

...

  1. open

...

  1. at

...

  1. the

...

  1. bottom

...

  1. of

...

  1. the

...

  1. PDI

...

  1. window

...

  1. and

...

  1. it

...

  1. will

...

  1. show

...

  1. you

...

  1. the

...

  1. progress

...

  1. of

...

  1. the

...

  1. job

...

  1. as

...

  1. it

...

  1. runs.

...

  1. After

...

  1. a

...

  1. few

...

  1. seconds

...

  1. the

...

  1. job

...

  1. should

...

  1. finish

...

  1. successfully:

...

  1. Image Added
    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.
    Code Block
    
    
    select * from weblogs_agg limit 10;
    

...

  1. 
    


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

...

  1. in

...

  1. the

...

  1. Hive

...

  1. Shell.

...

Summary

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