You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by dawangli <da...@163.com> on 2020/10/27 03:35:18 UTC
how to register udfa to calcite?
I hive the following code，it just valid a simple group by sql with a udaf, but i doesn't work, it seems like the calcite cannot identify the aggr udaf
,code and error displayed below
code
String sql = "SELECT\n" +
" dim,\n" +
" aggr(val)\n" +
"FROM\n" +
" tb\n" +
"GROUP BY\n" +
" dim";
CalciteSchema rootSchema = CalciteSchema
.createRootSchema(false, false);
rootSchema.add("tb", new AbstractTable() { //note: add a table
@Override
public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
RelDataTypeFactory.Builder builder = typeFactory.builder();
builder.add("dim", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.VARCHAR));
builder.add("val", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.BIGINT));
return builder.build();
}
});
SqlParser.ConfigBuilder builder = SqlParser.configBuilder();
builder.setQuotedCasing(Casing.TO_UPPER);
builder.setUnquotedCasing(Casing.TO_UPPER);
builder.setCaseSensitive(false);
builder.setLex(Lex.JAVA);
SqlStdOperatorTable sqlStdOperatorTable = SqlStdOperatorTable.instance();
sqlStdOperatorTable.register(new SqlFunction(
new SqlIdentifier("aggr", SqlParserPos.ZERO),
ReturnTypes.cascade(ReturnTypes.explicit(SqlTypeName.ANY), SqlTypeTransforms.TO_NULLABLE),
null,
OperandTypes.family(SqlTypeFamily.ANY),
Lists.newArrayList(new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT).createSqlType(SqlTypeName.ANY)),
SqlFunctionCategory.USER_DEFINED_FUNCTION));
final FrameworkConfig config = Frameworks.newConfigBuilder()
.defaultSchema(rootSchema.plus())
.parserConfig(builder.build())
.operatorTable(sqlStdOperatorTable)
.build();
Planner planner = Frameworks.getPlanner(config);
SqlNode originSqlNode = planner.parse(sql);
SqlNode sqlNode = planner.validate(originSqlNode);
error:
Exception in thread "main" org.apache.calcite.tools.ValidationException: org.apache.calcite.runtime.CalciteContextException: From line 3, column 8 to line 3, column 10: Expression 'val' is not being grouped
at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:217)
at com.kuaishou.dp.stream.metadata.sql.Test.main(Test.java:422)
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 3, column 8 to line 3, column 10: Expression 'val' is not being grouped
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:834)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:819)
at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4867)
at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:113)
at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:40)
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:317)
at org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:123)
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:868)
at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:212)
at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:40)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
at org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr(AggregatingSelectScope.java:228)
at org.apache.calcite.sql.validate.AggregatingSelectScope.validateExpr(AggregatingSelectScope.java:237)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr(SqlValidatorImpl.java:4162)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4136)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3392)
at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1005)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:965)
at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:216)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:940)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:647)
at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:215)
... 1 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Expression 'val' is not being grouped
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572)
... 25 more
Re:Re: Re: how to register udfa to calcite?
Posted by dawangli <da...@163.com>.
thanks!!! it works
At 2020-10-27 15:30:57, "Danny Chan" <da...@apache.org> wrote:
>Your "aggr" needs to implement the interface SqlAggFunction
><https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java>
>.
>
>dawangli <da...@163.com> 于2020年10月27日周二 下午3:24写道：
>
>> sorry, i means udaf
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> At 2020-10-27 15:21:36, "Danny Chan" <da...@apache.org> wrote:
>> >Do you mean DUTF or UDAF ?
>> >
>> >dawangli <da...@163.com> 于2020年10月27日周二 上午11:35写道：
>> >
>> >> I hive the following code，it just valid a simple group by sql with a
>> udaf,
>> >> but i doesn't work, it seems like the calcite cannot identify the aggr
>> udaf
>> >> ,code and error displayed below
>> >> code
>> >> String sql = "SELECT\n" +
>> >> " dim,\n" +
>> >> " aggr(val)\n" +
>> >> "FROM\n" +
>> >> " tb\n" +
>> >> "GROUP BY\n" +
>> >> " dim";
>> >>
>> >> CalciteSchema rootSchema = CalciteSchema
>> >> .createRootSchema(false, false);
>> >>
>> >> rootSchema.add("tb", new AbstractTable() { //note: add a table
>> >> @Override
>> >> public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
>> >> RelDataTypeFactory.Builder builder = typeFactory.builder();
>> >>
>> >> builder.add("dim", new BasicSqlType(new RelDataTypeSystemImpl() {},
>> >> SqlTypeName.VARCHAR));
>> >> builder.add("val", new BasicSqlType(new RelDataTypeSystemImpl() {},
>> >> SqlTypeName.BIGINT));
>> >> return builder.build();
>> >> }
>> >> });
>> >>
>> >> SqlParser.ConfigBuilder builder = SqlParser.configBuilder();
>> >> builder.setQuotedCasing(Casing.TO_UPPER);
>> >> builder.setUnquotedCasing(Casing.TO_UPPER);
>> >> builder.setCaseSensitive(false);
>> >> builder.setLex(Lex.JAVA);
>> >> SqlStdOperatorTable sqlStdOperatorTable =
>> SqlStdOperatorTable.instance();
>> >>
>> >> sqlStdOperatorTable.register(new SqlFunction(
>> >> new SqlIdentifier("aggr", SqlParserPos.ZERO),
>> >> ReturnTypes.cascade(ReturnTypes.explicit(SqlTypeName.ANY),
>> >> SqlTypeTransforms.TO_NULLABLE),
>> >> null,
>> >> OperandTypes.family(SqlTypeFamily.ANY),
>> >> Lists.newArrayList(new
>> >>
>> SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT).createSqlType(SqlTypeName.ANY)),
>> >> SqlFunctionCategory.USER_DEFINED_FUNCTION));
>> >>
>> >> final FrameworkConfig config = Frameworks.newConfigBuilder()
>> >> .defaultSchema(rootSchema.plus())
>> >> .parserConfig(builder.build())
>> >> .operatorTable(sqlStdOperatorTable)
>> >> .build();
>> >> Planner planner = Frameworks.getPlanner(config);
>> >>
>> >> SqlNode originSqlNode = planner.parse(sql);
>> >>
>> >> SqlNode sqlNode = planner.validate(originSqlNode);
>> >>
>> >>
>> >> error:
>> >>
>> >>
>> >> Exception in thread "main" org.apache.calcite.tools.ValidationException:
>> >> org.apache.calcite.runtime.CalciteContextException: From line 3, column
>> 8
>> >> to line 3, column 10: Expression 'val' is not being grouped
>> >> at
>> >> org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:217)
>> >> at com.kuaishou.dp.stream.metadata.sql.Test.main(Test.java:422)
>> >> Caused by: org.apache.calcite.runtime.CalciteContextException: From line
>> >> 3, column 8 to line 3, column 10: Expression 'val' is not being grouped
>> >> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
>> >> Method)
>> >> at
>> >>
>> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
>> >> at
>> >>
>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>> >> at
>> java.lang.reflect.Constructor.newInstance(Constructor.java:423)
>> >> at
>> >>
>> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
>> >> at
>> >> org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:834)
>> >> at
>> >> org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:819)
>> >> at
>> >>
>> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4867)
>> >> at
>> >> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:113)
>> >> at
>> >> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:40)
>> >> at
>> >> org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:317)
>> >> at
>> >>
>> org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:123)
>> >> at
>> >> org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:868)
>> >> at
>> >> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:212)
>> >> at
>> >> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:40)
>> >> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
>> >> at
>> >>
>> org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr(AggregatingSelectScope.java:228)
>> >> at
>> >>
>> org.apache.calcite.sql.validate.AggregatingSelectScope.validateExpr(AggregatingSelectScope.java:237)
>> >> at
>> >>
>> org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr(SqlValidatorImpl.java:4162)
>> >> at
>> >>
>> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4136)
>> >> at
>> >>
>> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3392)
>> >> at
>> >>
>> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
>> >> at
>> >>
>> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
>> >> at
>> >>
>> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1005)
>> >> at
>> >>
>> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:965)
>> >> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:216)
>> >> at
>> >>
>> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:940)
>> >> at
>> >>
>> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:647)
>> >> at
>> >> org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:215)
>> >> ... 1 more
>> >> Caused by: org.apache.calcite.sql.validate.SqlValidatorException:
>> >> Expression 'val' is not being grouped
>> >> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
>> >> Method)
>> >> at
>> >>
>> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
>> >> at
>> >>
>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>> >> at
>> java.lang.reflect.Constructor.newInstance(Constructor.java:423)
>> >> at
>> >>
>> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
>> >> at
>> >> org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572)
>> >> ... 25 more
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>>
Re: Re: how to register udfa to calcite?
Posted by Danny Chan <da...@apache.org>.
Your "aggr" needs to implement the interface SqlAggFunction
<https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java>
.
dawangli <da...@163.com> 于2020年10月27日周二 下午3:24写道：
> sorry, i means udaf
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> At 2020-10-27 15:21:36, "Danny Chan" <da...@apache.org> wrote:
> >Do you mean DUTF or UDAF ?
> >
> >dawangli <da...@163.com> 于2020年10月27日周二 上午11:35写道：
> >
> >> I hive the following code，it just valid a simple group by sql with a
> udaf,
> >> but i doesn't work, it seems like the calcite cannot identify the aggr
> udaf
> >> ,code and error displayed below
> >> code
> >> String sql = "SELECT\n" +
> >> " dim,\n" +
> >> " aggr(val)\n" +
> >> "FROM\n" +
> >> " tb\n" +
> >> "GROUP BY\n" +
> >> " dim";
> >>
> >> CalciteSchema rootSchema = CalciteSchema
> >> .createRootSchema(false, false);
> >>
> >> rootSchema.add("tb", new AbstractTable() { //note: add a table
> >> @Override
> >> public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
> >> RelDataTypeFactory.Builder builder = typeFactory.builder();
> >>
> >> builder.add("dim", new BasicSqlType(new RelDataTypeSystemImpl() {},
> >> SqlTypeName.VARCHAR));
> >> builder.add("val", new BasicSqlType(new RelDataTypeSystemImpl() {},
> >> SqlTypeName.BIGINT));
> >> return builder.build();
> >> }
> >> });
> >>
> >> SqlParser.ConfigBuilder builder = SqlParser.configBuilder();
> >> builder.setQuotedCasing(Casing.TO_UPPER);
> >> builder.setUnquotedCasing(Casing.TO_UPPER);
> >> builder.setCaseSensitive(false);
> >> builder.setLex(Lex.JAVA);
> >> SqlStdOperatorTable sqlStdOperatorTable =
> SqlStdOperatorTable.instance();
> >>
> >> sqlStdOperatorTable.register(new SqlFunction(
> >> new SqlIdentifier("aggr", SqlParserPos.ZERO),
> >> ReturnTypes.cascade(ReturnTypes.explicit(SqlTypeName.ANY),
> >> SqlTypeTransforms.TO_NULLABLE),
> >> null,
> >> OperandTypes.family(SqlTypeFamily.ANY),
> >> Lists.newArrayList(new
> >>
> SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT).createSqlType(SqlTypeName.ANY)),
> >> SqlFunctionCategory.USER_DEFINED_FUNCTION));
> >>
> >> final FrameworkConfig config = Frameworks.newConfigBuilder()
> >> .defaultSchema(rootSchema.plus())
> >> .parserConfig(builder.build())
> >> .operatorTable(sqlStdOperatorTable)
> >> .build();
> >> Planner planner = Frameworks.getPlanner(config);
> >>
> >> SqlNode originSqlNode = planner.parse(sql);
> >>
> >> SqlNode sqlNode = planner.validate(originSqlNode);
> >>
> >>
> >> error:
> >>
> >>
> >> Exception in thread "main" org.apache.calcite.tools.ValidationException:
> >> org.apache.calcite.runtime.CalciteContextException: From line 3, column
> 8
> >> to line 3, column 10: Expression 'val' is not being grouped
> >> at
> >> org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:217)
> >> at com.kuaishou.dp.stream.metadata.sql.Test.main(Test.java:422)
> >> Caused by: org.apache.calcite.runtime.CalciteContextException: From line
> >> 3, column 8 to line 3, column 10: Expression 'val' is not being grouped
> >> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> >> Method)
> >> at
> >>
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> >> at
> >>
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> >> at
> java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> >> at
> >>
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
> >> at
> >> org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:834)
> >> at
> >> org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:819)
> >> at
> >>
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4867)
> >> at
> >> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:113)
> >> at
> >> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:40)
> >> at
> >> org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:317)
> >> at
> >>
> org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:123)
> >> at
> >> org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:868)
> >> at
> >> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:212)
> >> at
> >> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:40)
> >> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
> >> at
> >>
> org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr(AggregatingSelectScope.java:228)
> >> at
> >>
> org.apache.calcite.sql.validate.AggregatingSelectScope.validateExpr(AggregatingSelectScope.java:237)
> >> at
> >>
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr(SqlValidatorImpl.java:4162)
> >> at
> >>
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4136)
> >> at
> >>
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3392)
> >> at
> >>
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
> >> at
> >>
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
> >> at
> >>
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1005)
> >> at
> >>
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:965)
> >> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:216)
> >> at
> >>
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:940)
> >> at
> >>
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:647)
> >> at
> >> org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:215)
> >> ... 1 more
> >> Caused by: org.apache.calcite.sql.validate.SqlValidatorException:
> >> Expression 'val' is not being grouped
> >> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> >> Method)
> >> at
> >>
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> >> at
> >>
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> >> at
> java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> >> at
> >>
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
> >> at
> >> org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572)
> >> ... 25 more
> >>
> >>
> >>
> >>
> >>
> >>
>
Re:Re: how to register udfa to calcite?
Posted by dawangli <da...@163.com>.
sorry, i means udaf
At 2020-10-27 15:21:36, "Danny Chan" <da...@apache.org> wrote:
>Do you mean DUTF or UDAF ?
>
>dawangli <da...@163.com> 于2020年10月27日周二 上午11:35写道：
>
>> I hive the following code，it just valid a simple group by sql with a udaf,
>> but i doesn't work, it seems like the calcite cannot identify the aggr udaf
>> ,code and error displayed below
>> code
>> String sql = "SELECT\n" +
>> " dim,\n" +
>> " aggr(val)\n" +
>> "FROM\n" +
>> " tb\n" +
>> "GROUP BY\n" +
>> " dim";
>>
>> CalciteSchema rootSchema = CalciteSchema
>> .createRootSchema(false, false);
>>
>> rootSchema.add("tb", new AbstractTable() { //note: add a table
>> @Override
>> public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
>> RelDataTypeFactory.Builder builder = typeFactory.builder();
>>
>> builder.add("dim", new BasicSqlType(new RelDataTypeSystemImpl() {},
>> SqlTypeName.VARCHAR));
>> builder.add("val", new BasicSqlType(new RelDataTypeSystemImpl() {},
>> SqlTypeName.BIGINT));
>> return builder.build();
>> }
>> });
>>
>> SqlParser.ConfigBuilder builder = SqlParser.configBuilder();
>> builder.setQuotedCasing(Casing.TO_UPPER);
>> builder.setUnquotedCasing(Casing.TO_UPPER);
>> builder.setCaseSensitive(false);
>> builder.setLex(Lex.JAVA);
>> SqlStdOperatorTable sqlStdOperatorTable = SqlStdOperatorTable.instance();
>>
>> sqlStdOperatorTable.register(new SqlFunction(
>> new SqlIdentifier("aggr", SqlParserPos.ZERO),
>> ReturnTypes.cascade(ReturnTypes.explicit(SqlTypeName.ANY),
>> SqlTypeTransforms.TO_NULLABLE),
>> null,
>> OperandTypes.family(SqlTypeFamily.ANY),
>> Lists.newArrayList(new
>> SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT).createSqlType(SqlTypeName.ANY)),
>> SqlFunctionCategory.USER_DEFINED_FUNCTION));
>>
>> final FrameworkConfig config = Frameworks.newConfigBuilder()
>> .defaultSchema(rootSchema.plus())
>> .parserConfig(builder.build())
>> .operatorTable(sqlStdOperatorTable)
>> .build();
>> Planner planner = Frameworks.getPlanner(config);
>>
>> SqlNode originSqlNode = planner.parse(sql);
>>
>> SqlNode sqlNode = planner.validate(originSqlNode);
>>
>>
>> error:
>>
>>
>> Exception in thread "main" org.apache.calcite.tools.ValidationException:
>> org.apache.calcite.runtime.CalciteContextException: From line 3, column 8
>> to line 3, column 10: Expression 'val' is not being grouped
>> at
>> org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:217)
>> at com.kuaishou.dp.stream.metadata.sql.Test.main(Test.java:422)
>> Caused by: org.apache.calcite.runtime.CalciteContextException: From line
>> 3, column 8 to line 3, column 10: Expression 'val' is not being grouped
>> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
>> Method)
>> at
>> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
>> at
>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
>> at
>> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
>> at
>> org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:834)
>> at
>> org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:819)
>> at
>> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4867)
>> at
>> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:113)
>> at
>> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:40)
>> at
>> org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:317)
>> at
>> org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:123)
>> at
>> org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:868)
>> at
>> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:212)
>> at
>> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:40)
>> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
>> at
>> org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr(AggregatingSelectScope.java:228)
>> at
>> org.apache.calcite.sql.validate.AggregatingSelectScope.validateExpr(AggregatingSelectScope.java:237)
>> at
>> org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr(SqlValidatorImpl.java:4162)
>> at
>> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4136)
>> at
>> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3392)
>> at
>> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
>> at
>> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
>> at
>> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1005)
>> at
>> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:965)
>> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:216)
>> at
>> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:940)
>> at
>> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:647)
>> at
>> org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:215)
>> ... 1 more
>> Caused by: org.apache.calcite.sql.validate.SqlValidatorException:
>> Expression 'val' is not being grouped
>> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
>> Method)
>> at
>> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
>> at
>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
>> at
>> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
>> at
>> org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572)
>> ... 25 more
>>
>>
>>
>>
>>
>>
Re: how to register udfa to calcite?
Posted by Danny Chan <da...@apache.org>.
Do you mean DUTF or UDAF ?
dawangli <da...@163.com> 于2020年10月27日周二 上午11:35写道：
> I hive the following code，it just valid a simple group by sql with a udaf,
> but i doesn't work, it seems like the calcite cannot identify the aggr udaf
> ,code and error displayed below
> code
> String sql = "SELECT\n" +
> " dim,\n" +
> " aggr(val)\n" +
> "FROM\n" +
> " tb\n" +
> "GROUP BY\n" +
> " dim";
>
> CalciteSchema rootSchema = CalciteSchema
> .createRootSchema(false, false);
>
> rootSchema.add("tb", new AbstractTable() { //note: add a table
> @Override
> public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
> RelDataTypeFactory.Builder builder = typeFactory.builder();
>
> builder.add("dim", new BasicSqlType(new RelDataTypeSystemImpl() {},
> SqlTypeName.VARCHAR));
> builder.add("val", new BasicSqlType(new RelDataTypeSystemImpl() {},
> SqlTypeName.BIGINT));
> return builder.build();
> }
> });
>
> SqlParser.ConfigBuilder builder = SqlParser.configBuilder();
> builder.setQuotedCasing(Casing.TO_UPPER);
> builder.setUnquotedCasing(Casing.TO_UPPER);
> builder.setCaseSensitive(false);
> builder.setLex(Lex.JAVA);
> SqlStdOperatorTable sqlStdOperatorTable = SqlStdOperatorTable.instance();
>
> sqlStdOperatorTable.register(new SqlFunction(
> new SqlIdentifier("aggr", SqlParserPos.ZERO),
> ReturnTypes.cascade(ReturnTypes.explicit(SqlTypeName.ANY),
> SqlTypeTransforms.TO_NULLABLE),
> null,
> OperandTypes.family(SqlTypeFamily.ANY),
> Lists.newArrayList(new
> SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT).createSqlType(SqlTypeName.ANY)),
> SqlFunctionCategory.USER_DEFINED_FUNCTION));
>
> final FrameworkConfig config = Frameworks.newConfigBuilder()
> .defaultSchema(rootSchema.plus())
> .parserConfig(builder.build())
> .operatorTable(sqlStdOperatorTable)
> .build();
> Planner planner = Frameworks.getPlanner(config);
>
> SqlNode originSqlNode = planner.parse(sql);
>
> SqlNode sqlNode = planner.validate(originSqlNode);
>
>
> error:
>
>
> Exception in thread "main" org.apache.calcite.tools.ValidationException:
> org.apache.calcite.runtime.CalciteContextException: From line 3, column 8
> to line 3, column 10: Expression 'val' is not being grouped
> at
> org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:217)
> at com.kuaishou.dp.stream.metadata.sql.Test.main(Test.java:422)
> Caused by: org.apache.calcite.runtime.CalciteContextException: From line
> 3, column 8 to line 3, column 10: Expression 'val' is not being grouped
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> Method)
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
> at
> org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:834)
> at
> org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:819)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4867)
> at
> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:113)
> at
> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:40)
> at
> org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:317)
> at
> org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:123)
> at
> org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:868)
> at
> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:212)
> at
> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:40)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
> at
> org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr(AggregatingSelectScope.java:228)
> at
> org.apache.calcite.sql.validate.AggregatingSelectScope.validateExpr(AggregatingSelectScope.java:237)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr(SqlValidatorImpl.java:4162)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4136)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3392)
> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1005)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:965)
> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:216)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:940)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:647)
> at
> org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:215)
> ... 1 more
> Caused by: org.apache.calcite.sql.validate.SqlValidatorException:
> Expression 'val' is not being grouped
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> Method)
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
> at
> org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572)
> ... 25 more
>
>
>
>
>
>