How do you update redshift database through rapidminer?

vbs2114
vbs2114 New Altair Community Member
edited November 2024 in Community Q&A
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

  • YYH
    YYH
    Altair Employee
    Answer ✓
    @vbs2114  You may need to use the Redshift driver with Redshift, not the Postgres driver. 

    http://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html 
  • YYH
    YYH
    Altair Employee
    Answer ✓
    Hi @vbs2114 , you may use "set role" to regularize the special attributes for confidence and predictions etc. Then remove some special symbols, ( or ), [ or ] from the attribute names.
    <pre class="CodeBlock"><code><?xml version="1.0" encoding="UTF-8"?><process version="9.2.001"> <context> <input/> <output/> <macros/> </context> <operator activated="true" class="process" compatibility="6.0.002" 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.2.001" expanded="true" height="68" name="Retrieve Polynomial" origin="GENERATED_TUTORIAL" width="90" x="112" y="85"> <parameter key="repository_entry" value="//Samples/data/Polynomial"/> </operator> <operator activated="true" class="split_data" compatibility="9.2.001" expanded="true" height="103" name="Split Data" origin="GENERATED_TUTORIAL" width="90" x="246" y="85"> <enumeration key="partitions"> <parameter key="ratio" value="0.8"/> <parameter key="ratio" value="0.2"/> </enumeration> <parameter key="sampling_type" value="automatic"/> <parameter key="use_local_random_seed" value="false"/> <parameter key="local_random_seed" value="1992"/> </operator> <operator activated="true" class="select_attributes" compatibility="9.2.001" expanded="true" height="82" name="Select Attributes" origin="GENERATED_TUTORIAL" width="90" x="447" y="289"> <parameter key="attribute_filter_type" value="single"/> <parameter key="attribute" value="label"/> <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="true"/> <parameter key="include_special_attributes" value="true"/> </operator> <operator activated="true" class="linear_regression" compatibility="9.2.001" expanded="true" height="103" name="Linear Regression" origin="GENERATED_TUTORIAL" width="90" x="447" y="85"> <parameter key="feature_selection" value="M5 prime"/> <parameter key="alpha" value="0.05"/> <parameter key="max_iterations" value="10"/> <parameter key="forward_alpha" value="0.05"/> <parameter key="backward_alpha" value="0.05"/> <parameter key="eliminate_colinear_features" value="true"/> <parameter key="min_tolerance" value="0.05"/> <parameter key="use_bias" value="true"/> <parameter key="ridge" value="1.0E-8"/> </operator> <operator activated="true" class="apply_model" compatibility="7.1.001" expanded="true" height="82" name="Apply Model" origin="GENERATED_TUTORIAL" width="90" x="715" y="187"> <list key="application_parameters"/> <parameter key="create_view" value="false"/> </operator> <operator activated="true" class="set_role" compatibility="9.2.001" expanded="true" height="82" name="Set Role" width="90" x="849" y="187"> <parameter key="attribute_name" value="prediction(label)"/> <parameter key="target_role" value="regular"/> <list key="set_additional_roles"/> </operator> <operator activated="true" class="rename_by_replacing" compatibility="9.2.001" expanded="true" height="82" name="Rename by Replacing" width="90" x="983" y="187"> <parameter key="attribute_filter_type" value="all"/> <parameter key="attribute" value=""/> <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"/> <parameter key="replace_what" value="\(|\)"/> </operator> <connect from_op="Retrieve Polynomial" from_port="output" to_op="Split Data" to_port="example set"/> <connect from_op="Split Data" from_port="partition 1" to_op="Linear Regression" to_port="training set"/> <connect from_op="Split Data" from_port="partition 2" to_op="Select Attributes" to_port="example set input"/> <connect from_op="Select Attributes" from_port="example set output" to_op="Apply Model" to_port="unlabelled data"/> <connect from_op="Linear Regression" from_port="model" to_op="Apply Model" to_port="model"/> <connect from_op="Apply Model" from_port="labelled data" to_op="Set Role" to_port="example set input"/> <connect from_op="Set Role" from_port="example set output" to_op="Rename by Replacing" to_port="example set input"/> <connect from_op="Rename by Replacing" from_port="example set output" 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,

    YY

