dongyou9373
dongyou9373
2015-05-22 11:42

使用Golang将嵌套数据插入BigQuery

已采纳

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dqsw7529 dqsw7529 6年前

    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

    点赞 评论 复制链接分享