How do you update redshift database through rapidminer?

vbs2114vbs2114 Member Posts: 9 Contributor II
edited June 2020 in Help
I get a database error when I try to use the "update database" operator. 

"Database error occurred: ERROR: type "e" does not exist.
The JDBC driver has thrown an SQLException. This may because of a lack of privileges, wrong table name or url and similar problems. Please note that some databases are case sensitive. Details are given in the message."

Anyone have a step by step guide for updating redshift database? It's definitely not the wrong table name, I have the appropriate permissions and the connection was tested (and works).

For simplicity purposes, just including three operators below:

<?xml version="1.0" encoding="UTF-8"?><process version="9.2.001">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.2.001" expanded="true" name="Process">
    <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="jdbc_connectors:read_database" compatibility="9.2.001" expanded="true" height="68" name="Read Database" width="90" x="179" y="136">
        <parameter key="define_connection" value="predefined"/>
        <parameter key="connection" value="Redshift_VS"/>
        <parameter key="database_system" value="MySQL"/>
        <parameter key="define_query" value="query"/>
        <parameter key="query" value="SELECT *&#10;FROM &quot;rm_schema&quot;.&quot;pre_industry_assignment&quot;"/>
        <parameter key="use_default_schema" value="true"/>
        <parameter key="table_name" value="pre_industry_assignment"/>
        <parameter key="prepare_statement" value="false"/>
        <enumeration key="parameters"/>
        <parameter key="datamanagement" value="double_array"/>
        <parameter key="data_management" value="auto"/>
      </operator>
      <operator activated="true" class="select_attributes" compatibility="9.2.001" expanded="true" height="82" name="Select Attributes" width="90" x="447" y="187">
        <parameter key="attribute_filter_type" value="subset"/>
        <parameter key="attribute" value=""/>
        <parameter key="attributes" value="ctr"/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="attribute_value"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="time"/>
        <parameter key="block_type" value="attribute_block"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="value_matrix_row_start"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="false"/>
      </operator>
      <operator activated="true" class="jdbc_connectors:update_database" compatibility="9.2.001" expanded="true" height="68" name="Update Database" width="90" x="715" y="136">
        <parameter key="define_connection" value="predefined"/>
        <parameter key="connection" value="Redshift_VS"/>
        <parameter key="database_system" value="MySQL"/>
        <parameter key="use_default_schema" value="true"/>
        <parameter key="schema_name" value="rm_schema"/>
        <parameter key="table_name" value="post_industry_assignment"/>
        <parameter key="attribute_filter_type" value="single"/>
        <parameter key="attribute" value="ctr"/>
        <parameter key="attributes" value=""/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="attribute_value"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="time"/>
        <parameter key="block_type" value="attribute_block"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="value_matrix_row_start"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="false"/>
      </operator>
      <connect from_op="Read Database" from_port="output" to_op="Select Attributes" to_port="example set input"/>
      <connect from_op="Select Attributes" 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>

Best Answers

Answers

  • vbs2114vbs2114 Member Posts: 9 Contributor II
    @yyhuang
     Thanks, yes for some reason the first time around I wasn't able to connect with the Redshift driver, now it seems to connect.

    But now I am getting a different error:

    Database error occurred: [Amazon](500310) Invalid operation: syntax error at or near "WHERE" Position: 52;. at the update database operator stage


  • sgenzersgenzer Administrator, Moderator, Employee, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 Community Manager
    @vbs2114 that sounds like a SQL syntax error
  • vbs2114vbs2114 Member Posts: 9 Contributor II
    @sgenzer @yyhuang

    can I remove special attributes like [confidence(?)] from my output when I update the redshift database? I tried setting it as a non-special role, but it doesn't allow me to do that, and says the attribute doesn't exist. 
Sign In or Register to comment.