[Bug] [dolphinscheduler] run fail when using clickhouse sql

[cnangel]

Search before asking

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

What happened

[INFO] 2024-07-05 15:02:31.775 +0800 - Sql Params are replaced sql , parameters: [INFO] 2024-07-05 15:02:31.776 +0800 - can't find udf function resource [INFO] 2024-07-05 15:02:31.862 +0800 - prepare statement replace sql : SELECT * FROM xxx_ods.s_yyy_infos_dist WHERE device_id GLOBAL IN ( SELECT device_id FROM xxx_ods.s_xxx_profile_dist final WHERE gmt_created >= toDateTime(now() - interval 2 hour, 3, 'Asia/Shanghai') AND gmt_created <= toDateTime(now(), 3, 'Asia/Shanghai') ) , sql parameters : {} [INFO] 2024-07-05 15:02:31.863 +0800 - main statement execute query, for sql: SELECT * FROM xxx_ods.s_yyy_infos_dist WHERE device_id GLOBAL IN ( SELECT device_id FROM xxx_ods.s_xxx_profile_dist final WHERE gmt_created >= toDateTime(now() - interval 2 hour, 3, 'Asia/Shanghai') AND gmt_created <= toDateTime(now(), 3, 'Asia/Shanghai') ) [ERROR] 2024-07-05 15:02:32.362 +0800 - execute sql error: Java 8 date/time type java.time.OffsetDateTimenot supported by default: add Module "com.fasterxml.jackson.datatype:jackson-datatype-jsr310" to enable handling [ERROR] 2024-07-05 15:02:32.365 +0800 - sql task error java.lang.IllegalArgumentException: Java 8 date/time typejava.time.OffsetDateTimenot supported by default: add Module "com.fasterxml.jackson.datatype:jackson-datatype-jsr310" to enable handling at com.fasterxml.jackson.databind.ObjectMapper.valueToTree(ObjectMapper.java:3394) at org.apache.dolphinscheduler.common.utils.JSONUtils.toJsonNode(JSONUtils.java:105) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.resultProcess(SqlTask.java:252) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.executeQuery(SqlTask.java:318) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.executeFuncAndSql(SqlTask.java:205) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.handle(SqlTask.java:159) at org.apache.dolphinscheduler.server.worker.runner.DefaultWorkerTaskExecutor.executeTask(DefaultWorkerTaskExecutor.java:54) at org.apache.dolphinscheduler.server.worker.runner.WorkerTaskExecutor.run(WorkerTaskExecutor.java:175) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) at java.base/java.lang.Thread.run(Thread.java:1583) Caused by: com.fasterxml.jackson.databind.exc.InvalidDefinitionException: Java 8 date/time typejava.time.OffsetDateTimenot supported by default: add Module "com.fasterxml.jackson.datatype:jackson-datatype-jsr310" to enable handling at com.fasterxml.jackson.databind.exc.InvalidDefinitionException.from(InvalidDefinitionException.java:77) at com.fasterxml.jackson.databind.SerializerProvider.reportBadDefinition(SerializerProvider.java:1300) at com.fasterxml.jackson.databind.ser.impl.UnsupportedTypeSerializer.serialize(UnsupportedTypeSerializer.java:35) at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider._serialize(DefaultSerializerProvider.java:480) at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:319) at com.fasterxml.jackson.databind.ObjectMapper.valueToTree(ObjectMapper.java:3389) ... 10 common frames omitted [ERROR] 2024-07-05 15:02:32.366 +0800 - Task execute failed, due to meet an exception org.apache.dolphinscheduler.plugin.task.api.TaskException: Execute sql task failed at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.handle(SqlTask.java:166) at org.apache.dolphinscheduler.server.worker.runner.DefaultWorkerTaskExecutor.executeTask(DefaultWorkerTaskExecutor.java:54) at org.apache.dolphinscheduler.server.worker.runner.WorkerTaskExecutor.run(WorkerTaskExecutor.java:175) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) at java.base/java.lang.Thread.run(Thread.java:1583) Caused by: java.lang.IllegalArgumentException: Java 8 date/time typejava.time.OffsetDateTimenot supported by default: add Module "com.fasterxml.jackson.datatype:jackson-datatype-jsr310" to enable handling at com.fasterxml.jackson.databind.ObjectMapper.valueToTree(ObjectMapper.java:3394) at org.apache.dolphinscheduler.common.utils.JSONUtils.toJsonNode(JSONUtils.java:105) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.resultProcess(SqlTask.java:252) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.executeQuery(SqlTask.java:318) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.executeFuncAndSql(SqlTask.java:205) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.handle(SqlTask.java:159) ... 5 common frames omitted Caused by: com.fasterxml.jackson.databind.exc.InvalidDefinitionException: Java 8 date/time typejava.time.OffsetDateTime not supported by default: add Module "com.fasterxml.jackson.datatype:jackson-datatype-jsr310" to enable handling at com.fasterxml.jackson.databind.exc.InvalidDefinitionException.from(InvalidDefinitionException.java:77) at com.fasterxml.jackson.databind.SerializerProvider.reportBadDefinition(SerializerProvider.java:1300) at com.fasterxml.jackson.databind.ser.impl.UnsupportedTypeSerializer.serialize(UnsupportedTypeSerializer.java:35) at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider._serialize(DefaultSerializerProvider.java:480) at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:319) at com.fasterxml.jackson.databind.ObjectMapper.valueToTree(ObjectMapper.java:3389) ... 10 common frames omitted [INFO] 2024-07-05 15:02:32.373 +0800 - Get appIds from worker 192.168.1.10:1234, taskLogPath: /home/foo/apache-dolphinscheduler-3.2.1-bin/standalone-server/logs/20240705/14173763226592/2/1/2.log [INFO] 2024-07-05 15:02:32.373 +0800 - Start finding appId in /home/foo/apache-dolphinscheduler-3.2.1-bin/standalone-server/logs/20240705/14173763226592/2/1/2.log, fetch way: log [INFO] 2024-07-05 15:02:32.376 +0800 - The appId is empty [INFO] 2024-07-05 15:02:32.376 +0800 - Cancel the task successfully [INFO] 2024-07-05 15:02:32.381 +0800 - Get a exception when execute the task, will send the task status: FAILURE to master: 192.168.1.10:1234 [INFO] 2024-07-05 15:02:32.382 +0800 - FINALIZE_SESSION

and the s_yyy_infos_dist table schema:
create table s_aquaman_user_device_profile_dist ( id UInt64 comment 'primary key', ... gmt_created DateTime64(3, 'Asia/Shanghai') comment 'create time', gmt_modified DateTime64(3, 'Asia/Shanghai') comment 'modify time' )

What you expected to happen

Please support DateTime type for clickhouse tasks.

How to reproduce

1. Add clickhouse source;
2. Add project and sql workflow.
3. Get the working instance immediately when running the workflow, find failure and check the log

Anything else

version: 3.2.1
clickhouse version: 24.1.5.6

Version

3.2.x

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

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