feat(explore): adhoc column expressions [ID-3] #17379

villebro · 2021-11-09T14:08:35Z

SUMMARY

Adds support for adhoc column expressions, similar to how adhoc metrics work. See video for example where gender that features values "boy" and "girl" is replaced with a case-statement that maps these values to "male" and "female":

adhoc_column.mp4

The resulting query:

Part of the work is done in apache-superset/superset-ui#1342 (featured in the included bump of superset-ui).

TESTING INSTRUCTIONS

Enable ENABLE_EXPLORE_DRAG_AND_DROP, UX_BETA, ENABLE_DND_WITH_CLICK_UX feature flags
Create a new chart
click on a column select component
Go to "Custom SQL" and create an expression

ADDITIONAL INFORMATION

Has associated issue:
Required feature flags:
Changes UI
Includes DB Migration (follow approval process in SIP-59)
- Migration is atomic, supports rollback & is backwards-compatible
- Confirm DB migration upgrade and downgrade tested
- Runtime estimates and downtime expectations provided
Introduces new feature or API
Removes existing feature or API

…he#16531)

…6570)

…#16412) * Support Jinja template functions in async queries * Pylint * Add tests for async tasks * Remove redundant has_request_context check

* feat: Add Aurora Data API engine spec * Fix lint

…#16548) * refactor sql_json view endpoint: encapsulate ctas parameters * fix failed tests * fix failed tests and ci issues

…pache#16595)

* fix:fix get permission function * feat: add cross filter chart in charts gallery under FF

villebro

First pass, just one minor nit

...rset-frontend/src/explore/components/controls/DndColumnSelectControl/ColumnSelectPopover.tsx

kgabryje · 2021-11-12T14:04:23Z

/testenv up FEATURE_UX_BETA=true FEATURE_ENABLE_EXPLORE_DRAG_AND_DROP=true FEATURE_ENABLE_DND_WITH_CLICK_UX=true

github-actions · 2021-11-12T14:08:54Z

@kgabryje Ephemeral environment spinning up at http://35.86.253.253:8080. Credentials are admin/admin. Please allow several minutes for bootstrapping and startup.

geido

Code LGTM! Going to do manual testing

...rset-frontend/src/explore/components/controls/DndColumnSelectControl/ColumnSelectPopover.tsx

jinghua-qa · 2021-11-14T07:29:26Z

Do we need to re-spin up the test env? Saw the test env is not spin up at the latest commit

kgabryje · 2021-11-14T07:54:55Z

/testenv up FEATURE_UX_BETA=true FEATURE_ENABLE_EXPLORE_DRAG_AND_DROP=true FEATURE_ENABLE_DND_WITH_CLICK_UX=true

kgabryje · 2021-11-14T07:55:35Z

Do we need to re-spin up the test env? Saw the test env is not spin up at the latest commit

Good point! The latest changes shouldn't affect the functionality in any way, but better to be safe than sorry

github-actions · 2021-11-14T07:59:18Z

@kgabryje Ephemeral environment spinning up at http://52.26.96.154:8080. Credentials are admin/admin. Please allow several minutes for bootstrapping and startup.

rumbin · 2021-11-14T11:15:34Z

Had a quick look at the ephemeral env.
The ad-hoc column expressions are such a beauty!

Thanks a lot for implementing it. I am eagerly awaiting it to be merged and released :-)

jinghua-qa

LGTM

geido

LGTM! Awesome work

github-actions · 2021-11-15T10:50:30Z

Ephemeral environment shutdown and build artifacts deleted.

sadpandajoe · 2021-11-17T00:37:10Z

🏷️ 2021.46

