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, 杨浩 <yangha...@gmail.com> wrote:
> int, such as 20180130
>
> 2018-01-30 21:39 GMT+08:00 yongjie zhao <yongjie.z...@gmail.com>:
>
> > What is your type of dimension, date? varchar?
> >
> > On Tue, Jan 30, 2018 at 6:17 PM, 杨浩 <yangha...@gmail.com> 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 杨浩 <yangha...@gmail.com>:
> > >
> > >> 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 杨浩 <yangha...@gmail.com>:
> > >>
> > >>> 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 <yongjie.z...@gmail.com>:
> > >>>
> > >>>> Are you in sqllab write this SQL?
> > >>>>
> > >>>> On Tue, Jan 30, 2018 at 2:57 PM, 杨浩 <yangha...@gmail.com> 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());
> > >>>> > > }
> > >>>> > > }
> > >>>> >
> > >>>>
> > >>>
> > >>>
> > >>
> > >
> >
>
