bigquery: add support for the GEOGRAPHY type.
BigQuery supports a GEOGRAPHY datatype, which is functionally
a string in a Well Known Text (WKT) format. This change adds
support for interacting with the GEOGRAPHY type.
For consuming data from a query/table, the NullGeography type
is provided (as it is for other supported datatypes) for disambiguating
the default value from a missing (null) value.
Type inference from native go types is not supported, as there's not
a clear WKT type available. We may add a string type alias in the
future if there's demand.
This change does NOT add GEOGRAPHY type support for query.
Added doc comments in params.go indicating the way to handle this.
Change-Id: I2b25fcdfd56fb9d3a774d4c93d13812ad19e6c72
Reviewed-on: https://code-review.googlesource.com/c/37810
Reviewed-by: kokoro <noreply+kokoro@google.com>
Reviewed-by: Jean de Klerk <deklerk@google.com>
diff --git a/bigquery/doc.go b/bigquery/doc.go
index a9888b5..6c7a654 100644
--- a/bigquery/doc.go
+++ b/bigquery/doc.go
@@ -174,9 +174,9 @@
Struct inference supports tags like those of the encoding/json package, so you can
change names, ignore fields, or mark a field as nullable (non-required). Fields
declared as one of the Null types (NullInt64, NullFloat64, NullString, NullBool,
-NullTimestamp, NullDate, NullTime and NullDateTime) are automatically inferred as
-nullable, so the "nullable" tag is only needed for []byte, *big.Rat and
-pointer-to-struct fields.
+NullTimestamp, NullDate, NullTime, NullDateTime, and NullGeography) are
+automatically inferred as nullable, so the "nullable" tag is only needed for []byte,
+*big.Rat and pointer-to-struct fields.
type student2 struct {
Name string `bigquery:"full_name"`
diff --git a/bigquery/integration_test.go b/bigquery/integration_test.go
index 6b99d0b..0f7c80d 100644
--- a/bigquery/integration_test.go
+++ b/bigquery/integration_test.go
@@ -865,6 +865,7 @@
Time civil.Time
DateTime civil.DateTime
Numeric *big.Rat
+ Geography string
StringArray []string
IntegerArray []int64
@@ -875,6 +876,7 @@
TimeArray []civil.Time
DateTimeArray []civil.DateTime
NumericArray []*big.Rat
+ GeographyArray []string
Record SubTestStruct
RecordArray []SubTestStruct
@@ -905,6 +907,8 @@
tm2 := civil.Time{Hour: 1, Minute: 2, Second: 4, Nanosecond: 0}
ts2 := time.Date(1994, 5, 15, 1, 2, 4, 0, time.UTC)
dtm2 := civil.DateTime{Date: d2, Time: tm2}
+ g := "POINT(-122.350220 47.649154)"
+ g2 := "POINT(-122.0836791 37.421827)"
// Populate the table.
ins := table.Inserter()
@@ -920,6 +924,7 @@
tm,
dtm,
big.NewRat(57, 100),
+ g,
[]string{"a", "b"},
[]int64{1, 2},
[]float64{1, 1.41},
@@ -929,6 +934,7 @@
[]civil.Time{tm, tm2},
[]civil.DateTime{dtm, dtm2},
[]*big.Rat{big.NewRat(1, 2), big.NewRat(3, 5)},
+ []string{g, g2},
SubTestStruct{
"string",
SubSubTestStruct{24},
@@ -1009,7 +1015,26 @@
ctm := civil.Time{Hour: 15, Minute: 4, Second: 5, Nanosecond: 6000}
cdt := civil.DateTime{Date: testDate, Time: ctm}
rat := big.NewRat(33, 100)
+ geo := "POINT(-122.198939 47.669865)"
+
+ // Nil fields in the struct.
testInsertAndReadNullable(t, testStructNullable{}, make([]Value, len(testStructNullableSchema)))
+
+ // Explicitly invalidate the Null* types within the struct.
+ testInsertAndReadNullable(t, testStructNullable{
+ String: NullString{Valid: false},
+ Integer: NullInt64{Valid: false},
+ Float: NullFloat64{Valid: false},
+ Boolean: NullBool{Valid: false},
+ Timestamp: NullTimestamp{Valid: false},
+ Date: NullDate{Valid: false},
+ Time: NullTime{Valid: false},
+ DateTime: NullDateTime{Valid: false},
+ Geography: NullGeography{Valid: false},
+ },
+ make([]Value, len(testStructNullableSchema)))
+
+ // Populate the struct with values.
testInsertAndReadNullable(t, testStructNullable{
String: NullString{"x", true},
Bytes: []byte{1, 2, 3},
@@ -1021,9 +1046,10 @@
Time: NullTime{ctm, true},
DateTime: NullDateTime{cdt, true},
Numeric: rat,
+ Geography: NullGeography{geo, true},
Record: &subNullable{X: NullInt64{4, true}},
},
- []Value{"x", []byte{1, 2, 3}, int64(1), 2.3, true, testTimestamp, testDate, ctm, cdt, rat, []Value{int64(4)}})
+ []Value{"x", []byte{1, 2, 3}, int64(1), 2.3, true, testTimestamp, testDate, ctm, cdt, rat, geo, []Value{int64(4)}})
}
func testInsertAndReadNullable(t *testing.T, ts testStructNullable, wantRow []Value) {
diff --git a/bigquery/nulls.go b/bigquery/nulls.go
index 5b4fbd9..bf3cb9d 100644
--- a/bigquery/nulls.go
+++ b/bigquery/nulls.go
@@ -41,6 +41,14 @@
func (n NullString) String() string { return nullstr(n.Valid, n.StringVal) }
+// NullGeography represents a BigQuery GEOGRAPHY string that may be NULL.
+type NullGeography struct {
+ GeographyVal string
+ Valid bool // Valid is true if GeographyVal is not NULL.
+}
+
+func (n NullGeography) String() string { return nullstr(n.Valid, n.GeographyVal) }
+
// NullFloat64 represents a BigQuery FLOAT64 that may be NULL.
type NullFloat64 struct {
Float64 float64
@@ -111,6 +119,9 @@
// MarshalJSON converts the NullString to JSON.
func (n NullString) MarshalJSON() ([]byte, error) { return nulljson(n.Valid, n.StringVal) }
+// MarshalJSON converts the NullGeography to JSON.
+func (n NullGeography) MarshalJSON() ([]byte, error) { return nulljson(n.Valid, n.GeographyVal) }
+
// MarshalJSON converts the NullTimestamp to JSON.
func (n NullTimestamp) MarshalJSON() ([]byte, error) { return nulljson(n.Valid, n.Timestamp) }
@@ -209,6 +220,20 @@
return nil
}
+// UnmarshalJSON converts JSON into a NullGeography.
+func (n *NullGeography) UnmarshalJSON(b []byte) error {
+ n.Valid = false
+ n.GeographyVal = ""
+ if bytes.Equal(b, jsonNull) {
+ return nil
+ }
+ if err := json.Unmarshal(b, &n.GeographyVal); err != nil {
+ return err
+ }
+ n.Valid = true
+ return nil
+}
+
// UnmarshalJSON converts JSON into a NullTimestamp.
func (n *NullTimestamp) UnmarshalJSON(b []byte) error {
n.Valid = false
@@ -290,6 +315,7 @@
typeOfNullFloat64 = reflect.TypeOf(NullFloat64{})
typeOfNullBool = reflect.TypeOf(NullBool{})
typeOfNullString = reflect.TypeOf(NullString{})
+ typeOfNullGeography = reflect.TypeOf(NullGeography{})
typeOfNullTimestamp = reflect.TypeOf(NullTimestamp{})
typeOfNullDate = reflect.TypeOf(NullDate{})
typeOfNullTime = reflect.TypeOf(NullTime{})
@@ -306,6 +332,8 @@
return BooleanFieldType
case typeOfNullString:
return StringFieldType
+ case typeOfNullGeography:
+ return GeographyFieldType
case typeOfNullTimestamp:
return TimestampFieldType
case typeOfNullDate:
diff --git a/bigquery/nulls_test.go b/bigquery/nulls_test.go
index 144e8e0..92a0b9c 100644
--- a/bigquery/nulls_test.go
+++ b/bigquery/nulls_test.go
@@ -37,6 +37,7 @@
{&NullFloat64{Valid: true, Float64: 3.14}, `3.14`},
{&NullBool{Valid: true, Bool: true}, `true`},
{&NullString{Valid: true, StringVal: "foo"}, `"foo"`},
+ {&NullGeography{Valid: true, GeographyVal: "ST_GEOPOINT(47.649154, -122.350220)"}, `"ST_GEOPOINT(47.649154, -122.350220)"`},
{&NullTimestamp{Valid: true, Timestamp: testTimestamp}, `"2016-11-05T07:50:22.000000008Z"`},
{&NullDate{Valid: true, Date: testDate}, `"2016-11-05"`},
{&NullTime{Valid: true, Time: nullsTestTime}, `"07:50:22.000001"`},
@@ -46,6 +47,7 @@
{&NullFloat64{}, `null`},
{&NullBool{}, `null`},
{&NullString{}, `null`},
+ {&NullGeography{}, `null`},
{&NullTimestamp{}, `null`},
{&NullDate{}, `null`},
{&NullTime{}, `null`},
diff --git a/bigquery/params.go b/bigquery/params.go
index 4ca6a67..868f2bb 100644
--- a/bigquery/params.go
+++ b/bigquery/params.go
@@ -101,6 +101,10 @@
// Arrays and slices of the above.
// Structs of the above. Only the exported fields are used.
//
+ // BigQuery does not support params of type GEOGRAPHY. For users wishing
+ // to parameterize Geography values, use string parameters and cast in the
+ // SQL query, e.g. `SELECT ST_GeogFromText(@string_param) as geo`
+ //
// When a QueryParameter is returned inside a QueryConfig from a call to
// Job.Config:
// Integers are of type int64.
diff --git a/bigquery/schema.go b/bigquery/schema.go
index e845476..3936aa4 100644
--- a/bigquery/schema.go
+++ b/bigquery/schema.go
@@ -131,6 +131,9 @@
// NumericFieldType is a numeric field type. Numeric types include integer types, floating point types and the
// NUMERIC data type.
NumericFieldType FieldType = "NUMERIC"
+ // GeographyFieldType is a string field type. Geography types represent a set of points
+ // on the Earth's surface, represented in Well Known Text (WKT) format.
+ GeographyFieldType FieldType = "GEOGRAPHY"
)
var (
@@ -151,6 +154,7 @@
TimeFieldType: true,
DateTimeFieldType: true,
NumericFieldType: true,
+ GeographyFieldType: true,
}
)
@@ -182,6 +186,9 @@
// A Go slice or array type is inferred to be a BigQuery repeated field of the
// element type. The element type must be one of the above listed types.
//
+// Due to lack of unique native Go type for GEOGRAPHY, there is no schema
+// inference to GEOGRAPHY at this time.
+//
// Nullable fields are inferred from the NullXXX types, declared in this package:
//
// STRING NullString
@@ -192,6 +199,7 @@
// DATE NullDate
// TIME NullTime
// DATETIME NullDateTime
+// GEOGRAPHY NullGeography
//
// For a nullable BYTES field, use the type []byte and tag the field "nullable" (see below).
// For a nullable NUMERIC field, use the type *big.Rat and tag the field "nullable".
diff --git a/bigquery/schema_test.go b/bigquery/schema_test.go
index e0eee7e..9eb0a39 100644
--- a/bigquery/schema_test.go
+++ b/bigquery/schema_test.go
@@ -170,6 +170,16 @@
},
},
{
+ bqSchema: &bq.TableSchema{
+ Fields: []*bq.TableFieldSchema{
+ bqTableFieldSchema("geo", "g", "GEOGRAPHY", ""),
+ },
+ },
+ schema: Schema{
+ fieldSchema("geo", "g", "GEOGRAPHY", false, false),
+ },
+ },
+ {
// nested
bqSchema: &bq.TableSchema{
Fields: []*bq.TableFieldSchema{
@@ -527,6 +537,7 @@
F NullTime
G NullDate
H NullDateTime
+ I NullGeography
}
func TestNullInference(t *testing.T) {
@@ -543,6 +554,7 @@
optField("F", "TIME"),
optField("G", "DATE"),
optField("H", "DATETIME"),
+ optField("I", "GEOGRAPHY"),
}
if diff := testutil.Diff(got, want); diff != "" {
t.Error(diff)
@@ -935,7 +947,8 @@
{"name":"flat_date","type":"DATE","mode":"NULLABLE","description":"Flat required DATE"},
{"name":"flat_time","type":"TIME","mode":"REQUIRED","description":"Flat nullable TIME"},
{"name":"flat_datetime","type":"DATETIME","mode":"NULLABLE","description":"Flat required DATETIME"},
- {"name":"flat_numeric","type":"NUMERIC","mode":"REQUIRED","description":"Flat nullable NUMERIC"}
+ {"name":"flat_numeric","type":"NUMERIC","mode":"REQUIRED","description":"Flat nullable NUMERIC"},
+ {"name":"flat_geography","type":"GEOGRAPHY","mode":"REQUIRED","description":"Flat required GEOGRAPHY"}
]`),
expectedSchema: Schema{
fieldSchema("Flat nullable string", "flat_string", "STRING", false, false),
@@ -948,6 +961,7 @@
fieldSchema("Flat nullable TIME", "flat_time", "TIME", false, true),
fieldSchema("Flat required DATETIME", "flat_datetime", "DATETIME", false, false),
fieldSchema("Flat nullable NUMERIC", "flat_numeric", "NUMERIC", false, true),
+ fieldSchema("Flat required GEOGRAPHY", "flat_geography", "GEOGRAPHY", false, true),
},
},
{
diff --git a/bigquery/value.go b/bigquery/value.go
index c4d32b8..3d0654b 100644
--- a/bigquery/value.go
+++ b/bigquery/value.go
@@ -170,6 +170,14 @@
return nil
}
+func setGeography(v reflect.Value, x interface{}) error {
+ if x == nil {
+ return errNoNulls
+ }
+ v.SetString(x.(string))
+ return nil
+}
+
func setBytes(v reflect.Value, x interface{}) error {
if x == nil {
v.SetBytes(nil)
@@ -289,6 +297,18 @@
}
}
+ case GeographyFieldType:
+ if ftype.Kind() == reflect.String {
+ return setGeography
+ }
+ if ftype == typeOfNullGeography {
+ return func(v reflect.Value, x interface{}) error {
+ return setNull(v, x, func() interface{} {
+ return NullGeography{GeographyVal: x.(string), Valid: true}
+ })
+ }
+ }
+
case BytesFieldType:
if ftype == typeOfByteSlice {
return setBytes
@@ -864,6 +884,8 @@
return nil, fmt.Errorf("bigquery: invalid NUMERIC value %q", val)
}
return Value(r), nil
+ case GeographyFieldType:
+ return val, nil
default:
return nil, fmt.Errorf("unrecognized type: %s", typ)
}
diff --git a/bigquery/value_test.go b/bigquery/value_test.go
index 9976dbb..ebd9e7f 100644
--- a/bigquery/value_test.go
+++ b/bigquery/value_test.go
@@ -36,6 +36,7 @@
{Type: BooleanFieldType},
{Type: BytesFieldType},
{Type: NumericFieldType},
+ {Type: GeographyFieldType},
}
row := &bq.TableRow{
F: []*bq.TableCell{
@@ -45,6 +46,7 @@
{V: "true"},
{V: base64.StdEncoding.EncodeToString([]byte("foo"))},
{V: "123.123456789"},
+ {V: testGeography},
},
}
got, err := convertRow(row, schema)
@@ -52,7 +54,7 @@
t.Fatalf("error converting: %v", err)
}
- want := []Value{"a", int64(1), 1.2, true, []byte("foo"), big.NewRat(123123456789, 1e9)}
+ want := []Value{"a", int64(1), 1.2, true, []byte("foo"), big.NewRat(123123456789, 1e9), testGeography}
if !testutil.Equal(got, want) {
t.Errorf("converting basic values: got:\n%v\nwant:\n%v", got, want)
}
@@ -404,6 +406,7 @@
{Name: "strField", Type: StringFieldType},
{Name: "dtField", Type: DateTimeFieldType},
{Name: "nField", Type: NumericFieldType},
+ {Name: "geoField", Type: GeographyFieldType},
},
InsertID: "iid",
Row: []Value{1, "a",
@@ -411,6 +414,7 @@
Date: civil.Date{Year: 1, Month: 2, Day: 3},
Time: civil.Time{Hour: 4, Minute: 5, Second: 6, Nanosecond: 7000}},
big.NewRat(123456789000, 1e9),
+ testGeography,
},
},
wantInsertID: "iid",
@@ -419,6 +423,7 @@
"strField": "a",
"dtField": "0001-02-03 04:05:06.000007",
"nField": "123.456789000",
+ "geoField": testGeography,
},
},
{
@@ -537,6 +542,8 @@
{Name: "p", Type: IntegerFieldType, Required: false},
{Name: "n", Type: NumericFieldType, Required: false},
{Name: "nr", Type: NumericFieldType, Repeated: true},
+ {Name: "g", Type: GeographyFieldType, Required: false},
+ {Name: "gr", Type: GeographyFieldType, Repeated: true},
}
type (
@@ -551,6 +558,8 @@
P NullInt64
N *big.Rat
NR []*big.Rat
+ G NullGeography
+ GR []string // Repeated Geography
}
)
@@ -584,6 +593,8 @@
P: NullInt64{Valid: true, Int64: 17},
N: big.NewRat(123456, 1000),
NR: []*big.Rat{big.NewRat(3, 1), big.NewRat(56789, 1e5)},
+ G: NullGeography{Valid: true, GeographyVal: "POINT(-122.350220 47.649154)"},
+ GR: []string{"POINT(-122.350220 47.649154)", "POINT(-122.198939 47.669865)"},
}
want := map[string]Value{
"s": "x",
@@ -595,10 +606,12 @@
"p": NullInt64{Valid: true, Int64: 17},
"n": "123.456000000",
"nr": []string{"3.000000000", "0.567890000"},
+ "g": NullGeography{Valid: true, GeographyVal: "POINT(-122.350220 47.649154)"},
+ "gr": []string{"POINT(-122.350220 47.649154)", "POINT(-122.198939 47.669865)"},
}
check("all values", in, want)
check("all values, ptr", &in, want)
- check("empty struct", T{}, map[string]Value{"s": "", "t": "00:00:00", "p": NullInt64{}})
+ check("empty struct", T{}, map[string]Value{"s": "", "t": "00:00:00", "p": NullInt64{}, "g": NullGeography{}})
// Missing and extra fields ignored.
type T2 struct {
@@ -613,6 +626,7 @@
"s": "",
"t": "00:00:00",
"p": NullInt64{},
+ "g": NullGeography{},
"rnested": []Value{map[string]Value{"b": true}, map[string]Value(nil), map[string]Value{"b": false}},
})
@@ -622,6 +636,7 @@
"s": "",
"t": "00:00:00",
"p": NullInt64{},
+ "g": NullGeography{},
})
}
@@ -677,6 +692,7 @@
{Type: IntegerFieldType},
{Type: FloatFieldType},
{Type: BooleanFieldType},
+ {Type: GeographyFieldType},
}
rows := []*bq.TableRow{
{F: []*bq.TableCell{
@@ -684,17 +700,19 @@
{V: "1"},
{V: "1.2"},
{V: "true"},
+ {V: "POINT(-122.350220 47.649154)"},
}},
{F: []*bq.TableCell{
{V: "b"},
{V: "2"},
{V: "2.2"},
{V: "false"},
+ {V: "POINT(-122.198939 47.669865)"},
}},
}
want := [][]Value{
- {"a", int64(1), 1.2, true},
- {"b", int64(2), 2.2, false},
+ {"a", int64(1), 1.2, true, "POINT(-122.350220 47.649154)"},
+ {"b", int64(2), 2.2, false, "POINT(-122.198939 47.669865)"},
}
got, err := convertRows(rows, schema)
if err != nil {
@@ -808,6 +826,7 @@
{Name: "T", Type: TimeFieldType},
{Name: "DT", Type: DateTimeFieldType},
{Name: "N", Type: NumericFieldType},
+ {Name: "G", Type: GeographyFieldType},
{Name: "nested", Type: RecordFieldType, Schema: Schema{
{Name: "nestS", Type: StringFieldType},
{Name: "nestI", Type: IntegerFieldType},
@@ -820,9 +839,11 @@
testTime = civil.Time{Hour: 7, Minute: 50, Second: 22, Nanosecond: 8}
testDateTime = civil.DateTime{Date: testDate, Time: testTime}
testNumeric = big.NewRat(123, 456)
+ // testGeography is a WKT string representing a single point.
+ testGeography = "POINT(-122.350220 47.649154)"
testValues = []Value{"x", "y", []byte{1, 2, 3}, int64(7), int64(8), 3.14, true,
- testTimestamp, testDate, testTime, testDateTime, testNumeric,
+ testTimestamp, testDate, testTime, testDateTime, testNumeric, testGeography,
[]Value{"nested", int64(17)}, "z"}
)
@@ -836,6 +857,7 @@
By []byte
F float64
N *big.Rat
+ G string
Nested nested
Tagged string `bigquery:"t"`
}
@@ -869,6 +891,7 @@
S2: "y",
By: []byte{1, 2, 3},
N: big.NewRat(123, 456),
+ G: testGeography,
Nested: nested{NestS: "nested", NestI: 17},
Tagged: "z",
}
@@ -969,6 +992,7 @@
Time NullTime
DateTime NullDateTime
Numeric *big.Rat
+ Geography NullGeography
Record *subNullable
}
@@ -987,6 +1011,7 @@
{Name: "Time", Type: TimeFieldType, Required: false},
{Name: "DateTime", Type: DateTimeFieldType, Required: false},
{Name: "Numeric", Type: NumericFieldType, Required: false},
+ {Name: "Geography", Type: GeographyFieldType, Required: false},
{Name: "Record", Type: RecordFieldType, Required: false, Schema: Schema{
{Name: "X", Type: IntegerFieldType, Required: false},
}},
@@ -1002,7 +1027,7 @@
}
nonnilVals := []Value{"x", []byte{1, 2, 3}, int64(1), 2.3, true, testTimestamp, testDate, testTime,
- testDateTime, big.NewRat(1, 2), []Value{int64(4)}}
+ testDateTime, big.NewRat(1, 2), testGeography, []Value{int64(4)}}
// All ts fields are nil. Loading non-nil values will cause them all to
// be allocated.
@@ -1018,6 +1043,7 @@
Time: NullTime{Time: testTime, Valid: true},
DateTime: NullDateTime{DateTime: testDateTime, Valid: true},
Numeric: big.NewRat(1, 2),
+ Geography: NullGeography{GeographyVal: testGeography, Valid: true},
Record: &subNullable{X: NullInt64{Int64: 4, Valid: true}},
}
if diff := testutil.Diff(ts, want); diff != "" {
@@ -1027,7 +1053,7 @@
// Struct pointers are reused, byte slices are not.
want = ts
want.Bytes = []byte{17}
- vals2 := []Value{nil, []byte{17}, nil, nil, nil, nil, nil, nil, nil, nil, []Value{int64(7)}}
+ vals2 := []Value{nil, []byte{17}, nil, nil, nil, nil, nil, nil, nil, nil, nil, []Value{int64(7)}}
mustLoad(t, &ts, testStructNullableSchema, vals2)
if ts.Record != want.Record {
t.Error("record pointers not identical")
