Due to recent updates, all users are required to create an Altair One account to login to the RapidMiner community. Click the Register button to create your account using the same email that you have previously used to login to the RapidMiner community. This will ensure that any previously created content will be synced to your Altair One account. Once you login, you will be asked to provide a username that identifies you to other Community users. Email us at Community with questions.
MS SQL AND QUERY INPUT
I am new to Dataminer and programming. We have a MSSQL 2008 database which I need to query.
Is there some way to create an input interface which would allow me change the data-time variable which is the only thing
that changes in the query? We use a "start-time" and "stop-time" variable.
That is: WHERE start-time > '2011-11-01 03:00:00' and start-time < '2011-11-01 04:45:00'
I would like to have two variables StartTime1 and StartTime2 such that I could use that as a variable instead of the having to
input the times. We have anywhere from two to nine areas where the time is referenced. Having a variable instead of needing to
change each entry would greatly speed up the process as I need to run queries on over six months worth of data with various start-stop times and durations.
Thanks in advance.
Is there some way to create an input interface which would allow me change the data-time variable which is the only thing
that changes in the query? We use a "start-time" and "stop-time" variable.
That is: WHERE start-time > '2011-11-01 03:00:00' and start-time < '2011-11-01 04:45:00'
I would like to have two variables StartTime1 and StartTime2 such that I could use that as a variable instead of the having to
input the times. We have anywhere from two to nine areas where the time is referenced. Having a variable instead of needing to
change each entry would greatly speed up the process as I need to run queries on over six months worth of data with various start-stop times and durations.
Thanks in advance.
Tagged:
0
Answers
Set Macro defines a macro, i.e. a variable, called birthdate with a certain value. To use the macro later on, just enter the macro name into any parameter field of any operator like this: %{birthdate}.
Read Database executes a query: Please not the questionmark: it indicates, that this statement is a prepared statement. That means, that any questionmarks will be replaced by the operator during execution with certain values. The values can be specified if you enable the "prepare statement" parameter and then add an entry to the "parameters". There you specify VARCHAR as type and as value "%{birthdate}".
When you execute this operator, two things will happen:
1. %{birthdate} is replaced by '1954-01-01'
2. The questionmark in the SQL statement is replaced by '1954-01-01'
Even though prepared statements may seem a bit complicated, it's quite easy in reality, and they prevent nasty things like SQL injection etc.
Hope this helps! If you have any question left, please ask.
Best, Marius
So if I had 5 dates:
2012-07-15
2012-07-16
2012-07-17
2012-07-18
2012-07-19
The Read Database would execute once for each date, along with the downstream processing (that includes writing the result back to the database). Then loop and repeat for the next date.
The real problem I'm working around is that the data sets I am querying from the database are too large to fit into memory, but if I work through them in smaller chunks, it all runs fine.
Hints are greatly appreciated - it looks like I'll also be looking for more information about macros.
Thanks,
Asoka
Best,
Marius
I've done some hunting for documentation on any flavor of Loop, but I'm not finding anything. Do you know of a short "here's how to use Loop Value" video or other primer?
In effect, we're using the LOOP VALUE operator to establish a cursor, and then using the cursor variable on the inner set of queries to do some processing. The concept is clear, but getting them linked back and forth isn't working at a very basic level (I can get a straight passthrough on the inner loop to show me the loop values, but nothing else will pass the error stage).
And yes - by 'chunks', I mean those date (or in this case, hour) increments of the data set.
Loop Values iterates all unique values of the specified attribute, and sets a so-called macro to that value. Then it executes its inner process. To use the value of the macro, you write its name into a percentage sign followed by curly braces, like this: %{myMacro}
The attached process simply outputs the current loop value to the console, but you can use macros in any parameter text field (e.g. in Read Database)
This requires of course that you already retrieved the list of dates over which you want to iterate.
Best regards,
Marius