ErrorDescription:[The table of SeaTunnel_test.pg_test has no primary keys

[bluerabbitcute]

Search before asking

• [X] I had searched in the issues and found no similar issues.

What happened

When I use SeaTunnel for data synchronization, it fails to detect the primary key in the Oracle database table during runtime. I need to manually specify the hash field in the Doris table creation template using save_mode_create_template

SeaTunnel Version

2.3.8

SeaTunnel Config

env {
parallelism = 2
job.mode = "BATCH"
}
source {
Jdbc {
url = "xxx"
driver = "oracle.jdbc.OracleDriver"
connection_check_timeout_sec = 100
user = "lyerp"
password = "lyerp"
table_path = "PUB_ENTRY_GOODS"
query = "select * from PUB_ENTRY_GOODS"
}
}
sink {
Doris {
fenodes = "xxx"
username = "root"
password = "doris123"
database = "SeaTunnel_test"
table = "${table_name}"
save_mode_create_template = """
CREATE TABLE IF NOT EXISTS `${database}`.`${table}` (
${rowtype_fields}
) ENGINE=OLAP
DISTRIBUTED BY HASH (GOODSID)
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2",
"disable_auto_compaction" = "false"
)
"""
doris.config = {
format = "json"
read_json_by_line="true"
}
}
}

Running Command

./bin/seatunnel.sh --config job/oracle2doris/test.conf -m local

Error Exception

Caused by: org.apache.seatunnel.common.exception.SeaTunnelRuntimeException: ErrorCode:[COMMON-24], ErrorDescription:[The table of SeaTunnel_test.pg_test has no primary keys, but the template 
 CREATE TABLE IF NOT EXISTS `${database}`.`${table}` (
${rowtype_primary_key},
${rowtype_fields}
) ENGINE=OLAP
 UNIQUE KEY (${rowtype_primary_key})
DISTRIBUTED BY HASH (${rowtype_primary_key})
 PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2",
"disable_auto_compaction" = "false"
)

Zeta or Flink or Spark Version

zeta

Java or Scala Version

1.8

Screenshots

No response

Are you willing to submit PR?

• [ ] Yes I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[Hisoka-X]

The tablepath in your source config not right. we need set tablepath to schemaName.tableName, not only tableName. Please refer https://seatunnel.apache.org/docs/2.3.8/connector-v2/source/Jdbc

[hailin0]

[bluerabbitcute]

Using this method, the issue with the PG database was resolved, but Oracle still has the problem of not being able to detect the 'primary_key'. After consulting with the DBA, they indicated that there are no permission issues.

[bluerabbitcute]

env { parallelism = 2 job.mode = "BATCH" } source { Jdbc { url = "xxx" driver = "oracle.jdbc.OracleDriver" connection_check_timeout_sec = 100 user = "lyerp" password = "lyerp" table_path = "LYERP.T_GOODSTYPE" query = "select * from T_GOODSTYPE" } } sink { Doris { fenodes = "xxx" username = "root" password = "doris123" database = "SeaTunnel_test" table = "${table_name}" doris.config = { format = "json" read_json_by_line="true" } } }

[bluerabbitcute]

Caused by: org.apache.seatunnel.common.exception.SeaTunnelRuntimeException: ErrorCode:[COMMON-24], ErrorDescription:[The table of SeaTunnel_test.pg_test has no primary keys, but the template CREATE TABLE IF NOT EXISTS ${database}.${table} ( ${rowtype_primary_key}, ${rowtype_fields} ) ENGINE=OLAP UNIQUE KEY (${rowtype_primary_key}) DISTRIBUTED BY HASH (${rowtype_primary_key}) PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "in_memory" = "false", "storage_format" = "V2", "disable_auto_compaction" = "false" )