Icon

Data Blending

Data Blending and ETL
Data Blending and ETLThis workflow illustrates how to blend different data sources whithin KNIME Analytics Platform. A Java Script node is used to visualize average age vs. product in the resulting blended table.Task Blend together many different data sources through an effective ETL (Extract, Transform and Loading).Learning Hub http://www.knime.org/learning-hub contains useful links, e.g. tutorials, videos, books, slides, etc ... for each kind of data analytics topic. Data Reading 6 data sources: - CSV file - Webdata Old System.csv (Old WebSystem) - SQLite Database - WebActivity.sqlite (New WebSystem) - .table file - Sentiment Analysis.table - CSV file - Sentiment Rating.csv - CSV file - Demographics.csv - Excel file - Prooduct Data2.xlsThe files are located in TheData/Misc/ Data Blending & ETL - Concatenate web activity data from old and new systems - Replace sentiment texts with numeric scores - Set all product names to lowercase - Join all data together by the customerKey column - Group by products, and aggregate by age (mean) Visualize Create a bar chart showing the average age of customers foreach product All joins are inner joinson column CustomerKey Try this:KNIME's InteractiveVisualizations:1) Execute the workflow up tothe GroupBy node2) Open the configurationwindow for the JavaScript BarChart node 3) Try to personalize thevisualization of the plot, usingthe General Plot options andthe Control option. Replace sentiment labels with sentiment ratingsConcatenate the two data tablesAdd Demographics datajoinweb activity &sentiment scoresAdd product dataExclude Sentiment Ratingand Web ActivityRename First(WebActivity) as WebActivityReadingDemographics.csvReading SentimentAnalysis.tableReadingSentimentRating.csvReading WebdataOldSystem.csvProducts in lower caseCalculate average customer agebyproductvisualizeaverage age vs. productReadingProductData2.xlsReading WebActivity.sqliteFirst Web Activityfrom table WebActivity Cell Replacer Concatenate Joiner Joiner Joiner Column Filter Column Rename File Reader Table Reader File Reader File Reader String Manipulation GroupBy Bar Chart(JavaScript) Excel Reader (XLS) SQLite Connector DB Query Reader Data Blending and ETLThis workflow illustrates how to blend different data sources whithin KNIME Analytics Platform. A Java Script node is used to visualize average age vs. product in the resulting blended table.Task Blend together many different data sources through an effective ETL (Extract, Transform and Loading).Learning Hub http://www.knime.org/learning-hub contains useful links, e.g. tutorials, videos, books, slides, etc ... for each kind of data analytics topic. Data Reading 6 data sources: - CSV file - Webdata Old System.csv (Old WebSystem) - SQLite Database - WebActivity.sqlite (New WebSystem) - .table file - Sentiment Analysis.table - CSV file - Sentiment Rating.csv - CSV file - Demographics.csv - Excel file - Prooduct Data2.xlsThe files are located in TheData/Misc/ Data Blending & ETL - Concatenate web activity data from old and new systems - Replace sentiment texts with numeric scores - Set all product names to lowercase - Join all data together by the customerKey column - Group by products, and aggregate by age (mean) Visualize Create a bar chart showing the average age of customers foreach product All joins are inner joinson column CustomerKey Try this:KNIME's InteractiveVisualizations:1) Execute the workflow up tothe GroupBy node2) Open the configurationwindow for the JavaScript BarChart node 3) Try to personalize thevisualization of the plot, usingthe General Plot options andthe Control option. Replace sentiment labels with sentiment ratingsConcatenate the two data tablesAdd Demographics datajoinweb activity &sentiment scoresAdd product dataExclude Sentiment Ratingand Web ActivityRename First(WebActivity) as WebActivityReadingDemographics.csvReading SentimentAnalysis.tableReadingSentimentRating.csvReading WebdataOldSystem.csvProducts in lower caseCalculate average customer agebyproductvisualizeaverage age vs. productReadingProductData2.xlsReading WebActivity.sqliteFirst Web Activityfrom table WebActivity Cell Replacer Concatenate Joiner Joiner Joiner Column Filter Column Rename File Reader Table Reader File Reader File Reader String Manipulation GroupBy Bar Chart(JavaScript) Excel Reader (XLS) SQLite Connector 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
On NodePit since: 2019-12-14
Last update: 2024-04-20
Created with KNIME version: v4.0.0
Tags: Sources SQLite Data Blending ETL

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

Try NodePit Runner!