KNIME<\/span>, short for Konstanz Information Miner, is an open source data mining and analytics platform for turning data from multiple sources into charts, images, data models, and outputs, all in a single workflow designed and run from Eclipse. KNIME has become a very powerful, and popular ecosystem for predictions, machine learning, and other areas of data science.<\/span><\/p>\n KNIME projects consist of task-specific nodes, which are all the input, analytic and visualization pieces of the larger or job. KNIME provides many free nodes of its own, and a marketplace of nodes built by the <\/span>KNIME community<\/span><\/a>. There are nodes for machine and deep learning (AI), predictive analytics, custom code, and nodes for topics like molecular chemistry.\u00a0<\/span><\/p>\n The nodes are strung together in workflows can also run individually or in groups. But as with other BI and analytic platforms, KNIME jobs run slow given large data sources. Its own data sourcing and transformation nodes are inherently slow, and external nodes like Spark are limited in functional and data source scope.<\/span><\/p>\n Fortunately, a new node compatible with the <\/span>IRI Voracity<\/span><\/a> data manipulation and management platform — which is also built on Eclipse but powered by the <\/span>IRI CoSort<\/span><\/a> data manipulation engine\u00a0<\/span>called <\/span>SortCL<\/span><\/a> — can result in KNIME workflows finishing up to up to 10x faster. CoSort is a proven big data workhorse featuring superior transformation algorithms, optimized I\/O and memory use, and task consolidation.<\/span>1<\/sup><\/a><\/span><\/span><\/p>\n The “Voracity Job Source\u201d node for KNIME rapidly <\/span>wrangles raw data<\/span><\/a> using SortCL and pumps its results into a KNIME model in memory. This model can then be used by almost every other node in KNIME. Those nodes can thus work immediately with the filtered, transformed, cleansed, and PII-masked data that they need to succeed. This article covers the relative planning and performance of data wrangling in KNIME without, and then with, the Voracity node.<\/span><\/p>\n For testing the node, I used CSV source files of increasing size<\/span>2<\/sup><\/a><\/span>\u00a0<\/span>to compare the relative processing speeds and scalability of the single Voracity Job Source node against the typical KNIME nodes provided to read the same input and perform the same data transformations.\u00a0<\/span><\/p>\n My test files contained four transaction items: department, store number, date, and sales by department. They were chosen to simulate a data set from a department store chain where product sales are tallied by department and by store.\u00a0<\/span><\/p>\n All tests were performed in the <\/span>IRI Workbench<\/span><\/a> IDE for Voracity with the KNIME Analytics Platform installed in the same Eclipse workspace. I will explain how to load KNIME components into the IRI Workbench along with the Voracity node in another blog post.<\/span><\/p>\n The hardware is used for the benchmarks of both approaches was on an Intel hexa-core Windows 10 64-bit desktop with 12GB of RAM. The software versions used are CoSort v10 (in Voracity v2) and KNIME v4.<\/span><\/p>\n To perform a<\/span> group by<\/span><\/i> to find the total sales for each store, I needed to configure, connect, and run five nodes in KNIME to achieve the same result Voracity will using only one node.<\/span><\/p>\n The nodes needed for this test:<\/span><\/p>\n These nodes and others can be found in the node repository by either using the search bar or by navigating to, and selected them from, the respective categories in which they appear.<\/span><\/p>\n For the <\/span>Voracity Job Source<\/span><\/i> node to work, there needs to be a preexisting SortCL job script with a stdout (standard output \/ unnamed pipe) target specification. These jobs are also made in IRI Workbench, through a <\/span>variety of methods<\/span><\/a> like automatic job creation wizards, ETL-style mapping diagrams, and a syntax aware editor.<\/span><\/p>\n In this example, I am sorting and aggregating the test department store transaction data set in different-sized CSV files. This requires a single job script for the SortCL program to parse and run on the command line, or this case, to be launched in the Voracity provider node for KNIME.\u00a0<\/span><\/p>\n My job script is shown in IRI Workbench below, along with its dynamic outline and diagram view:<\/span><\/p>\n This job is then selected for execution in the one (and only) <\/span>Voracity <\/span><\/i>Job Source node required:<\/span><\/p>\n Note that a SortCL job can not only perform multiple transformations in the same script and I\/O pass, it can take multiple sources (files, tables, pipes, URLs, etc.) and produce multiple targets all at the same time. It uses source field names as the symbolic references for mapping the data to the outputs.<\/span>\u00a0<\/span>3<\/sup><\/a><\/span><\/span><\/p>\n Remember, the first target you want feeding to KNIME must be designated as stdout (standard output) for the data to flow from SortCL in memory to the KNIME table node. Without that defined pipe, the Voracity node will not work.<\/span><\/p>\n Using the Voracity Job Source node is similar to using any other node in KNIME. Just go to the IRI Voracity Node category and drag the Voracity Job Source into the workflow:<\/span><\/p>\n Open the Node and use the file browser to locate and select the target script (.scl file) that SortCL will run in the Voracity Job Source node for KNIME:\u00a0<\/span><\/p>\n After you select the script, click Apply and run the Voracity node to create and feed the SortCL results directly, through memory, into the target KNIME table. That created table can be transferred to other nodes for analytics, other mining, or visual nodes in KNIME:<\/span><\/p>\n Using the Global Timer node provided by KNIME, we can compare the speed of KNIME with and without the Voracity node in performing the same work. The listed times are each node\u2019s individual time in milliseconds from the workflow.<\/span><\/p>\n The following screenshots show how the normal Global Timer node\u2019s table would appear.<\/span><\/p>\n Times for 1 million rows with just KNIME Nodes:<\/span><\/p>\n Total time for those nodes:<\/span><\/p>\n Times for 1 million rows with Voracity in KNIME:<\/span><\/p>\n Total time for Voracity in KNIME:<\/span><\/p>\n After various tests on the increasing file sizes, below are my final results. The times listed are the average totals of all nodes involved to create the same table at the end of each workflow.<\/span><\/p>\n These times illustrate a major improvement over the functionally equivalent, required KNIME nodes; i.e., what takes seconds for Voracity takes minutes in KNIME.<\/p>\n More specifically, given the same flat-file CSV sources, Voracity wrangled that data up to 9 – 10x faster than KNIME! In fact, KNIME needed more time just to read the CSV file than Voracity needed to complete the entire data transformation job and transfer that data to KNIME.<\/span><\/p>\n By pre-processing and calculating data for KNIME <\/span>before <\/span><\/i>it has to convert it into its own table, we can greatly increase the speed at which KNIME can perform its total set of data preparation and analytic functions.\u00a0<\/span><\/p>\n In both methods of course, downstream analytics in the workflow proceeds identically, but the major bottleneck associated with them is alleviated when the Voracity node provides the data.<\/span><\/p>\n So far, the Voracity node works with KNIME inside native Eclipse builds like, or including, IRI Workbench. Learn how to install it here<\/a>. <\/span><\/p>\n![\"\"](\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/07\/Group-by-Store-Sales-Average-Times.png\")
<\/a><\/p>\nBackground<\/b><\/h4>\n
Benchmarking Environment<\/b><\/h4>\n
Prerequisite KNIME Nodes<\/b><\/h4>\n
![\"KNIME](\"\/blog\/wp-content\/uploads\/2019\/07\/Knime_close_up.png\")
<\/a><\/p>\n\n
Prerequisite Voracity (CoSort SortCL) Data Wrangling Job<\/b><\/h4>\n
![\"Voracity](\"\/blog\/wp-content\/uploads\/2019\/07\/Voracity_collage-1024x576.png\")
<\/a><\/p>\n![\"Voracity](\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/07\/Voracity_close_up-e1564520571932.png\")
<\/a><\/p>\nRunning the Voracity Job Source Node<\/b><\/h4>\n
![\"node](\"\/blog\/wp-content\/uploads\/2019\/07\/node_repo.png\")
<\/a><\/p>\n![\"Voracity](\"\/blog\/wp-content\/uploads\/2019\/07\/Voracity_box.png\")
<\/a><\/p>\n![\"Voracity](\"\/blog\/wp-content\/uploads\/2019\/07\/Voracity_collage2-1024x575.png\")
<\/a><\/p>\nRelative Speeds<\/b><\/h4>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2019\/07\/refined_knime_time_table.png\")
<\/a><\/p>\n![\"\"](\"\/blog\/wp-content\/uploads\/2019\/07\/knime_time_table_sum.png\")
<\/a><\/p>\n![\"\"](\"\/blog\/wp-content\/uploads\/2019\/07\/refined_voracity_time_table.png\")
<\/a><\/p>\n![\"\"](\"\/blog\/wp-content\/uploads\/2019\/07\/voracity_time_table_sum.png\")
<\/a><\/p>\n![\"\"](\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/07\/KNIME-4.0-times.png\")
<\/a><\/p>\nBottom Line<\/b><\/h4>\n
Next Steps<\/b><\/h4>\n