googleapis / googleapis/google-cloud-java

[java-bigquery] BigQuery: Unable to insert an empty array for an array of struct when using parameterized query

Đang mở
#12,152 1 bình luận 0 reaction 0 người được giao Xem trên GitHub
api: bigquery priority: p3 type: bug
Ngôn ngữ chính
Java
Star
2.1k
Fork
1.2k
Merge trung bình
1 ngày 23 giờ
Pull request đã merge (30 ngày)
154

Mô tả

#### Environment details

1. Specify the API at the beginning of the title. For example, "BigQuery: ...").
General, Core, and Other are also allowed as types
2. OS type and version: macOS 14.7.1
3. Java version: JDK Zulu 21.0.3
4. version(s): 2.44.0

#### Steps to reproduce

1. Create a BigQuery table that contains a field, which is an `ARRAY>`. E.g.
```sql
create table `.example.bug` (
people ARRAY>
);
```
2. Create a parameterized query, such as:
```
insert into `.example.bug` (people) values (@people);
```
3. Prepare the query job configuration (assume a `BigQuery` service is configured in the snippet below):
```kotlin
data class Person(val name: String)

val people = emptyList()
val sql = "insert into `.example.bug` (people) values (@people);"
val params = mapOf("people" to QueryParameterValue.array(people, StandardSQLTypeName.STRUCT))
val jobConfig = QueryJobConfiguration.newBuilder(sql).setNamedParameters(params).build()

val tableResult = bigQuery.query(jobConfig)
```

The code above will fail with ``.

#### Stack trace
```
com.google.cloud.bigquery.BigQueryException: Value has type ARRAY> which cannot be inserted into column must_not_contain, which has type ARRAY> at [7:9]
...
Caused by: com.google.api.client.googleapis.json.GoogleJsonResponseException: 400 Bad Request
POST https://bigquery.googleapis.com/bigquery/v2/projects/bolcom-stg-nostradamus-916/queries
{
"code": 400,
"errors": [
{
"domain": "global",
"location": "q",
"locationType": "parameter",
"message": "Value has type ARRAY> which cannot be inserted into column people, which has type ARRAY> at [7:9]",
"reason": "invalidQuery"
}
],
"message": "Value has type ARRAY> which cannot be inserted into column people, which has type ARRAY> at [7:9]",
"status": "INVALID_ARGUMENT"
}
at com.google.api.client.googleapis.json.GoogleJsonResponseException.from(GoogleJsonResponseException.java:146)
at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:118)
at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:37)
at com.google.api.client.googleapis.services.AbstractGoogleClientRequest$3.interceptResponse(AbstractGoogleClientRequest.java:479)
at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:1111)
at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:565)
at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:506)
at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:616)
at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.queryRpc(HttpBigQueryRpc.java:771)
... 19 more
```

#### Any additional information below

This is caused by the fact that setting the value and the schema are intertwined in the Java SDK. In the Python SDK, this is not the case, an empty array and the corresponding schema can be set individually. [See here as a reference](https://dev.to/stack-labs/how-to-pass-an-array-of-structs-in-bigquerys-parameterized-queries-39nm#:~:text=Gotcha%20n%C2%B02%3A%20Provide%20the%20full%20structure%20type%20as%20second%20argument).

#### Proposed resolution

There are two options here:
1. `QueryParameterValue` should allow for a separation of schema definition and value. Currently it's not possible to provide the structure of a field without specifying a value.
2. BigQuery itself should not require the full schema when an empty array is provided to a field that has an `ARRAY>` as a structure.

Thanks!

Hướng dẫn đóng góp

Mở hướng dẫn đóng góp

Đánh giá

Issue này chưa được đánh giá.

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.