Bug Transformer SQL Functions - CAST / CONVERT unsupported

[a11dev]

Bug Transformer SQL Functions - CAST / CONVERT unsupported

[Hisoka-X]

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)

[a11dev]

Hi Jia, sorry if I'm disturbing you! I hope you could help me. from docs sink jars must be put into lib folder in case of zeta engine. Mine configuration is like this one: jdbc { source_table_name = ["fake"] # if you would use json or jsonb type insert please add jdbc url stringtype=unspecified option url = "jdbc:postgresql://localhost:5432/NWFMLOGMON" driver = "org.postgresql.Driver" user = user password = pwd query = "insert into test_table(name,age) values(?,?)" } and lib folder contains the postgres jar file. Still I have this exception: Exception in thread "main" 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: java.lang.RuntimeException: Plugin PluginIdentifier{engineType='seatunnel', pluginType='sink', pluginName='jdbc'} not found. at org.apache.seatunnel.plugin.discovery.AbstractPluginDiscovery.createPluginInstance(AbstractPluginDiscovery.java:234) at org.apache.seatunnel.engine.core.parse.ConnectorInstanceLoader.loadSinkInstance(ConnectorInstanceLoader.java:77) at org.apache.seatunnel.engine.core.parse.JobConfigParser.parseSink(JobConfigParser.java:159) at org.apache.seatunnel.engine.core.parse.JobConfigParser.parseSinks(JobConfigParser.java:135) at org.apache.seatunnel.engine.core.parse.MultipleTableJobConfigParser.parseSink(MultipleTableJobConfigParser.java:517) at org.apache.seatunnel.engine.core.parse.MultipleTableJobConfigParser.parse(MultipleTableJobConfigParser.java:200) 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) ... 2 more Where exactly should I put the jar? thanks a lot Alessandro Il giorno mar 25 giu 2024 alle ore 12:59 Jia Fan ***@***.***> ha scritto:

…

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) — Reply to this email directly, view it on GitHub <#7058 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AI2V567U46XSM6HYVDY64J3ZJFERXAVCNFSM6AAAAABJ3LG5OCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCOBYGYZDQMZUGQ> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[Hisoka-X]

Please share your seatunnel directory structure. By default, you don't need to modify the path of the jar package. You only need to put the mysql driver jar in the lib directory.

[a11dev]

Thanks for your reply, I'm using a standard folder structure and the basic example "fake+console" is working fine. Adding jdbc is causing some issue. below the folder structure ( I've also listed jars inside connector/lib/plugins) plus the bat command used to start the engine: F:\path\apache-seatunnel-2.3.5 .mvn bin config connector connector-console-2.3.5.jar connector-fake-2.3.5.jar ojdbc8-19.22.0.0.jar plugin-mapping.properties postgresql-42.7.3.jar lib ojdbc8-19.22.0.0.jar orai18n-19.3.0.0.jar postgresql-42.7.3.jar seatunnel-hadoop3-3.1.4-uber seatunnel-transforms-v2.jar LICENSE licenses mvnw mvnw.cmd NOTICE plugins jdbc ojdbc8-19.22.0.0.jar orai18n-19.3.0.0.jar README.md runseatun starter java -Dlog4j2.configurationFile=F:\path\apache-seatunnel-2.3.5\config\log4j2_client.properties -Dhazelcast.client.config=F:\path\apache-seatunnel-2.3.5\config\hazelcast-client.yaml -Dseatunnel.config=F:\path\apache-seatunnel-2.3.5\config\seatunnel.yaml -Dhazelcast.config=F:\path\apache-seatunnel-2.3.5\config\hazelcast.yaml -Dseatunnel.logs.file_name=seatunnel-starter-clienttest -Xms256m -Xmx512m -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=F:\path\apache-seatunnel-2.3.5\dump\zeta-client -cp F:\path\apache-seatunnel-2.3.5\lib\*;F:\path\apache-seatunnel-2.3.5\starter\seatunnel-starter.jar org.apache.seatunnel.core.starter.seatunnel.SeaTunnelClient --config .\config\v2.batch.config.template -m local thnaks Alessandro Il giorno mer 3 lug 2024 alle ore 13:29 Jia Fan ***@***.***> ha scritto:

…

Please share your seatunnel directory structure. By default, you don't need to modify the path of the jar package. You only need to put the mysql driver jar in the lib directory. — Reply to this email directly, view it on GitHub <#7058 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AI2V563Y2L7QLNLH522OVJTZKPODLAVCNFSM6AAAAABJ3LG5OCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEMBVHA2TQNJWGQ> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[Hisoka-X]

oh, you should execute install-plugin.sh before use jdbc connector. You can refer https://seatunnel.apache.org/docs/2.3.5/start-v2/locally/deployment#step-3-install-connectors-plugin

