Hi,
Firstly, define a UDF like this:
public class UDFDayOfMonth implements UDF { private final SimpleDateFormat
formatter = new SimpleDateFormat("yyyy-MM-dd"); private final Calendar
calendar = Calendar.getInstance(); /** * Get the day of month from a
date string. * * @param dateStr the dateStr in the format of
"yyyy-MM-dd HH:mm:ss" or * "yyyy-MM-dd". * @return an
int from 1 to 31. null if the dateStr is not a valid date * string. */
public Integer evaluate(String dateStr) throws ParseException { if
(dateStr == null) { return null; } try {
java.util.Date date = formatter.parse(dateStr);
calendar.setTime(date); return calendar.get(Calendar.DAY_OF_MONTH);
} catch (ParseException e) { throw e; } } public
Integer evaluate(Date date) { if (date == null) { return
null; } calendar.setTime(date); // Time doesn't matter.
return calendar.get(Calendar.DAY_OF_MONTH); } public Integer
evaluate(Timestamp timestamp) { if (timestamp == null) {
return null; } calendar.setTime(timestamp); return
calendar.get(Calendar.DAY_OF_MONTH); }}
Secondly, Define filds like this:
CREATE TABLE kafka_source1 ( name CHARACTER(250), price TIMESTAMP) Thirdly,
CREATE TEMPORARY function 'UDFDAYOFMONTH' AS
'com.netease.sloth.server.udf.UDFDayOfMonth';INSERT INTO db_sink SELECT name,
UDFDAYOFMONTH(price) FROM kafka_source1; After debugging, find that But,
typeNames only has 'TIMESTAMP' field，no 'Date' field
assert containsType(types) occurs error.
The error stack:
java.lang.AssertionError
at
org.apache.calcite.sql.type.SqlTypeExplicitPrecedenceList.compareTypePrecedence(SqlTypeExplicitPrecedenceList.java:153)
at org.apache.calcite.sql.SqlUtil.bestMatch(SqlUtil.java:622)
at
org.apache.calcite.sql.SqlUtil.filterRoutinesByTypePrecedence(SqlUtil.java:589)
at org.apache.calcite.sql.SqlUtil.lookupSubjectRoutines(SqlUtil.java:443)
at org.apache.calcite.sql.SqlUtil.lookupRoutine(SqlUtil.java:366)
at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:244)
at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:223)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4445)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4432)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:137)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1532)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1515)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:428)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:3581)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3088)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateInsert(SqlValidatorImpl.java:3754)
at org.apache.calcite.sql.SqlInsert.validate(SqlInsert.java:148)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:865)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:576)
at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:185)
at
com.netease.sloth.server.plan.SlothPlanner.parseSqlDml(SlothPlanner.java:377)
at
com.netease.sloth.server.actor.AbstractJobManager.handle(AbstractJobManager.java:149)
at
com.netease.sloth.server.actor.BuildInUDFTest.testRunJobLocal(BuildInUDFTest.java:57)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at
com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:51)
at
com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
2017-05-04
黄珍锡
网易杭州研究院数据科学中心
杭州市滨江区网商路599号
Tel：18358190458
Email：[email protected]
