I have a BigQuery table with this schema:
name STRING NULLABLE
age INTEGER NULLABLE
amount INTEGER NULLABLE
and this two rows:
1 Batgirl 23 123
2 Batman 22 null
What I am trying to do is a select from Go on this table which is working really fine:
ctx := context.Background()
client, err := bigquery.NewClient(ctx, projectID)
q := client.Query("SELECT * FROM test.user_test LIMIT 1000")
it, err := q.Read(ctx)
if err != nil {
log.Fatal(err)
}
for {
var values []bigquery.Value
err := it.Next(&values)
if err == iterator.Done {
break
}
if err != nil {
log.Fatal(err)
}
fmt.Println(values)
}
the above code is working like a charm, it gets the select and it prints both rows like this:
[Batman 22 <nil>]
[Batgirl 23 123]
Batman is shown with the nil value. The problem comes when I am trying to store this rows in a struct with this code:
type test struct {
Name string
Age int
Amount int `nullable`
}
q := client.Query("SELECT * FROM test.test_user LIMIT 1000")
if err != nil {
log.Fatal(err)
}
it, err := q.Read(ctx)
if err != nil {
log.Fatal(err)
}
for {
var c test
err := it.Next(&c)
if err == iterator.Done {
break
}
if err != nil {
log.Fatal(err)
}
fmt.Println(c)
}
the above code iterates the results query and it stores both values in a struct that I can manipulate later. It works fine when there aren't any null columns (which is not my case now because I have a null amount on Batman), so I am getting the next error:
bigquery: NULL values cannot be read into structs
What I have tried to fix this is making the struct field nullable like this:
type test struct {
Name string
Age int
Amount int `bigquery:",nullable"`
}
But this is doing basically nothing. I am starting to learn Go and I don't really understand why I can't have nil values in structs or how I can fix this, so these are my two questions:
How can I store my row results with null values?
Why has google decided that a nil value can't be set on a struct field?
You can use any of the bigquery.Null types that you can see in this link:
STRING NullString
BOOL NullBool
INTEGER NullInt64
FLOAT NullFloat64
TIMESTAMP NullTimestamp
DATE NullDate
TIME NullTime
DATETIME NullDateTime
In your case you would have to change your line
Amount int `nullable`
to:
Amount bigquery.NullInt64
In the same link it states:
It is an error to attempt to read a BigQuery NULL value into a struct field, unless the field is of type []byte or is one of the special Null types: NullInt64, NullFloat64, NullBool, NullString, NullTimestamp, NullDate, NullTime or NullDateTime.