CSV文件导入报错

csv的数据

"DISTRIBUTOR_CODE","DISTRIBUTOR_NAME","BRANCHCODE","BRANCHCODE_NAME","FUNDCODE","FUNDNAME","CERTIFICATE_TYPE","START_DATE","END_DATE","SPECIAL_CACUL","RATE_TYPE","RATE_RATIO","MAX_BELONE_SCALE","MIN_BELONE_SCALE","LAST_UPDATE_TIME","USER_NAME","USER_CODE","DATA_STATUS"
"034",xx银行,,,"00xxxx",xx健康环保混合,"0","2021-02-22 00:00:00.0","2999-01-01 00:00:00.0",A,B,0.2,99999999990000,0,1650805437240,林x尧,x_linziyao,"2"

报的错误:

2023-06-28 16:54:05 [WARN] Retry Table: "NTA_TASK_FAIL_RATE", Partition: 0. Records: 200. Error: ORA-01830: date format picture ends before converting entire input string. Retry Mode: SERIAL.
2023-06-28 16:54:05 [ERROR] INSERT INTO "YDT_DB"."NTA_TASK_FAIL_RATE" ("DISTRIBUTOR_CODE","DISTRIBUTOR_NAME","BRANCHCODE","BRANCHCODE_NAME","FUNDCODE","FUNDNAME","CERTIFICATE_TYPE","START_DATE","END_DATE","SPECIAL_CACUL","RATE_TYPE","RATE_RATIO","MAX_BELONE_SCALE","MIN_BELONE_SCALE","LAST_UPDATE_TIME","USER_NAME","USER_CODE","DATA_STATUS") VALUES ('xxx','xxxx西部','','','xxx630','xx国防A','0','2022-04-22 00:00:00.0','2999-01-01 00:00:00.0','A','B','0.1','99999999990000','0','1650519396371','xx平','zhoupingping','2');
2023-06-28 16:54:05 [ERROR] Too many errors occurred. Table: "NTA_TASK_FAIL_RATE"
2023-06-28 16:54:05 [ERROR] Too many errors occurred while loading data into table: "NTA_TASK_FAIL_RATE"....
2023-06-28 16:54:05 [ERROR] Error occurred when loading data into "YDT_DB"."NTA_TASK_FAIL_RATE"
java.sql.SQLException: ORA-01830: date format picture ends before converting entire input string
	at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:2147) ~[oceanbase-client-2.2.11.jar!/:?]
	at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:2012) ~[oceanbase-client-2.2.11.jar!/:?]
	at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1905) ~[oceanbase-client-2.2.11.jar!/:?]
	at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.executePreparedQuery(AbstractQueryProtocol.java:482) ~[oceanbase-client-2.2.11.jar!/:?]
	at com.oceanbase.jdbc.JDBC4ServerPreparedStatement.executeInternal(JDBC4ServerPreparedStatement.java:799) ~[oceanbase-client-2.2.11.jar!/:?]
	at com.oceanbase.jdbc.JDBC4ServerPreparedStatement.execute(JDBC4ServerPreparedStatement.java:778) ~[oceanbase-client-2.2.11.jar!/:?]
	at com.oceanbase.jdbc.JDBC4ServerPreparedStatement.executeUpdate(JDBC4ServerPreparedStatement.java:767) ~[oceanbase-client-2.2.11.jar!/:?]
	at com.oceanbase.tools.loaddump.dumper.writer.rdb.DatabaseWriter$RetryCallback.executeUpdate(DatabaseWriter.java:721) ~[ob-loader-dumper-4.1.0-SNAPSHOT.jar!/:?]
	at com.oceanbase.tools.loaddump.dumper.writer.rdb.DatabaseWriter$RetryCallback.call(DatabaseWriter.java:531) ~[ob-loader-dumper-4.1.0-SNAPSHOT.jar!/:?]
	at com.oceanbase.tools.loaddump.dumper.writer.rdb.DatabaseWriter$RetryCallback.call(DatabaseWriter.java:421) ~[ob-loader-dumper-4.1.0-SNAPSHOT.jar!/:?]
	at com.github.rholder.retry.AttemptTimeLimiters$NoAttemptTimeLimit.call(AttemptTimeLimiters.java:78) ~[guava-retrying-2.0.0.jar!/:?]
	at com.github.rholder.retry.Retryer.call(Retryer.java:160) ~[guava-retrying-2.0.0.jar!/:?]
	at com.oceanbase.tools.loaddump.dumper.writer.rdb.DatabaseWriter.onEvent(DatabaseWriter.java:303) [ob-loader-dumper-4.1.0-SNAPSHOT.jar!/:?]
	at com.oceanbase.tools.loaddump.dumper.writer.rdb.DatabaseWriter.onEvent(DatabaseWriter.java:80) [ob-loader-dumper-4.1.0-SNAPSHOT.jar!/:?]
	at com.lmax.disruptor.WorkProcessor.run(WorkProcessor.java:143) [disruptor-3.4.4.jar!/:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_312]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_312]
	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_312]
