feat(db_engine_specs): big query cost estimation #21325

zamar-roura · 2022-09-04T19:19:21Z

SUMMARY

We are adding four methods inside superset/db_engine_specs/bigquery.py

For now only Postgres and Presto had cost estimation capabilities. For that they used the cursor object.
Bigquery needs to use a dry run to get the query cost estimation. Sadly, you can't use the cursor to execute dry runs (I have tried). And that's why there is a need to override the two functions. estimate_statement_cost and estimate_query_cost. The other two methods get_allow_cost_estimate and query_cost_formatter are just added because the base class doesn't have functionality or gives a False.

Also the QueryEditor.py object doesn't have sqlEditorId anymore in it's variables and the Cost Estimation Reducer is looking for it, thus giving error, that is also changed.

BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF

BEFORE

After

TESTING INSTRUCTIONS

Add ESTIMATE_QUERY_COST = True inside superset/config.py.
Add a valid BigQuery database, activate option Enable query cost estimation inside advanced options.
Go to SQL Lab and put a valid query, then press Estimate Cost button.

ADDITIONAL INFORMATION

[ X] Has associated issue: [SIP-88] BigQuery Estimate Cost Button for SQL Lab #20832
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
[ X] Introduces new feature or API
Removes existing feature or API

github-actions

Congrats on making your first PR and thank you for contributing to Superset! 🎉 ❤️
We hope to see you in our Slack community too!

zamar-roura · 2022-09-06T09:35:33Z

Thanks for running the tests.

I'll check on the lint errors and try to see why the cypress test is showing the "cant create chart from query error. Maybe the estimate cost button is changing the selector of the other button? Lets see.

codecov · 2022-09-27T05:34:47Z

Codecov Report

Merging #21325 (ef5c3b1) into master (9cfbc22) will decrease coverage by 0.03%.
The diff coverage is 18.75%.

@@            Coverage Diff             @@
##           master   #21325      +/-   ##
==========================================
- Coverage   67.03%   66.99%   -0.04%     
==========================================
  Files        1859     1859              
  Lines       71043    71090      +47     
  Branches     7776     7776              
==========================================
+ Hits        47622    47630       +8     
- Misses      21397    21436      +39     
  Partials     2024     2024

Flag	Coverage Δ
hive	`52.43% <18.75%> (-0.05%)`	⬇️
javascript	`53.85% <ø> (ø)`
mysql	`77.96% <18.75%> (-0.09%)`	⬇️
postgres	`78.03% <18.75%> (-0.09%)`	⬇️
presto	`52.33% <18.75%> (-0.05%)`	⬇️
python	`81.29% <18.75%> (-0.09%)`	⬇️
sqlite	`76.41% <18.75%> (-0.09%)`	⬇️
unit	`51.43% <18.75%> (-0.05%)`	⬇️

Flags with carried forward coverage won't be shown. Click here to find out more.

Impacted Files	Coverage Δ
superset-frontend/src/SqlLab/reducers/sqlLab.js	`36.87% <ø> (ø)`
.../CRUD/data/database/DatabaseModal/ExtraOptions.tsx	`70.37% <ø> (ø)`
superset/config.py	`91.64% <ø> (-0.03%)`	⬇️
superset/db_engine_specs/bigquery.py	`69.60% <18.75%> (-12.09%)`	⬇️

📣 We’re building smart automated test selection to slash your CI/CD build times. Learn more

zamar-roura · 2022-10-02T15:12:59Z

@villebro is it okay like this? The cypress E2E tests sometimes work sometimes don't, but everything else is fixed and working.

zamar-roura · 2022-10-05T17:01:34Z

Do I need to do something else to merge this?

fernandofernandezgonzalez · 2022-11-25T12:23:47Z

Wowwww!!! This is a so, so much needed feature!!

villebro · 2022-11-25T13:44:10Z

@zamar-roura would you be able to rebase the PR to resolve the conflicts?

zamar-roura · 2022-11-27T11:09:55Z

@villebro Tell me if you need anything else. I'll be

The reducer/SqlLab.js needed some fixes in the ESTIMATE QUERY action as action.query didn't have any sqlEditorId parameter. It always comes as action.query.id not action.query.sqlEditorId.

villebro · 2022-11-28T19:03:03Z

Thanks @zamar-roura - I'll do a review + test pass tomorrow 👍

zamar-roura · 2022-12-07T09:38:50Z

Hi @villebro, I'm at your full disposal to improve the merge as much as needed, it's my first merge in superset, if anything is not as it should be tell me and I'll put my full effort into it.

