TC6.11 - Add a new ETL coping with a new Protocol

Primary tabs

Test Case Title

TC6.11 - Add a new ETL coping with a new Protocol

Goal

Snap4City, provide support for real time analytics which is performed by using a set of services. One of these is the possibility to:

Create ETL a new ETL Process with a new protocol.

Prerequisites

Using a PC or Mobile with a web browser

The following functionalities are available only for specific Snap4city users with specific privileges.

Expected successful result

Create an ETL process for data transformation directly in the snap4city context.

Steps

 

 

Please note that to correctly perform this Test Case you need and access to the ETL Virtual Machine as described below. To have access to a Virtual Machine to perform ETL please contact snap4city@disit.org. You can download the VM and use on your premise.

 

Users roles for which this test case is available: Area Manager, ToolAdmin, RootAdmin
Users roles for which the test case is not available: Manager
User used to describe the Example: ‘snap4city’

Examples:

  • Login on the snap4city platform
  • Open the VM from the snap4city home page
  • Create the ETL processes DIRECTLY from the Virtual Machine in the snap4city portal 

 


Example 0: Login on the snap4city platform

Users roles for which this test case is available: All
User used to describe this Examples: ‘snap4city’ (role: AreaManager)

  • Go to the snap4city home page 
  • Make the login
  • I can see the snap4city home page
  • Now you can select one of the functionalities on the left panel
    • Note that the list of the functionalities depends on the user role (and on the related permissions).


Fig: snap4city home page (after login).

 


Example 1: Open the VM from the snap4city home page

Users roles for which this test case is available: AreaManager, ToolAdmin, RootAdmin
User used to describe this Examples: ‘snap4city’ (role: AreaManager)

  • Open the VM from the snap4city home page (Example 1)
  • Go to the snap4city home page 
  • Make the login
  • If the user has the developer permissions (as provided for the ‘AreaManager’), he/she finds the menu ‘Development Tools > ETL development’
  • Click on the menu and insert the correct password (the same credentials two times)

 

Fig.: vnc connection.

 

Fig.: Virtual machine connection.


Example 2: Create the ETL processes DIRECTLY from the Virtual Machine in the snap4city portal
Users roles for which this test case is available: AreaManager, ToolAdmin, RootAdmin
User used to describe this Examples: ‘snap4city’ (role: AreaManager)

  • Make the login on the snap4city portal and open the Virtual Machine (Example 1)
  • To create an ETL process, it is necessary the use of the Pentaho Kettle tool Spoon (http://www.pentaho.com) installed on the Virtual Machine available in the snap4city portal

ETL creation, follow these steps:

  • Locate a dataset of interest, that can be static (e.g., the ‘http://www.nk.hel.fi/avoindata/avoin_data-avustukset.xls’) or real-time, then you can create the ETL (Extract Transform and Load) process to ingest the data.
  • Open Spoon via a Terminal emulator (search the Icon  at the bottom of the screen or click on ‘Application’ menu > ‘Terminal Emulator’), then write the command ‘spoon.sh’ and click ‘enter’. 


Fig. –Virtual Machine (VM) Terminal Emulator (command line).

 

  • The Spoon Interface will open, now it is possible to create/load ETL processes.


Fig.: Spoon Interface.

  • Create a new ETL MANAGING a new Protocolo, for example the ‘OneM2M’. Crete the ETL with a double click on the tab ‘jobs’ (or a right click and select ‘New’) in the left menu.


Fig.: Spoon search the menu for a new job. 

  • Then a new job ‘OneM2M’ is created (you can save/find it in the VM folder ‘/home/ubuntu/Desktop/snap4cityETL/Create_Snap4city_ETL_Sample‘). You can add to it the following blocks with the drag and drop functionality and connect each block with the hooks:
    • The ‘Start’ step
    • A new Transformation ‘OneM2M_protocol’
    • The Success step

 


Fig.: Spoon new job.

  • In the ‘OneM2M_protocol’ Transformation, you can manage the data download with the new protocol (from the job, right click > Open Referenced Object > Transformation):

  • You can use a javascript block ‘Parameters’ (in the tab ‘Design>Scripting>Modified Java Script Value’) and insert the credential as spoon variables:
    • Put the new variables in the javascript block (e.g. XM2MOrigin, XM2MRI, Accept=’application’, Authorization)
    • These variables must be passed as output variables of the block (‘Output fields’)
  • Now use the HTTP block (‘OneM2M_protocol’) and connect it with the previous ‘Parameters’ block. Fill the fields:
    • URL: ‘web service from which you want to take data’
    • Method: select the method used (e.g. HTTP)
    • Application type: select the application type (es. JSON)
    • Result field name: choose the name of the spoon variable into which the data are saved (e.g. ‘result’)

  • Write the data in a file:
    • Use a set of transformation block (if needed, for example the ‘Design>Transform> Replace in string’ step present in the following figure)
    • Use the block to the ‘Design>Output>Text file output and choose the directory (e.g. ‘home/ubuntu/Desktop/ETL_Output/OneM2M’), the file name (‘Result’) and its extension (‘json’)

  • Now you can launch your new ETL, from the job (click on ) and verify the presence of the results (‘Results.json’  file)