https://flink.apache.org/ logo
Join Slack
Powered by
I have a problem with flink sql function `to_date`...
# troubleshooting
s
I have a problem with flink sql function 
to_date
, docs say 
TO_DATE(string1[, string2])
, I am calling it using
Copy code
TO_DATE(CAST(`OAORDT` as string), 'yyyyMMdd') AS `orderDate`,
Invalid function call:\nTO_DATE(STRING, STRING NOT NULL) Expected signatures are:\nTO_DATE(*). Any Ideas what is wrong? flink 1.16.1
Copy code
hive error: ('Query error', 'org.apache.flink.table.gateway.service.utils.SqlExecutionException: Failed to execute the operation ef31415c-89d7-477d-86b1-fec288643bff.\n\tat org.apache.flink.table.gateway.service.operation.OperationManager$Operation.processThrowable(OperationManager.java:389)\n\tat org.apache.flink.table.gateway.service.operation.OperationManager$Operation.lambda$run$0(OperationManager.java:248)\n\tat java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)\n\tat java.util.concurrent.FutureTask.run(FutureTask.java:266)\n\tat java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)\n\tat java.util.concurrent.FutureTask.run(FutureTask.java:266)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\tat java.lang.Thread.run(Thread.java:750)\nCaused by: org.apache.flink.table.api.ValidationException: SQL validation failed. SQL validation failed. Invalid function call:\nTO_DATE(STRING, STRING NOT NULL)\n\tat <http://org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org|org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org>$apache$flink$table$planner$calcite$FlinkPlannerImpl$$validate(FlinkPlannerImpl.scala:186)\n\tat org.apache.flink.table.planner.calcite.FlinkPlannerImpl.validate(FlinkPlannerImpl.scala:113)\n\tat org.apache.flink.table.planner.operations.SqlToOperationConverter.convert(SqlToOperationConverter.java:261)\n\tat org.apache.flink.table.planner.delegation.ParserImpl.parse(ParserImpl.java:106)\n\tat org.apache.flink.table.gateway.service.operation.OperationExecutor.executeStatement(OperationExecutor.java:90)\n\tat org.apache.flink.table.gateway.service.SqlGatewayServiceImpl.lambda$executeStatement$0(SqlGatewayServiceImpl.java:182)\n\tat org.apache.flink.table.gateway.service.operation.OperationManager.lambda$submitOperation$1(OperationManager.java:111)\n\tat org.apache.flink.table.gateway.service.operation.OperationManager$Operation.lambda$run$0(OperationManager.java:239)\n\t... 7 more\nCaused by: org.apache.flink.table.api.ValidationException: SQL validation failed. Invalid function call:\nTO_DATE(STRING, STRING NOT NULL)\n\tat <http://org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org|org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org>$apache$flink$table$planner$calcite$FlinkPlannerImpl$$validate(FlinkPlannerImpl.scala:186)\n\tat org.apache.flink.table.planner.calcite.FlinkPlannerImpl.validate(FlinkPlannerImpl.scala:113)\n\tat org.apache.flink.table.planner.calcite.FlinkPlannerImpl.validateRichSqlInsert(FlinkPlannerImpl.scala:291)\n\tat <http://org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org|org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org>$apache$flink$table$planner$calcite$FlinkPlannerImpl$$validate(FlinkPlannerImpl.scala:174)\n\t... 14 more\nCaused by: org.apache.flink.table.api.ValidationException: Invalid function call:\nTO_DATE(STRING, STRING NOT NULL)\n\tat org.apache.flink.table.types.inference.TypeInferenceUtil.createInvalidCallException(TypeInferenceUtil.java:193)\n\tat org.apache.flink.table.planner.functions.inference.TypeInferenceOperandChecker.checkOperandTypes(TypeInferenceOperandChecker.java:89)\n\tat org.apache.calcite.sql.SqlOperator.checkOperandTypes(SqlOperator.java:679)\n\tat org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:444)\n\tat org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:330)\n\tat org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:226)\n\tat org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5716)\n\tat org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5703)\n\tat org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)\n\tat org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1736)\n\tat org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1727)\n\tat org.apache.calcite.sql.SqlAsOperator.deriveType(SqlAsOperator.java:133)\n\tat org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5716)\n\tat org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5703)\n\tat org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)\n\tat org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1736)\n\tat org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1727)\n\tat org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:421)\n\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4061)\n\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3347)\n\tat org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)\n\tat org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)\n\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:997)\n\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:975)\n\tat org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:232)\n\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:952)\n\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:704)\n\tat <http://org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org|org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org>$apache$flink$table$planner$calcite$FlinkPlannerImpl$$validate(FlinkPlannerImpl.scala:182)\n\t... 17 more\nCaused by: org.apache.flink.table.api.ValidationException: Invalid input arguments. Expected signatures are:\nTO_DATE(*)\n\tat org.apache.flink.table.types.inference.TypeInferenceUtil.createInvalidInputException(TypeInferenceUtil.java:180)\n\tat org.apache.flink.table.planner.functions.inference.TypeInferenceOperandChecker.checkOperandTypesOrError(TypeInferenceOperandChecker.java:124)\n\tat org.apache.flink.table.planner.functions.inference.TypeInferenceOperandChecker.checkOperandTypes(TypeInferenceOperandChecker.java:86)\n\t... 43 more\nCaused by: org.apache.flink.table.api.ValidationException: Cannot find a suitable Hive function from org.apache.hadoop.hive.ql.udf.generic.GenericUDFDate for the input arguments\n\tat org.apache.flink.table.types.inference.CallContext.newValidationError(CallContext.java:93)\n\tat org.apache.flink.table.functions.hive.HiveFunction$HiveFunctionInputStrategy.inferInputTypes(HiveFunction.java:87)\n\tat org.apache.flink.table.types.inference.TypeInferenceUtil.inferInputTypes(TypeInferenceUtil.java:434)\n\tat org.apache.flink.table.types.inference.TypeInferenceUtil.adaptArguments(TypeInferenceUtil.java:123)\n\tat org.apache.flink.table.types.inference.TypeInferenceUtil.adaptArguments(TypeInferenceUtil.java:100)\n\tat org.apache.flink.table.planner.functions.inference.TypeInferenceOperandChecker.checkOperandTypesOrError(TypeInferenceOperandChecker.java:122)\n\t... 44 more\n')
As a workaround this works, but I am still confused why I can’t provide the format directly
Copy code
TO_DATE(SUBSTR(CAST(`OAORDT` as string), 1, 4) || '-' || SUBSTR(CAST(`OAORDT` as string), 5, 2) || '-' || SUBSTR(CAST(`OAORDT` as string), 7, 2)) AS `orderDate`,
6 Views
Open in Slack
PreviousNext
Powered by