Icon

01_​SQL_​Server_​InDB_​Processing(Azure)

In-Database Processing on SQL Server

This workflow shows how to perform in-database processing on Micrsoft SQL Server. It starts with a dedicated SQL Server Connector node. It then shows column filtering, row filtering, aggregation, joining, and sorting. Many more in-database processing operations are possible either via nodes with graphical UI such as the ones below or via the Database Query node. The Database Query node in fact allows for custom manual SQL queries. The Database Connection Table Reader node at the end of most branches here passes the data into KNIME Analytics Platform. The Database Connection Table Writer node in the last branch writes the data into a new table in the SQL Server. In this last case, the data never leaves the SQL Server. Data for this example come from the new CENSUS dataset which is publicly available and can be downloaded from: http://www.census.gov/programs-surveys/acs/data/pums.html

Joiner Row Filter Aggregation Sorting In-Database Processing In-Database Processing on SQL ServerThis workflow shows how to perform in-database processing on Micrsoft SQL Server. It starts with a dedicated SQL Server Connector node.It then shows column filtering, row filtering, aggregation, joining, and sorting. Many more in-database processing operations are possible either via nodes with graphical UI suchas the ones below or via the Database Query node. The Database Query node in fact allows for custom manual SQL queries.The Database Connection Table Reader node at the end of most branches here passes the data into KNIME Analytics Platform. The Database Connection Table Writer node inthe last branch writes the data into a new table in the SQL Server. In this last case, the data never leaves the SQL Server.Data for this example come from the new CENSUS dataset which is publicly available and can be downloaded from: http://www.census.gov/programs-surveys/acs/data/pums.html A full explanation of all attributes can be found in: http://www2.census.gov/programs-surveys/acs/tech_docs/pums/data_dict/PUMSDataDict15.pdf SQL Query Datasets used inthis example areavailable here. COW is NULLconnect toSQL ServerInsert <hostname> and<Credentials> here.Use of Credentials is recommended over simpleusername and password.Credentials are defined at the workflow levelRight-click the workflow in KNIME Explorer panel andselect "Workflow Credentials".Parameter field might need customization.average AGEPfor each SEX groupCOW is NOT NULLimport all rows where COW is NOT NULLimport all rows whereCOW is NULLsex groups = 1, 2descendingby AGEPoldest 10 peopleby AGEPselect * from ss13pmeinner join on SERIAL NOselect * from ss13hmeimport all data forperson & housefilesrm PUMA*PWGTP*Node 181write your own SQL queryfile ss13hme.csvfile ss13pme.csvDatabase Row Filter SQL ServerConnector Database GroupBy Database Row Filter Database ConnectionTable Reader Database ConnectionTable Reader Database ConnectionTable Reader Database Sorter Database ConnectionTable Reader Database TableSelector Database Joiner Database TableSelector Database ConnectionTable Reader DatabaseColumn Filter Database ConnectionTable Writer Database Query File Reader File Reader Joiner Row Filter Aggregation Sorting In-Database Processing In-Database Processing on SQL ServerThis workflow shows how to perform in-database processing on Micrsoft SQL Server. It starts with a dedicated SQL Server Connector node.It then shows column filtering, row filtering, aggregation, joining, and sorting. Many more in-database processing operations are possible either via nodes with graphical UI suchas the ones below or via the Database Query node. The Database Query node in fact allows for custom manual SQL queries.The Database Connection Table Reader node at the end of most branches here passes the data into KNIME Analytics Platform. The Database Connection Table Writer node inthe last branch writes the data into a new table in the SQL Server. In this last case, the data never leaves the SQL Server.Data for this example come from the new CENSUS dataset which is publicly available and can be downloaded from: http://www.census.gov/programs-surveys/acs/data/pums.html A full explanation of all attributes can be found in: http://www2.census.gov/programs-surveys/acs/tech_docs/pums/data_dict/PUMSDataDict15.pdf SQL Query Datasets used inthis example areavailable here. COW is NULLconnect toSQL ServerInsert <hostname> and<Credentials> here.Use of Credentials is recommended over simpleusername and password.Credentials are defined at the workflow levelRight-click the workflow in KNIME Explorer panel andselect "Workflow Credentials".Parameter field might need customization.average AGEPfor each SEX groupCOW is NOT NULLimport all rows where COW is NOT NULLimport all rows whereCOW is NULLsex groups = 1, 2descendingby AGEPoldest 10 peopleby AGEPselect * from ss13pmeinner join on SERIAL NOselect * from ss13hmeimport all data forperson & housefilesrm PUMA*PWGTP*Node 181write your own SQL queryfile ss13hme.csvfile ss13pme.csvDatabase Row Filter SQL ServerConnector Database GroupBy Database Row Filter Database ConnectionTable Reader Database ConnectionTable Reader Database ConnectionTable Reader Database Sorter Database ConnectionTable Reader Database TableSelector Database Joiner Database TableSelector Database ConnectionTable Reader DatabaseColumn Filter Database ConnectionTable Writer Database Query File Reader File 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: koetter
Created at: 2015-07-31
On NodePit since: 2019-07-12
Last update: 2025-02-11
Created with KNIME version: v3.3.2
Tags: ETL KNIME Microsoft Azure In database Processing SQL Server

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

Try NodePit Runner!