feat: add `api_method` parameter to `Client.query` to select `INSERT` or `QUERY` API #967

tswast · 2021-09-13T21:11:22Z

This PR is the first step in enabling the "fast query path". At the moment, selecting api_method="QUERY" is actually likely to be slower because it fetches the first page of results and discards them. A PR will follow-up to cache this first page for use by the row iterator.

This commit only refactors to allow jobs.insert to be selected.
Supporting jobs.query will require more transformations to QueryJobConfig,
QueryJob, and RowIterator.

Thank you for opening a Pull Request! Before submitting your PR, there are a few things you can do to make sure it goes smoothly:

Make sure to open an issue as a bug/issue before writing your code! That way we can discuss the change, evaluate designs, and agree on the general idea
Ensure the tests and linter pass
Code coverage does not decrease (if any source code was changed)
Appropriate docs were updated (if necessary)

Towards #589 🦕

google/cloud/bigquery/_job_helpers.py

tswast · 2021-09-17T15:11:56Z

Even though this isn't a breaking change, it refactors enough that maybe this should target v3 so as not to delay v3 any longer.

snippet-bot · 2021-10-05T15:41:42Z

No region tags are edited in this PR.

This comment is generated by snippet-bot.
If you find problems with this result, please file an issue at:
https://github.com/googleapis/repo-automation-bots/issues.
To update this comment, add snippet-bot:force-run label or use the checkbox below:

Refresh this comment

… or `query` API Work in Progress. This commit only refactors to allow jobs.insert to be selected. Supporting jobs.query will require more transformations to QueryJobConfig, QueryJob, and RowIterator.

tswast · 2021-11-18T23:09:06Z

I've just synced to the latest v3 branch. Unit tests and query system tests are passing. I think this is ready for review.

Note: This won't actually result in any performance improvements until the # TODO: https://github.com/googleapis/python-bigquery/issues/589 in _job_helpers.py is resolved. Since that requires more hacks to the QueryJob class, I figure that's worth postponing to a separate PR. As-is, this PR should be showing the same behavior for api_method="INSERT" and api_method="QUERY" options.

tests/system/conftest.py

plamut

Looks really promising and structured, mostly just minor remarks and questions.

google/cloud/bigquery/_job_helpers.py

tests/system/test_query.py

tests/unit/test__job_helpers.py

plamut

LGTM.

There's one nit, but decide for yourself if it's worth updating the PR.

plamut · 2021-11-22T18:10:36Z

google/cloud/bigquery/client.py