2023-06-28 16:54:05 [ERROR] Too many errors occurred. Table: "NTA_TASK_FAIL_RATE"
2023-06-28 16:54:05 [ERROR] Too many errors occurred. Table: "NTA_TASK_FAIL_RATE"
2023-06-28 16:54:05 [WARN] Failed to execute data transfer task
java.lang.IllegalStateException: YDT_DB.NTA_TASK_FAIL_RATE: ORA-01830: date format picture ends before converting entire input string
	at com.oceanbase.odc.service.datatransfer.task.BaseDataTransferTask.syncWaitFinished(BaseDataTransferTask.java:167) ~[classes!/:?]
	at com.oceanbase.odc.service.datatransfer.task.BaseDataTransferTask.call(BaseDataTransferTask.java:128) ~[classes!/:?]
	at com.oceanbase.odc.service.datatransfer.task.BaseDataTransferTask.call(BaseDataTransferTask.java:35) ~[classes!/:?]
	at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_312]
	at com.oceanbase.odc.common.trace.TraceDecorator.lambda$decorate$0(TraceDecorator.java:25) ~[odc-common-4.1.2-20230414.jar!/:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_312]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_312]
	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_312]
2023-06-28 16:54:05 [WARN] The connection holder is invalid or closed, reset a new direct connection now...
2023-06-28 16:54:05 [WARN] Reset a new direct connection and init session variables finished. Elapsed: 6.710 ms
2023-06-28 16:54:05 [WARN] The connection holder is invalid or closed, reset a new direct connection now...
2023-06-28 16:54:05 [WARN] Reset a new direct connection and init session variables finished. Elapsed: 6.535 ms
2023-06-28 16:54:05 [WARN] The connection holder is invalid or closed, reset a new direct connection now...
2023-06-28 16:54:05 [WARN] Reset a new direct connection and init session variables finished. Elapsed: 5.843 ms

麻烦贴一下版本

4.0.0版本

Oracle模式请设置一下日期时间格式,否则数据插入,日期时间格式化错误。
set nls_date_format, set nls_timestamp_format, set nls_timestamp_tz_format 等,可以看一下数据库文档。参考一下:https://www.coder.work/article/7087327
PS: 要设置成业务数据中的日期时间格式,而不是 EUR or US Date format。

1 个赞

但是,我是使用csv导入的时候,报的这个错呢。这时候csv里都已格式化成对应的字符串,oceanbase里是日期类型。

文件中的数据是 “2021-02-22 00:00:00.0”,所以 Oracle 数据库请使用 TIMESTAMP 类型。因为 DATE 类型没有办法处理精度。类型修改完毕,请设置 nls_timestamp_format=‘yyyy-mm-dd hh24:mi:ss.ff9’ 即可。

如图所示,使用 to_date 函数无法处理带小数精度的日期时间,使用 to_timestamp 函数才可以进一步转换。