You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by GitBox <gi...@apache.org> on 2022/02/15 08:57:32 UTC

[GitHub] [superset] wuyue92tree opened a new issue #18733: query from kylin4 raise exception

wuyue92tree opened a new issue #18733:
URL: https://github.com/apache/superset/issues/18733


   When make a query from kylin4, I got error like blow.
   ```
   Error: From line 2, column 24 to line 2, column 30: Expression 'VIRTUAL_TABLE.VDATE' is not being grouped while executing SQL: "SELECT "DOMAIN" AS "DOMAIN", CAST(FLOOR(CAST("VDATE" AS TIMESTAMP) TO DAY) AS DATE) AS "VDATE", count(*) AS "count" FROM (Select VDATE, DOMAIN from STG.S_HAPROXY_LOG) AS virtual_table WHERE "VDATE" < '2022-02-15 00:00:00.000000' GROUP BY "DOMAIN", CAST(FLOOR(CAST("VDATE" AS TIMESTAMP) TO DAY) AS DATE) ORDER BY "count" DESC LIMIT 10000"
   ```
   
   - superset version: 1.3.2
   - kylin version: 4.0.1
   - spark version: 2.4.7
   
   
   #### How to reproduce the bug
   ![image](https://user-images.githubusercontent.com/7899044/154024559-91199446-58b4-40b7-adb1-28f52500c97a.png)
   
   ```
   # sql generate by superset
   SELECT "DOMAIN" AS "DOMAIN",
          CAST(FLOOR(CAST("VDATE" AS TIMESTAMP) TO DAY) AS DATE) AS "VDATE",
          count(*) AS "count"
   FROM
     (Select VDATE,
             DOMAIN
      from STG.S_HAPROXY_LOG) AS virtual_table
   WHERE "VDATE" < '2022-02-15 00:00:00.000000'
   GROUP BY "DOMAIN",
            CAST(FLOOR(CAST("VDATE" AS TIMESTAMP) TO DAY) AS DATE)
   ORDER BY "count" DESC
   LIMIT 10000;
   ```
   
   ### THINKING
   kylin4 use spark as engine only，but spark not support `FLOOR` function like hive
   
   spark sql
   ![image](https://user-images.githubusercontent.com/7899044/154025966-9f21e393-afcb-45b1-a27c-11da90307481.png)
   
   hsql
   ![image](https://user-images.githubusercontent.com/7899044/154026135-b33b6cf5-bef4-488f-b22c-9e9d2b994cd9.png)
   
   So, any suggestion to fix this? ☺
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

[GitHub] [superset] zhaoyongjie commented on issue #18733: query from kylin4 raise exception

Posted by GitBox <gi...@apache.org>.
zhaoyongjie commented on issue #18733:
URL: https://github.com/apache/superset/issues/18733#issuecomment-1040058716


   Hi @wuyue92tree, I remember that Kylin uses Apache Calcite for SQL Parser and [FLOOR](https://calcite.apache.org/docs/reference.html) is Calcite function. This seems like a backport issue and you probably file in Kylin community.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

[GitHub] [superset] wuyue92tree commented on issue #18733: query from kylin4 raise exception

Posted by GitBox <gi...@apache.org>.
wuyue92tree commented on issue #18733:
URL: https://github.com/apache/superset/issues/18733#issuecomment-1041260823


   > Hi @wuyue92tree, I remember that Kylin uses Apache Calcite for SQL Parser and [FLOOR](https://calcite.apache.org/docs/reference.html) is Calcite function. This seems like a backport issue and you should file it in Kylin community.
   
   Thanks for reply! in fact when doing query, superset generate sql for kylin base on https://github.com/apache/superset/blob/59b811ac5bf6ea64da8e9343fed28676001b9015/superset/db_engine_specs/kylin.py#L30-L40
   
   So, I make a change here and It's work. Wait for a better solution ^_^
   ```
   _time_grain_expressions = {
           None: "{col}",
           "PT1S": "TIMESTAMPADD(SECOND, -(SECOND(CAST({col} AS TIMESTAMP))), CAST({col} AS TIMESTAMP))",
           "PT1M": "TIMESTAMPADD(SECOND, -(MINUTE(CAST({col} AS TIMESTAMP))*60+SECOND(CAST({col} AS TIMESTAMP))), CAST({col} AS TIMESTAMP))",
           "PT1H": "TIMESTAMPADD(SECOND, -(HOUR(CAST({col} AS TIMESTAMP))*60*60+MINUTE(CAST({col} AS TIMESTAMP))*60+SECOND(CAST({col} AS TIMESTAMP))), CAST({col} AS TIMESTAMP))",
           "P1D": "CAST(CAST({col} AS TIMESTAMP) AS DATE)",
           "P1W": "CAST(TIMESTAMPADD(DAY, -(DAYOFWEEK(CAST({col} AS TIMESTAMP))-1), CAST({col} AS TIMESTAMP)) AS DATE)",
           "P1M": "CAST(TIMESTAMPADD(DAY, -(DAYOFMONTH(CAST({col} AS TIMESTAMP))-1), CAST({col} AS TIMESTAMP)) AS DATE)",
           "P0.25Y": "CAST(TIMESTAMPADD(MONTH, (QUARTER(CAST({col} AS TIMESTAMP))-1)*3, CAST((CAST(YEAR(CAST({col} AS TIMESTAMP)) AS VARCHAR) + '-01-01 00:00:00') AS TIMESTAMP)) AS DATE)",
           "P1Y": "CAST(TIMESTAMPADD(DAY, -(DAYOFYEAR(CAST({col} AS TIMESTAMP))-1), CAST({col} AS TIMESTAMP)) AS DATE)",
   }
   ```
   make spark support floor function like hive is hard for me, orz~  
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

[GitHub] [superset] zhaoyongjie commented on issue #18733: query from kylin4 raise exception

Posted by GitBox <gi...@apache.org>.
zhaoyongjie commented on issue #18733:
URL: https://github.com/apache/superset/issues/18733#issuecomment-1040058716


   Hi @wuyue92tree, I remember that Kylin uses Apache Calcite for SQL Parser and [FLOOR](https://calcite.apache.org/docs/reference.html) is Calcite function. This seems like a backport issue and you probably file in Kylin community.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

[GitHub] [superset] zhaoyongjie edited a comment on issue #18733: query from kylin4 raise exception

Posted by GitBox <gi...@apache.org>.
zhaoyongjie edited a comment on issue #18733:
URL: https://github.com/apache/superset/issues/18733#issuecomment-1040058716


   Hi @wuyue92tree, I remember that Kylin uses Apache Calcite for SQL Parser and [FLOOR](https://calcite.apache.org/docs/reference.html) is Calcite function. This seems like a backport issue and you probably file it in Kylin community.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

[GitHub] [superset] zhaoyongjie edited a comment on issue #18733: query from kylin4 raise exception

Posted by GitBox <gi...@apache.org>.
zhaoyongjie edited a comment on issue #18733:
URL: https://github.com/apache/superset/issues/18733#issuecomment-1040058716


   Hi @wuyue92tree, I remember that Kylin uses Apache Calcite for SQL Parser and [FLOOR](https://calcite.apache.org/docs/reference.html) is Calcite function. This seems like a backport issue and you should file it in Kylin community.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

[GitHub] [superset] zhaoyongjie edited a comment on issue #18733: query from kylin4 raise exception

Posted by GitBox <gi...@apache.org>.
zhaoyongjie edited a comment on issue #18733:
URL: https://github.com/apache/superset/issues/18733#issuecomment-1040058716






-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org