Icon

m_​001_​prepare_​northwind_​db_​h2

Prepare the Northwind database into a consistant H2 db to provide a database environment to play with

These tables are included in the local database
knime://knime.workflow/../data/Northwind_small.h2

(they all have a unique ID "id")

shipper
product
customerdemographic
employee
customercustomerdemo
supplier
orders
category
region
employeeterritory
customer
orderdetail
territory

one VIEW with a combination of informations
productdetails_v

library(RSQLite)# ----- location of SQLite Database# define some basic variablesvar_loc_sqlite <- paste0(knime.flow.in[["var_path_data"]], "Northwind_small.sqlite")var_read_table <- knime.flow.in[["name"]]con = dbConnect(drv=RSQLite::SQLite(), dbname=var_loc_sqlite)tables <- dbListTables(con)# print(tables)knime.out <- dbReadTable(con, c(var_read_table))dbDisconnect(con) Prepare the Northwind database into a consistant H2 db to provide a database environment to play with These tables are included in the local databaseknime://knime.workflow/../data/Northwind_small.h2(they all have a unique ID "<tablename>id")shipperproductcustomerdemographicemployeecustomercustomerdemosupplierorderscategoryregionemployeeterritorycustomerorderdetailterritoryone VIEW with a combination of informationsproductdetails_v Northwind_small.sqliteNorthwind_small.h2determinepath and name of workflowread data from SQLitetableselect all Tablestransfer to tableset some PRIMARY KEYsList allTablesList all Columnsof "PUBLIC"v_big_data_schemaPUBLICremove H2 tableloop databaseslowercase column namesname_lowercreate tbalenotes in long characterno listsEND$name_lower$ = "order" =>"orders"TRUE => $name_lower$CREATE VIEWproductdetails_vremove H2 VIEWproductdetails_vList allViewsget data fromproductdetails_vVIEWPropagate R environmentfor KNIME on MacOS withMiniforge / Minicondaconfigure how to handle the environmentdefault = just check the namesPropagate R environmentfor KNIME on Windows withMinicondaconfigure how to handle the environmentdefault = just check the namesSHUTDOWN COMPACT; SQLite Connector H2 Connector Extract ContextProperties R Source (Table) determine paths DB Query Reader DB Writer DB SQL Executor DB Query Reader DB Query Reader String Input DB SQL Executor Table Row ToVariable Loop Start lowercasecolumn names String Manipulation DB Table Creator Column Filter Variable Loop End Rule Engine DB SQL Executor DB SQL Executor DB Query Reader DB Query Reader Merge Variables knime_r_environment knime_r_environment_windows DB SQL Executor library(RSQLite)# ----- location of SQLite Database# define some basic variablesvar_loc_sqlite <- paste0(knime.flow.in[["var_path_data"]], "Northwind_small.sqlite")var_read_table <- knime.flow.in[["name"]]con = dbConnect(drv=RSQLite::SQLite(), dbname=var_loc_sqlite)tables <- dbListTables(con)# print(tables)knime.out <- dbReadTable(con, c(var_read_table))dbDisconnect(con) Prepare the Northwind database into a consistant H2 db to provide a database environment to play with These tables are included in the local databaseknime://knime.workflow/../data/Northwind_small.h2(they all have a unique ID "<tablename>id")shipperproductcustomerdemographicemployeecustomercustomerdemosupplierorderscategoryregionemployeeterritorycustomerorderdetailterritoryone VIEW with a combination of informationsproductdetails_v Northwind_small.sqliteNorthwind_small.h2determinepath and name of workflowread data from SQLitetableselect all Tablestransfer to tableset some PRIMARY KEYsList allTablesList all Columnsof "PUBLIC"v_big_data_schemaPUBLICremove H2 tableloop databaseslowercase column namesname_lowercreate tbalenotes in long characterno listsEND$name_lower$ = "order" =>"orders"TRUE => $name_lower$CREATE VIEWproductdetails_vremove H2 VIEWproductdetails_vList allViewsget data fromproductdetails_vVIEWPropagate R environmentfor KNIME on MacOS withMiniforge / Minicondaconfigure how to handle the environmentdefault = just check the namesPropagate R environmentfor KNIME on Windows withMinicondaconfigure how to handle the environmentdefault = just check the namesSHUTDOWN COMPACT;SQLite Connector H2 Connector Extract ContextProperties R Source (Table) determine paths DB Query Reader DB Writer DB SQL Executor DB Query Reader DB Query Reader String Input DB SQL Executor Table Row ToVariable Loop Start lowercasecolumn names String Manipulation DB Table Creator Column Filter Variable Loop End Rule Engine DB SQL Executor DB SQL Executor DB Query Reader DB Query Reader Merge Variables knime_r_environment knime_r_environment_windows DB SQL Executor

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: m_lauber
Created at: 2021-12-29
On NodePit since: 2021-12-30
Last update: 2024-04-27
Created with KNIME version: v4.6.1
Tags: H2SQLItenorthwindsqldatabaserelational

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

Try NodePit Runner!