Red Sqirl - Data Analytics platform introduction

Step 1: Learn about the Red Sqirl interface
Step 2: Learn how to copy data into HDFS
Step 3: Learn how to build a workflow
Step 4: Learn how to run that workflow
Step 5: Learn how to extract the result from that workflow

Section 1: On the left we have two tabs

The flow-chart canvas: this is where the workflow will be executed.
The remote file system: the system through which we can connect to any ssh (Secure Shell) server.

Section 2: On the top right is the help tab

A main section where we can find the generic options
An action section
We can find the action section by clicking this Home button,
This section describes each of the drag & drop actions that we can use.

Section 3: On the bottom right, we have the Hadoop Distributed File System

Go into the Red Sqirl installation folder, in my case it is /opt/redsqirl-2.4.0-0.12
Find the folder called “tutorial_data” and drag-and-drop it anywhere into our HDFS window.

Configure the source for a HDFS file
Select the sum of two columns
Get the average of a column
Get the sum of a column when grouping by another
Join the two tables
Filter a table with a condition

Click the green information symbol.
Once the configuration popup has appeared in the left column click “+”.
Type “extraPig” on the new empty line.
Click the “...” symbol.
On the new window, select redsqirl_pig in the drop-down menu
Click on the check box next to pig_audit, and click on the Select button.
Click OK.
Click OK.

Click the green information symbol.
Click on the check box beside “extraPig”.
Click on the Delete button (bin icon) in the table header.
Click OK.

An analysis always starts with a source action, so let’s set this action up first.
In the actions footer, drag a new source icon onto the canvas.

Now we just double click on it to configure it.
Name the action “communication“
Comment the action “Configure tutorial data“.
Click OK.
Now we’ll see a step-by-step window.
Select “Hadoop Distributed File System“ as the data type then click next.
Select “TEXT MAP-REDUCE DIRECTORY” as the data subtype and click next.
Next we have to chose the path, that we saved our file into.
Click on the search button, and click the refresh button, then find the tutorial data file and then the getting started file pig_tutorial_data.mrtxt
Click on the radio button beside pig_tutorial_data.mrtxt. If you cannot find it, refresh the view by clicking on the search button. Click OK.
Our data is now in the source action. At this stage, you will see the data correctly displayed on the screen, the name of the fields are “Field1 Long, Field2 Long...”
In this next step we can change the field name and its type by clicking the edit button on the top-left of the table

Copy and paste “subscriber_number STRING , Friend STRING , offpeak_voice INT , offpeak_sms INT , offpeak_mms INT ,peak_voice INT,peak_sms INT , peak_mms INT , sna_weight INT , subscriber_onnet INT ,friend_onnet INT” into the value field

Click OK. You will have the confirmation that the Header is correct.
Click OK to exit from the Configuration window.
If you leave the mouse cursor on the source action you will be able to see some configuration details
Save the Workflow by going into File > Save, name it pig_tutorial. By default, it is saved in the redsqirl-save HDFS directory and the file will have the extension ‘.rs’. Click OK to save.

We’ve gone through the interface
We’ve copied our data into the HDFS
And we’ve set up our source file and configured it

Set Up a Pig Aggregator Action (PAA)

Drag a PAA to the canvas.
Create a link between the source that was just configured and the new PAA by clicking between the image and the arc of the source action and then clicking on the pig aggregator image. (you’ll see an arrow connecting the two icons)
Open the new pig aggregator icon, name the element “nl_sum” and Click OK.
This first page lets us choose the field by which we want to aggregate. In this case, we want to aggregate by “subscriber_number” Select “subscriber_number” and click "Select" in the Group by interaction.
Click next.
On this second page we can do the operation field by field.
Select "copy" from the dropdown menu on the generator interaction and click OK. (We can also have a more granular choice of generation by clicking on the “Configure” button. Each Tab is a different configuration option.)

We can also sort the rows -- On the top of the table click the “+” symbol to add a new row to the table. One thing to note: the check box on each row is only used for sorting and deleting, we don’t need to have each row ticked in order to continue.

Click on the pen in Operation field of the new row and click the “SUM()” function and add the parameters “communication.offpeak_voice” and “communication.peak_voice”. In between the parameters add a “+” symbol so that the operation would read “SUM(communication.offpeak_voice + communication.peak_voice)”
click OK.
In the Field Name, type “total_voice” for the new column, and change the type to DOUBLE.
Click next.
The next page shows that we can sort the data. We won't sort it now, so click next.
The following page is about filter and format, click OK to leave the default parameters. (The filtering option is another text editor where we can just create a condition for filtering.)
We can choose the delimiter of the output, let’s use comma in the delimiter box. By default, the output type is compressed. In Red Sqirl the output format is important for linking one action to the next, the two types need to be compatible.
If we want we can also choose to do an audit of the output.
Click OK.

While this is running, the canvas can’t be modified. However, we can still create more workflows at this time.
Click on a new tab and drag and drop something onto a new canvas
We can create and run multiple workflows at the same time.
Also while the workflow is running, we can check progress in the Process Manager on the right-hand side.

Click on the Process Manager tab, and hit refresh. In here, we’ll see all of our processes This is also where you can pause or kill any running processes. We can also check the logs in the Oozie Control; just click on the left green button.

Create another PAA

Drop another Pig Aggregator onto the canvas.
Make a link between the source and the new Pig Aggregator.
Open it, and give it the name "comm_groupbyall" and click OK.
This time leave the group by list alone so nothing is selected and click next.
Create a new row (use the + button).
In this new row copy and paste “AVG(communication.offpeak_voice + communication.peak_voice)”.
Call the field “total_voice_avg” and select the DOUBLE type.
Click next.
Click next on the sorting page.
Click OK on the final page.

Perform a Pig Join Action

Drop a Pig Join onto the canvas.
Create a link from “comm_groupbyall” to the new Pig Join Action.
Create a link from “nl_sum” to the new pig join action.
Double click the Pig Join and call it “nl_vs_total”.
The first page lists the table aliases. Click next.
On the following page, make sure that “copy” is selected as the generator and click OK.
Click next.
This page has two interactions that specify the join type and the fields on which to join. We'll use the default join type (“Join”).
In “Join Field” column, type “1” in the two rows. This condition will join the two tables together.
Click next.
Click next on the sorting page.
Click OK on the final page.

Drop a new Pig Select action onto the canvas.
Create a link from the Pig Join Action to the new Pig Select Action.
Open it and change the element ID to be “high_voice”.
In the generation drop-down menu, select "copy" and click OK.
Click next.
In the Sorting page, select “nl_sum_total_voice” and “DESCENDING” order.
Click Next.
Click on the Pen in the Condition section and write “nl_sum_total_voice > comm_groupbyall_total_voice_av”.
Click OK, and finish the action by clicking OK.

Leave your mouse on “nl_vs_total”. Click Options button and select “Data Output” from the drop-down menu.
Select “BUFFERED” instead of “TEMPORARY”.
Click OK.

Covered the interface
Copied data into HDFS
Built a workflow
Extracted the results from that workflow
Configured the source for a HDFS file
Selected the sum of two columns
Got the average of a column
Got the sum of a column when grouping by another
Joined two tables
Filtered a table using a condition