ETL using RapidMiner Community Edition

Introduction

RapidMiner is one of the leading data mining software suites. With more than 400 data mining modules or operators, it is one of the most comprehensive and most flexible data mining tools available. With over 10,000 downloads from SourceForge.net each month and more than 300,000 downloads in total, it is also one of the most widespread-used data mining tools. According to polls by the popular data mining web portal KDnuggets.com among several hundred data mining experts, RapidMiner was the most widely used open source data mining tool and among the top three data mining tools overall in 2007 and 2008.

RapidMiner supports all steps of the data mining process from data loading, pre-processing, visualization, interactive data mining process design and inspection, automated modeling, automated parameter and process optimization, automated feature construction and feature selection, evaluation, and deployment. RapidMiner can be used as stand-alone program on the desktop with its graphical user interface (GUI), on a server via its command line version, or as data mining engine for your own products and Java library for developers.

Background

For my recent BI project I need a tool to transform a SQL Server 2008 database to PostgreSQL database with all data. After working with several tools I chose RapidMiner for the task and it shows its full color for this purpose.

ETL

ETL stands for Extract, Transform, Load. For example, you receive files or other data from vendors or other third parties which you need to manipulate in some way and then insert into your own database.

What you Need

Microsoft® SQL Server® 2008 Express

SQL Server 2008 Express is a free edition of SQL Server that is an ideal data platform for learning and building desktop and small server applications, and for redistribution by ISVs. You can download from http://www.microsoft.com/en-us/download/details.aspx?id=1695[^]

I assume you have installed Microsoft® SQL Server® 2008 Express. For further information you can visit http://msdn.microsoft.com/en-us/library/dd981045(v=sql.100).aspx.

AdventureWorks database

You can download from http://msftdbprodsamples.codeplex.com/downloads/get/478218[^] and attach:

RapidMiner

You can download from http://sourceforge.net/projects/rapidminer/files/1.%20RapidMiner/5.2/rapidminer-5.2.008x32-install.exe/download[^].

I assume you have installed RapidMiner. For further information you can visit http://rapid-i.com/wiki/index.php?title=RapidMiner_Installation_Guide.

ETL Process

Step 1:

Create a new process from File->New.

Step 2:

Create a new process from File->New.

Step 3:

Click Operators->Import->Data->Read Database.

Step 4:

Now we have to create a SQL Server connection for reading.

After creating the connection, assign the connection to read Database link.

Step 5:

Click Build Query and assign your Table.

Step 6:

Click Operators->Export->Data->Write CSV. Select Write CSV icon and assign value.

CSV file: Location of the file.

Column separator: Column separator character.

Connect out from "Read Database" to inp from "Write CSV".

Behind the scenes

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="5.2.008">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="5.2.008" expanded="true" name="Process">
    <process expanded="true" height="370" width="614">
      <operator activated="true" class="read_database" compatibility="5.2.008" 
              expanded="true" height="60" name="Read Database" width="90" x="179" y="75">
        <parameter key="connection" value="MSSQLServer-AdventureWorks2008"/>
        <parameter key="query" value="SELECT *
FROM "HumanResources"."Employee""/>
        <enumeration key="parameters"/>
      </operator>
      <operator activated="true" class="write_csv" compatibility="5.2.008" 
             expanded="true" height="76" name="Write CSV" width="90" x="380" y="75">
        <parameter key="csv_file" value="C:\Users\Masud\Desktop\exportcsv.csv"/>
        <parameter key="column_separator" value=","/>
      </operator>
      <connect from_op="Read Database" from_port="output" to_op="Write CSV" to_port="input"/>
      <connect from_op="Write CSV" from_port="through" to_port="result 1"/>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="0"/>
      <portSpacing port="sink_result 2" spacing="0"/>
    </process>
  </operator>
</process>

Step 7:

Now press Run or F11 you will see that all data will be exported as a CSV file in your destination. And output will be like:

Conclusion

RapidMiner Community Edition is an excellent tool for ETL.

History

None so far.