[a11dev]

thanks for the feedback, I'm working "offline" by the way checking pom file I was able to identify all references and install it manually. Alessandro Il giorno mer 3 lug 2024 alle ore 14:26 Jia Fan ***@***.***> ha scritto:

…

oh, you should execute install-plugin.sh before use jdbc connector. You can refer https://seatunnel.apache.org/docs/2.3.5/start-v2/locally/deployment#step-3-install-connectors-plugin — Reply to this email directly, view it on GitHub <#7058 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AI2V562SFV7WXLULPTZUEMTZKPUV5AVCNFSM6AAAAABJ3LG5OCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEMBVHE2TMNRTGE> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[Hisoka-X]

you can use source code to build binary package. This way more easier to get all connector jar. Just run mvn clean package -DskipTests in source code, then get binary package in seatunnel-dist/target

[a11dev]

Thanks, I'll try as soon as I can. Alessandro Il giorno gio 4 lug 2024 alle ore 04:08 Jia Fan ***@***.***> ha scritto:

…

you can use source code to build binary package. This way more easier to get all connector jar. Just run mvn clean package -DskipTests in source code, then get binary package in seatunnel-dist/target — Reply to this email directly, view it on GitHub <#7058 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AI2V567ITTZUKI7A7NZCOA3ZKSVBJAVCNFSM6AAAAABJ3LG5OCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEMBXHA3TANJVHA> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[a11dev]

Hi Jin, question, jdbc source can be used with STREAM job.mode? If yes, it is not clear for me how to maintain a checkpoint for the last row updated or inserted. Can you help me? Thank you Alessandro Il giorno gio 4 lug 2024 alle ore 04:08 Jia Fan ***@***.***> ha scritto:

…

you can use source code to build binary package. This way more easier to get all connector jar. Just run mvn clean package -DskipTests in source code, then get binary package in seatunnel-dist/target — Reply to this email directly, view it on GitHub <#7058 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AI2V567ITTZUKI7A7NZCOA3ZKSVBJAVCNFSM6AAAAABJ3LG5OCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEMBXHA3TANJVHA> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[Hisoka-X]

question, jdbc source can be used with STREAM job.mode?

no, you can't

[NoPr]

配置应该是：

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"
	}
}

cast (name as int)不使用cast(name,int)

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)

你好，我有一个问题：
"query " : "select col1 from fake_1810212286042378240_fa where col1 > 2023-12-16T10:58:24.844'"
"query " : "select col1 from fake_1810212286042378240_fa where cast(col1 as integer) > 1720690455"
"query " : "select cast(col1 as integer) as col1 from fake_1810212286042378240_fa where col1 > 1720690455"
或者
"query " : "select col1 from fake_1810212286042378240_fa where col1 > cast('2023-12-16T10:58:24.844' as localdaetime)"并不能够找出增量数据。
前三者会报错左右字段不匹配，后者会报错不能转换。而且增量查询只能在transform中使用？请问我的query语句哪里有问题，谢谢你。col1是timestamp字段类型，

[Hisoka-X]

你好，我有一个问题：
"query " : "select col1 from fake_1810212286042378240_fa where col1 > 2023-12-16T10:58:24.844'"
"query " : "select col1 from fake_1810212286042378240_fa where cast(col1 as integer) > 1720690455"
"query " : "select cast(col1 as integer) as col1 from fake_1810212286042378240_fa where col1 > 1720690455"
或者
"query " : "select col1 from fake_1810212286042378240_fa where col1 > cast('2023-12-16T10:58:24.844' as localdaetime)"并不能够找出增量数据。
前三者会报错左右字段不匹配，后者会报错不能转换。而且增量查询只能在transform中使用？请问我的query语句哪里有问题，谢谢你。col1是timestamp字段类型，

Seem like a bug, could you create an issue for this? Thanks.

[NoPr]

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)

你好，我有一个问题：
"query " : "select col1 from fake_1810212286042378240_fa where col1 > 2023-12-16T10：58：24.844'"
"query " : "select col1 from fake_1810212286042378240_fa where cast(col1 as integer) > 1720690455"
"query " : "select cast(col1 as integer) as col1 from fake_1810212286042378240_fa where col1 > 1720690455"
或者
"query " : "select col1 from fake_1810212286042378240_fa where col1 > cast('2023-12-16T10：58：24.844' as localdaetime)"我并不能找出增量数据。
前三者会报错左右字段不匹配，后者会报错不能转换。而且增量查询只能在transform使用中？请问我的查询语句在哪里有问题，谢谢你。col1是timestamp字段类型，

看起来像是一个错误，你能为此创建一个问题吗？谢谢。

你好，这是我创建的issues
#7172