Icon

01a Development data pipeline - Exercise

This workflow reads restaurant inspection reports in PDF format and inspection reviews in a JSON format, blends and transforms them, and loads the transformed data to the development database. Besides, it captures the data transformation segment and saves it as a separate Data Transformation workflow.

Learning objective: Practice using the Integrated Deployment extension to capture the data transformation workflow segment and save it as a new workflow


Workflow description: This workflow reads restaurant inspection reports in PDF format and inspection reviews in a JSON format, blends and transforms them, and loads the transformed data to the development database. Besides, it captures the data transformation segment and saves it as a separate Data Transformation workflow.


You'll find the instructions to the exercises in the yellow annotations.

Session 3 - ETL and ELT; Data Pipelines Validation and Deployment Automation

Exercise 01a Development data pipeline

Transform
Extract
Load

Get text from PDFs and Transform

Get Reviews from JSON files and transform

Calculate next Inspection date

Step 2 : Upload the Data Transformation workflow automatically

  1. Connect to KNIME Business Hub within the workflow

    • Use Space Connector node to connect to your private space on the KNIME Business Hub

    • Set knime-edu-hub as the Hub mountpoint, and select your space (ExerciseSpace-User##)

    • Connect the Space Connector node to the Workflow Writer node

  2. Write the workflow directly to the KNIME Business Hub Space. In the Workflow Writer node:

    • Overwrite if the workflow exists

    • Use custom workflow name: Inspection_Results_Transformation



PostgreSQL Database


Step 1 : Write the transformed data to database

  1. The Connector and DB Writer node are pre-configured. Execute them.


Note. When deploying on KNIME Business Hub, you'll use the PostgreSQL database.



Development Database


String to Date&Time
Capture Workflow End
Difference between Inspection date
Date&Time Difference
Path to URI
List Files/Folders
Append:inspection_cleaned
DB Writer
Capture Workflow Start
Column Filter
Next Inspection date
Date&Time Shift
Tika Parser URL Input
SQLite Connector
JSON Path
Read Inspection Reviews
JSON Reader
Text processing
Space Connector
Workflow Writer
Joiner

Nodes

Extensions

Links