Reading from a SQL Server database

Reading from a SQL Server database is easy using R.

Here's a process that shows how to do this from within a RapidMiner process.

Of course, you can use the built in "Read Database" operator to read from a database, but there are restrictions in the community version. By using R you can get partially round the restrictions but you should always be aware of your license agreement. Just because you can get round the license does not mean that the terms no longer apply. If you do something that would normally trigger the purchase of an additional license then you still need to. I'm not a lawyer thankfully but, you have been warned.

Having said that, there are situations where you have to try things to prove viability and get political buy-in before committing to a more serious plan where money is to be spent. Political buy-in, as everyone knows, can sometimes takes a very long time and even the most trivial objection can completely de-rail progress. Removing the ability to make a full prototype is just such a potential trivial objection.

Having said all of that, the method the process uses here will have some subtle differences in the way it interacts with the database when compared to the "Read Database" operator. This means it might not work for some reason as yet unknown. Simple advice, don't rely on it.

Enough words, on with the process.

The process has two parts, the first sets some macros that are used within the second. It's a little known fact that you can use macros in this way but it's extremely powerful and allows the code to work in lots of places. The macros themselves are shown in the following table.



Change these to match what you have in your environment. Note that I am using SQL Server authentication so this means you have to set up your environment like this. I am led to believe that built-in authentication is possible but I have not tried it.

The R code itself is shown here.



Additional points:

1. Install the RJDBC package into your environment, rJava may also be required.
2. Download the Microsoft JDBC drivers from here (note that care is always needed with downloads such as these because the vendors keep changing their Web sites).
3. If you are running on Ubuntu, the process will still work but there are some changes to do as shown in the R code.
4. I have not tried it on a Mac.
5. Change the query to whatever you want. The example here queries the system table.

The end result is an example set. The query shown in the example yields this.

You will see that the attribute names have been created automatically and a basic mapping to types has been done. The following shows part of the statistics for this example set.

One mapping that would need additional downstream work is the create_date attribute. It looks like it has been transformed into a polynominal. Closer inspection would, no doubt, reveal other foibles.

The example set can then be used in the normal way 

In summary, you can see that it is very easy to access SQL Server using R. It is therefore easy to do it from within RapidMiner.

Finding quartiles

Here's a process that finds the upper, middle and lower quartiles of a real valued special attribute within an example set and discretizes all the real values into the corresponding bins. It assumes there is one special attribute only. Additional special attributes would need to be de-selected as an extra step before being processed.

The process works as follows. After sorting the example set, it uses various macro extraction and manipulation operators to work out how many examples there are, determine the index corresponding to the quartile locations and from there the values of the attributes at these locations. These values are set as macros that are used in the "Discretize by User Specification" operator as boundaries between the quartile ranges in order to place each example into the correct bin.

The main work happens in a subprocess which makes the process easier to read and allows the operators to be moved to other processes more easily. The very useful operator "Rename by Generic Names" is used. This allows the macro manipulation operators to work without having to be concerned about the name of the special attribute which again allows the operators to be more portable when used in other processes.

Using Groovy to extract the last part of a folder structure

Imagine you are using "Loop Files" to find files one by one and import them perhaps using the "Read CSV" operator. The "Loop Files" operator provides macros such as file_path, file_name and so on to allow you to create meta data with the example set.

So if you have a folder name like this...

c:\users\andrew\bigdata\lotsofdata\subregion\

where each subregion contains many files and there are many different subregions. It makes sense to label all the files for a subregion. This can be done by using the folder name which is contained in the parent_path macro provided by the "Loop Files" operator. There is a lot of redundant information that it would be sensible to get rid of and I suppose it would be possible using some heavy combination of macro and attribute manipulation operators but I decided to write some Groovy to do it. The resulting script is simple.

String filePath = operator.getProcess().macroHandler.getMacro("parent_path")
String lastPart = filePath.tokenize('\\').last()
operator.getProcess().getMacroHandler().addMacro("subregion", lastPart);

It assumes a macro called parent_path which contains the folder name. The tokenize function splits this into tokens separated by "\" and the last one is returned using the last function. A macro called subregion is then created. This can be used as a normal macro.

Random walk in 3D

Here's a process that draws a pretty picture of a random walk in 3 dimensions in 3D.

OK - you have to work at it. But by squinting at the screen and going cross-eyed so the right side image appears in your left eye and the other in your right, you should see a 3 dimensional view of a random walk.

You may have to adjust the width of your window and who knows what else to make the images appear side by side. Persistence is valuable.

The colour of the points is set by the id. Blue is near 0 and red is at the end of the series. In this case, 2000 data points.

The process uses macros and the Loop Example operator to calculate a random amount to add to each data point. Use of the Integrate operator builds a cumulative example set where each example depends on the ones before it.

The process has the random seed set to -1. This means that it is very unlikely that the picture shown here will ever be recreated again.

How to read the contents of a file into a macro

I won't bore you with the "why", but suffice to say there are certain situations where it is useful to have the contents of a file contained in a macro. Obviously don't read a multi-Gb file into a macro, it might struggle.

