Icon

kn_​forum_​44727_​l4_​bd_​course_​practice_​db_​tables

forum 44727 - 01 Practicing with Traditional Databases

forum 44727 - 01 Practicing with Traditional Databases

Exercise 1: Practicing with Traditional Databases

In this exercise you'll create a database table, and load and manipulate airline data (US domestic flights in January 2007) in the database.

1) Read the AirlineDataset.table file and connect to the H2 database by executing the nodes below
2) Create a new table "airline" on the database based on the table specs of the local data table. Replace the existing table. Use dynamic settings.
3) Insert the data into the airline database table

Perform the following tasks directly on the database using the DB nodes:
4) Filter the data to flights that were not cancelled AND had Austin-Bergstrom International Airport ("AUS") as destination
5) Write the filtered data into a new database table called "airline_Austin"
6) Read the airline_Austin table into KNIME
7) Optional: Delete the cancelled flights directly on the database with the following statement in the DB SQL Executor node:
DELETE from "PUBLIC"."airline" where "Cancelled" = 1;

forum 44727 - 01 Practicing with Traditional DatabasesExercise 1: Practicing with Traditional Databases (https://hub.knime.com/-/spaces/-/~LdjNrZw6Hm7ZUg4J/current-state/)In this exercise you'll create a database table, and load and manipulate airline data (US domestic flights in January 2007) in the database.1) Read the AirlineDataset.table file and connect to the H2 database by executing the nodes below2) Create a new table "airline" on the database based on the table specs of the local data table. Replace the existing table. Use dynamic settings.3) Insert the data into the airline database tablePerform the following tasks directly on the database using the DB nodes:4) Filter the data to flights that were not cancelled AND had Austin-Bergstrom International Airport ("AUS") as destination5) Write the filtered data into a new database table called "airline_Austin" 6) Read the airline_Austin table into KNIME7) Optional: Delete the cancelled flights directly on the database with the following statement in the DB SQL Executor node:DELETE from "PUBLIC"."airline" where "Cancelled" = 1; KNIME, Databases and SQLhttps://medium.com/p/273e27c9702a If you want to insert binary / blob data into a H2 database - KNIME Forum (77927)https://forum.knime.com/t/inserting-binary-data-into-sql-database-using-knime/77927/5?u=mlauber71 knime://knime.workflow/data/database.mv.dbdatabase.mv.dbAirlineDataset_10k.tablecreate the (empty)table with justthe structure of the databsefrom your KNIME tablefill the table"airline"with dataname of the table"airline"define the filterDest = "AUS"extract the SQL syntax(of the Row Filter)read thedatafrom"airline_Austin"apply the 'pure'SQL syntaxselect the tableread the (empty)tableDELETE from "PUBLIC"."airline" where "Cancelled" = 1;directlycreate new table"airline_Austin"List allTables in the databaseSHUTDOWN COMPACT;https://www.h2database.com/html/commands.html#shutdown=> you can send commands in open code that will just be executedon the database H2 Connector Table Reader DB Table Creator DB Insert StringConfiguration DB Row Filter DB Query Extractor DB Reader DB ConnectionExtractor DB Query Reader DB Table Selector DB Reader DB SQL Executor Merge Variables DB ConnectionTable Writer DB Query Reader DB SQL Executor forum 44727 - 01 Practicing with Traditional DatabasesExercise 1: Practicing with Traditional Databases (https://hub.knime.com/-/spaces/-/~LdjNrZw6Hm7ZUg4J/current-state/)In this exercise you'll create a database table, and load and manipulate airline data (US domestic flights in January 2007) in the database.1) Read the AirlineDataset.table file and connect to the H2 database by executing the nodes below2) Create a new table "airline" on the database based on the table specs of the local data table. Replace the existing table. Use dynamic settings.3) Insert the data into the airline database tablePerform the following tasks directly on the database using the DB nodes:4) Filter the data to flights that were not cancelled AND had Austin-Bergstrom International Airport ("AUS") as destination5) Write the filtered data into a new database table called "airline_Austin" 6) Read the airline_Austin table into KNIME7) Optional: Delete the cancelled flights directly on the database with the following statement in the DB SQL Executor node:DELETE from "PUBLIC"."airline" where "Cancelled" = 1; KNIME, Databases and SQLhttps://medium.com/p/273e27c9702a If you want to insert binary / blob data into a H2 database - KNIME Forum (77927)https://forum.knime.com/t/inserting-binary-data-into-sql-database-using-knime/77927/5?u=mlauber71 knime://knime.workflow/data/database.mv.dbdatabase.mv.dbAirlineDataset_10k.tablecreate the (empty)table with justthe structure of the databsefrom your KNIME tablefill the table"airline"with dataname of the table"airline"define the filterDest = "AUS"extract the SQL syntax(of the Row Filter)read thedatafrom"airline_Austin"apply the 'pure'SQL syntaxselect the tableread the (empty)tableDELETE from "PUBLIC"."airline" where "Cancelled" = 1;directlycreate new table"airline_Austin"List allTables in the databaseSHUTDOWN COMPACT;https://www.h2database.com/html/commands.html#shutdown=> you can send commands in open code that will just be executedon the database H2 Connector Table Reader DB Table Creator DB Insert StringConfiguration DB Row Filter DB Query Extractor DB Reader DB ConnectionExtractor DB Query Reader DB Table Selector DB Reader DB SQL Executor Merge Variables DB ConnectionTable Writer DB Query Reader 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: Maarit Laukkanen
Created at: 2021-03-11
On NodePit since: 2022-07-05
Last update: 2024-04-27
Created with KNIME version: v4.7.8
Tags: big datadatabaseh2sqlqueryknime

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

Try NodePit Runner!