Skip to content

Instantly share code, notes, and snippets.

@santiago

santiago/BigQuery.go

Created April 9, 2019 15:55
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save santiago/fe88e4ee7647512c651918e97eb54d9c to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save santiago/fe88e4ee7647512c651918e97eb54d9c to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. santiago created this gist Apr 9, 2019.
    202 changes: 202 additions & 0 deletions BigQuery.go
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,202 @@
    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)

    }