Basic BigQuery with Go

BigQuery.go

package main

import (
	"cloud.google.com/go/bigquery"
	"context"
	"fmt"
	"google.golang.org/api/iterator"
	"log"
	"math/rand"
	"os"
	"time"
)

// Item represents a row item.
type Item struct {
	FullName string `bigquery:"full_name" json:"full_name"`
	Age      int    `bigquery:"age" json:"name"`
}

func main() {

	log.Printf("Logging in...")
	// Point to the service account file
	os.Setenv("GOOGLE_APPLICATION_CREDENTIALS", "UniteaBQ-8c67413e1cd5.json")

	// Details on the project ID and Number
	projectID := "uniteabq"
	//projectNumber := "753727166645"

	log.Println("Setting up the client for the current project", projectID)

	// Create an empty context
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		log.Println("Fucked up!:")
		log.Fatal(err)
	}

	// Show the client, just a pointer
	log.Println(client)

	log.Println("Making a query over a public dataset...")

	// Perform a query over a dataset
	q := client.Query(`
    SELECT year, SUM(number) as num
    FROM ` + "`bigquery-public-data.usa_names.usa_1910_2013`" + `
    WHERE name = "Daniel"
    GROUP BY year
    ORDER BY year
`)

	// Read the results
	// by generating an iterator
	it, err := q.Read(ctx)
	if err != nil {
		log.Println(err)
		// TODO: Handle error.
	}

	// Iterate through the results
	for {
		var values []bigquery.Value
		err := it.Next(&values)
		if err == iterator.Done {
			break
		}
		if err != nil {
			log.Fatal(err)
		}
		log.Println(values)
	}

	// Create the dataset
	datasetID := "testingDataset1"

	log.Println("Attempting to create a dataset", datasetID)

	metaDataset := &bigquery.DatasetMetadata{
		Location: "US", // Create the dataset in the US.
	}

	if err := client.Dataset(datasetID).Create(ctx, metaDataset); err != nil {
		log.Println(err)
	}

	// Prepare a dataset iterator
	dataSetIt := client.Datasets(ctx)
	for {
		dataset, err := dataSetIt.Next()
		if err == iterator.Done {
			break
		}
		log.Println(dataset.DatasetID)
	}

	log.Println("Attempting to create a table schema")
	// Now,  create a table schema
	myTableSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType},
		{Name: "age", Type: bigquery.IntegerFieldType},
	}

	log.Println(myTableSchema)

	// Set a name of the table
	tableID := "TableForNewSchema"

	metaData := &bigquery.TableMetadata{
		Schema:         myTableSchema,
		ExpirationTime: time.Now().AddDate(0, 0, 5), // Table will be automatically deleted in 5 days.
	}

	log.Println("Writing some data entries to the table")

	// Now,  create the table
	tableRef := client.Dataset(datasetID).Table(tableID)

	if err := tableRef.Create(ctx, metaData); err != nil {
		log.Println(err)
	}

	log.Println("Let us list our tables and views(which also are like tables)")

	// List the tables in the dataset. This also lists views
	// We can check that the table was created
	ts := client.Dataset(datasetID).Tables(ctx)
	for {
		t, err := ts.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			log.Println(err)
		}
		log.Printf("Table: %q\n", t.TableID)
	}

	// Now, we can browse all the table data.
	// We will need this if what follows.
	table := client.Dataset(datasetID).Table(tableID)

	r := rand.New(rand.NewSource(time.Now().UnixNano()))

	//Lets create an uploader
	// An option is to use the line
	//u := client.Dataset(datasetID).Table(tableID).Uploader()
	// but given we already declare our Table `table`
	u := table.Uploader()
	items := []*Item{
		// Item implements the ValueSaver interface.
		{FullName: "Alfred", Age: r.Intn(100)},
		{FullName: "Bruce", Age: r.Intn(100)},
		{FullName: "Jason", Age: r.Intn(100)},
		{FullName: "Gordon", Age: r.Intn(100)},
		{FullName: "Barbara", Age: r.Intn(100)},
		{FullName: "Harvey", Age: r.Intn(100)},
	}

	if err := u.Put(ctx, items); err != nil {
		log.Println(err)
	}

	tableIt := table.Read(ctx)
	for {
		var row []bigquery.Value
		err := tableIt.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			log.Println(err)
		}
		log.Println(row)
	}

	viewTableID := "associatedTableView"
	log.Println("Now, lets create a view called", viewTableID)
	// Now, lets create a view
	// The view is created by performing a SQL query over the dataset [PROJECT_ID].[DATASET].[TABLE]
	// Here the queryText
	queryText := fmt.Sprintf("SELECT full_name, age FROM `%s.%s.%s` WHERE age > 25", projectID, datasetID, tableID)

	metaTableMetadata := &bigquery.TableMetadata{
		ViewQuery: queryText,
	}
	if err := client.Dataset(datasetID).Table(viewTableID).Create(ctx, metaTableMetadata); err != nil {
		log.Println(err)
	}

	log.Println("Now lets look at our recently created view...")

	view := client.Dataset(datasetID).Table(viewTableID)
	meta, err := view.Metadata(ctx)
	if err != nil {
		log.Println(err)
	}

	fmt.Printf("View %s, query: %s\n", view.FullyQualifiedName(), meta.ViewQuery)

}