RM5: Use macros in SQL statements/iterate over tables

NoSilver

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.

land

Hi,
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

NoSilver

Sorry, but this don't work.

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?

Cleo

I am able to add macros to my sql queries.

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>

IngoRM

Hi,

just a small addition since not everyone might know this really great feature:

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.

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

cthiel

In RM 5.0.008, I am able to use macros in SQL statements.

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

land

Hi,
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

cthiel

Hi 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

IngoRM

Hi,

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

cthiel

Hi 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