I want to access bigquery array structure with golang.
GCP billing standard table query
q := client.Query(
SELECT billing_account_id,credits FROM
"project.dataset.gcp_billing_export_xxxx
"
WHERE DATE(_PARTITIONTIME) = '2021-11-24' and array_length(credits) > 0 LIMIT 1
)
and explore data
for {
var row []bigquery.Value
err := it.Next(&row)
if err == iterator.Done {
break
}
if err != nil {
return err
}
fmt.Fprintln(w, row[1] )
}
row[1] output : [ [GCP Enhanced Support customers receive a 50% promotional discount that ends on Dec 31st 2021. -0.03 GCP Enhanced Support customers receive a 50% promotional discount that ends on Dec 31st 2021. DISCOUNT] [Discount on Total Spend -0.001 Discount on Total Spend RESELLER_MARGIN] ]
row[1] looks like an arrary structure,but I can't access its element through row[1][0] here is the error message: invalid operation: row[1][0] (type "cloud.google.com/go/bigquery".Value does not support indexing
Any advices?
Regards, Steven
CodePudding user response:
Try to use Struct of Struct. credits
is described here.
type creditsStruct struct {
id string
full_name string
type string
name string
amount float64
}
type myResultsStruct struct {
billing_account_id string
credits creditsStruct
}
for {
var c myResultsStruct
err := it.Next(&c)
if err == iterator.Done {
break
}
if err != nil {
// TODO: Handle error.
}
fmt.Println(c)
}
CodePudding user response:
You can use “func (*RowIterator) Next“
Next loads the next row into dst. Its return value is iterator.Done if there are no more results. Once Next returns iterator.Done, all subsequent calls will return iterator.Done.
dst may implement ValueLoader, or may be a *[]Value, *map[string]Value, or struct pointer. If dst is a *map[string]Value, a new map will be created if dst is nil. Then for each schema column name, the map key of that name will be set to the column's value. STRUCT types (RECORD types or nested schemas) become nested maps.
Each BigQuery column type corresponds to one or more Go types; a matching struct field must be of the correct type. The correspondences are:
STRING string
BOOL bool
INTEGER int, int8, int16, int32, int64, uint8, uint16, uint32
FLOAT float32, float64
BYTES []byte
TIMESTAMP time.Time
DATE civil.Date
TIME civil.Time
DATETIME civil.DateTime
A repeated field corresponds to a slice or array of the element type. A STRUCT type (RECORD or nested schema) corresponds to a nested struct or struct pointer. All calls to Next on the same iterator must use the same struct type. You can see more information. You can see this example:
package main
import (
"context"
"fmt"
"cloud.google.com/go/bigquery"
"google.golang.org/api/iterator"
)
func main() {
ctx := context.Background()
client, err := bigquery.NewClient(ctx, "project-id")
if err != nil {
// TODO: Handle error.
}
type score struct {
Name string
Num int
}
q := client.Query("select name, num from t1")
it, err := q.Read(ctx)
if err != nil {
// TODO: Handle error.
}
for {
var s score
err := it.Next(&s)
if err == iterator.Done {
break
}
if err != nil {
// TODO: Handle error.
}
fmt.Println(s)
}
}