🎉 🎉 RAPIDMINER 9.10 IS OUT!!! 🎉🎉

Download the latest version helping analytics teams accelerate time-to-value for streaming and IIOT use cases.

CLICK HERE TO DOWNLOAD

Given a database table how to use macros and loop to change the multiple "where" values of SELECT

Somnath_DasSomnath_Das Member Posts: 25  Maven
Given a database table how do I use macros to change the multiple "where" values of a select query using rapidminer? I have created a sample process but there the WHERE clause is having a single condition.. I want to check for multiple conditions.

For example if I want to check like ACCOUNT_NO & CUSTOMER_ID how do I use macros and loops so a subprocess will run the following statement?

Select * from Table where account_no=suppliedValue_1 and customer_id=suppliedValue_2


<?xml version="1.0" encoding="UTF-8"?><process version="9.9.000">

  <context>

    <input/>

    <output/>

    <macros/>

  </context>

  <operator activated="true" class="process" compatibility="9.4.000" expanded="true" name="Process" origin="GENERATED_TUTORIAL">

    <parameter key="logverbosity" value="init"/>

    <parameter key="random_seed" value="2001"/>

    <parameter key="send_mail" value="never"/>

    <parameter key="notification_email" value=""/>

    <parameter key="process_duration_for_mail" value="30"/>

    <parameter key="encoding" value="SYSTEM"/>

    <process expanded="true">

      <operator activated="true" class="retrieve" compatibility="9.9.000" expanded="true" height="68" name="Retrieve card_fraud_txns_customer" width="90" x="45" y="34">

        <parameter key="repository_entry" value="../data/card/card_fraud_txns_customer"/>

      </operator>

      <operator activated="true" class="concurrency:loop_values" compatibility="9.9.000" expanded="true" height="82" name="Loop Values" width="90" x="179" y="34">

        <parameter key="attribute" value="CUST_CARD_ID"/>

        <parameter key="iteration_macro" value="loop_value"/>

        <parameter key="reuse_results" value="false"/>

        <parameter key="enable_parallel_execution" value="true"/>

        <process expanded="true">

          <operator activated="true" class="jdbc_connectors:read_database" compatibility="9.9.000" expanded="true" height="82" name="Read Database" width="90" x="179" y="34">

            <parameter key="define_connection" value="repository"/>

            <parameter key="connection_entry" value="/Connections/ML_UAT_DB"/>

            <parameter key="database_system" value="MySQL"/>

            <parameter key="define_query" value="query"/>

            <parameter key="query" value="select * from card_fraud_all where CUST_CARD_ID = ?"/>

            <parameter key="use_default_schema" value="true"/>

            <parameter key="prepare_statement" value="true"/>

            <enumeration key="parameters">

              <parameter key="parameter" value="VARCHAR.%{loop_value}"/>

            </enumeration>

            <parameter key="datamanagement" value="double_array"/>

            <parameter key="data_management" value="auto"/>

          </operator>

          <connect from_op="Read Database" from_port="output" to_port="output 1"/>

          <portSpacing port="source_input 1" spacing="0"/>

          <portSpacing port="source_input 2" spacing="0"/>

          <portSpacing port="sink_output 1" spacing="0"/>

          <portSpacing port="sink_output 2" spacing="0"/>

        </process>

      </operator>

      <operator activated="true" class="append" compatibility="9.9.000" expanded="true" height="82" name="Append (2)" width="90" x="313" y="34">

        <parameter key="datamanagement" value="double_array"/>

        <parameter key="data_management" value="auto"/>

        <parameter key="merge_type" value="all"/>

      </operator>

      <connect from_op="Retrieve card_fraud_txns_customer" from_port="output" to_op="Loop Values" to_port="input 1"/>

      <connect from_op="Loop Values" from_port="output 1" to_op="Append (2)" to_port="example set 1"/>

      <connect from_op="Append (2)" from_port="merged 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"/>

      <background height="232" location="//Samples/Tutorials/Basics/01/tutorial1" width="1502" x="26" y="47"/>

    </process>

  </operator>

</process>


Best Answer

  • kaymankayman Member Posts: 652   Unicorn
    Solution Accepted
    Same logic, but use 'generate macro' instead of set macro. Your logic then defines the value assigned to the macros. 

Answers

  • kaymankayman Member Posts: 652   Unicorn
    edited May 23
    You can use the set macros operator. In this one you define the values like acountnumber = 1234.

    In you database query you then use the macro syntax, like %{acountnumber}

    That's actually it. You can create as many macros as you need, so your select statement would look like

    Select * from Table where account_no=%{acountnumber} and customer_id=%{custid} 
  • Somnath_DasSomnath_Das Member Posts: 25  Maven
    I want to assign it through macros dynamically.
  • BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 691   Unicorn
    Hi!

    It's a good practice to use prepared queries, enter the paramaters with their types (in your case, the %{macros} in this syntax) and refer to the parameters with ? (question marks) in the appropriate order.

    This results in a better performance with many database systems, and avoids SQL injections.

    Regards,
    Balázs
Sign In or Register to comment.