FlinkSQL split报错No match found for function signature 使用UDF ScalarFunction/TableFunction

1 用TableFunction报错,用ScalarFunction正常

        stEnv.registerFunction("SplitLine",new SplitLine("_"));
        //报错SqlValidatorException: No match found for function signature SplitLine(<CHARACTER>)
        Table result = stEnv.sqlQuery("SELECT name,name2,SplitLine(name2) as name3,qtty," +
                "case " +
                "     when coalesce(name,'')='' then qtty*0 " +
                "     when name = 'a苹果' then qtty*1 " +
                "     when name = 'b梨' then qtty*2 " +
                "      when name = 'c西瓜' then qtty*3 " +
                "      else qtty*4 " +
                " end as total" +
                " FROM tableA where qtty > 5");



public static class SplitLine extends TableFunction<String> {
        private String separator = " ";
        public SplitLine(String separator) {
            this.separator = separator;
        }
        public String eval(String str) {
            return str.split(separator)[0];
        }
    }

    public static class SplitLine2 extends ScalarFunction {
        private String separator = " ";
        public SplitLine2(String separator) {
            this.separator = separator;
        }
        public String eval(String str) {
            return str.split(separator)[0];
        }
    }
        stEnv.registerFunction("SplitLine2",new SplitLine2("_"));

        //报错SqlValidatorException: No match found for function signature SplitLine(<CHARACTER>)
        Table result = stEnv.sqlQuery("SELECT name,name2,SplitLine2(name2) as name3,qtty," +
                "case " +
                "     when coalesce(name,'')='' then qtty*0 " +
                "     when name = 'a苹果' then qtty*1 " +
                "     when name = 'b梨' then qtty*2 " +
                "      when name = 'c西瓜' then qtty*3 " +
                "      else qtty*4 " +
                " end as total" +
                " FROM tableA where qtty > 5");

Wed Mar 10 10:24:40 CST 2021,提交元素:Order{name='e火龙果', name2='e_火龙果', name3='null', qtty=6, total=null}
6> Order{name='e火龙果', name2='e_火龙果', name3='e', qtty=6, total=24}
Wed Mar 10 10:24:41 CST 2021,提交元素:Order{name='c西瓜', name2='c_西瓜', name3='null', qtty=7, total=null}
7> Order{name='c西瓜', name2='c_西瓜', name3='c', qtty=7, total=21}
Wed Mar 10 10:24:42 CST 2021,提交元素:Order{name='b梨', name2='b_梨', name3='null', qtty=1, total=null}
Wed Mar 10 10:24:43 CST 2021,提交元素:Order{name='a苹果', name2='a_苹果', name3='null', qtty=6, total=null}
9> Order{name='a苹果', name2='a_苹果', name3='a', qtty=6, total=6}
Wed Mar 10 10:24:44 CST 2021,提交元素:Order{name='a苹果', name2='a_苹果', name3='null', qtty=4, total=null}
Wed Mar 10 10:24:45 CST 2021,提交元素:Order{name='a苹果', name2='a_苹果', name3='null', qtty=1, total=null}
Wed Mar 10 10:24:46 CST 2021,提交元素:Order{name='e火龙果', name2='e_火龙果', name3='null', qtty=8, total=null}
12> Order{name='e火龙果', name2='e_火龙果', name3='e', qtty=8, total=32}

 

2 在sql中用[0]报错

       stEnv.registerFunction("SplitLine4", new SplitLine4());

        Table result = stEnv.sqlQuery("SELECT name,name2, SplitLine4(name2,'_')[0] as name3,qtty," +
                "case " +
                "     when coalesce(name,'')='' then qtty*0 " +
                "     when name = 'a苹果' then qtty*1 " +
                "     when name = 'b梨' then qtty*2 " +
                "      when name = 'c西瓜' then qtty*3 " +
                "      else qtty*4 " +
                " end as total" +
                " FROM tableA where qtty > 5");

 public static class SplitLine4 extends ScalarFunction {
        private String separator;
        private Integer index;

        public SplitLine4() {
        }

        public String[] eval(String str, String separator) {
            if (StringUtils.isNotBlank(str)) {
                String[] arr = str.split(separator);
                return arr;
            }
            return new String[10];
        }
    }


