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.

building up dynamic SQL queries

rschillingrschilling Member Posts: 1 Learner III
edited November 2018 in Help
I am not certain how to do this, although my research into macros seems to indicate they're involved somehow.

I have a database table (MySQL) with some meta-data in it.  Say, one column in the data is a table name:

select tbl_name from mymetadata;

That returns a list of table names that I want to feed into a SQL statement.  So, for each item from tbl_name, I want to run:

select count(*) from %{tbl_name}

How do I do this?

Thanks in advance..

Richard Schilling

Answers

  • TobiasMalbrechtTobiasMalbrecht Moderator, Employee, Member Posts: 295 RM Product Management
    Hi Richard,

    you should iterate over the examples, extract a macro for the table name and use the [tt]Read Database[/tt] operator in the loop to read the tables. It should work like this... 

    <process version="5.0">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" expanded="true" name="Process">
        <process expanded="true" height="145" width="279">
          <operator activated="true" class="read_database" expanded="true" height="60" name="Read Database" width="90" x="45" y="30"/>
          <operator activated="true" class="loop_examples" expanded="true" height="76" name="Loop Examples" width="90" x="179" y="30">
            <process expanded="true" height="419" width="492">
              <operator activated="true" class="extract_macro" expanded="true" height="60" name="Extract Macro" width="90" x="45" y="30">
                <parameter key="macro" value="table"/>
                <parameter key="macro_type" value="data_value"/>
                <parameter key="attribute_name" value="tbl_name"/>
                <parameter key="example_index" value="%{example}"/>
              </operator>
              <operator activated="true" class="read_database" expanded="true" height="60" name="Read Database (2)" width="90" x="179" y="75">

                <parameter key="query" value="SELECT * FROM `%{table}`">

    	      </operator>
              <connect from_port="example set" to_op="Extract Macro" to_port="example set"/>
              <connect from_op="Extract Macro" from_port="example set" to_port="example set"/>
              <connect from_op="Read Database (2)" from_port="output" to_port="output 1"/>
              <portSpacing port="source_example set" spacing="0"/>
              <portSpacing port="sink_example set" spacing="0"/>
              <portSpacing port="sink_output 1" spacing="0"/>
              <portSpacing port="sink_output 2" spacing="0"/>
            </process>
          </operator>
          <connect from_op="Read Database" from_port="output" to_op="Loop Examples" to_port="example set"/>
          <connect from_op="Loop Examples" from_port="example set" 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>
    Hope that gives you a hint,
    Tobias
Sign In or Register to comment.