What is your type of dimension, date? varchar? On Tue, Jan 30, 2018 at 6:17 PM, 杨浩 <[email protected]> wrote:
> 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.deser >>>> ialize( >>>> > 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.enum >>>> erable( >>>> > 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()); >>>> > > } >>>> > > } >>>> > >>>> >>> >>> >> >
