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

Somnath_DasSomnath_Das Member Posts: 31 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">






  <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 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}"/>


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

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


          <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"/>



      <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"/>


      <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"/>




Best Answer

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


  • Options
    kaymankayman Member Posts: 662 Unicorn
    edited May 2021
    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} 
  • Options
    Somnath_DasSomnath_Das Member Posts: 31 Maven
    I want to assign it through macros dynamically.
  • Options
    BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 955 Unicorn

    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.

Sign In or Register to comment.