The Altair Community is migrating to a new platform to provide a better experience for you. In preparation for the migration, the Altair Community is on read-only mode from October 28 - November 6, 2024. Technical support via cases will continue to work as is. For any urgent requests from Students/Faculty members, please submit the form linked here

Row Change Timestamp

mtranmtran Member Posts: 4 Contributor I
edited December 2018 in Help

Hello.  Currently I am writing a table to SQL Server from a flat file.  When a new flat file comes in, I have a process created to update the current table.  I am using the 'Update Database' operator.  I would like to have a 'Timestamp' column that will update when that row is updated with new data.  I tried to create a 'Timestamp' column using 'Generate Attributes' but of course when I update the table it updates every row.  My 'update' process is pasted below.  

 

TIA

 

<?xml version="1.0" encoding="UTF-8"?><process version="8.2.000">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="8.2.000" expanded="true" name="Process">
    <process expanded="true">
      <operator activated="true" class="open_file" compatibility="8.2.000" expanded="true" height="68" name="Open File" width="90" x="45" y="340">
        <parameter key="filename" value="Z:\Project Cost Drop\Copy of Project Costs - New Format FY11-FY18 TEST.xlsx"/>
      </operator>
      <operator activated="true" class="read_excel" compatibility="8.2.000" expanded="true" height="68" name="Read Excel" width="90" x="45" y="442">
        <parameter key="excel_file" value="Z:\Project Cost Drop\Project Costs - New Format FY11-FY18.xlsx"/>
        <parameter key="imported_cell_range" value="A1:L50000"/>
        <parameter key="first_row_as_names" value="false"/>
        <list key="annotations"/>
        <list key="data_set_meta_data_information">
          <parameter key="0" value="Year.true.polynominal.attribute"/>
          <parameter key="1" value="B.false.polynominal.attribute"/>
          <parameter key="2" value="Month.true.polynominal.attribute"/>
          <parameter key="3" value="D.false.polynominal.attribute"/>
          <parameter key="4" value="Project_ID.true.polynominal.attribute"/>
          <parameter key="5" value="Project_Name.true.polynominal.attribute"/>
          <parameter key="6" value="Hours.true.numeric.attribute"/>
          <parameter key="7" value="Labor_Costs.true.numeric.attribute"/>
          <parameter key="8" value="Pro_Srvcs.true.numeric.attribute"/>
          <parameter key="9" value="Other_Purchases.true.numeric.attribute"/>
          <parameter key="10" value="Capex.true.numeric.attribute"/>
          <parameter key="11" value="Hosted.true.numeric.attribute"/>
        </list>
      </operator>
      <operator activated="true" class="subprocess" compatibility="8.2.000" expanded="true" height="82" name="Concatenate" width="90" x="45" y="544">
        <process expanded="true">
          <operator activated="true" class="generate_concatenation" compatibility="8.2.000" expanded="true" height="82" name="Generate Concatenation" width="90" x="45" y="34">
            <parameter key="first_attribute" value="Year"/>
            <parameter key="second_attribute" value="Month"/>
          </operator>
          <operator activated="true" class="generate_concatenation" compatibility="8.2.000" expanded="true" height="82" name="Generate Concatenation (2)" width="90" x="179" y="34">
            <parameter key="first_attribute" value="Year_Month"/>
            <parameter key="second_attribute" value="Project_ID"/>
          </operator>
          <connect from_port="in 1" to_op="Generate Concatenation" to_port="example set input"/>
          <connect from_op="Generate Concatenation" from_port="example set output" to_op="Generate Concatenation (2)" to_port="example set input"/>
          <connect from_op="Generate Concatenation (2)" from_port="example set output" to_port="out 1"/>
          <portSpacing port="source_in 1" spacing="0"/>
          <portSpacing port="source_in 2" spacing="0"/>
          <portSpacing port="sink_out 1" spacing="0"/>
          <portSpacing port="sink_out 2" spacing="0"/>
        </process>
      </operator>
      <operator activated="true" class="subprocess" compatibility="8.2.000" expanded="true" height="82" name="Select &amp; Rename" width="90" x="45" y="646">
        <process expanded="true">
          <operator activated="true" class="select_attributes" compatibility="8.2.000" expanded="true" height="82" name="Select Attributes (2)" width="90" x="45" y="34">
            <parameter key="attribute_filter_type" value="subset"/>
            <parameter key="attributes" value="Capex|Hosted|Hours|Labor_Costs|Month|Other_Purchases|Pro_Srvcs|Project_ID|Project_Name|Year|A_C_E|Year_Month_Project_ID"/>
          </operator>
          <operator activated="true" class="rename" compatibility="8.2.000" expanded="true" height="82" name="Rename" width="90" x="313" y="85">
            <parameter key="old_name" value="Year_Month_Project_ID"/>
            <parameter key="new_name" value="ACE"/>
            <list key="rename_additional_attributes"/>
          </operator>
          <connect from_port="in 1" to_op="Select Attributes (2)" to_port="example set input"/>
          <connect from_op="Select Attributes (2)" from_port="example set output" to_op="Rename" to_port="example set input"/>
          <connect from_op="Rename" from_port="example set output" to_port="out 1"/>
          <portSpacing port="source_in 1" spacing="0"/>
          <portSpacing port="source_in 2" spacing="0"/>
          <portSpacing port="sink_out 1" spacing="0"/>
          <portSpacing port="sink_out 2" spacing="0"/>
        </process>
      </operator>
      <operator activated="true" class="generate_attributes" compatibility="8.2.000" expanded="true" height="82" name="Generate Attributes" width="90" x="246" y="544">
        <list key="function_descriptions">
          <parameter key="Updated_Date" value="date_now()"/>
        </list>
      </operator>
      <operator activated="true" class="set_role" compatibility="8.2.000" expanded="true" height="82" name="Set Role" width="90" x="380" y="544">
        <parameter key="attribute_name" value="ACE"/>
        <parameter key="target_role" value="id"/>
        <list key="set_additional_roles"/>
      </operator>
      <operator activated="true" class="jdbc_connectors:update_database" compatibility="8.2.000" expanded="true" height="68" name="Update Database" width="90" x="514" y="544">
        <parameter key="connection" value="RapidMiner_02"/>
        <parameter key="table_name" value="Fin_Extract_Stage"/>
        <parameter key="attribute_filter_type" value="single"/>
        <parameter key="attribute" value="ACE"/>
        <parameter key="attributes" value="|Updated_Date|Pro_Srvcs|Other_Purchases|Labor_Costs|Hours|Hosted|Capex"/>
      </operator>
      <operator activated="false" class="jdbc_connectors:read_database" compatibility="8.2.000" expanded="true" height="68" name="Read Database" width="90" x="45" y="34">
        <parameter key="connection" value="RapidMiner_02"/>
        <parameter key="query" value="SELECT *&#10;FROM &quot;dbo&quot;.&quot;Fin_Extract_Stage&quot;"/>
        <enumeration key="parameters"/>
      </operator>
      <operator activated="false" class="append" compatibility="8.2.000" expanded="true" height="68" name="Append" width="90" x="179" y="34"/>
      <operator activated="false" class="remove_duplicates" compatibility="8.2.000" expanded="true" height="103" name="Remove Duplicates" width="90" x="313" y="34">
        <parameter key="attribute_filter_type" value="subset"/>
        <parameter key="attributes" value="ACE"/>
        <parameter key="invert_selection" value="true"/>
        <parameter key="include_special_attributes" value="true"/>
      </operator>
      <connect from_op="Open File" from_port="file" to_op="Read Excel" to_port="file"/>
      <connect from_op="Read Excel" from_port="output" to_op="Concatenate" to_port="in 1"/>
      <connect from_op="Concatenate" from_port="out 1" to_op="Select &amp; Rename" to_port="in 1"/>
      <connect from_op="Select &amp; Rename" from_port="out 1" to_op="Generate Attributes" to_port="example set input"/>
      <connect from_op="Generate Attributes" from_port="example set 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>

 

 

Answers

  • FBTFBT Member Posts: 106 Unicorn

    I am not sure I fully understand what you would like to achieve, but it appears that you would want to use the "Write Database" operator instead of "Update Database". That way you could write your updated timestamp attribute directly in SQL and add it to the respective example (i.e. row).

  • SGolbertSGolbert RapidMiner Certified Analyst, Member Posts: 344 Unicorn

    Hi,

     

    Your database setup is also important here. If the timestamp is not indexed, you will have a lot of random accesses to the disk, which could be worst than writing the whole table again. Can you explain the problem in detail?

     

    Regards,

    Sebastian

Sign In or Register to comment.