Icon

kn_​example_​db_​h2_​create_​table_​from_​scratch

Example how to use H2 database to create table with upload and from scratch

Example how to use H2 database to create table with upload and from scratch
H2 is an SQL database that 'lives' in a single file on your disk or in memory so you have a fully functional databse at your fingertips.
An alternative (maybe mor robust) would be SQLite

Example how to use H2 database to create table with upload and from scratchH2 is an SQL database that 'lives' in a single file on your disk or in memory so you have a fully functional databse at your fingertips.An alternative (maybe more robust) would be SQLite -- create table in code from scratchDROP TABLE IF EXISTS "PUBLIC"."sample_01"; CREATE TABLE "PUBLIC"."sample_01" ( ID BIGINT NOT NULL AUTO_INCREMENT COMMENT 'id = Primary id (auto-increment)' , v_sale DOUBLE COMMENT 'v_sale = amount of sale in $' , v_cluster BIGINT COMMENT 'v_cluster = cluster of customer' , v_name VARCHAR COMMENT 'v_name = name of customer' , due_date TIMESTAMP COMMENT 'due_date = due date' , PRIMARY KEY (ID) ) ; H2 Supported Data Typeshttps://www.h2database.com/html/datatypes.htmlINTBOOLEANTINYINTSMALLINTBIGINTIDENTITYDECIMALDOUBLEREALTIMETIME WITH TIME ZONEDATETIMESTAMPTIMESTAMP WITH TIME ZONEBINARYOTHERVARCHARVARCHAR_IGNORECASECHARBLOBCLOBUUIDARRAYENUMGEOMETRYJSONINTERVAL -- 'manually' insert values into a tableINSERT INTO "PUBLIC"."sample_01" (v_sale, v_cluster, v_name, due_date)-- v_sale DOUBLE-- v_cluster BIGINT-- v_name VARCHAR-- due_date TIMESTAMPVALUES (45.45 , 2, 'Santa Claus' , '2020-01-02 11:00:00') , (34.8965 , 4, 'Mary Jane' , '2020-01-03 13:00:00') , (984.75 , 4, 'Peter Parker' , '2020-01-04 12:00:00'); CREATE TABLE "PUBLIC"."sample_05" AS (SELECT * FROM "PUBLIC"."sample_01" WHERE 1=2);ALTER TABLE "PUBLIC"."sample_05" ADD CONSTRAINT ID UNIQUE(ID);ALTER TABLE "PUBLIC"."sample_05" ALTER COLUMN ID SET NOT NULL;ALTER TABLE "PUBLIC"."sample_05" ADD CONSTRAINT "primary" PRIMARY KEY (ID);-- ALTER TABLE "PUBLIC"."sample_05" MODIFY ID BIGINT NOT NULL AUTO_INCREMENT;ALTER TABLE "PUBLIC"."sample_05" ALTER ID BIGINT NOT NULL AUTO_INCREMENT; INSERT INTO "PUBLIC"."sample_05"(v_sale, v_cluster, v_name, due_date)SELECT v_sale, v_cluster, v_name, due_date FROM "PUBLIC"."sample_01"; If you want to use WINDOW functions like RANK with H2 you can easily update the H2 driver:https://forum.knime.com/t/sqlite-and-window-functions/31608/4?u=mlauber71 DB in memoryor as fileon a drive=> modify driver if necessarycreatesample_01from scratchremove table if it exists to have a clean startsample_00sample filesample_00read emptysample_00sample_01=> check the typemappingsample_01sample_00sample_00with datainsert data into table by manually declaring the relevant partitionsample_01=> check the typemappingsample_01create a new emptytable sample_05with thestructure of an existing onesample_05=> check the typemappingsample_05insert data from the originaltable sample_01into the newtable sample_05sample_05=> check the typemappingsample_05List allTablesList all Columnsof "PUBLIC"1st create (empty)H2 tablesample_00with "new_id" as primary keynew_idGET H2 Database versionSELECT H2VERSION()https://www.h2database.com/html/functions.html#h2version H2 Connector DB SQL Executor DB Table Remover Data Generator DB Reader DB Table Selector DB Table Selector DB Reader DB Writer DB Reader DB SQL Executor DB Table Selector DB Reader DB SQL Executor DB Table Selector DB Reader DB SQL Executor DB Table Selector DB Reader DB Query Reader DB Query Reader DB Table Creator Java Snippet(simple) DB Query Reader Example how to use H2 database to create table with upload and from scratchH2 is an SQL database that 'lives' in a single file on your disk or in memory so you have a fully functional databse at your fingertips.An alternative (maybe more robust) would be SQLite -- create table in code from scratchDROP TABLE IF EXISTS "PUBLIC"."sample_01"; CREATE TABLE "PUBLIC"."sample_01" ( ID BIGINT NOT NULL AUTO_INCREMENT COMMENT 'id = Primary id (auto-increment)' , v_sale DOUBLE COMMENT 'v_sale = amount of sale in $' , v_cluster BIGINT COMMENT 'v_cluster = cluster of customer' , v_name VARCHAR COMMENT 'v_name = name of customer' , due_date TIMESTAMP COMMENT 'due_date = due date' , PRIMARY KEY (ID) ) ; H2 Supported Data Typeshttps://www.h2database.com/html/datatypes.htmlINTBOOLEANTINYINTSMALLINTBIGINTIDENTITYDECIMALDOUBLEREALTIMETIME WITH TIME ZONEDATETIMESTAMPTIMESTAMP WITH TIME ZONEBINARYOTHERVARCHARVARCHAR_IGNORECASECHARBLOBCLOBUUIDARRAYENUMGEOMETRYJSONINTERVAL -- 'manually' insert values into a tableINSERT INTO "PUBLIC"."sample_01" (v_sale, v_cluster, v_name, due_date)-- v_sale DOUBLE-- v_cluster BIGINT-- v_name VARCHAR-- due_date TIMESTAMPVALUES (45.45 , 2, 'Santa Claus' , '2020-01-02 11:00:00') , (34.8965 , 4, 'Mary Jane' , '2020-01-03 13:00:00') , (984.75 , 4, 'Peter Parker' , '2020-01-04 12:00:00'); CREATE TABLE "PUBLIC"."sample_05" AS (SELECT * FROM "PUBLIC"."sample_01" WHERE 1=2);ALTER TABLE "PUBLIC"."sample_05" ADD CONSTRAINT ID UNIQUE(ID);ALTER TABLE "PUBLIC"."sample_05" ALTER COLUMN ID SET NOT NULL;ALTER TABLE "PUBLIC"."sample_05" ADD CONSTRAINT "primary" PRIMARY KEY (ID);-- ALTER TABLE "PUBLIC"."sample_05" MODIFY ID BIGINT NOT NULL AUTO_INCREMENT;ALTER TABLE "PUBLIC"."sample_05" ALTER ID BIGINT NOT NULL AUTO_INCREMENT; INSERT INTO "PUBLIC"."sample_05"(v_sale, v_cluster, v_name, due_date)SELECT v_sale, v_cluster, v_name, due_date FROM "PUBLIC"."sample_01"; If you want to use WINDOW functions like RANK with H2 you can easily update the H2 driver:https://forum.knime.com/t/sqlite-and-window-functions/31608/4?u=mlauber71 DB in memoryor as fileon a drive=> modify driver if necessarycreatesample_01from scratchremove table if it exists to have a clean startsample_00sample filesample_00read emptysample_00sample_01=> check the typemappingsample_01sample_00sample_00with datainsert data into table by manually declaring the relevant partitionsample_01=> check the typemappingsample_01create a new emptytable sample_05with thestructure of an existing onesample_05=> check the typemappingsample_05insert data from the originaltable sample_01into the newtable sample_05sample_05=> check the typemappingsample_05List allTablesList all Columnsof "PUBLIC"1st create (empty)H2 tablesample_00with "new_id" as primary keynew_idGET H2 Database versionSELECT H2VERSION()https://www.h2database.com/html/functions.html#h2versionH2 Connector DB SQL Executor DB Table Remover Data Generator DB Reader DB Table Selector DB Table Selector DB Reader DB Writer DB Reader DB SQL Executor DB Table Selector DB Reader DB SQL Executor DB Table Selector DB Reader DB SQL Executor DB Table Selector DB Reader DB Query Reader DB Query Reader DB Table Creator Java Snippet(simple) DB Query Reader

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: 2020-09-18
On NodePit since: 2020-09-18
Last update: 2024-04-27
Created with KNIME version: v4.6.3
Tags: h2sqlcreatefromscratchdatabasewindowrank

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

Try NodePit Runner!