How do you update redshift database through rapidminer?
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:
"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 * FROM "rm_schema"."pre_industry_assignment""/>
<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>
Find more posts tagged with
Sort by:
1 - 5 of
51
@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
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
Altair Employee@vbs2114 that sounds like a SQL syntax error
Altair EmployeeHi @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.
Best,
YY
<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>
YY
Sort by:
1 - 2 of
21
Altair Employee@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
http://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html
Altair EmployeeHi @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.
Best,
YY
<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>
YY
http://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html