villebro · 2022-12-07T09:47:17Z

@zamar-roura I haven't forgotten about this, I've just been insanely busy. But I'll do my absolute best to review this today!

zamar-roura · 2022-12-07T10:30:30Z

@villebro I totally understand, thank you so much for the quick reply!

villebro · 2022-12-08T07:49:41Z

Restarted CI, the cypress workflow seems to be having issues

villebro

Thanks for the contribution and for fixing this, as this feature appears to be broken right now 🙁 Added a few comments after my first pass review and test.

villebro · 2022-12-08T08:30:13Z

superset/config.py

+# The feature is off by default, and currently only supported in Presto and Postgres,
+# and Bigquery.


The current config, docs and the examples in config.py are in a pretty horrible state right now. I observed the following:

The ESTIMATE_QUERY_COST config is in fact a feature flag and needs to be moved to DEFAULT_FEATURE_FLAGS:

superset/superset/config.py

Line 879 in 49f1cfc

ESTIMATE_QUERY_COST = False

This should be updated

This example is broken:

superset/superset/config.py

Line 905 in 49f1cfc

# "QUERY_COST_FORMATTERS_BY_ENGINE": {"postgresql": postgres_query_cost_formatter},

. It should in fact be # QUERY_COST_FORMATTERS_BY_ENGINE = {"postgresql": postgres_query_cost_formatter}. This should also be updated.

There are no docs for this feature. In the deprecated docs there's a SQL Lab section that seems to have been lost over the years, and the content is also incorrect: https://apache-superset.readthedocs.io/en/latest/sqllab.html I don't expect you to add docs for this, but just mentioning this here to call attention to it. FYI @rusackas

Thanks for the callout... putting it on my very long to-do list!

Fixed the things and added some more comments.

About the docs I would love to fix the content. Only thing is that the superset/blob/master/docs/sqllab.rst file doesnt even exist now.

@zamar-roura let's leave the docs for a follow-up PR 👍

villebro

A few last minute comments/questions

superset/config.py

villebro · 2022-12-11T18:59:41Z

superset/db_engine_specs/bigquery.py

