Icon

04_​DB_​WritingToDB

04 DB WritingToDB Exercise Solution
Missing Values Strategy: 04_DB_WritingToDB This workflow implements a missing value strategy. It creates a model to predict values for column COW and uses COW predictions to overridemissing COW values. 1. It connects to the newCensus.sqlite database (SQLite) 2. reads the ss13pme table and joins, filters, and aggregates data through in-database manipulation 3. predicts COW value based on all other attributes in the same row 4. writes COW prediction where COW value is missing through a Database Update node 5. writes original table to ss13pme_original table with a Database Connection Table Writer node ... just in case we mess up with the updates 6. writes model and timestamp with a Database Writer node Note. Here you update the ss13pme table inthe SQLite database not to have any missingCOW values anymore. If you want to rerun thisworkflow successfully, please run the"01_regenerate ss13me table" workflow in1_Data folder. newCensus.sqliteselect * from ss13pmeremovePUMA* &PWGTP*COW is not NULLCOW is NULLremove COWCOW to stringappend predictedCOW columnimport all rows where COW is NOT NULLpredict COWimport all rows whereCOW is NULLsave original tablejust in case ...save modelswith timestampcheck update status(Should be empty)update theCOW valuefrom COW predictionin ss13pme where missing SQLite Connector DB Table Selector DB Column Filter DB Row Filter DB Row Filter DB Column Filter Number To String Decision TreePredictor DB Reader DecisionTree Learner DB Reader timestamp & model DB ConnectionTable Writer DB Writer Row Filter DB Update Missing Values Strategy: 04_DB_WritingToDB This workflow implements a missing value strategy. It creates a model to predict values for column COW and uses COW predictions to overridemissing COW values. 1. It connects to the newCensus.sqlite database (SQLite) 2. reads the ss13pme table and joins, filters, and aggregates data through in-database manipulation 3. predicts COW value based on all other attributes in the same row 4. writes COW prediction where COW value is missing through a Database Update node 5. writes original table to ss13pme_original table with a Database Connection Table Writer node ... just in case we mess up with the updates 6. writes model and timestamp with a Database Writer node Note. Here you update the ss13pme table inthe SQLite database not to have any missingCOW values anymore. If you want to rerun thisworkflow successfully, please run the"01_regenerate ss13me table" workflow in1_Data folder. newCensus.sqliteselect * from ss13pmeremovePUMA* &PWGTP*COW is not NULLCOW is NULLremove COWCOW to stringappend predictedCOW columnimport all rows where COW is NOT NULLpredict COWimport all rows whereCOW is NULLsave original tablejust in case ...save modelswith timestampcheck update status(Should be empty)update theCOW valuefrom COW predictionin ss13pme where missing SQLite Connector DB Table Selector DB Column Filter DB Row Filter DB Row Filter DB Column Filter Number To String Decision TreePredictor DB Reader DecisionTree Learner DB Reader timestamp & model DB ConnectionTable Writer DB Writer Row Filter DB Update

Nodes

Extensions

Links