If the format of "DATE" is YYYYMMDD，and superset changes it to format YYYY-MM-DD, so we should use the UDF [image: 内嵌图片 1]
2018-01-30 16:00 GMT+08:00 杨浩 <[email protected]>: > Or the problem may be that how should we write UDF to support function in > filter position > > 2018-01-30 15:45 GMT+08:00 杨浩 <[email protected]>: > >> Do you mean sqllab in superset? We have solved relevant problems, except >> for the exception supplied by me >> >> 2018-01-30 15:38 GMT+08:00 yongjie zhao <[email protected]>: >> >>> Are you in sqllab write this SQL? >>> >>> On Tue, Jan 30, 2018 at 2:57 PM, 杨浩 <[email protected]> wrote: >>> >>> > kylin developers >>> > We have used superset as BI tool. Superset uses to_date to represent >>> > time, and we add the to_date udf in our env. A query may be like >>> select *** >>> > from table_1 where 'DATE' >= TO_DATE('2017-12-31 00:00:00', >>> 'yyyy-MM-dd'). >>> > The executing result is right, but the query will not use kylin >>> optimize, >>> > because some error has happened , and every query will scan all hbase >>> > table. How should we add the udf ? >>> > >>> > 2018-01-30 13:10:03,400 WARN [Query >>> > >> f029cbac-2aba-456c-b857-f65c8661e39c-90] >>> > >> filter.BuiltInFunctionTupleFilter:143 : Reflection failed for >>> > methodParams. >>> > > >>> > > java.lang.NullPointerException >>> > > >>> > > at >>> > >> org.apache.kylin.metadata.filter.BuiltInFunctionTupleFilter. >>> addChild( >>> > BuiltInFunctionTupleFilter.java:136) >>> > > >>> > > at >>> > >> org.apache.kylin.metadata.filter.TupleFilterSerializer.deserialize( >>> > TupleFilterSerializer.java:146) >>> > > >>> > > at >>> > >> org.apache.kylin.storage.gtrecord.CubeSegmentScanner.< >>> > init>(CubeSegmentScanner.java:65) >>> > > >>> > > at >>> > >> org.apache.kylin.storage.gtrecord.GTCubeStorageQueryBase.search( >>> > GTCubeStorageQueryBase.java:93) >>> > > >>> > > at >>> > >> org.apache.kylin.query.enumerator.OLAPEnumerator. >>> > queryStorage(OLAPEnumerator.java:117) >>> > > >>> > > at >>> > >> org.apache.kylin.query.enumerator.OLAPEnumerator. >>> > moveNext(OLAPEnumerator.java:64) >>> > > >>> > > at Baz$1$1.moveNext(Unknown Source) >>> > > >>> > > at >>> > >> org.apache.calcite.linq4j.EnumerableDefaults.groupBy_( >>> > EnumerableDefaults.java:826) >>> > > >>> > > at >>> > >> org.apache.calcite.linq4j.EnumerableDefaults.groupBy( >>> > EnumerableDefaults.java:761) >>> > > >>> > > at >>> > >> org.apache.calcite.linq4j.DefaultEnumerable.groupBy( >>> > DefaultEnumerable.java:302) >>> > > >>> > > at Baz.bind(Unknown Source) >>> > > >>> > > at >>> > >> org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable( >>> > CalcitePrepare.java:335) >>> > > >>> > > at >>> > >> org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable( >>> > CalciteConnectionImpl.java:294) >>> > > >>> > > at >>> > >> org.apache.calcite.jdbc.CalciteMetaImpl._createIterable( >>> > CalciteMetaImpl.java:559) >>> > > >>> > > at >>> > >> org.apache.calcite.jdbc.CalciteMetaImpl.createIterable( >>> > CalciteMetaImpl.java:550) >>> > > >>> > > at >>> > >> org.apache.calcite.avatica.AvaticaResultSet.execute( >>> > AvaticaResultSet.java:204) >>> > > >>> > > at >>> > >> org.apache.calcite.jdbc.CalciteResultSet.execute( >>> > CalciteResultSet.java:67) >>> > > >>> > > at >>> > >> org.apache.calcite.jdbc.CalciteResultSet.execute( >>> > CalciteResultSet.java:44) >>> > > >>> > > at >>> > >> org.apache.calcite.avatica.AvaticaConnection$1.execute( >>> > AvaticaConnection.java:630) >>> > > >>> > > at >>> > >> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute( >>> > CalciteMetaImpl.java:607) >>> > > >>> > > at >>> > >> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecu >>> teInternal( >>> > AvaticaConnection.java:638) >>> > > >>> > > at >>> > >> org.apache.calcite.avatica.AvaticaStatement.executeInternal( >>> > AvaticaStatement.java:149) >>> > > >>> > > at >>> > >> org.apache.calcite.avatica.AvaticaStatement.executeQuery( >>> > AvaticaStatement.java:218) >>> > > >>> > > at >>> > >> org.apache.kylin.rest.service.QueryService.execute( >>> > QueryService.java:845) >>> > > >>> > > at >>> > >> org.apache.kylin.rest.service.QueryService.queryWithSqlMassage( >>> > QueryService.java:572) >>> > > >>> > > at >>> > >> org.apache.kylin.rest.service.QueryService.query(QueryServic >>> e.java:181) >>> > > >>> > > at >>> > >> org.apache.kylin.rest.service.QueryService.doQueryWithCache( >>> > QueryService.java:428) >>> > > >>> > > >>> > ToDateUDF likes this, and I have adding it on KylinConfigBase.getUDFs >>> > >>> > >>> > public class ToDateUDF { >>> > > private static final Logger logger = >>> > > LoggerFactory.getLogger(ToDateUDF.class); >>> > > public String eval(String sourceDateStr, String >>> sourceDateFormat) { >>> > > sourceDateStr = sourceDateStr.replaceAll("'", "").trim(); >>> > > sourceDateFormat = sourceDateFormat.replaceAll("'", >>> "").trim(); >>> > > try { >>> > > SimpleDateFormat dateFormat = new >>> > > SimpleDateFormat(sourceDateFormat); >>> > > long ts = dateFormat.parse(sourceDateStr).getTime(); >>> > > return getFormatTime(ts, "yyyyMMdd"); >>> > > } catch (ParseException e) { >>> > > logger.error("parse error", e); >>> > > logger.error("sourceDateStr:{},sourceDateFormat:{}", >>> > > sourceDateStr, sourceDateFormat); >>> > > return ""; >>> > > } >>> > > } >>> > > public static String getFormatTime(long timeStamp, String >>> format) { >>> > > if (StringUtils.isBlank(format)) { >>> > > format = "yyyyMMdd"; >>> > > } >>> > > Calendar cal = Calendar.getInstance(); >>> > > SimpleDateFormat formatter = new SimpleDateFormat(format); >>> > > cal.setTimeInMillis(timeStamp); >>> > > return formatter.format(cal.getTime()); >>> > > } >>> > > } >>> > >>> >> >> >