+        # Format Bytes.
+        # TODO: In case more db engine specs need to be added,
+        # this should be made a function outside this scope.
+        byte_division = 1024
+        if hasattr(query_job, "total_bytes_processed"):
+            query_bytes_processed = query_job.total_bytes_processed
+            if query_bytes_processed // byte_division == 0:
+                byte_type = "B"
+                total_bytes_processed = query_bytes_processed
+            elif query_bytes_processed // (byte_division**2) == 0:
+                byte_type = "KB"
+                total_bytes_processed = round(query_bytes_processed / byte_division, 2)
+            elif query_bytes_processed // (byte_division**3) == 0:
+                byte_type = "MB"
+                total_bytes_processed = round(
+                    query_bytes_processed / (byte_division**2), 2
+                )
+            else:
+                byte_type = "GB"
+                total_bytes_processed = round(


For a future cleanup PR, we should really harmonize thees across specs (I seem to recall someone already did this, but I was unable to find it..)

Perfect, after pushing this I saw that there was an early PR of 2022 that did humanize it #18694

superset/db_engine_specs/bigquery.py

zamar-roura · 2022-12-23T23:06:07Z

Hi @villebro. I don't remember if there was anything else we needed to do. Its a busy end of the year and also full of festivities so if you want we can see what´s needed after all this ends, cheers!

zamar-roura · 2023-01-08T16:13:54Z

@villebro I'll be checking from time to time to resolve merge conflicts

villebro

LGTM, thanks for your patience with this PR. Also, thanks for bringing up #18694 which I appear to have dropped the ball on 🙁

pull-request-size bot added the size/L label Sep 4, 2022

github-actions bot reviewed Sep 4, 2022

View reviewed changes

villebro self-requested a review September 6, 2022 07:52

zamar-roura changed the title ~~Feat(db_engine_specs) big query cost estimation~~ Feat(db_engine_specs): Big query cost estimation Sep 27, 2022

zamar-roura changed the title ~~Feat(db_engine_specs): Big query cost estimation~~ feat(db_engine_specs): Big query cost estimation Sep 27, 2022

zamar-roura changed the title ~~feat(db_engine_specs): Big query cost estimation~~ feat(db_engine_specs): big query cost estimation Sep 29, 2022

zamar roura and others added 9 commits November 26, 2022 23:50

Add BigQueryCost Estimation

2af39cb

Separate methods it's better

ba2e703

feat(db_engine_specs) bq cost estimation (apache#21325)

2a11c96

fix pr lint issues

6bcebf5

bigquery changes

1b15463

pylint fixes

f7a0d3c

type ignore

dbc467a

Bigquery in documentation

3d02618

Fixes of rebase

58705fa

zamar-roura force-pushed the feat--Big-Query-Cost-Estimation branch from f855f45 to 58705fa Compare November 26, 2022 23:33

pull-request-size bot added size/XXL and removed size/L labels Nov 26, 2022

Fix formats with prettier

3286374

pull-request-size bot added size/L and removed size/XXL labels Nov 26, 2022

villebro self-assigned this Nov 28, 2022

Zamar Roura added 2 commits December 7, 2022 21:36

Its with 1024

a8393ba

Conform change of 1000 bytes to 1024 bytes pylint

3db26a3

villebro reviewed Dec 8, 2022

View reviewed changes

Zamar Roura added 3 commits December 9, 2022 20:02

Update docs and fix config problems

8a2ff97

Add TODO

5ab7fa4

Line was too long

72f86c4

villebro reviewed Dec 11, 2022

View reviewed changes

Zamar Roura added 2 commits December 11, 2022 20:04

Remove printing of debugging

a5a94ba

config nit

4fa1e56

Zamar Roura added 2 commits January 7, 2023 22:05

MERGE master

de90b65

Todo Humanize

ef5c3b1

villebro approved these changes Jan 9, 2023

View reviewed changes

villebro merged commit 001100d into apache:master Jan 9, 2023

villebro mentioned this pull request Jan 9, 2023

feat(bigquery): add support for query cost estimate #18694

Closed

9 tasks

mistercrunch added the 🚢 2.1.3 label Feb 18, 2024

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

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

feat(db_engine_specs): big query cost estimation #21325

feat(db_engine_specs): big query cost estimation #21325

zamar-roura commented Sep 4, 2022

github-actions bot left a comment

zamar-roura commented Sep 6, 2022

codecov bot commented Sep 27, 2022 •

edited

zamar-roura commented Oct 2, 2022

zamar-roura commented Oct 5, 2022

fernandofernandezgonzalez commented Nov 25, 2022

villebro commented Nov 25, 2022

zamar-roura commented Nov 27, 2022

villebro commented Nov 28, 2022

zamar-roura commented Dec 7, 2022

villebro commented Dec 7, 2022

zamar-roura commented Dec 7, 2022

villebro commented Dec 8, 2022

villebro left a comment

villebro Dec 8, 2022

rusackas Dec 8, 2022

zamar-roura Dec 11, 2022

villebro Dec 11, 2022

villebro left a comment

villebro Dec 11, 2022

zamar-roura Dec 11, 2022

zamar-roura commented Dec 23, 2022

zamar-roura commented Jan 8, 2023

villebro left a comment

		# The feature is off by default, and currently only supported in Presto and Postgres,
		# and Bigquery.

feat(db_engine_specs): big query cost estimation #21325

feat(db_engine_specs): big query cost estimation #21325

Conversation

zamar-roura commented Sep 4, 2022

SUMMARY

BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF

TESTING INSTRUCTIONS

ADDITIONAL INFORMATION

github-actions bot left a comment

Choose a reason for hiding this comment

zamar-roura commented Sep 6, 2022

codecov bot commented Sep 27, 2022 • edited

Codecov Report

zamar-roura commented Oct 2, 2022

zamar-roura commented Oct 5, 2022

fernandofernandezgonzalez commented Nov 25, 2022

villebro commented Nov 25, 2022

zamar-roura commented Nov 27, 2022

villebro commented Nov 28, 2022

zamar-roura commented Dec 7, 2022

villebro commented Dec 7, 2022

zamar-roura commented Dec 7, 2022

villebro commented Dec 8, 2022

villebro left a comment

Choose a reason for hiding this comment

villebro Dec 8, 2022

Choose a reason for hiding this comment

rusackas Dec 8, 2022

Choose a reason for hiding this comment

zamar-roura Dec 11, 2022

Choose a reason for hiding this comment

villebro Dec 11, 2022

Choose a reason for hiding this comment

villebro left a comment

Choose a reason for hiding this comment

villebro Dec 11, 2022

Choose a reason for hiding this comment

zamar-roura Dec 11, 2022

Choose a reason for hiding this comment

zamar-roura commented Dec 23, 2022

zamar-roura commented Jan 8, 2023

villebro left a comment

Choose a reason for hiding this comment

codecov bot commented Sep 27, 2022 •

edited