Here's a process to do it.

It's very simple in fact. The trick is to use the "Read Document" operator followed by "Documents to Data". This has the effect of reading the entire contents of a file into a single named attribute in a one row example set. This by itself is useful but from there, it's a simple matter to use "Extract Macro" to make a macro equal to the value of the attribute for the single row.

Bulk export of processes

I am doing something at the moment which requires me to to export a load of processes contained in folders within a single repository to a single disk location. Rather than do it one by one which is error prone and time consuming, I decided to make a RapidMiner process to do the export.

It turns out that the files in the repository with the extension .rmp are valid xml files that can be imported so all I did was point a Loop Files operator at the folder where my repository was and looked for files ending in .rmp. Inside this loop, I used the Generate Macro operator to generate the new location and the Copy File operator to copy the file from the repository to the new location.

The location of the repository and the location where the files to be copied are defined in macros in the process context. Set these to the values you want. Note that on Windows machines it is necessary to use double backslashes to delimit folders.

The process is here.

Scaling attribute values using weights

Here's a process that multiplies each value of an attribute within one example set by a constant in another example set. The constants are specific for each attribute and the process uses weights derived from the example set. In effect, a matrix multiplication is happening.

At a high level, the process works as follows.

1. The Iris data set is used with weights being produced using "Weight By Information Gain"
2. These weights are transformed into an example set and stored for later use inside a Loop operator
3. A subprocess is used to make sure everything works in the right order (this technique is also used inside the Loop).
4. A "Loop Attributes" operator iterates over all attributes and generates a new attribute based on multiplying the existing value by a weight. The attribute name is required to be contained in the weights example set. 
5. The weight for each example is calculated with a combination of filtering and macro extraction.

Built-in macros

There are a number of pre-defined macros that can be used within RapidMiner. I keep forgetting the details of these so I decided to write them down once and for all.

These do not show up in the macro view but it is possible to use them like other macros.

I copied the following text from the version 4.6 RapidMiner documentation...

%{a} is replaced by the number of times the operator was applied.
%{b} is replaced by the number of times the operator was applied plus one, i.e. %a + 1. This is a shortcut for %p[1].
%{p[number }] is replaced by the number of times the operator was applied plus the given number, i.e. %a + number. (note - this should be %{p[N]}
%{t} is replaced by the system time.
%{n} is replaced by the name of the operator.
%{c} is replaced by the class of the operator.
%{%} becomes %.
%{process_name} becomes the name of the process file (without path and extension).
%{process_file} becomes the name of the process file (with extension).
%{process_path} becomes the path of the process file.

I've tried these - I can't get %{p[n]} to work nor all the ones starting with "process_". No matter, the others work.

Here is a screenshot of a Generate Macro process that uses them.

Here is a screenshot of the results from the Macro view.

Selecting attribute subsets using macros: a minor foible and workaround.

It is completely possible in RapidMiner to use macros as parameters to the "Select Attributes" operator but there are a couple of things to bear in mind.

Firstly, when displaying the parameters for the operator, the macro's "%", "{" and "}" are suppressed from the display as shown below.

Checking the XML reveals that all is well.



Secondly, there is another more subtle point. If the macro has been defined in the process context, its value is substituted into the parameter list. So in the example above, if the macro "id_attribute" had the value "fred" in the process context, this value is explicitly placed in the operator's parameters. That's fine for a local process but it makes it impossible to execute a process containing a macro used in this way if you want to pass a different value to the macro. Fortunately, it's easy to work around. Just ensure that any macros referenced in attribute selection do not exist in the process context by creating them using the "Set Macros" operator and setting their values to those already in the context.

Operators that deserve to be better known: part V

The "loop batches" operator splits an example set into batches for the inner operators to work on. The output is simply a copy of the full input example set. The results of the inner operators are not passed to the output because the idea is for these to process the batches perhaps by writing to a file or to a database.

When writing a large example set to a database, machine resource limits can prevent this from working so batching is a good way to proceed.

An example is provided. This takes an example set with 100 examples and uses the "loop batches" operator to write each batch to a file. A macro is used to make the file names unique.

Outputting the names and values of macros

Sometimes you need to know the name and value of a macro that has been defined somewhere else. To help myself, I created a Groovy script that reads all the defined macros and their value and prints them to the log. Add this small Groovy script at strategic places in your process to get visibility of what is happening.

for (String macroName : operator.getProcess().macroHandler.getDefinedMacroNames()) {
    String macroValue = operator.getProcess().macroHandler.getMacro(macroName);
    operator.logNote ("Macro name: value: " + macroName + " : " + macroValue);
}
return input;

Edit: Version 5.3 has a new macro view that does this :)

Generate Macro bonus features

I was pleased to discover that some of the functions available in the operator "Generate Attributes" are also available in the "Generate Macro" operator.

For example the following functions work.

concat
contains
matches
index
str
upper
lower
escape_html
replace

and I imagine that similar text processing functions will also work.

I tried date_now() and some other date functions and I got a result that looked like an error (actually quite an interesting error). So I assume date functions cannot be used.