Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

feat(bigquery): add support for explicit query parameter type #6596

Merged
merged 15 commits into from
Sep 16, 2022

Conversation

alvarowolfx
Copy link
Contributor

@alvarowolfx alvarowolfx commented Aug 31, 2022

Allow users to pass query parameter with an explicit data types instead of relying just on reflection of Go types. Right now the data type that is affect by this change is *big.Rat, which before was only converted to NUMERIC with 9 digits, and now can be send as a BIGNUMERIC.

This adds two methods:

  • NewScalarQueryParameter(name string, paramType FieldType, value interface{}) QueryParameter
  • NewArrayQueryParameter(name string, paramType FieldType, value interface{}) QueryParameter

Example usage:

ctx := context.Background()
client, err := bigquery.NewClient(ctx, projectID)
if err != nil {
        return fmt.Errorf("bigquery.NewClient: %v", err)
}
defer client.Close()

rat := big.NewRat(12345, 10e10)
q := client.Query("SELECT @bigval, @array")
q.Parameters = []bigquery.QueryParameter{
        {
                Value: NewScalarQueryParameter("bigval", "BIGNUMERIC", rat)
        },
        {
                Value: NewArrayQueryParameter("array", "STRING", []string{"a","b","c"}
        },
}

I'm still evaluating if we need something like NewStructQueryParameter to pass a list of QueryParameters with explicit data types for each field.

Most of this was inspired by the Python implementation that has the same kind of methods for creating query parameters.

see: https://cloud.google.com/bigquery/docs/parameterized-queries#python_1

Resolves #4704

@product-auto-label product-auto-label bot added the size: m Pull request size is medium. label Aug 31, 2022
@product-auto-label product-auto-label bot added the api: bigquery Issues related to the BigQuery API. label Aug 31, 2022
@product-auto-label product-auto-label bot added size: l Pull request size is large. and removed size: m Pull request size is medium. labels Sep 2, 2022
@alvarowolfx
Copy link
Contributor Author

alvarowolfx commented Sep 2, 2022

I changed to a slight different approach, having some specific struct and interface that can be used explicitly or with the current QueryParameter struct.

ctx := context.Background()
client, err := bigquery.NewClient(ctx, projectID)
if err != nil {
        return fmt.Errorf("bigquery.NewClient: %v", err)
}
defer client.Close()

rat := big.NewRat(12345, 10e10)
q := client.Query("SELECT @bigval, @array, @struct")
q.Parameters = []bigquery.QueryParameter{
        {
                Value: ScalarQueryParameter{Name: "bigval", Type: "BIGNUMERIC", Value: rat}
        },
        ArrayQueryParameter{Name: "array", Value: []string{"a","b","c"}, Type: ScalarQueryParameter{Type: "STRING"}}.QueryParameter(),
        StructQueryParameter{
	        Name: "val",
	        Value: map[string]ParameterValue{
		        "Timestamp": ScalarQueryParameter{
			        Value: ts,
		        },
		        "BigNumericArray": ArrayQueryParameter{
			        Value: []string{
				        BigNumericString(bigRat),
				        BigNumericString(rat),
			        },
		        },
		        "SubStruct": StructQueryParameter{
			        Value: map[string]ParameterValue{
				        "String": ScalarQueryParameter{
					        Value: "c",
				        },
			        },
		        },
	        },
	        Schema: map[string]ParameterType{
		        "Timestamp": ScalarQueryParameter{
			        Type: "TIMESTAMP",
		        },
		        "BigNumericArray": ArrayQueryParameter{
			        Type: ScalarQueryParameter{
				        Type: "BIGNUMERIC",
			        },
		        },
		        "SubStruct": StructQueryParameter{
			        Schema: map[string]ParameterType{
				        "String": ScalarQueryParameter{
					        Type: "STRING",
				        },
			        },
		        },
	        },
        }.QueryParameter()
}

bigquery/params.go Outdated Show resolved Hide resolved
bigquery/params.go Outdated Show resolved Hide resolved
bigquery/params.go Outdated Show resolved Hide resolved
@alvarowolfx alvarowolfx marked this pull request as ready for review September 8, 2022 19:19
@alvarowolfx alvarowolfx requested a review from a team September 8, 2022 19:19
@alvarowolfx alvarowolfx requested a review from a team as a code owner September 8, 2022 19:19
func (p QueryParameter) toBQ() (*bq.QueryParameter, error) {
// QueryParameterValue is a go type for representing a explicit typed BigQuery Query Parameter.
type QueryParameterValue struct {
Type StandardSQLDataType
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Document these fields as appropriate.

@@ -129,12 +139,57 @@ type QueryParameter struct {
Value interface{}
}

func (p QueryParameter) toBQ() (*bq.QueryParameter, error) {
// QueryParameterValue is a go type for representing a explicit typed BigQuery Query Parameter.
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I'm still on the fence about the name due to the collision with the API, but it's a reasonable name otherwise.

Should we document the behavior for nested types as part of the type description? Specifically that you need to fully specify types and values for complex params?

bigquery/standardsql.go Outdated Show resolved Hide resolved
bigquery/params.go Outdated Show resolved Hide resolved
bigquery/integration_test.go Show resolved Hide resolved
bigquery/params.go Outdated Show resolved Hide resolved
Copy link
Contributor

@shollyman shollyman left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I think the only work remaining is to flesh out the documentation for QueryParameterValue (and it's fields) and ship it. Approving contingent on that.

Copy link
Contributor

@shollyman shollyman left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Looks good, and I like the explicit examples of usage. Thanks for putting this together!

@alvarowolfx alvarowolfx merged commit d59b5b2 into googleapis:main Sep 16, 2022
gcf-merge-on-green bot pushed a commit that referenced this pull request Sep 21, 2022
🤖 I have created a release *beep* *boop*
---


## [1.42.0](bigquery/v1.41.0...bigquery/v1.42.0) (2022-09-21)


### Features

* **bigquery/analyticshub:** Start generating apiv1 ([#6707](#6707)) ([feb7d7d](feb7d7d))
* **bigquery/datapolicies:** Start generating apiv1beta1 ([#6697](#6697)) ([f5443e8](f5443e8))
* **bigquery/reservation/apiv1beta1:** add REST transport ([f7b0822](f7b0822))
* **bigquery/storage/managedwriter:** Define append retry predicate ([#6650](#6650)) ([478b8dd](478b8dd))
* **bigquery/storage:** add proto annotation for non-ascii field mapping ([ec1a190](ec1a190))
* **bigquery:** Add reference file schema option for federated formats ([#6693](#6693)) ([3d26091](3d26091))
* **bigquery:** Add support for explicit query parameter type ([#6596](#6596)) ([d59b5b2](d59b5b2)), refs [#4704](#4704)


### Bug Fixes

* **bigquery/connection:** integrate  gapic-generator-python-1.4.1 and enable more py_test targets ([ec1a190](ec1a190))

---
This PR was generated with [Release Please](https://github.com/googleapis/release-please). See [documentation](https://github.com/googleapis/release-please#release-please).
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
api: bigquery Issues related to the BigQuery API. size: l Pull request size is large.
Projects
None yet
Development

Successfully merging this pull request may close these issues.

bigquery: big.Rat limited to 9 digits
2 participants