Query a public dataset with the BigQuery Go client library

Query a public dataset with the BigQuery Go client library

Learn how to:

  1. Activate Cloud Shell in a Google Cloud project.
  2. Open the Cloud Shell Editor.
  3. Prepare files for queries.
  4. Query a public dataset in BigQuery.
  5. Clean up.

Estimated time to complete:

Click Start to begin.

Activate Cloud Shell in a Google Cloud project

  1. If you do not enable billing for a project, you automatically work in the BigQuery sandbox. The BigQuery sandbox lets you learn BigQuery with a limited set of BigQuery features at no charge. If you do not plan to use your project beyond this document, we recommend that you use the BigQuery sandbox.

  3. Click Activate Cloud Shell. Show me

To learn how to open the Cloud Shell Editor, click Next.

Open the Cloud Shell Editor

  1. In Cloud Shell, create a new Go project and file:

    mkdir bigquery-go-quickstart \
    && touch \
    bigquery-go-quickstart/app.go

    This command creates a Go project that's named bigquery-go-quickstart and a file that's named app.go.

  2. Open the Cloud Shell Editor:

    cloudshell workspace bigquery-go-quickstart

To learn how to prepare files for queries, click Next.

Prepare files for queries

  1. To open a terminal in the Cloud Shell Editor, click Open Terminal.

  2. Open your project directory:

    cd bigquery-go-quickstart

  3. Create a go.mod file:

    go mod init quickstart

    The output is similar to the following:

    go: creating new go.mod: module quickstart
go: to add module requirements and sums:
        go mod tidy

  4. Install the BigQuery client library for Go:

    go get cloud.google.com/go/bigquery

    The output is similar to the following. Several lines are omitted to simplify the output.

    go: downloading cloud.google.com/go/bigquery v1.49.0
go: downloading cloud.google.com/go v0.110.0
...
go: added cloud.google.com/go/bigquery v1.49.0
go: added cloud.google.com/go v0.110.0

To learn how to query a public dataset in BigQuery, click Next.

Query a public dataset in BigQuery

  1. Click Open Editor.

  2. In the Explorer pane, locate your BIGQUERY-GO-QUICKSTART project.

  3. Click the app.go file to open it.

  4. To create a query against the bigquery-public-data.stackoverflow dataset that returns the top 10 most viewed Stack Overflow pages and their view counts, copy the following code into the app.go file:

    
// Command simpleapp queries the Stack Overflow public dataset in Google BigQuery.
package main

import (
	"context"
	"fmt"
	"io"
	"log"
	"os"

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


func main() {
	projectID := os.Getenv("GOOGLE_CLOUD_PROJECT")
	if projectID == "" {
		fmt.Println("GOOGLE_CLOUD_PROJECT environment variable must be set.")
		os.Exit(1)
	}

	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		log.Fatalf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	rows, err := query(ctx, client)
	if err != nil {
		log.Fatal(err)
	}
	if err := printResults(os.Stdout, rows); err != nil {
		log.Fatal(err)
	}
}

// query returns a row iterator suitable for reading query results.
func query(ctx context.Context, client *bigquery.Client) (*bigquery.RowIterator, error) {

	query := client.Query(
		`SELECT
			CONCAT(
				'https://stackoverflow.com/questions/',
				CAST(id as STRING)) as url,
			view_count
		FROM ` + "`bigquery-public-data.stackoverflow.posts_questions`" + `
		WHERE tags like '%google-bigquery%'
		ORDER BY view_count DESC
		LIMIT 10;`)
	return query.Read(ctx)
}

type StackOverflowRow struct {
	URL       string `bigquery:"url"`
	ViewCount int64  `bigquery:"view_count"`
}

// printResults prints results from a query to the Stack Overflow public dataset.
func printResults(w io.Writer, iter *bigquery.RowIterator) error {
	for {
		var row StackOverflowRow
		err := iter.Next(&row)
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return fmt.Errorf("error iterating through results: %w", err)
		}

		fmt.Fprintf(w, "url: %s views: %d\n", row.URL, row.ViewCount)
	}
}

  5. Click Open Terminal.

  6. In the terminal, run the app.go script. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.

    go run app.go

    The result is similar to the following:

    https://stackoverflow.com/questions/35159967 : 170023 views
https://stackoverflow.com/questions/22879669 : 142581 views
https://stackoverflow.com/questions/10604135 : 132406 views
https://stackoverflow.com/questions/44564887 : 128781 views
https://stackoverflow.com/questions/27060396 : 127008 views
https://stackoverflow.com/questions/12482637 : 120766 views
https://stackoverflow.com/questions/20673986 : 115720 views
https://stackoverflow.com/questions/39109817 : 108368 views
https://stackoverflow.com/questions/11057219 : 105175 views
https://stackoverflow.com/questions/43195143 : 101878 views

You have successfully queried a public dataset with the BigQuery Go client library.

To avoid incurring charges to your account and learn about next steps, click Next.

Next steps

Keep the resources that you created and do more with BigQuery, or clean up to avoid billing charges.

Do more with BigQuery

Clean up

To avoid incurring charges to your Google Cloud account, either delete your Google Cloud project, or delete the resources that you created in this walkthrough.

Delete the project

If you created a new project to learn about BigQuery and you no longer need the project, delete it. Be aware that deleting a project deletes everything in the project and custom project IDs are lost.

Delete the resources

If you used an existing project, delete the bigquery-go-quickstart folder that you created:

  1. In Cloud Shell, move up a directory:

    cd ..

  2. Delete the resources that you created:

    rm -R bigquery-go-quickstart

    The -R flag deletes all assets in a folder.