How to use a PDI job to load a data file into a Hive table.

Prerequisites

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

• Hadoop
• Pentaho Data Integration
• Hive

Sample Files

The sample data file needed for this guide is:

NOTE: If you have previously completed the "Using Pentaho MapReduce to Parse Weblog Data" guide the necessary files will already be the proper directory.

This file should be placed in the /user/pdi/weblogs/parse directory of HDFS using the following commands.

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

Step-By-Step Instructions

Setup

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

Create a Hive Table

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.

   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';
   

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

Create a Job to Load Hive

In this task you will be creating a job to load parsed and delimited weblog data into a Hive table. Once the data is loaded into the table, you will be able to run HiveQL statements to query this data.

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' job entry onto the job canvas. Your canvas should look like:
   
   
3. Add a Copy File Job Entry: You will need to copy the parsed file into the Hive table, so expand the 'Big Data' section of the Design palette and drag a 'Hadoop Copy Files' job entry onto the job canvas. Your canvas should look like:
   
   
4. Connect the Start and Copy Files job entries: Hover the mouse over the 'Start' job entry and a tooltip will appear. Click on the output connector (the green arrow pointing to the right) and drag a connector arrow to the 'Hadoop Copy Files' node. Your canvas should look like:
   
   
5. Edit the Copy Files Job Entry: Double-click on the 'Hadoop Copy Files' job entry to edit its properties. Enter this information:
   1. File/Folder source: hdfs://<NAMENODE>:<PORT>/user/pdi/weblogs/parse
      
   2. File/Folder destination: hdfs://<NAMENODE>:<PORT>/user/hive/warehouse/weblogs
   3. Wildcard (RegExp): Enter 'part-.*'
   4. Click the 'Add' button to add the files to the list of files to copy.
      When you are done your window should look like (your folder path may be different):
      
      Click 'OK' to close the window.
      Notice that you could also load a local file into hive using this step. The file does not already have to be in Hadoop.
6. Save the Job: Choose 'File' -> 'Save as...' from the menu system. Save the transformation as 'load_hive.kjb' into a folder of your choice.
7. 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:
   

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 limit 10;

3. 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 load data into a Hive table using a PDI job. PDI jobs can be used to put files into Hive from many different sources.
Other guides in this series cover how to transform data in Hive, get data out of the Hive, and report on data within the Hive.