* add support for adhoc columns to api and sqla model * fix some types * fix duplicates in column names * fix more lint * fix schema and dedup * clean up some logic * first pass at fixing viz.py * Add frontend support for adhoc columns * Add title edit * Fix showing custom title * Use column name as default value in sql editor * fix: Adds a loading message when needed in the Select component (#16531) * fix(tests): make parquet select deterministic with order by (#16570) * bump emotion to help with cache clobbering (#16559) * fix: Support Jinja template functions in global async queries (#16412) * Support Jinja template functions in async queries * Pylint * Add tests for async tasks * Remove redundant has_request_context check * fix: impersonate user label/tooltip (#16573) * docs: update for small typos (#16568) * feat: Add Aurora Data API engine spec (#16535) * feat: Add Aurora Data API engine spec * Fix lint * refactor: sql_json view endpoint: encapsulate ctas parameters (#16548) * refactor sql_json view endpoint: encapsulate ctas parameters * fix failed tests * fix failed tests and ci issues * refactor sql_json view endpoint: separate concern into ad hod method (#16595) * feat: Experimental cross-filter plugins (#16594) * fix:fix get permission function * feat: add cross filter chart in charts gallery under FF * chore(deps): bump superset-ui to 0.18.2 (#16601) * update type guard references * fix imports * update series_columns schema * Add changes that got lost in rebase * Use current columns name or expression as sql editor init value * add integration test and do minor fixes * Bump superset-ui * fix linting issue * bump superset-ui to 0.18.22 * resolve merge conflict * lint * fix select filter infinite loop * bump superset-ui to 0.18.23 * Fix auto setting column popover title * Enable adhoc columns only if UX_BETA enabled * put back removed test * Move popover height and width to constants * Refactor big ternary expression Co-authored-by: Kamil Gabryjelski <kamil.gabryjelski@gmail.com> Co-authored-by: Michael S. Molina <70410625+michael-s-molina@users.noreply.github.com> Co-authored-by: Elizabeth Thompson <eschutho@gmail.com> Co-authored-by: Rob DiCiuccio <rob.diciuccio@gmail.com> Co-authored-by: Beto Dealmeida <roberto@dealmeida.net> Co-authored-by: joeADSP <75027008+joeADSP@users.noreply.github.com> Co-authored-by: ofekisr <35701650+ofekisr@users.noreply.github.com> Co-authored-by: simcha90 <56388545+simcha90@users.noreply.github.com>

mayurnewase · 2022-02-03T07:51:19Z

superset/viz.py

+            for column in columns or []:
+                label = get_column_name(column)
+                if label not in labels:
+                    deduped_columns.append(column)


Maybe we also need to append label in labels ?
but this didn't cause any issue, because there are checks in /explore_json api somewhere which throws { "error": "Group By' and 'Columns' can't overlap" }

@mayurnewase do you have repro steps for this error?

I was just going through this feature and found pivot table allows same group by and column name, but looking at this function it's not expected.
So I thought this might be incorrect as its not really putting anything in labels list.
So may need to add labels.append(label) here?

I did a quick test, and it seems the legacy pivot table does work with overlapping groupby and column names:

villebro and others added 28 commits September 1, 2021 12:02

add support for adhoc columns to api and sqla model

e7943e3

fix some types

0da5b0b

Merge branch 'master' into hack2020/adhoc-columns

1ad9e80

fix duplicates in column names

7368ed2

fix more lint

e2b1d5d

fix schema and dedup

634ddbf

clean up some logic

43ee0ed

first pass at fixing viz.py

71981bc

Add frontend support for adhoc columns

ee38f54

Add title edit

3c4d6be

Fix showing custom title

9bafd93

Use column name as default value in sql editor

9215821

fix: Adds a loading message when needed in the Select component (apac…

d5a655c

…he#16531)

fix(tests): make parquet select deterministic with order by (apache#1…

661e499

…6570)

bump emotion to help with cache clobbering (apache#16559)

793c027

fix: Support Jinja template functions in global async queries (apache…

e7262bf

…#16412) * Support Jinja template functions in async queries * Pylint * Add tests for async tasks * Remove redundant has_request_context check

fix: impersonate user label/tooltip (apache#16573)

1acd0f4

docs: update for small typos (apache#16568)

14c025c

feat: Add Aurora Data API engine spec (apache#16535)

d92ba0f

* feat: Add Aurora Data API engine spec * Fix lint

refactor: sql_json view endpoint: encapsulate ctas parameters (apache…

f007f05

…#16548) * refactor sql_json view endpoint: encapsulate ctas parameters * fix failed tests * fix failed tests and ci issues

refactor sql_json view endpoint: separate concern into ad hod method (a…

f2c42f3

…pache#16595)

feat: Experimental cross-filter plugins (apache#16594)

64043f2

* fix:fix get permission function * feat: add cross filter chart in charts gallery under FF

chore(deps): bump superset-ui to 0.18.2 (apache#16601)

ab4388e

update type guard references

f501b70

fix imports

470ad60

Merge branch 'master' into hack2021/adhoc-columns

81f1527

update series_columns schema

ee3cc61

Add changes that got lost in rebase

b97da4e

superset-github-bot bot added the preset-io label Nov 9, 2021

pull-request-size bot added the size/XL label Nov 9, 2021

put back removed test

9dd2f75

villebro commented Nov 12, 2021

View reviewed changes

...rset-frontend/src/explore/components/controls/DndColumnSelectControl/ColumnSelectPopover.tsx Outdated Show resolved Hide resolved

geido self-requested a review November 12, 2021 14:57

Move popover height and width to constants

f0f87e2

geido reviewed Nov 12, 2021

View reviewed changes

...rset-frontend/src/explore/components/controls/DndColumnSelectControl/ColumnSelectPopover.tsx Outdated Show resolved Hide resolved

kgabryje and others added 2 commits November 12, 2021 18:40

Refactor big ternary expression

7db4c1d

Merge branch 'master' into hack2021/adhoc-columns

177ce6e

jinghua-qa approved these changes Nov 14, 2021

View reviewed changes

Merge branch 'master' into hack2021/adhoc-columns

d426d2f

villebro force-pushed the hack2021/adhoc-columns branch from be16261 to d426d2f Compare November 15, 2021 07:13

villebro mentioned this pull request Nov 15, 2021

chore: bump superset-ui 0.18.24 #17423

Closed

geido approved these changes Nov 15, 2021

View reviewed changes

villebro merged commit e2a429b into apache:master Nov 15, 2021

villebro deleted the hack2021/adhoc-columns branch November 15, 2021 10:50

superset-github-bot bot added the preset:2021.46 label Nov 17, 2021

kgabryje mentioned this pull request Jan 4, 2022

chore(explore): make adhoc columns available without UX BETA ff #17921

Merged

9 tasks

villebro mentioned this pull request Jan 27, 2022

fix(echarts): Area Chart regression — bumping to 0.18.22 for v1.4.x #18204

Closed

9 tasks

mayurnewase reviewed Feb 3, 2022

View reviewed changes

mistercrunch added 🏷️ bot A label used by `supersetbot` to keep track of which PR where auto-tagged with release labels 🚢 1.5.0 labels Mar 13, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

feat(explore): adhoc column expressions [ID-3] #17379

feat(explore): adhoc column expressions [ID-3] #17379

villebro commented Nov 9, 2021 •

edited

villebro left a comment

kgabryje commented Nov 12, 2021

github-actions bot commented Nov 12, 2021

geido left a comment

jinghua-qa commented Nov 14, 2021

kgabryje commented Nov 14, 2021

kgabryje commented Nov 14, 2021

github-actions bot commented Nov 14, 2021

rumbin commented Nov 14, 2021

jinghua-qa left a comment

geido left a comment

github-actions bot commented Nov 15, 2021

sadpandajoe commented Nov 17, 2021

mayurnewase Feb 3, 2022 •

edited

villebro Feb 3, 2022

mayurnewase Feb 3, 2022 •

edited

villebro Feb 3, 2022

feat(explore): adhoc column expressions [ID-3] #17379

feat(explore): adhoc column expressions [ID-3] #17379

Conversation

villebro commented Nov 9, 2021 • edited

SUMMARY

TESTING INSTRUCTIONS

ADDITIONAL INFORMATION

villebro left a comment

Choose a reason for hiding this comment

kgabryje commented Nov 12, 2021

github-actions bot commented Nov 12, 2021

geido left a comment

Choose a reason for hiding this comment

jinghua-qa commented Nov 14, 2021

kgabryje commented Nov 14, 2021

kgabryje commented Nov 14, 2021

github-actions bot commented Nov 14, 2021

rumbin commented Nov 14, 2021

jinghua-qa left a comment

Choose a reason for hiding this comment

geido left a comment

Choose a reason for hiding this comment

github-actions bot commented Nov 15, 2021

sadpandajoe commented Nov 17, 2021

mayurnewase Feb 3, 2022 • edited

Choose a reason for hiding this comment

villebro Feb 3, 2022

Choose a reason for hiding this comment

mayurnewase Feb 3, 2022 • edited

Choose a reason for hiding this comment

villebro Feb 3, 2022

Choose a reason for hiding this comment

villebro commented Nov 9, 2021 •

edited

mayurnewase Feb 3, 2022 •

edited

mayurnewase Feb 3, 2022 •

edited