@@ -3215,6 +3219,11 @@ def query(
                called on the job returned. The ``job_retry``
                specified here becomes the default ``job_retry`` for
                ``result()``, where it can also be specified.
+            api_method (Union[str, enums.QueryApiMethod]):


(nit) Duplicate annotation info

loferris

LGTM

deps!: BigQuery Storage and pyarrow are required dependencies (#776) fix!: use nullable `Int64` and `boolean` dtypes in `to_dataframe` (#786) feat!: destination tables are no-longer removed by `create_job` (#891) feat!: In `to_dataframe`, use `dbdate` and `dbtime` dtypes from db-dtypes package for BigQuery DATE and TIME columns (#972) fix!: automatically convert out-of-bounds dates in `to_dataframe`, remove `date_as_object` argument (#972) feat!: mark the package as type-checked (#1058) feat!: default to DATETIME type when loading timezone-naive datetimes from Pandas (#1061) feat: add `api_method` parameter to `Client.query` to select `INSERT` or `QUERY` API (#967) fix: improve type annotations for mypy validation (#1081) feat: use `StandardSqlField` class for `Model.feature_columns` and `Model.label_columns` (#1117) docs: Add migration guide from version 2.x to 3.x (#1027) Release-As: 3.0.0

deps!: BigQuery Storage and pyarrow are required dependencies (googleapis#776) fix!: use nullable `Int64` and `boolean` dtypes in `to_dataframe` (googleapis#786) feat!: destination tables are no-longer removed by `create_job` (googleapis#891) feat!: In `to_dataframe`, use `dbdate` and `dbtime` dtypes from db-dtypes package for BigQuery DATE and TIME columns (googleapis#972) fix!: automatically convert out-of-bounds dates in `to_dataframe`, remove `date_as_object` argument (googleapis#972) feat!: mark the package as type-checked (googleapis#1058) feat!: default to DATETIME type when loading timezone-naive datetimes from Pandas (googleapis#1061) feat: add `api_method` parameter to `Client.query` to select `INSERT` or `QUERY` API (googleapis#967) fix: improve type annotations for mypy validation (googleapis#1081) feat: use `StandardSqlField` class for `Model.feature_columns` and `Model.label_columns` (googleapis#1117) docs: Add migration guide from version 2.x to 3.x (googleapis#1027) Release-As: 3.0.0

…1014) Thank you for opening a Pull Request! Before submitting your PR, there are a few things you can do to make sure it goes smoothly: - [ ] Make sure to open an issue as a [bug/issue](https://github.com/googleapis/python-bigquery/issues/new/choose) before writing your code! That way we can discuss the change, evaluate designs, and agree on the general idea - [ ] Ensure the tests and linter pass - [ ] Code coverage does not decrease (if any source code was changed) - [ ] Appropriate docs were updated (if necessary) Issue discovered while investigating what properties are needed in googleapis#967

deps!: BigQuery Storage and pyarrow are required dependencies (googleapis#776) fix!: use nullable `Int64` and `boolean` dtypes in `to_dataframe` (googleapis#786) feat!: destination tables are no-longer removed by `create_job` (googleapis#891) feat!: In `to_dataframe`, use `dbdate` and `dbtime` dtypes from db-dtypes package for BigQuery DATE and TIME columns (googleapis#972) fix!: automatically convert out-of-bounds dates in `to_dataframe`, remove `date_as_object` argument (googleapis#972) feat!: mark the package as type-checked (googleapis#1058) feat!: default to DATETIME type when loading timezone-naive datetimes from Pandas (googleapis#1061) feat: add `api_method` parameter to `Client.query` to select `INSERT` or `QUERY` API (googleapis#967) fix: improve type annotations for mypy validation (googleapis#1081) feat: use `StandardSqlField` class for `Model.feature_columns` and `Model.label_columns` (googleapis#1117) docs: Add migration guide from version 2.x to 3.x (googleapis#1027) Release-As: 3.0.0

google-cla bot added the cla: yes This human has signed the Contributor License Agreement. label Sep 13, 2021

product-auto-label bot added the api: bigquery Issues related to the googleapis/python-bigquery API. label Sep 13, 2021

tswast commented Sep 14, 2021

View reviewed changes

google/cloud/bigquery/_job_helpers.py Outdated Show resolved Hide resolved

tswast changed the base branch from main to v3 October 5, 2021 15:41

google-cla bot added cla: no This human has *not* signed the Contributor License Agreement. and removed cla: yes This human has signed the Contributor License Agreement. labels Oct 5, 2021

tswast changed the title ~~feat: add api_method parameter to Client.query to select insert or query API~~ feat: add api_method parameter to Client.query to select INSERT or QUERY API Oct 6, 2021

This comment has been minimized.

Sign in to view

tswast added 7 commits October 7, 2021 16:43

feat: add api_method parameter to Client.query to select insert…

575966d

… or `query` API Work in Progress. This commit only refactors to allow jobs.insert to be selected. Supporting jobs.query will require more transformations to QueryJobConfig, QueryJob, and RowIterator.

WIP: begin implementation of jobs.query usage

094e3cb

remove extra files

17994f5

insert query with jobs.query

c963e25

fix merge between job config and query request

5093378

add tests

e2e1c7d

update todo with thoughts on future perf update

2e4af92

tswast force-pushed the issue589-queries-endpoint branch from 7b9457c to 2e4af92 Compare October 7, 2021 21:43

googleapis deleted a comment from google-cla bot Oct 7, 2021

google-cla bot added cla: yes This human has signed the Contributor License Agreement. and removed cla: no This human has *not* signed the Contributor License Agreement. labels Oct 7, 2021

googleapis deleted a comment from google-cla bot Oct 7, 2021

tswast added 3 commits October 7, 2021 16:49

clarify TODO comment

d700df5

add placeholders for needed tests

1e73a56

add schema property

7435c8d

tswast mentioned this pull request Oct 11, 2021

feat: add QueryJob.schema property for dry run queries #1014

Merged

4 tasks

tswast added 2 commits October 12, 2021 10:33

feat: add QueryJob.schema property for dry run queries

d383847

add more job properties

8bc2458

tswast marked this pull request as ready for review November 18, 2021 23:05

tswast requested a review from a team November 18, 2021 23:05

tswast requested a review from a team as a code owner November 18, 2021 23:05

tswast requested a review from loferris November 18, 2021 23:05

This comment has been minimized.

Sign in to view

tswast added 2 commits November 19, 2021 09:51

adjust query job construction

0598ace

avoid conflicting table IDs

4f36bae

tswast commented Nov 19, 2021

View reviewed changes

tests/system/conftest.py Outdated Show resolved Hide resolved

mock query response

3058498

tswast requested a review from plamut November 19, 2021 16:25

This comment has been minimized.

Sign in to view

tswast added 3 commits November 19, 2021 14:12

fix unit test coverage

ba785d9

fix type errors

b67ac5a

fix docs formatting

a3223b1

plamut reviewed Nov 20, 2021

View reviewed changes

comments and additional unit tests

b257609

tswast requested a review from plamut November 22, 2021 16:54

plamut approved these changes Nov 22, 2021

View reviewed changes

loferris approved these changes Nov 29, 2021

View reviewed changes

tswast merged commit 3b3ebff into googleapis:v3 Dec 2, 2021

tswast deleted the issue589-queries-endpoint branch December 2, 2021 20:15

tswast mentioned this pull request Mar 29, 2022

fix!: remove out-of-date BigQuery ML protocol buffers #1178

Merged

4 tasks

release-please bot mentioned this pull request Mar 29, 2022

chore(main): release 3.0.0 #1179

Merged

tswast mentioned this pull request Apr 13, 2024

fix: avoid unnecessary API call in QueryJob.result() when job is already finished #1900

Merged

4 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

feat: add `api_method` parameter to `Client.query` to select `INSERT` or `QUERY` API #967

feat: add `api_method` parameter to `Client.query` to select `INSERT` or `QUERY` API #967

tswast commented Sep 13, 2021 •

edited

tswast commented Sep 17, 2021 •

edited

snippet-bot bot commented Oct 5, 2021 •

edited

This comment has been minimized.

tswast commented Nov 18, 2021

This comment has been minimized.

This comment has been minimized.

This comment has been minimized.

plamut left a comment

plamut left a comment

plamut Nov 22, 2021

loferris left a comment

feat: add api_method parameter to Client.query to select INSERT or QUERY API #967

feat: add api_method parameter to Client.query to select INSERT or QUERY API #967

Conversation

tswast commented Sep 13, 2021 • edited

tswast commented Sep 17, 2021 • edited

snippet-bot bot commented Oct 5, 2021 • edited

No region tags are edited in this PR.

This comment has been minimized.

tswast commented Nov 18, 2021

This comment has been minimized.

This comment has been minimized.

This comment has been minimized.

plamut left a comment

Choose a reason for hiding this comment

plamut left a comment

Choose a reason for hiding this comment

plamut Nov 22, 2021

Choose a reason for hiding this comment

loferris left a comment

Choose a reason for hiding this comment

feat: add `api_method` parameter to `Client.query` to select `INSERT` or `QUERY` API #967

feat: add `api_method` parameter to `Client.query` to select `INSERT` or `QUERY` API #967

tswast commented Sep 13, 2021 •

edited

tswast commented Sep 17, 2021 •

edited

snippet-bot bot commented Oct 5, 2021 •

edited