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.
RM5: Use macros in SQL statements/iterate over tables
Hy, in RM 4.6 something like this was possible:"(select * from %{turn})" if you want to get data from several database tables)
How can you do this in RM 5. It is not possible to iterate over SQL tables or to use macros in SQL queries.
How can you do this in RM 5. It is not possible to iterate over SQL tables or to use macros in SQL queries.
0
Answers
why do you mean, that's not possible to use macros in SQL Querries in RM5? You might enter the macro in the table_name parameter for example.
Greetings,
Sebastian
Can you please try the following:
use a query that contains a macro, e.g.
select * from %{my_macro}
After confirming with OK, the query is parsed to
select * from my_macro
which will definitively generate an error.
Can you try it and send me a short example script?
Make sure that you are using the most current version of RapidMiner (5.0.001).
Also you might want to try to click on View -> Show View -> Tree
Then make sure the macro is defined before the read database operator.
Hope this helps,
Cleo
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="5.0">
<context>
<input>
<location/>
</input>
<output>
<location/>
<location/>
</output>
<macros/>
</context>
<operator activated="true" class="process" expanded="true" name="Process">
<process expanded="true" height="251" width="882">
<operator activated="true" class="set_macro" expanded="true" height="60" name="Set Macro" width="90" x="45" y="75">
<parameter key="macro" value="tableName"/>
<parameter key="value" value="gbpusd_d1"/>
</operator>
<operator activated="true" class="read_database" expanded="true" height="60" name="Read Database" width="90" x="179" y="75">
<parameter key="connection" value="ForexDBConnection"/>
<parameter key="query" value="select * FROM %{tableName} LIMIT 10"/>
</operator>
<connect from_op="Read Database" from_port="output" 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>
just a small addition since not everyone might know this really great feature: This is correct. You can use the tree for that.
However, there is another - in my opinion even more intuitive - way for defining the order of execution: In the process view (the main area where you add the operators to and connect them), there is a icon in the top toolbar:
If you press this icon, the process view shows the execution order of the operators, for example like this:
Here you can click on operators and redefine the execution order. Of course, this is not arbitrarily possible but also in ways which still obey the connections of the operators. Finally, you can also right click on an operator and bring it to the front of the execution order. I really like this part of RapidMiner a lot ;D
Cheers,
Ingo
However, the meta-data for this query can not be extracted and complains. I have detailed this in bug 367, http://bugs.rapid-i.com/show_bug.cgi?id=367
of course the meta data cannot be extracted. Before actually executing the process, the macro isn't assigned and since cannot be replaced by its value. Without this the sql query will fail.
Greetings,
Sebastian
that would mean that in every process that uses macros, even statically defined ones (Set Macro), I will have to live with warnings and yellow attention-signs for every single operator that uses macros? (With the additional hardship that those warnings/fails will propagate downstream throughout my whole process, creating huge amounts or warningsl/errors.)
If yes, please set the bugs I posted for this (367, 377) to wontfix.
(My uses at the moment are, for example, reading repositorys whose name is constructed from a static macro, or using macros inside SQL queries.)
Greetings,
Christian
as Sebastian has pointed out: there is no difference between "static" macros and "dynamic" macros. I assume that everyone will agree that the macro definitions by Extract Macro or Construct Macro are not possible to calculate during the design phase / meta data propagation. "Set Macro" seems to be more static but nevertheless the process has to be runned in order to actually make the macro setting. So basically all operator-based macro definitions are indeed dynamic and there is indeed no way out for using those macro definitions within meta data propagation...
...but as Simon has pointed out in another thread: did you use the Context view? Here you can define macros as well. Right now this process context is also only used during runtime but it would be easily possible to make the context also available during design / meta data propagation. But this would indeed be a new feature and - let me show a bit of pride here - not a bugfix ;-)
Cheers,
Ingo
I see the technical point in the macros not being processed for meta-data generation. However, for a tool that advertises its meta-data designtime-help capabilities as strong advantage, it is surprising.
Context-view macros would be an option, of course, if they could indeed be processed during meta-data generation.
There is no means to selectively disable certain warnings for certain operators, I guess?
Still using RM, will have to work my way round those things...
Christian