It doesn't take effect[image: 内嵌图片 1] 2018-01-31 10:59 GMT+08:00 yongjie zhao <[email protected]>:
> you can try this expression in Superset column editor > ``` > (SUBSTRING(CAST(DIM_INTEGER as VARCHAR) FROM 1 FOR 4) || '-' || > SUBSTRING(CAST(DIM_INTEGER as VARCHAR) FROM 5 FOR 2) || '-' || > SUBSTRING(CAST(DIM_INTEGER as VARCHAR) FROM 7 FOR 2) ) > ``` > > > > > On Wed, Jan 31, 2018 at 9:36 AM, 杨浩 <[email protected]> wrote: > >> int, such as 20180130 >> >> 2018-01-30 21:39 GMT+08:00 yongjie zhao <[email protected]>: >> >> > 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.queryWithSqlMassa >> ge( >> > >>>> > 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()); >> > >>>> > > } >> > >>>> > > } >> > >>>> > >> > >>>> >> > >>> >> > >>> >> > >> >> > > >> > >> > >
