RM5: Use macros in SQL statements/iterate over tables

NoSilverNoSilver Member Posts: 4 Contributor I
edited November 2018 in Help
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.

Answers

  • landland RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 2,531 Unicorn
    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
  • NoSilverNoSilver Member Posts: 4 Contributor I
    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?
  • CleoCleo Member Posts: 44 Maven
    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>
  • IngoRMIngoRM Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, Community Manager, RMResearcher, Member, University Professor Posts: 1,751 RM Founder
    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:

    image

    If you press this icon, the process view shows the execution order of the operators, for example like this:

    image

    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
  • cthielcthiel Member Posts: 16 Contributor II
    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
  • landland RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 2,531 Unicorn
    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
  • cthielcthiel Member Posts: 16 Contributor II
    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
  • IngoRMIngoRM Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, Community Manager, RMResearcher, Member, University Professor Posts: 1,751 RM Founder
    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
  • cthielcthiel Member Posts: 16 Contributor II
    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
Sign In or Register to comment.