KNIME Advanced Luck A Guide to KNIME Analytics Platform for Advanced Users

222 Pages·2019·8.99 MB·English

by  Rosario Silipo

1
Copyright©2019 by KNIME Press
All rights reserved. This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a
retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording or likewise.
This book has been updated for KNIME 3.7.
For information regarding permissions and sales, write to:
KNIME Press
Technoparkstr. 1
8005 Zurich
Switzerland
[email protected]

ISBN: 978-3-9523926-0-7

2
Table of Contents
Acknowledgements ...............................................................................................................................................................................................................11
Chapter 1. Introduction .........................................................................................................................................................................................................12
1.1. Purpose and Structure of this Book ..........................................................................................................................................................................12
1.2. Data and Workflows for this Book ............................................................................................................................................................................13
1.3. Memory Usage in KNIME Analytics Platform ............................................................................................................................................................15
Chapter 2. Database Operations ...........................................................................................................................................................................................18
2.1. Database Nodes: Modular vs. All-in-One Approach .................................................................................................................................................18
2.2. Connect to a Database: Database Connector Nodes ................................................................................................................................................19
(Generic) Database Connector ..........................................................................................................................................................................................20
Workflow Credentials ........................................................................................................................................................................................................21
Upload a new JDBC Driver .................................................................................................................................................................................................22
(Dedicated) SQLite Connector ...........................................................................................................................................................................................23
2.3. Select the Table to work on: the Database Table Selector Node..............................................................................................................................24
Database Table Selector ....................................................................................................................................................................................................24
2.4. Connect to a Database AND Select the Table: the Database Table Connector Node ...............................................................................................25
Database Table Connector ................................................................................................................................................................................................26
2.5. In-Database Processing .............................................................................................................................................................................................27
Database Row Filter ..........................................................................................................................................................................................................27
Database Column Filter .....................................................................................................................................................................................................29
Database Query .................................................................................................................................................................................................................30
Database SQL Executor .....................................................................................................................................................................................................31
SQL Inject ...........................................................................................................................................................................................................................32
3
SQL Extract ........................................................................................................................................................................................................................32
2.6. Looping on Database Data ........................................................................................................................................................................................33
Table Creator .....................................................................................................................................................................................................................34
Database Looping without Database Connection .............................................................................................................................................................35
Database Looping with Database Connection ..................................................................................................................................................................36
2.7. Read and Write Data resulting from a SQL Query ....................................................................................................................................................37
Database Connection Table Reader ..................................................................................................................................................................................37
Database Connection Table Writer ...................................................................................................................................................................................38
2.8. All in one Node: Database Connection, Table Selection, SQL Query, and Exporting Data .......................................................................................38
Database Reader without Database Connection ..............................................................................................................................................................39
Database Reader with Database Connection ....................................................................................................................................................................40
Database Writer: Settings tab with no Database Connection ..........................................................................................................................................41
Database Writer: SQL Types tab .......................................................................................................................................................................................42
2.9. Database UPDATE and DELETE Commands ..............................................................................................................................................................43
Database Delete ................................................................................................................................................................................................................43
Database Update ...............................................................................................................................................................................................................44
2.10. Big Data Platforms and MongoDB .........................................................................................................................................................................46
2.11. Exercises ................................................................................................................................................................................................................47
Exercise 1 ...........................................................................................................................................................................................................................47
Exercise 2 ...........................................................................................................................................................................................................................49
Exercise 3 ...........................................................................................................................................................................................................................51
Chapter 3. Accessing Information from the Web ..............................................................................................................................................................52
3.1. Accessing Google Sheets..........................................................................................................................................................................................52
4
Google Authentication ......................................................................................................................................................................................................53
Google Sheets Connection ................................................................................................................................................................................................54
Google Sheets Reader .......................................................................................................................................................................................................55
Google Sheets Appender ...................................................................................................................................................................................................57
Google Sheets Updater .....................................................................................................................................................................................................58
Google Sheets Writer ........................................................................................................................................................................................................59
3.2. Accessing REST Services .........................................................................................................................................................................................59
GET Request: “Configuration Settings” Tab ......................................................................................................................................................................62
GET Request: the other Tabs .............................................................................................................................................................................................63
JSON to Table ....................................................................................................................................................................................................................65
POST Request: “Configuration Settings” Tab ....................................................................................................................................................................67
POST Request: “Request Body” Tab ..................................................................................................................................................................................68
3.3. Web Crawling ...........................................................................................................................................................................................................68
HttpRetriever.....................................................................................................................................................................................................................70
HtmlParser ........................................................................................................................................................................................................................71
Content Extractor ..............................................................................................................................................................................................................71
3.4. Exercises ....................................................................................................................................................................................................................72
Exercise 1 ...........................................................................................................................................................................................................................72
Chapter 4. Date&Time Manipulation .................................................................................................................................................................................73
4.1. The Date&Time Type ...............................................................................................................................................................................................73
4.2. How to produce a Date&Time Column ..................................................................................................................................................................74
String to Date&Time ..........................................................................................................................................................................................................75
Date&Time to String ..........................................................................................................................................................................................................77
5
Create Date&Time Range ..................................................................................................................................................................................................78
4.3. Refine Date&Time Values .......................................................................................................................................................................................79
Modify Time ......................................................................................................................................................................................................................79
Date&Time Shift ................................................................................................................................................................................................................80
4.4. Row Filtering based on Date&Time Criteria .........................................................................................................................................................81
Date&Time-based Row Filter ............................................................................................................................................................................................82
Extract Date&Time Fields ..................................................................................................................................................................................................83
Date&Time Difference ......................................................................................................................................................................................................85
4.5. Moving Average and Aggregation .............................................................................................................................................................................86
Moving Average ................................................................................................................................................................................................................88
Moving Aggregation ..........................................................................................................................................................................................................89
4.6. Time Series Analysis ................................................................................................................................................................................................91
Lag Column ........................................................................................................................................................................................................................92
4.7. Exercises ...................................................................................................................................................................................................................94
Exercise 1 ...........................................................................................................................................................................................................................94
Exercise 2 ...........................................................................................................................................................................................................................95
Chapter 5. Flow Variables .....................................................................................................................................................................................................97
5.1. What is a Flow Variable? ...........................................................................................................................................................................................97
5.2. Creating a Flow Variable for all Nodes in the Workflow ...........................................................................................................................................98
5.3. Flow Variable Values as Node Settings ...................................................................................................................................................................100
The “Flow Variable” Button ............................................................................................................................................................................................101
The “Flow Variables” Tab in the Configuration Window ................................................................................................................................................102
5.4. Creating a Flow Variable from within a Workflow ..................................................................................................................................................103
6
Transform a Data Value into a Flow Variable..................................................................................................................................................................104
TableRow To Variable......................................................................................................................................................................................................104
Transform a Configuration Setting into a Flow Variable .................................................................................................................................................105
Quickforms to Create Flow Variables ..............................................................................................................................................................................108
Integer Input....................................................................................................................................................................................................................109
5.5. Inject a Flow Variable through the Flow Variable Ports ..........................................................................................................................................110
Flow Variable Injection into the Workflow .....................................................................................................................................................................111
Merge Variables ..............................................................................................................................................................................................................112
5.6. Quickforms, Wrapped Meta-nodes, and KNIME WebPortal ..................................................................................................................................112
5.7. Transform a Flow Variable into a Data Value..........................................................................................................................................................115
Variable To TableRow......................................................................................................................................................................................................115
5.8. Modifying Flow Variable Values ..............................................................................................................................................................................116
5.9. Other Quickform Nodes ..........................................................................................................................................................................................119
Value Selection ................................................................................................................................................................................................................120
File Upload ......................................................................................................................................................................................................................121
5.10. Composite View in Wrapped Metanodes ...........................................................................................................................................................122
Range Slider Filter Definition ..........................................................................................................................................................................................125
5.11. Exercises ..............................................................................................................................................................................................................127
Exercise 1 .........................................................................................................................................................................................................................127
Exercise 2 .........................................................................................................................................................................................................................128
Exercise 3 .........................................................................................................................................................................................................................130
Exercise 4 .........................................................................................................................................................................................................................132
Chapter 6. Loops .................................................................................................................................................................................................................134
7
6.1. What is a Loop .........................................................................................................................................................................................................134
6.2. Loop with a pre-defined number of iterations .......................................................................................................................................................136
Data Generator ...............................................................................................................................................................................................................137
Counting Loop Start ........................................................................................................................................................................................................139
Loop End ..........................................................................................................................................................................................................................139
6.3. Dedicated Commands for Loop Execution ..............................................................................................................................................................142
6.4. Appending Columns to the Output Data Table .......................................................................................................................................................144
Loop End (Column Append) ............................................................................................................................................................................................145
6.5. Loop on a List of Columns .......................................................................................................................................................................................147
Column List Loop Start ....................................................................................................................................................................................................148
6.6. Loop on a List of Values ...........................................................................................................................................................................................151
TableRow To Variable Loop Start ....................................................................................................................................................................................151
Cache ...............................................................................................................................................................................................................................153
Loop End (2 ports) ...........................................................................................................................................................................................................154
6.7. Loop on Data Groups and Data Chunks ..................................................................................................................................................................155
Group Loop Start .............................................................................................................................................................................................................156
Chunk Loop Start .............................................................................................................................................................................................................157
Breakpoint .......................................................................................................................................................................................................................159
6.8. Keep Looping till a Condition is verified ..................................................................................................................................................................159
Generic Loop Start ...........................................................................................................................................................................................................159
Variable Condition Loop End ...........................................................................................................................................................................................160
6.9. Recursive Loop ........................................................................................................................................................................................................161
Recursive Loop Start ........................................................................................................................................................................................................162
8
Recursive Loop End .........................................................................................................................................................................................................163
6.10. Exercises ..............................................................................................................................................................................................................164
Exercise 1 .........................................................................................................................................................................................................................164
Exercise 2 .........................................................................................................................................................................................................................166
Exercise 3 .........................................................................................................................................................................................................................167
Exercise 4 .........................................................................................................................................................................................................................169
Chapter 7. Switches .............................................................................................................................................................................................................172
7.1. Introduction to Switches .........................................................................................................................................................................................172
7.2. The “IF Switch”- “END IF” switch block ...................................................................................................................................................................173
IF Switch ..........................................................................................................................................................................................................................174
End IF ...............................................................................................................................................................................................................................175
Auto-Binner .....................................................................................................................................................................................................................177
7.3. The “Java IF (Table)” node.......................................................................................................................................................................................178
Java IF (Table) ..................................................................................................................................................................................................................179
7.4. The CASE Switch Block ............................................................................................................................................................................................180
CASE Switch Data (Start) .................................................................................................................................................................................................181
CASE Switch Data (End) ...................................................................................................................................................................................................182
7.5. Transforming an Empty Data Table Result into an Inactive Branch ........................................................................................................................183
Empty Table Switch .........................................................................................................................................................................................................184
7.6. Exercises ..................................................................................................................................................................................................................185
Exercise 1 .........................................................................................................................................................................................................................185
Exercise 2 .........................................................................................................................................................................................................................187
Chapter 8. Advanced Reporting ..........................................................................................................................................................................................190
9