Wed Mar 10 10:56:05 CST 2021,提交元素:Order{name='c西瓜', name2='c_西瓜', name3='null', qtty=0, total=null}
Wed Mar 10 10:56:06 CST 2021,提交元素:Order{name='c西瓜', name2='c_西瓜', name3='null', qtty=9, total=null}
Exception in thread "main" org.apache.flink.runtime.client.JobExecutionException: Job execution failed.
	at org.apache.flink.runtime.jobmaster.JobResult.toJobExecutionResult(JobResult.java:147)
	at org.apache.flink.runtime.minicluster.MiniCluster.executeJobBlocking(MiniCluster.java:626)
	at org.apache.flink.streaming.api.environment.LocalStreamEnvironment.execute(LocalStreamEnvironment.java:117)
	at org.apache.flink.streaming.api.environment.StreamExecutionEnvironment.execute(StreamExecutionEnvironment.java:1507)
	at SQL.jd.DataStreamSql2_Select_CaseWhen_Where.main(DataStreamSql2_Select_CaseWhen_Where.java:159)
Caused by: java.lang.ArrayIndexOutOfBoundsException: -1
	at DataStreamCalcRule$72.processElement(Unknown Source)
	at org.apache.flink.table.runtime.CRowProcessRunner.processElement(CRowProcessRunner.scala:66)
	at org.apache.flink.table.runtime.CRowProcessRunner.processElement(CRowProcessRunner.scala:35)
	at org.apache.flink.streaming.api.operators.ProcessOperator.processElement(ProcessOperator.java:66)
	at org.apache.flink.streaming.runtime.tasks.OperatorChain$CopyingChainingOutput.pushToOperator(OperatorChain.java:637)
	at org.apache.flink.streaming.runtime.tasks.OperatorChain$CopyingChainingOutput.collect(OperatorChain.java:612)
	at org.apache.flink.streaming.runtime.tasks.OperatorChain$CopyingChainingOutput.collect(OperatorChain.java:592)
	at org.apache.flink.streaming.api.operators.AbstractStreamOperator$CountingOutput.collect(AbstractStreamOperator.java:727)
	at org.apache.flink.streaming.api.operators.AbstractStreamOperator$CountingOutput.collect(AbstractStreamOperator.java:705)
	at org.apache.flink.streaming.api.operators.TimestampedCollector.collect(TimestampedCollector.java:51)
	at org.apache.flink.table.runtime.CRowWrappingCollector.collect(CRowWrappingCollector.scala:37)
	at org.apache.flink.table.runtime.CRowWrappingCollector.collect(CRowWrappingCollector.scala:28)
	at DataStreamSourceConversion$15.processElement(Unknown Source)
	at org.apache.flink.table.runtime.CRowOutputProcessRunner.processElement(CRowOutputProcessRunner.scala:70)
	at org.apache.flink.streaming.api.operators.ProcessOperator.processElement(ProcessOperator.java:66)
	at org.apache.flink.streaming.runtime.io.StreamOneInputProcessor.processElement(StreamOneInputProcessor.java:164)
	at org.apache.flink.streaming.runtime.io.StreamOneInputProcessor.processInput(StreamOneInputProcessor.java:143)
	at org.apache.flink.streaming.runtime.tasks.StreamTask.processInput(StreamTask.java:279)
	at org.apache.flink.streaming.runtime.tasks.StreamTask.run(StreamTask.java:321)
	at org.apache.flink.streaming.runtime.tasks.StreamTask.runAndHandleCancel(StreamTask.java:286)
	at org.apache.flink.streaming.runtime.tasks.StreamTask.invoke(StreamTask.java:426)
	at org.apache.flink.runtime.taskmanager.Task.doRun(Task.java:705)
	at org.apache.flink.runtime.taskmanager.Task.run(Task.java:530)
	at java.lang.Thread.run(Thread.java:748)

 

 

3 用无参构造函数注册;下标通过eval()传入 ;这个是正确的!

  stEnv.registerFunction("SplitLine3", new SplitLine3());

        Table result = stEnv.sqlQuery("SELECT name,name2, SplitLine3(name2,'_',4) as name3,qtty," + //下标4
                "case " +
                "     when coalesce(name,'')='' then qtty*0 " +
                "     when name = 'a苹果' then qtty*1 " +
                "     when name = 'b梨' then qtty*2 " +
                "      when name = 'c西瓜' then qtty*3 " +
                "      else qtty*4 " +
                " end as total" +
                " FROM tableA where qtty > 5");



    public static class SplitLine3 extends ScalarFunction {
        private String separator;
        private Integer index;

        public SplitLine3() {
        }

        public String eval(String str, String separator, Integer index) {
            if (StringUtils.isNotBlank(str)) {
                String[] arr = str.split(separator);
                if (arr.length > index) {
                    return arr[index];
                }
            }
            return "";
        }
    }


