[Bug] [JDBC-SQLSERVER] sink is doris,can not find pk

13813586515 commented 3 months ago

Search before asking

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

What happened

sqlserver存在主键，写入到doris中，自动建表不识别 [DLDM] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL, [DLMC] varchar(50) COLLATE Chinese_PRC_CI_AS NULL, [XTMR] varchar(1) COLLATE Chinese_PRC_CI_AS NULL, [BYZD1] varchar(20) COLLATE Chinese_PRC_CI_AS NULL, [BYZD2] varchar(20) COLLATE Chinese_PRC_CI_AS NULL, [BYZD3] int NULL, [BYZD4] numeric(18,4) NULL, [LastChanged] timestamp NOT NULL, PRIMARY KEY NONCLUSTERED ([DLDM]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY] )
ON [PRIMARY] GO 以上是sqlserver建表语句

SeaTunnel Version

2.3.6

SeaTunnel Config

env {
  parallelism = 10
  job.mode = "BATCH"
}

source {
    Jdbc {
        driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
        url = "jdbc:sqlserver://192.168.0.102:1433;databaseName=BSERP3_LP"
        user = sa
        password = "123"
        # Define query logic as required
      table_list = [
        {
          table_path = "BSERP3_LP.dbo.SHANGPIN"
          # Use query filetr rows & columns
          query = "select * from BSERP3_LP.dbo.SHANGPIN where spdm='0000000003'"
        },
        {
          table_path = "BSERP3_LP.dbo.dalei"
          # Use query filetr rows & columns
          query = "select * from BSERP3_LP.dbo.dalei "
        }
    ]

    }
}

sink {
  Doris {
    fenodes = "192.168.0.168:8030,192.168.0.169:8030,192.168.0.170:8030"
    username = root
    password = "lampo123!@#"
    database = "ods_bs"
    table = "ods_${table_name}"
    # 保证每个文件唯一，拷贝之后文件需要修改此处
    sink.label-prefix = "bs-product-${table_name}-jdbc"
    sink.enable-2pc = "true"
    # sink.enable-delete = "true"
    save_mode_create_template="CREATE TABLE IF NOT EXISTS `${database}`.`${table_name}` (${rowtype_fields},dt datetime(0) default current_timestamp(0) on update current_timestamp(0)) ENGINE=OLAP UNIQUE KEY (${rowtype_primary_key}) DISTRIBUTED BY HASH (${rowtype_primary_key}) BUCKETS 3 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

集群

Error Exception

Caused by: org.apache.seatunnel.common.exception.SeaTunnelRuntimeException: ErrorCode:[COMMON-24], ErrorDescription:[The table of ods_bs.ods_dalei has no primary keys, but the template 
     CREATE TABLE IF NOT EXISTS `${database}`.`${table_name}` (${rowtype_fields},dt datetime(0) default current_timestamp(0) on update current_timestamp(0)) ENGINE=OLAP UNIQUE KEY (${rowtype_primary_key}) DISTRIBUTED BY HASH (${rowtype_primary_key}) BUCKETS 3 PROPERTIES ( "replication_allocation" = "tag.location.default: 1","in_memory" = "false","storage_format" = "V2", "disable_auto_compaction" = "false") 
     which has the place holder named ${rowtype_primary_key}. Please use the option named save_mode_create_template to specify sql template]

Zeta or Flink or Spark Version

zeta

Java or Scala Version

java

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

13813586515 commented 3 months ago

同样的问题在oracle中也有存在，这里有个特殊的地方是，主键都是大写，猜测是不是大小写的问题导致

github-actions[bot] commented 2 months ago

This issue has been automatically marked as stale because it has not had recent activity for 30 days. It will be closed in next 7 days if no further activity occurs.

github-actions[bot] commented 1 month ago

This issue has been closed because it has not received response for too long time. You could reopen it if you encountered similar problems in the future.

apache / seatunnel