Icon

01a Development data pipeline

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. Connect to development database and write the data

    • In the PostgreSQL Connection component:

      • Provide the credentials, hostname, database and schema names as provided by the course trainers

      • Provide the table name as inspection_cleaned

    • The DB Writer node is pre-configured and is controlled by the variables coming from the component.



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
JSON Path
Read Inspection Reviews
JSON Reader
Text processing
Space Connector
Workflow Writer
Joiner
PostgreSQL Connection

Nodes

Extensions

Links