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 2021/06/09 10:04:22 UTC
[GitHub] [superset] EBoisseauSierra opened a new issue #15060: Impossible to use “Calculated columns” in “Custom metrics”, as they are not expanded.
EBoisseauSierra opened a new issue #15060:
URL: https://github.com/apache/superset/issues/15060
When creating a “calculated column” `foo` (defined, e.g. as `col1 + col2`), I can't reuse it in the “custom metric” `bar`, as the generated SQL query uses `foo` literally (rather than expanding into its `col1 + col2` definition).
This fails as `foo` doesn't actually exist as a column in the table I query.
## Expected results
1. I define a **calculated column** (here, as the difference of 2 columns of my PostgreSQL table):
2. I define a **custom metrics** that notably **reuses my previously defined calculated column**:
I want to be able to now build charts using that custom metrics:
This means, running the following query:
```sql
SELECT DATE_TRUNC('hour', timestamp) AS __timestamp,
AVG(ABS(temperature_feelslike - temperature))::numeric(10, 2)/AVG(temperature) AS avg_temp_offset
FROM public.weather_forecast
GROUP BY DATE_TRUNC('hour', timestamp)
LIMIT 50000;
```
### Actual results
However, trying to use the custom metrics in Explore fails:
Indeed, the following query is executed:
```sql
SELECT DATE_TRUNC('hour', timestamp) AS __timestamp,
AVG(ABS(feelslike_offset))::numeric(10, 2)/AVG(temperature) AS avg_temp_offset
FROM public.weather_forecast
GROUP BY DATE_TRUNC('hour', timestamp)
LIMIT 50000;
```
(I.e. the `feelslike_offset` calculated column is not expanded to its definition: `temperature_feelslike - temperature`.)
## Environment
(please complete the following information):
- superset version: `1.1.0`
- python version: `3.8.9`
- node.js version: `node -v`
## Checklist
Make sure to follow these steps before submitting your issue - thank you!
- [ ] I have checked the superset logs for python stacktraces and included it here as text if there are any.
- [x] I have reproduced the issue with at least the latest released version of superset.
- [x] I have checked the issue tracker for the same issue and I haven't found one similar.
--
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.
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] EBoisseauSierra commented on issue #15060: Impossible to use “Calculated columns” in “Custom metrics”, as they are not expanded.
Posted by GitBox <gi...@apache.org>.
EBoisseauSierra commented on issue #15060:
URL: https://github.com/apache/superset/issues/15060#issuecomment-857633337
You're right:
* I naively posted that as a bug, because I assumed that the default/expected behaviour is for calculated columns to be re-usable elsewhere. However, they aren't, for example, made available in the SQL-lab — cf. the discussion on Table vs. Dataset in SIP-68 (#14909). I'm fine with reframing this as a feature request.
* I understand that expansion could lead to some possibly tricky situations. I believe it would make sense to wait for post SIP-68 to develop this feature (if ever), as significant changes are likely to happen in the meantime.
Meanwhile, the already implementable workaround is to manually expand the calculated columns in a custom metric definition — in other words, to base the metric on table's native columns only.
--
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.
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 #15060: Impossible to use “Calculated columns” in “Custom metrics”, as they are not expanded.
Posted by GitBox <gi...@apache.org>.
zhaoyongjie commented on issue #15060:
URL: https://github.com/apache/superset/issues/15060#issuecomment-857682000
currently, Superset's "Calculated columns" is SQL snippet for specific database, instead of defined in BI-side expression (for instance: tableau function, power bi dax).
It is not easy to know whether "Calculated columns" is a reference type, or a primitive type.
--
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.
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] villebro commented on issue #15060: Impossible to use “Calculated columns” in “Custom metrics”, as they are not expanded.
Posted by GitBox <gi...@apache.org>.
villebro commented on issue #15060:
URL: https://github.com/apache/superset/issues/15060#issuecomment-857635421
Another viable solution could be to make a virtual table where the expression is precalculated. This way you get to reference it similar to a regular column without that much trouble, as most modern DBs are able to effectively optimize subqueries so the performance hit should be negligible.
--
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.
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] villebro commented on issue #15060: Impossible to use “Calculated columns” in “Custom metrics”, as they are not expanded.
Posted by GitBox <gi...@apache.org>.
villebro commented on issue #15060:
URL: https://github.com/apache/superset/issues/15060#issuecomment-857569047
Thanks for posting! I'm not sure this is a bug, rather a feature request to expand calculated columns when referenced in other calculated columns. Something to keep in mind: The implementation could get nasty fairly quickly, as it would involve having to parse the expression, make assumptions about which elements are column references (`sqlparse` might do this fairly well, not sure) and match them against existing physical and calculated columns, not to mention making sure there are no circular dependencies. But definitely worth considering.
--
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.
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