Answers

  • YYH
    YYH
    Altair Employee
    Answer ✓
    @vbs2114  You may need to use the Redshift driver with Redshift, not the Postgres driver. 

    http://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html 
  • vbs2114
    vbs2114 New Altair Community Member
    @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


  • sgenzer
    sgenzer
    Altair Employee
    @vbs2114 that sounds like a SQL syntax error
  • vbs2114
    vbs2114 New Altair Community Member
    @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. 
  • YYH
    YYH
    Altair Employee
    Answer ✓
    Hi @vbs2114 , you may use "set role" to regularize the special attributes for confidence and predictions etc. Then remove some special symbols, ( or ), [ or ] from the attribute names.
    <pre class="CodeBlock"><code><?xml version="1.0" encoding="UTF-8"?><process version="9.2.001"> <context> <input/> <output/> <macros/> </context> <operator activated="true" class="process" compatibility="6.0.002" 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.2.001" expanded="true" height="68" name="Retrieve Polynomial" origin="GENERATED_TUTORIAL" width="90" x="112" y="85"> <parameter key="repository_entry" value="//Samples/data/Polynomial"/> </operator> <operator activated="true" class="split_data" compatibility="9.2.001" expanded="true" height="103" name="Split Data" origin="GENERATED_TUTORIAL" width="90" x="246" y="85"> <enumeration key="partitions"> <parameter key="ratio" value="0.8"/> <parameter key="ratio" value="0.2"/> </enumeration> <parameter key="sampling_type" value="automatic"/> <parameter key="use_local_random_seed" value="false"/> <parameter key="local_random_seed" value="1992"/> </operator> <operator activated="true" class="select_attributes" compatibility="9.2.001" expanded="true" height="82" name="Select Attributes" origin="GENERATED_TUTORIAL" width="90" x="447" y="289"> <parameter key="attribute_filter_type" value="single"/> <parameter key="attribute" value="label"/> <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="true"/> <parameter key="include_special_attributes" value="true"/> </operator> <operator activated="true" class="linear_regression" compatibility="9.2.001" expanded="true" height="103" name="Linear Regression" origin="GENERATED_TUTORIAL" width="90" x="447" y="85"> <parameter key="feature_selection" value="M5 prime"/> <parameter key="alpha" value="0.05"/> <parameter key="max_iterations" value="10"/> <parameter key="forward_alpha" value="0.05"/> <parameter key="backward_alpha" value="0.05"/> <parameter key="eliminate_colinear_features" value="true"/> <parameter key="min_tolerance" value="0.05"/> <parameter key="use_bias" value="true"/> <parameter key="ridge" value="1.0E-8"/> </operator> <operator activated="true" class="apply_model" compatibility="7.1.001" expanded="true" height="82" name="Apply Model" origin="GENERATED_TUTORIAL" width="90" x="715" y="187"> <list key="application_parameters"/> <parameter key="create_view" value="false"/> </operator> <operator activated="true" class="set_role" compatibility="9.2.001" expanded="true" height="82" name="Set Role" width="90" x="849" y="187"> <parameter key="attribute_name" value="prediction(label)"/> <parameter key="target_role" value="regular"/> <list key="set_additional_roles"/> </operator> <operator activated="true" class="rename_by_replacing" compatibility="9.2.001" expanded="true" height="82" name="Rename by Replacing" width="90" x="983" y="187"> <parameter key="attribute_filter_type" value="all"/> <parameter key="attribute" value=""/> <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"/> <parameter key="replace_what" value="\(|\)"/> </operator> <connect from_op="Retrieve Polynomial" from_port="output" to_op="Split Data" to_port="example set"/> <connect from_op="Split Data" from_port="partition 1" to_op="Linear Regression" to_port="training set"/> <connect from_op="Split Data" from_port="partition 2" to_op="Select Attributes" to_port="example set input"/> <connect from_op="Select Attributes" from_port="example set output" to_op="Apply Model" to_port="unlabelled data"/> <connect from_op="Linear Regression" from_port="model" to_op="Apply Model" to_port="model"/> <connect from_op="Apply Model" from_port="labelled data" to_op="Set Role" to_port="example set input"/> <connect from_op="Set Role" from_port="example set output" to_op="Rename by Replacing" to_port="example set input"/> <connect from_op="Rename by Replacing" from_port="example set output" 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,

    YY