This is an implementation of the BigQuery Client as a database/sql/driver for easy integration and usage.
This module implements a BigQuery SQL driver and GORM dialect.
As this is using the Google Cloud Go SDK, you will need to have your credentials available via the GOOGLE_APPLICATION_CREDENTIALS environment variable point to your credential JSON file.
Just like any other database/sql driver you'll need to import it
package main
import (
"database/sql"
_ "github.com/juaismar/bigqueryCustom/driver"
"log"
)
func main() {
db, err := sql.Open("bigquery",
"bigquery://projectid/dataset")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// Do Something with the DB
}Opening a Gorm bigquery db
package main
import (
"github.com/juaismar/bigqueryCustom"
"gorm.io/gorm"
"log"
)
func main() {
// You can also use the location format: "bigquery://projectid/location/dataset"
db, err := gorm.Open(bigquery.Open("bigquery://go-bigquery-driver/playground"), &gorm.Config{})
if err != nil {
log.Fatal(err)
}
defer db.Close()
// Do Something with the DB
}Using gorm with a BigQuery query that has a record
package main
import (
"github.com/juaismar/bigqueryCustom"
"gorm.io/gorm"
"log"
)
type ComplexRecord struct {
Name string `gorm:"column:Name"`
Record ComplexSubRecord `gorm:"column:Record:type:RECORD"`
}
type ComplexSubRecord struct {
Name string `gorm:"column:Name"`
Age int `gorm:"column:Age"`
}
func main() {
db, err := gorm.Open(bigquery.Open("bigquery://go-bigquery-driver/playground"), &gorm.Config{})
if err != nil {
log.Fatal(err)
}
var records []ComplexRecord
// Delete complex record table if exists
db.Migrator().DropTable(&ComplexRecord{})
// Make sure we have a complex_records table
db.AutoMigrate(&ComplexRecord{})
// Insert new records to table
db.Create(&ComplexRecord{Name: "test", Record: ComplexSubRecord{Name: "dd", Age: 1}})
db.Create(&ComplexRecord{Name: "test2", Record: ComplexSubRecord{Name: "dd2", Age: 444}})
// Select records from table
db.Order("Name").Find(&records)
}Using gorm with a BigQuery query that has an array
package main
import (
"github.com/juaismar/bigqueryCustom"
"gorm.io/gorm"
"log"
)
type ArrayRecord struct {
Name string `gorm:"column:Name"`
Records []ComplexSubRecord `gorm:"column:Records;type:ARRAY"`
}
type ComplexSubRecord struct {
Name string `gorm:"column:Name"`
Age int `gorm:"column:Age"`
}
func main() {
db, err := gorm.Open(bigquery.Open("bigquery://go-bigquery-driver/playground"), &gorm.Config{})
if err != nil {
log.Fatal(err)
}
var records []ArrayRecord
// Delete array_records table if exists
db.Migrator().DropTable(&ArrayRecord{})
// Make sure we have an array_records table
db.AutoMigrate(&ArrayRecord{})
// Insert new records to table
db.Create(&ArrayRecord{Name: "test", Records: []ComplexSubRecord{{Name: "dd", Age: 1}, {Name: "dd1", Age: 1}}})
db.Create(&ArrayRecord{Name: "test2", Records: []ComplexSubRecord{{Name: "dd2", Age: 444}, {Name: "dd3", Age: 1}}})
// Select records from table ordered by name
db.Order("Name").Find(&records)
}Using gorm with a BigQuery query that uses unnest
package main
import (
"github.com/juaismar/bigqueryCustom"
"gorm.io/gorm"
"log"
)
type Version struct {
Label string `gorm:"column:Label"`
}
func main() {
db, err := gorm.Open(bigquery.Open("bigquery://go-bigquery-driver/playground"), &gorm.Config{})
if err != nil {
log.Fatal(err)
}
var versions []Version
query := db.Table("charts, UNNEST(Samples) as sample")
query = query.Select("DISTINCT CONCAT(" +
"CAST(sample.MajorVersion AS STRING), '.'," +
"CAST(sample.MinorVersion AS STRING), '.'," +
"CAST(sample.RevisionVersion AS STRING)" +
") as Label")
err = query.Find(&versions).Error
if err != nil {
log.Fatal(err)
}
}