You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@flink.apache.org by "Tan Nguyen (Jira)" <ji...@apache.org> on 2024/09/26 03:51:00 UTC
[jira] [Created] (FLINK-36372) Can not insert data to sink with reserved keywords column name
Tan Nguyen created FLINK-36372:
----------------------------------
Summary: Can not insert data to sink with reserved keywords column name
Key: FLINK-36372
URL: https://issues.apache.org/jira/browse/FLINK-36372
Project: Flink
Issue Type: Bug
Reporter: Tan Nguyen
Attachments: image-2024-09-26-10-33-37-134.png, image-2024-09-26-10-40-07-605.png, image-2024-09-26-10-41-10-327.png, image-2024-09-26-10-48-11-315.png
Hi teams,
I created a flink application running as batch mode, we read data from kafka then we run the query transform data from sql file. Finally execute insert query from sql file to sink data. Here the insert query:
!image-2024-09-26-10-33-37-134.png!
Even I use the backticks to escape the reserved keywords, still failed, here the error:
{code:java}
// code placeholder
java.lang.RuntimeException: Writing records to JDBC failed.at org.apache.flink.connector.jdbc.internal.JdbcOutputFormat.close(JdbcOutputFormat.java:265)at org.apache.flink.connector.jdbc.internal.GenericJdbcSinkFunction.close(GenericJdbcSinkFunction.java:70)at org.apache.flink.api.common.functions.util.FunctionUtils.closeFunction(FunctionUtils.java:41)at org.apache.flink.streaming.api.operators.AbstractUdfStreamOperator.close(AbstractUdfStreamOperator.java:115)at org.apache.flink.streaming.runtime.tasks.StreamOperatorWrapper.close(StreamOperatorWrapper.java:163)at org.apache.flink.streaming.runtime.tasks.RegularOperatorChain.closeAllOperators(RegularOperatorChain.java:125)at org.apache.flink.streaming.runtime.tasks.StreamTask.closeAllOperators(StreamTask.java:1062)at org.apache.flink.streaming.runtime.tasks.StreamTask.afterInvoke(StreamTask.java:930)at org.apache.flink.streaming.runtime.tasks.StreamTask.invoke(StreamTask.java:813)at org.apache.flink.runtime.taskmanager.Task.runWithSystemExitMonitoring(Task.java:955)at org.apache.flink.runtime.taskmanager.Task.restoreAndInvoke(Task.java:934)at org.apache.flink.runtime.taskmanager.Task.doRun(Task.java:748)at org.apache.flink.runtime.taskmanager.Task.run(Task.java:564)at java.base/java.lang.Thread.run(Thread.java:829)Caused by: java.io.IOException: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO native_program_domain.member_program_aux_data(id, member_program_id, program_provider_id, enrollment_event_type, cancellation_reason, member_start_date, house_hold_id, enrollment_source, plan_contract_id, protocol, enrollment_priority, member_disenroll_date, pbp, hras_needed, hra_due_date, completion_status, hra_completion_method, hra_completion_description, enrollment_lob, clock_start_date, csp_group_id, csp_plan_id, group, segment_id, division, created_by, created_date, last_modified_by, last_modified_date) VALUES (('3319eb7d-e247-4eec-9c3e-e0ba9ee5b7aa'), ('5e4fd136-2b14-3b63-9b9c-e3c1af424c55'), (NULL), ('R'), (NULL), ('20140608'), (NULL), (NULL), ('CONT-00008'), ('PR01'), (NULL), (NULL), ('PBP-0008'), ('FALSE'::boolean), (NULL), (NULL), (NULL), ('NextGen'), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL)) ON CONFLICT (id) DO UPDATE SET id=EXCLUDED.id, member_program_id=EXCLUDED.member_program_id, program_provider_id=EXCLUDED.program_provider_id, enrollment_event_type=EXCLUDED.enrollment_event_type, cancellation_reason=EXCLUDED.cancellation_reason, member_start_date=EXCLUDED.member_start_date, house_hold_id=EXCLUDED.house_hold_id, enrollment_source=EXCLUDED.enrollment_source, plan_contract_id=EXCLUDED.plan_contract_id, protocol=EXCLUDED.protocol, enrollment_priority=EXCLUDED.enrollment_priority, member_disenroll_date=EXCLUDED.member_disenroll_date, pbp=EXCLUDED.pbp, hras_needed=EXCLUDED.hras_needed, hra_due_date=EXCLUDED.hra_due_date, completion_status=EXCLUDED.completion_status, hra_completion_method=EXCLUDED.hra_completion_method, hra_completion_description=EXCLUDED.hra_completion_description, enrollment_lob=EXCLUDED.enrollment_lob, clock_start_date=EXCLUDED.clock_start_date, csp_group_id=EXCLUDED.csp_group_id, csp_plan_id=EXCLUDED.csp_plan_id, group=EXCLUDED.group, segment_id=EXCLUDED.segment_id, division=EXCLUDED.division, created_by=EXCLUDED.created_by, created_date=EXCLUDED.created_date, last_modified_by=EXCLUDED.last_modified_by, last_modified_date=EXCLUDED.last_modified_date was aborted: ERROR: syntax error at or near "group"Position: 435 Call getNextException to see other errors in the batch.at org.apache.flink.connector.jdbc.internal.JdbcOutputFormat.flush(JdbcOutputFormat.java:222)at org.apache.flink.connector.jdbc.internal.JdbcOutputFormat.close(JdbcOutputFormat.java:262)... 13 moreCaused by: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO native_program_domain.member_program_aux_data(id, member_program_id, program_provider_id, enrollment_event_type, cancellation_reason, member_start_date, house_hold_id, enrollment_source, plan_contract_id, protocol, enrollment_priority, member_disenroll_date, pbp, hras_needed, hra_due_date, completion_status, hra_completion_method, hra_completion_description, enrollment_lob, clock_start_date, csp_group_id, csp_plan_id, group, segment_id, division, created_by, created_date, last_modified_by, last_modified_date) VALUES (('3319eb7d-e247-4eec-9c3e-e0ba9ee5b7aa'), ('5e4fd136-2b14-3b63-9b9c-e3c1af424c55'), (NULL), ('R'), (NULL), ('20140608'), (NULL), (NULL), ('CONT-00008'), ('PR01'), (NULL), (NULL), ('PBP-0008'), ('FALSE'::boolean), (NULL), (NULL), (NULL), ('NextGen'), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL)) ON CONFLICT (id) DO UPDATE SET id=EXCLUDED.id, member_program_id=EXCLUDED.member_program_id, program_provider_id=EXCLUDED.program_provider_id, enrollment_event_type=EXCLUDED.enrollment_event_type, cancellation_reason=EXCLUDED.cancellation_reason, member_start_date=EXCLUDED.member_start_date, house_hold_id=EXCLUDED.house_hold_id, enrollment_source=EXCLUDED.enrollment_source, plan_contract_id=EXCLUDED.plan_contract_id, protocol=EXCLUDED.protocol, enrollment_priority=EXCLUDED.enrollment_priority, member_disenroll_date=EXCLUDED.member_disenroll_date, pbp=EXCLUDED.pbp, hras_needed=EXCLUDED.hras_needed, hra_due_date=EXCLUDED.hra_due_date, completion_status=EXCLUDED.completion_status, hra_completion_method=EXCLUDED.hra_completion_method, hra_completion_description=EXCLUDED.hra_completion_description, enrollment_lob=EXCLUDED.enrollment_lob, clock_start_date=EXCLUDED.clock_start_date, csp_group_id=EXCLUDED.csp_group_id, csp_plan_id=EXCLUDED.csp_plan_id, group=EXCLUDED.group, segment_id=EXCLUDED.segment_id, division=EXCLUDED.division, created_by=EXCLUDED.created_by, created_date=EXCLUDED.created_date, last_modified_by=EXCLUDED.last_modified_by, last_modified_date=EXCLUDED.last_modified_date was aborted: ERROR: syntax error at or near "group"Position: 435 Call getNextException to see other errors in the batch.at org.postgresql.jdbc.BatchResultHandler.handleCompletion(BatchResultHandler.java:186)at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:585)at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:896)at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:919)at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1685)at org.apache.flink.connector.jdbc.statement.FieldNamedPreparedStatementImpl.executeBatch(FieldNamedPreparedStatementImpl.java:65)at org.apache.flink.connector.jdbc.internal.executor.TableSimpleStatementExecutor.executeBatch(TableSimpleStatementExecutor.java:64)at org.apache.flink.connector.jdbc.internal.executor.TableBufferReducedStatementExecutor.executeBatch(TableBufferReducedStatementExecutor.java:101)at org.apache.flink.connector.jdbc.internal.JdbcOutputFormat.attemptFlush(JdbcOutputFormat.java:246)at org.apache.flink.connector.jdbc.internal.JdbcOutputFormat.flush(JdbcOutputFormat.java:216)... 14 moreCaused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "group"Position: 435at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:574)... 22 more {code}
I try to remove backticks and other syntax like: `"group"`, ``group``, `'group'`, etc, still failed. I also copy the insert query from the log and execute in SQL Server, the same error happen. But when I change group to "group" the query ran success
!image-2024-09-26-10-41-10-327.png!
+Note:+ The select query working fine with the backticks, but the insert failed. Here is my insert code
!image-2024-09-26-10-48-11-315.png!
I hope the teams could take a look this issue, thanks!
--
This message was sent by Atlassian Jira
(v8.20.10#820010)