Golang and BigQuery sample codes

000_index.md

Index

• Create and Delete Dataset
• Create and Delete Table
• Show Table info
• Put into Table
• Exec query
• Exec query and insert result

100_common.go

func GetBQClient() *bigquery.Client {
  ctx := context.Background()

  projectID := "your-project-id"
 
  client, err := bigquery.NewClient(ctx, projectID)
  if err != nil {
    log.Fatalf("Failed to create client: %v", err)
  }
 
  return client
}

101_create_and_delete_dataset.go

package main
  
// Sample to create/delete BigQuery dataset
// https://cloud.google.com/bigquery/docs/reference/libraries?hl=ja

import (
  "fmt"
  "log"

  "cloud.google.com/go/bigquery"
  "golang.org/x/net/context"
)

func main() {
  ctx := context.Background()

  client := GetBQClient()
 
  datasetName := "hamakn_dataset"

  // create dataset
  if err := client.Dataset(datasetName).Create(ctx, &bigquery.DatasetMetadata{}); err != nil {
    log.Fatalf("Failed to create dataset: %v", err)
  }

  // delete dataset
  if err := client.Dataset(datasetName).Delete(ctx); err != nil {
    log.Fatalf("Failed to delete dataset: %v", err)
  }

  fmt.Println("DONE!!")
}

102_create_and_delete_table.go

package main

import (
  "fmt"
  "log"

  "cloud.google.com/go/bigquery"
  "golang.org/x/net/context"
)

type MySchema struct {
  ID   int    `bigquery:"id"`
  Name string `bigquery:"name"`
} 

func main() {
  client := GetBQClient()
 
  datasetName := "hamakn_dataset"
 
  ctx := context.Background()
 
  schema, err := bigquery.InferSchema(MySchema{})
  fmt.Println(schema)
  if err != nil {
    log.Fatalf("Failed to infer schema: %v", err)
  }

  tableMetadata := bigquery.TableMetadata{
    Schema:           schema,
    TimePartitioning: &bigquery.TimePartitioning{Expiration: 0},
  }

  table := client.Dataset(datasetName).Table("bygolang")
  if err := table.Create(ctx, &tableMetadata); err != nil {
    log.Fatalf("Failed to create table: %v", err)
  }

  if err := table.Delete(ctx); err != nil {
    log.Fatalf("Failed to delete table: %v", err)
  }
}

103_show_table_info.go

package main

import (
  "fmt"
  "log"
  "strconv"

  "cloud.google.com/go/bigquery"
  "golang.org/x/net/context"
  "google.golang.org/api/iterator"
)

func printTableMetadata(t *bigquery.Table) {
  ctx := context.Background()

  fmt.Println("== print Table ==")
  fmt.Printf("TableID: %v\n", t.TableID)
  metadata, _ := t.Metadata(ctx)
  fmt.Printf("Metadata.Name: %v\n", metadata.Name)
  fmt.Printf("Metadata.Description: %v\n", metadata.Description)
  fmt.Printf("Metadata.Schema: %v\n", metadata.Schema)
  schema := metadata.Schema
  for i, fieldschema := range schema {
    fmt.Println("  -- fieldschema: " + strconv.Itoa(i) + " --")
    fmt.Printf("  Name: %v\n", fieldschema.Name)
    fmt.Printf("  Type: %v\n", fieldschema.Type)
  }
  timePartitioning := metadata.TimePartitioning
  if timePartitioning != nil {
    fmt.Printf("Metadata.TimePartitioning: %v\n", timePartitioning)
  }
  fmt.Printf("Metadata.UseLegacySQL: %v\n", metadata.UseLegacySQL)
  fmt.Println("==")
  fmt.Println("")
}

func main() {
  client := GetBQClient()

  datasetName := "hamakn_dataset"

  ctx := context.Background()

  // Get Tables
  tables := client.Dataset(datasetName).Tables(ctx)
  for {
    t, err := tables.Next()
    if err == iterator.Done {
      break
    }
    if err != nil {
      fmt.Println(err)
      return
    }
    printTableMetadata(t)
  }

  // Get Table
  t := client.Dataset(datasetName).Table("bygolang")
  printTableMetadata(t)
}

104_put_into_table.go

package main
  
import (
  "fmt"
  "log"
  "strconv"
  "time"

  "cloud.google.com/go/bigquery"
  "golang.org/x/net/context"
)

func main() {
  client := GetBQClient()

  datasetName := "hamakn_dataset"

  ctx := context.Background()

  schema, err := bigquery.InferSchema(MySchema{})
  if err != nil {
    log.Fatalf("Failed to infer schema: %v", err)
  }

  ss := bigquery.StructSaver{
    Schema: schema,
    Struct:   &MySchema{ID: 1, Name: "hamakn"},
    InsertID: strconv.Itoa(1),
  }

  table := client.Dataset(datasetName).Table("bygolang$20180301")
  if err := table.Uploader().Put(ctx, &ss); err != nil {
    log.Fatalf("Failed to put: %v", err)
  }
}

105_exec_query.go

package main

import (
  "fmt"
  "log"
   
  "cloud.google.com/go/bigquery"
  "golang.org/x/net/context"
  "google.golang.org/api/iterator"
)

var query = `
select
  *
from
  hamakn_dataset.bygolang
where
  _PARTITIONTIME = "2018-03-01 00:00:00"
`

func main() {
  client := GetBQClient()

  ctx := context.Background()

  // exec query
  query := client.Query(query)
  query.QueryConfig.UseStandardSQL = true
  rows, _ := query.Read(ctx)

  // wait and print query result
  for {
    var row []bigquery.Value
    err := rows.Next(&row)
    if err == iterator.Done {
      break
    }
    if err != nil {
      fmt.Printf("error!: %v\n", err)
    }

    fmt.Println(row)
  }
}

106_exec_query_and_insert_result.go

package main
  
import (
  "fmt"
  "log"
 
  "cloud.google.com/go/bigquery"
  "golang.org/x/net/context"
  "google.golang.org/api/iterator"
)

var query = `
select
  user_id
from
  ` + "`somedataset.sometable$20180228`" + `
where
  user_id != ""
group by
  user_id
`

func main() {
  client := GetBQClient()

  datasetName := "hamakn_dataset"
  ctx := context.Background()

  // query
  query := client.Query(query)
  query.QueryConfig.UseStandardSQL = true
  query.Dst = client.Dataset(datasetName).Table("user_access_dates$20180228")
  // 上書きする場合
  query.WriteDisposition = bigquery.WriteTruncate
  // // 追加する場合
  // query.WriteDisposition = bigquery.WriteAppend

  rows, err := query.Read(ctx)

  // wait query
  for {
    var row []bigquery.Value
    err := rows.Next(&row)
    if err == iterator.Done {
      break
    }
    if err != nil {
      fmt.Printf("error!: %v\n", err)
    }
  }
}