Icon

02.2 ELT on website usage data - transform big data on cloud and Spark

<p>The company tracks the usage of the website and stores the information about different actions during each session: login and logout times, opened pages, clicked buttons, as well as the session satisfaction score (optional) and wants to calculate statistics for each customer, e.g., total number of visits, average satisfaction, etc.</p><p><em>Note. Session satisfaction score column has missing values which can be imputed using machine learning predictions.</em></p><p>We access the website usage data from the local big data environment (set up in the exercise workflow 02.1) and personal data (anonymized &amp; updated in exercise workflow 01) and contracts data from a database. We then perform in-database processing, import data into Spark, enrich the website usage data with the personal and contract data to predict missing session satisfaction scores, and save the aggregated data.</p>

Exercise workflow 02.2

ELT on website usage data - transform big data on cloud and Spark

Learning objective: Transform big data using SQL and Spark.


Workflow description: The company tracks the usage of the website and stores the information about different actions during each session: login and logout times, opened pages, clicked buttons, as well as the session satisfaction score (optional) and wants to calculate statistics for each customer, e.g., total number of visits, average satisfaction, etc.

Note. Session satisfaction score column has missing values which can be imputed using machine learning predictions.

We access the website usage data from the local big data environment (set up in the exercise workflow 02.1) and personal data (anonymized & updated in exercise workflow 01) and contracts data from a database. We then perform in-database processing, import data into Spark, enrich the website usage data with the personal and contract data to predict missing session satisfaction scores, and save the aggregated data.


You'll find the instructions to the exercises in the yellow annotations. Make sure you completed the previous exercises before running this workflow.

Step 2. Aggregate and save statistics table

Open the component to find the instructions



Step 1. Access the big data, transform on database, import into Spark

Open the component to find the next instructions.



Step 3.1. Handle errors

  1. Initiate the Try-Catch construction

    1. Add a Variable Creator node. You don't need to create any variable as this node's purpose is to initiate the Try (Variable Ports) node.

    2. Add the Try (Variable Ports) node and connect it to the Access & Transformation component



Step 3.2 Handle errors

  1. Close the Try-Catch construction to output an integer flow variable called ELT_failed indicating either a successful ETL process execution (=0) or a failure (=1)

    1. Add two Variable Creator nodes:

      • The 1st one should create a variable ELT_failed = 0

      • The 2nd one - a variable ELT_failed = 1

    2. Connect the Database Update component to the 1st Variable Creator node

    3. Provide the variable ELT_failed = 0 to the upper port of the Catch Errors (Var Ports) node and ELT_failed = 1 to the bottom port of the Catch Errors (Var Ports) node



Step 4. Export workflow status

  1. Filter out all the variables except ELT_failed, FailingNodeMessage, and FailingNode to the calling workflow (Variable Filter node)

  2. Export the mentioned variables to the caller workflow (Workflow Service Output node, flow variable ports)



Transform

Execute up-stream before configuration
Saving
Missing Value Imputation
Access & Transformation

Nodes

Extensions

Links