输出:
Wed Mar 10 10:50:49 CST 2021,提交元素:Order{name='d葡萄', name2='d_葡萄', name3='null', qtty=0, total=null}
Wed Mar 10 10:50:50 CST 2021,提交元素:Order{name='a苹果', name2='a_苹果', name3='null', qtty=8, total=null}
8> Order{name='a苹果', name2='a_苹果', name3='', qtty=8, total=8}
Wed Mar 10 10:50:51 CST 2021,提交元素:Order{name='d葡萄', name2='d_葡萄', name3='null', qtty=2, total=null}
Wed Mar 10 10:50:52 CST 2021,提交元素:Order{name='e火龙果', name2='e_火龙果', name3='null', qtty=9, total=null}
10> Order{name='e火龙果', name2='e_火龙果', name3='', qtty=9, total=36}
Wed Mar 10 10:50:53 CST 2021,提交元素:Order{name='a苹果', name2='a_苹果', name3='null', qtty=7, total=null}
11> Order{name='a苹果', name2='a_苹果', name3='', qtty=7, total=7}
Wed Mar 10 10:50:54 CST 2021,提交元素:Order{name='', name2='', name3='null', qtty=7, total=null}
12> Order{name='', name2='', name3='', qtty=7, total=0}
Wed Mar 10 10:50:55 CST 2021,提交元素:Order{name='e火龙果', name2='e_火龙果', name3='null', qtty=5, total=null}
Wed Mar 10 10:50:56 CST 2021,提交元素:Order{name='', name2='', name3='null', qtty=5, total=null}
 Table result = stEnv.sqlQuery("SELECT name,name2, SplitLine3(name2,'_',0) as name3,qtty," + //下标0


Wed Mar 10 10:52:59 CST 2021,提交元素:Order{name='c西瓜', name2='c_西瓜', name3='null', qtty=2, total=null}
Wed Mar 10 10:53:00 CST 2021,提交元素:Order{name='a苹果', name2='a_苹果', name3='null', qtty=1, total=null}
Wed Mar 10 10:53:01 CST 2021,提交元素:Order{name='d葡萄', name2='d_葡萄', name3='null', qtty=7, total=null}
5> Order{name='d葡萄', name2='d_葡萄', name3='d', qtty=7, total=28}
Wed Mar 10 10:53:02 CST 2021,提交元素:Order{name='b梨', name2='b_梨', name3='null', qtty=4, total=null}
Wed Mar 10 10:53:03 CST 2021,提交元素:Order{name='d葡萄', name2='d_葡萄', name3='null', qtty=5, total=null}
Wed Mar 10 10:53:04 CST 2021,提交元素:Order{name='d葡萄', name2='d_葡萄', name3='null', qtty=3, total=null}
Wed Mar 10 10:53:05 CST 2021,提交元素:Order{name='a苹果', name2='a_苹果', name3='null', qtty=4, total=null}
Wed Mar 10 10:53:06 CST 2021,提交元素:Order{name='a苹果', name2='a_苹果', name3='null', qtty=0, total=null}
Wed Mar 10 10:53:07 CST 2021,提交元素:Order{name='a苹果', name2='a_苹果', name3='null', qtty=2, total=null}
Wed Mar 10 10:53:08 CST 2021,提交元素:Order{name='c西瓜', name2='c_西瓜', name3='null', qtty=2, total=null}
Wed Mar 10 10:53:09 CST 2021,提交元素:Order{name='', name2='', name3='null', qtty=6, total=null}
1> Order{name='', name2='', name3='', qtty=6, total=0}
Wed Mar 10 10:53:10 CST 2021,提交元素:Order{name='c西瓜', name2='c_西瓜', name3='null', qtty=9, total=null}
2> Order{name='c西瓜', name2='c_西瓜', name3='c', qtty=9, total=27}
Wed Mar 10 10:53:11 CST 2021,提交元素:Order{name='a苹果', name2='a_苹果', name3='null', qtty=9, total=null}
3> Order{name='a苹果', name2='a_苹果', name3='a', qtty=9, total=9}

 

4 自定义的函数需要先注册才能用

//SqlValidatorException: No match found for function signature splitLine(<CHARACTER>)
Table result = stEnv.sqlQuery("SELECT name,splitLine(name2) as name2,qtty," +
        "case " +
        "     when coalesce(name,'')='' then qtty*0 " +
        "     when name = 'a苹果' then qtty*1 " +
        "     when name = 'b梨' then qtty*2 " +
        "      when name = 'c西瓜' then qtty*3 " +
        "      else qtty*4 " +
        " end as total" +
        " FROM tableA where qtty > 5");


public String splitLine(String s){
    return s.split("_")[0];
}

 

Flink 1.9.2 ,JDK1.8

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页