Icon

02_​In-Database_​processing_​and_​Logging - Exercise

Activity I. Extract the Context Properties for Logging1. Use the Extract Context Properties node, to read workflow context-related properties,including the workflow name and the logged-in user. This information will be used for logging.2. In the Configuration dialog of the node, include all the fields like context.workflow.name,context.workflow.path etc.3. Connect the output of the Extract Context Properties node to the input of the Try(VariablePort) node Session 4 - Introduction to Data EngineeringExercise 02 Loading Tables to Database and Logging Events Learning objective: In this exercise you will practice the concept of in-database processing and creation of logs that can register all the events.Workflow description: This workflow reads data from two separate tables in databases, performs in-database processing on these tables to create areport on the store level. The logging component at the end logs the important fields like execution status, time etc- In the first segment of the workflow, the DB tables - Orders and License Renewal are read and then in-database processing is performed to create astore-level report- The Try and Catch block makes sure the workflow execution never fails and the Catch Error node provides information on whether any nodes in theblock have failed during the execution- At the end, all the information from the Catch error node is used to log the events using the Logging ComponentYou will find the instructions to the exercises in the yellow annotations. Activity III. Logging the Events1. Set up the success and failure status messages using a flow variable with the VariableCreator node.- Open the Configuration dialog of the Variable Creator node on the top branch and create anew flow variable with Type: StringVariable Name: StatusValue: Success- Connect the flow variable output to the top input of the Catch Errors (Var Ports) node- Open the Configuration dialog of the Variable Creator node on the bottom branch and createa new flow variable with Type: StringVariable Name: StatusValue: Failure- Connect the flow variable output to the bottom input of the Catch Errors (Var Ports) node2. Set up the Logging component to log events. Further instructions are provided in theLogging component Activity II. Perform In-Database Processing1. Join the "Orders" data with the "Inspection Overall" table to get the order information alongwith the license renewal and validity using the DB Joiner node- Connect the DB Update node's bottom output (Inspection Overall table) to the top input ofthe DB Joiner node- Connect the DB Table Selector node's output (Orders data) to the bottom input of the DBJoiner node- Open the Configuration dialog. In the "Joiner Settings" tab, select the Join mode as "LeftOuter Join" and the Joining Columns as "Store_ID" in the left and right tables2. Let's create a report at the store level that summarizes the count of orders for various agegroups. Use the DB Pivot node to perform this pivot operation- Connect the output of the DB Joiner node to the input of DB Pivot node- Open the Configuration dialog. In the "Groups" tab, include the "Store_ID", "Renewal Status"and "License Validity" in the Group column(s).- In the "Pivots" tab, select the column "AgeGroup" and in the "Manual Aggregation" tab selectthe column "OrderNumber" and the Aggregation as "COUNT"- Execute the node and connect the output of this node to the DB Reader node Update Inspection table in DB In-Database Processing Export the Results Date Conversiondata WarehouseOrdersFailure messageSuccess message Try (VariablePorts) Data Extractionfrom NoSQL and PDF String to Date&Time SQLite Connector DB Update DB Table Selector DB Reader CSV Writer Replace MissingValues Active BranchInverter Variable Creator Variable Creator Catch Errors(Var Ports) Logging Activity I. Extract the Context Properties for Logging1. Use the Extract Context Properties node, to read workflow context-related properties,including the workflow name and the logged-in user. This information will be used for logging.2. In the Configuration dialog of the node, include all the fields like context.workflow.name,context.workflow.path etc.3. Connect the output of the Extract Context Properties node to the input of the Try(VariablePort) node Session 4 - Introduction to Data EngineeringExercise 02 Loading Tables to Database and Logging Events Learning objective: In this exercise you will practice the concept of in-database processing and creation of logs that can register all the events.Workflow description: This workflow reads data from two separate tables in databases, performs in-database processing on these tables to create areport on the store level. The logging component at the end logs the important fields like execution status, time etc- In the first segment of the workflow, the DB tables - Orders and License Renewal are read and then in-database processing is performed to create astore-level report- The Try and Catch block makes sure the workflow execution never fails and the Catch Error node provides information on whether any nodes in theblock have failed during the execution- At the end, all the information from the Catch error node is used to log the events using the Logging ComponentYou will find the instructions to the exercises in the yellow annotations. Activity III. Logging the Events1. Set up the success and failure status messages using a flow variable with the VariableCreator node.- Open the Configuration dialog of the Variable Creator node on the top branch and create anew flow variable with Type: StringVariable Name: StatusValue: Success- Connect the flow variable output to the top input of the Catch Errors (Var Ports) node- Open the Configuration dialog of the Variable Creator node on the bottom branch and createa new flow variable with Type: StringVariable Name: StatusValue: Failure- Connect the flow variable output to the bottom input of the Catch Errors (Var Ports) node2. Set up the Logging component to log events. Further instructions are provided in theLogging component Activity II. Perform In-Database Processing1. Join the "Orders" data with the "Inspection Overall" table to get the order information alongwith the license renewal and validity using the DB Joiner node- Connect the DB Update node's bottom output (Inspection Overall table) to the top input ofthe DB Joiner node- Connect the DB Table Selector node's output (Orders data) to the bottom input of the DBJoiner node- Open the Configuration dialog. In the "Joiner Settings" tab, select the Join mode as "LeftOuter Join" and the Joining Columns as "Store_ID" in the left and right tables2. Let's create a report at the store level that summarizes the count of orders for various agegroups. Use the DB Pivot node to perform this pivot operation- Connect the output of the DB Joiner node to the input of DB Pivot node- Open the Configuration dialog. In the "Groups" tab, include the "Store_ID", "Renewal Status"and "License Validity" in the Group column(s).- In the "Pivots" tab, select the column "AgeGroup" and in the "Manual Aggregation" tab selectthe column "OrderNumber" and the Aggregation as "COUNT"- Execute the node and connect the output of this node to the DB Reader node Update Inspection table in DB In-Database Processing Export the Results Date Conversiondata WarehouseOrdersFailure messageSuccess message Try (VariablePorts) Data Extractionfrom NoSQL and PDF String to Date&Time SQLite Connector DB Update DB Table Selector DB Reader CSV Writer Replace MissingValues Active BranchInverter Variable Creator Variable Creator Catch Errors(Var Ports) Logging

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: elisabeth.richter
Created at: 2023-11-07
On NodePit since: 2023-11-13
Last update: 2024-04-27
Created with KNIME version: v4.7.7

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!