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;
External resources
- Accessing Databases
- Manipulating Data on Databases
- Advanced Query Building
- Database Structure Manipulation
- Airline Dataset complete
- Example how to use H2 database to create table with upload and from scratch
- more H2 database examples
- KNIME Database Extension Guide
- about using KNIME and database connections
- SQL and Hive - special use cases
- (44727) forum entry
- Medium Blog: KNIME, Databases and SQL
- Adapted from this sample workflow "01 Practicing with Traditional Databases - Solution"
- If you want to insert binary / blob data into a H2 database - KNIME Forum (77927)
Used extensions & nodes
Created with KNIME Analytics Platform version 4.7.8
- Go to item
- Go to item
- Go to item
- Go to item
- Go to item
- Go to item
Loading deployments
Loading ad hoc jobs
Legal
By using or downloading the workflow, you agree to our terms and conditions.