search

apache / seatunnel

SeaTunnel is a next-generation super high-performance, distributed, massive data integration tool.
https://seatunnel.apache.org/
Apache License 2.0
7.57k stars 1.66k forks source link

Bug Transformer SQL Functions - CAST / CONVERT unsupported #7058

Open a11dev opened 6 days ago

a11dev commented 6 days ago

Search before asking

What happened

SQL Cast function https://seatunnel.apache.org/docs/2.3.5/transform-v2/sql-functions/#cast CAST "Unsupported function: CAST" documentation example is with CONVERT result is the same "Unsupported function: CONVERT "

SeaTunnel Version

2.3.5

SeaTunnel Config

transform {
    Sql {
        source_table_name = "assignments"
        result_table_name = "mongoassignments"
        query = "select CAST(W6KEY,INT) as w6key, CAST(IFNULL(TASK,0),INT) as task from w6assignments"
    }

}

Running Command

java -Dlog4j2.configurationFile=E:\programmi\apache-seatunnel-2.3.5\config\log4j2_client.properties -Dhazelcast.client.config=E:\programmi\apache-seatunnel-2.3.5\config\hazelcast-client.yaml -Dseatunnel.config=E:\programmi\apache-seatunnel-2.3.5\config\seatunnel.yaml -Dhazelcast.config=E:\programmi\apache-seatunnel-2.3.5\config\hazelcast.yaml -Dseatunnel.logs.file_name=seatunnel-starter-clienttest -Xms256m -Xmx512m -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=E:\programmi\apache-seatunnel-2.3.5\dump\zeta-client  -cp E:\programmi\apache-seatunnel-2.3.5\lib\*;E:\programmi\apache-seatunnel-2.3.5\starter\seatunnel-starter.jar org.apache.seatunnel.core.starter.seatunnel.SeaTunnelClient  --config .\config\v2.batch.config.template -m local

Error Exception

2024-06-25 08:38:11,334 ERROR [o.a.s.c.s.SeaTunnel           ] [main] - Exception StackTrace:org.apache.seatunnel.core.starter.exception.CommandExecuteException: SeaTunnel job executed failed
    at org.apache.seatunnel.core.starter.seatunnel.command.ClientExecuteCommand.execute(ClientExecuteCommand.java:202)
    at org.apache.seatunnel.core.starter.SeaTunnel.run(SeaTunnel.java:40)
    at org.apache.seatunnel.core.starter.seatunnel.SeaTunnelClient.main(SeaTunnelClient.java:34)
Caused by: org.apache.seatunnel.transform.exception.TransformException: ErrorCode:[COMMON-05], ErrorDescription:[Unsupported operation] - Unsupported function: CAST
    at org.apache.seatunnel.transform.sql.zeta.ZetaSQLType.getFunctionType(ZetaSQLType.java:457)
    at org.apache.seatunnel.transform.sql.zeta.ZetaSQLType.getExpressionType(ZetaSQLType.java:143)
    at org.apache.seatunnel.transform.sql.zeta.ZetaSQLEngine.typeMapping(ZetaSQLEngine.java:206)
    at org.apache.seatunnel.transform.sql.SQLTransform.transformTableSchema(SQLTransform.java:122)
    at org.apache.seatunnel.transform.common.AbstractCatalogSupportTransform.transformCatalogTable(AbstractCatalogSupportTransform.java:50)
    at org.apache.seatunnel.transform.common.AbstractCatalogSupportTransform.getProducedCatalogTable(AbstractCatalogSupportTransform.java:40)
    at org.apache.seatunnel.engine.core.parse.MultipleTableJobConfigParser.parseTransform(MultipleTableJobConfigParser.java:434)
    at org.apache.seatunnel.engine.core.parse.MultipleTableJobConfigParser.parseTransforms(MultipleTableJobConfigParser.java:362)
    at org.apache.seatunnel.engine.core.parse.MultipleTableJobConfigParser.parse(MultipleTableJobConfigParser.java:193)
    at org.apache.seatunnel.engine.client.job.ClientJobExecutionEnvironment.getLogicalDag(ClientJobExecutionEnvironment.java:88)
    at org.apache.seatunnel.engine.client.job.ClientJobExecutionEnvironment.execute(ClientJobExecutionEnvironment.java:156)
    at org.apache.seatunnel.core.starter.seatunnel.command.ClientExecuteCommand.execute(ClientExecuteCommand.java:149)

same with convert/cast/CONVERT

Caused by: org.apache.seatunnel.transform.exception.TransformException: ErrorCode:[COMMON-05], ErrorDescription:[Unsupported operation] - Unsupported function: cast 
    at org.apache.seatunnel.transform.sql.zeta.ZetaSQLType.getFunctionType(ZetaSQLType.java:457)
    at org.apache.seatunnel.transform.sql.zeta.ZetaSQLType.getExpressionType(ZetaSQLType.java:143)
    at org.apache.seatunnel.transform.sql.zeta.ZetaSQLEngine.typeMapping(ZetaSQLEngine.java:206)
    at org.apache.seatunnel.transform.sql.SQLTransform.transformTableSchema(SQLTransform.java:122)
    at org.apache.seatunnel.transform.common.AbstractCatalogSupportTransform.transformCatalogTable(AbstractCatalogSupportTransform.java:50)
    at org.apache.seatunnel.transform.common.AbstractCatalogSupportTransform.getProducedCatalogTable(AbstractCatalogSupportTransform.java:40)
    at org.apache.seatunnel.engine.core.parse.MultipleTableJobConfigParser.parseTransform(MultipleTableJobConfigParser.java:434)
    at org.apache.seatunnel.engine.core.parse.MultipleTableJobConfigParser.parseTransforms(MultipleTableJobConfigParser.java:362)
    at org.apache.seatunnel.engine.core.parse.MultipleTableJobConfigParser.parse(MultipleTableJobConfigParser.java:193)
    at org.apache.seatunnel.engine.client.job.ClientJobExecutionEnvironment.getLogicalDag(ClientJobExecutionEnvironment.java:88)
    at org.apache.seatunnel.engine.client.job.ClientJobExecutionEnvironment.execute(ClientJobExecutionEnvironment.java:156)
    at org.apache.seatunnel.core.starter.seatunnel.command.ClientExecuteCommand.execute(ClientExecuteCommand.java:149)

Zeta or Flink or Spark Version

No response

Java or Scala Version

No response

Screenshots

No response

Are you willing to submit PR?

Code of Conduct

Hisoka-X commented 6 days ago

The config should be:

transform {
    Sql {
        source_table_name = "assignments"
        result_table_name = "mongoassignments"
        query = "select CAST(W6KEY as INT) as w6key, CAST(IFNULL(TASK,0),INT) as task from w6assignments"
    }
}

use cast (name as int) not cast(name,int)