"Using multiple id to update database"

RayJhongRayJhong RapidMiner Certified Analyst, Member Posts: 11 Contributor II
edited June 2019 in Help

When using "Update Database" operator, there is a description for "attribute filter type" says:

subset : This option allows the selection of multiple id attributes throught a list ...

Screen Shot 2018-02-05 at 2.43.36 PM.png

My question is how to set multiple id attributes using "set role" operator, i have two attributes(machine_id & time_stamp) to set as id in order to update my orginal DB table, however, "set role" operator always keep lastest one attribute as id not both. Is there any way to set multiple attribute, or i misunderstand the description above?

 

by the way, i've tried using user define attribute set machine_id as id_1, time_stamp as id_2, but didn't work on "Update Database" operator, it will be recognized as different row.

 

Here is the operator.

<?xml version="1.0" encoding="UTF-8"?><process version="8.0.001">
<context>
<input/>
<output/>
<macros/>
</context>
<operator activated="true" class="process" compatibility="8.0.001" expanded="true" name="Process">
<process expanded="true">
<operator activated="true" class="retrieve" compatibility="8.0.001" expanded="true" height="68" name="Retrieve labeled data" width="90" x="179" y="85">
<parameter key="repository_entry" value="//rm-server/projects/Model Management/generate data/labeled data"/>
</operator>
<operator activated="true" class="set_role" compatibility="8.0.001" expanded="true" height="82" name="Set Role" width="90" x="313" y="85">
<parameter key="attribute_name" value="Time_Stamp"/>
<parameter key="target_role" value="id"/>
<list key="set_additional_roles">
<parameter key="Machine_ID" value="id"/>
<parameter key="Time_Stamp" value="id"/>
</list>
</operator>
<operator activated="true" class="jdbc_connectors:update_database" compatibility="8.0.001" expanded="true" height="68" name="Update Database" width="90" x="447" y="85">
<parameter key="connection" value="rm_repository"/>
<parameter key="table_name" value="machine_sensors"/>
<parameter key="attribute_filter_type" value="subset"/>
<parameter key="attribute" value="Machine_ID"/>
<parameter key="attributes" value="Time_Stamp|Machine_ID"/>
</operator>
<connect from_op="Retrieve labeled data" from_port="output" to_op="Set Role" to_port="example set input"/>
<connect from_op="Set Role" from_port="example set output" to_op="Update Database" to_port="input"/>
<connect from_op="Update Database" from_port="through" 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>

Appreciated,

Ray Jhong

 

Tagged:

Answers

  • MartinLiebigMartinLiebig Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 3,503 RM Data Scientist

    Hi @RayJhong,

     

    roles are unique. it is only possible to set each role once. However, if you choose more than one attribute in the selector of Update Database, it will take them for the update in the DB. This is independent of roles (except for the include special).

     

    Best,

    Martin

    - Sr. Director Data Solutions, Altair RapidMiner -
    Dortmund, Germany
  • RayJhongRayJhong RapidMiner Certified Analyst, Member Posts: 11 Contributor II

    Hi @mschmitz

    Thank you for quick reply, i've tried choosing both attribute as selector but got wrong result as below:Screen Shot 2018-02-05 at 3.34.24 PM.png

    Result seems like it didn't treat Machine_ID & Time_Stamp as keys to update Failure, instead, it became a new row.

    Screen Shot 2018-02-05 at 3.38.08 PM.png

     

     

     

  • MartinLiebigMartinLiebig Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 3,503 RM Data Scientist

    Hi,

     

    have you tried "Include Special Attributes"? Otherwise, specials of your selection are ignored. Not very intuitive in this operator, but this is how it works.

     

    Best,

    Martin

    - Sr. Director Data Solutions, Altair RapidMiner -
    Dortmund, Germany
  • RayJhongRayJhong RapidMiner Certified Analyst, Member Posts: 11 Contributor II

    Hi @mschmitz

     

    The same result. and i also tried remove "set role" operator to prevent any special attribute to be excluded but nothing changes. Is is possible for you to reproduce my situation? here is the process:

    <?xml version="1.0" encoding="UTF-8"?><process version="8.0.001">
    <context>
    <input/>
    <output/>
    <macros/>
    </context>
    <operator activated="true" class="process" compatibility="8.0.001" expanded="true" name="Process">
    <process expanded="true">
    <operator activated="true" class="retrieve" compatibility="8.0.001" expanded="true" height="68" name="Retrieve labeled data" width="90" x="179" y="85">
    <parameter key="repository_entry" value="//rm-server/projects/Model Management/generate data/labeled data"/>
    </operator>
    <operator activated="true" class="jdbc_connectors:update_database" compatibility="8.0.001" expanded="true" height="68" name="Update Database" width="90" x="380" y="85">
    <parameter key="connection" value="rm_repository"/>
    <parameter key="table_name" value="machine_sensors"/>
    <parameter key="attribute_filter_type" value="subset"/>
    <parameter key="attribute" value="Machine_ID"/>
    <parameter key="attributes" value="Time_Stamp|Machine_ID"/>
    <parameter key="include_special_attributes" value="true"/>
    </operator>
    <connect from_op="Retrieve labeled data" from_port="output" to_op="Update Database" to_port="input"/>
    <connect from_op="Update Database" from_port="through" 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>

    And datasource is in Samples/Templates/Predictive Maintenance/reference data with a time_stamp capturing current date time.

     

    Sincerely,

    Ray Jhong

Sign In or Register to comment.