4

I can insert a flat object into BigQuery using Golang - how I can insert nested data into a table?

My BigQuery schema looks like this (from the example):

[{
    "name": "kind",
    "mode": "nullable",
    "type": "string"
  },
  {
    "name": "fullName",
    "type": "string",
    "mode": "required"
  },
  { "name": "visit",
    "type": "record",
    "mode": "repeated",
    "fields": [
    {
       "name": "time",
       "type": "timestamp",
       "mode": "nullable"
    },
    {
       "name": "duration",
       "type": "integer",
       "mode": "nullable"
    }
   ]
  }
]

My first attempt to insert looked like this (example):

func ExampleInsert(f string,) {

  jsonRow := make(map[string]bigquery.JsonValue)

  bq, _ := bigquery.New(client)
  request := new(bigquery.TableDataInsertAllRequest)

  rows := make([]*bigquery.TableDataInsertAllRequestRows, 1)

  jsonRow["kind"] = bigquery.JsonValue(kind)
  jsonRow["visit_duration"] = bigquery.JsonValue(duration)

  rows[i] = new(bigquery.TableDataInsertAllRequestRows)
  rows[i].Json = jsonRow

  bq.Tabledata.InsertAll(projectID, "visits", "visitsv4", request)
  ...
}

Which flattens and inserts without problems. I'm just using visit_duration

But, I need to loop through a slice and add to the visits record. I've tried to build a visit object (without a loop to test) and add that to the row but it's not inserting and I get no errors:

func ExampleInsert(f string,) {

  jsonRow := make(map[string]bigquery.JsonValue)

  bq, _ := bigquery.New(client)
  request := new(bigquery.TableDataInsertAllRequest)

  rows := make([]*bigquery.TableDataInsertAllRequestRows, 1)

  jsonRow["kind"] = bigquery.JsonValue(kind)

  visits := make([]*bigquery.TableDataInsertAllRequestRows, 1)

  jsonVisit := make(map[string]bigquery.JsonValue)
  jsonVisit["duration"] = rand.Intn(1000)
  visits[0] = new(bigquery.TableDataInsertAllRequestRows)
  visits[0].Json = jsonVisit

  jsonRow["visit"] = visits

  rows[i] = new(bigquery.TableDataInsertAllRequestRows)
  rows[i].Json = jsonRow

  bq.Tabledata.InsertAll(projectID, "visits", "visitsv4", request)

  _, err := Call.Do()
}

---[SOLUTION]----

As suggested in the comments, I have also tried creating a slice and then appending the visit:

var visits []bigquery.JsonValue
visit := make(map[string]bigquery.JsonValue)
visit["duration"] = rand.Intn(100)
visits = append(visits, visit)

jsonRow["visit"] = visits

I can confirm this does in fact work :) For those of you reading this, the reason it wasn't initially, even after adding a slice, was because I had copied the table. In doing so, I had also flattened the results. Watch out.

13
  • I am not a Golang expert, but visits should be a map of bigquery.JsonValue Commented May 22, 2015 at 12:01
  • Could you give an example? Commented May 22, 2015 at 12:08
  • visits := make(map[string]bigquery.JsonValue) Commented May 22, 2015 at 12:12
  • I am not sure why you have used: TableDataInsertAllRequestRows that should be only used one time for the payload descriptor. Commented May 22, 2015 at 12:13
  • And you are reusing the wrong i value also, i is different for rows, and a different must be on multiple visits. Commented May 22, 2015 at 12:14

1 Answer 1

2

Visits should be a slice of bigquery.JsonValue I am not sure why you have used: TableDataInsertAllRequestRows that should be only used one time for the payload descriptor.

var visits []bigquery.JsonValue
visit := make(map[string]bigquery.JsonValue)
visit["duration"] = rand.Intn(100)
visits = append(visits, visit)

jsonRow["visit"] = visits

ps. also make sure you have your schema flatten

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.