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

Versions Compared

Key

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

...

Tip
titleSpeed Tip

You can download the Kettle Job ^aggregate 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:


  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:


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


  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
      
      When you are done your window should look like:

      Click 'OK' to close the window.

  6. Save the Job: Choose 'File' -> 'Save as...' from the menu system. Save the transformation as 'aggregate_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.

...