Insert row in BigQuery having repeated record column in C#

Question

I have a C# code which inserts a row into BigQuery. But the table in BigQuery has a record field. How should I insert the data into that table?

BigQuery Table:
column1 | column2 | column3.a | column3.b | column4

*column3 is a RECORD having multiple rows of "a" and "b"

This is what I have got so far in my C# code:

BigQueryClient BQclient = BigQueryClient.Create(project_id, credentials);
var table = BQclient.GetDataset("dataset").GetTable("table");
List < BigQueryInsertRow > rows = new List < BigQueryInsertRow >();
try {
      for (int i=0; i < data.Count; i++ ) {
          BigQueryInsertRow insertRow = new BigQueryInsertRow();
          insertRow.Add("column1", data[i].column1);
          insertRow.Add("column2", data[i].column2);

↓↓↓ this is where I am having a hard time ↓↓↓

          for (int j=0; j < data[i].column3.Count; j++ ) {
              insertRow.Add("colum3.a", data[i].column3[j].a);
              insertRow.Add("colum3.b", data[i].column3[j].b);
          }

↑↑↑ this is where I am having a hard time ↑↑↑

          insertRow.Add("column4"), data[i].column4);
          rows.Add(insertRow);
      }
      table.InsertRows(rows);
}
catch (Exception e) {
    throw e;
}

I'm pretty sure I know what the problem is here, but I want to ask a couple of clarification questions first. 1) What kind of object is table? Is it a DataTable? 2) Can we see the definition of BigQueryInsertRow? — Casey Crookston
'table' is the BigQuery table name where data rows will be inserted into. BigQueryInsertRow is a class. Its definition can be found here: googleapis.github.io/google-cloud-dotnet/docs/… — Catherine Idul
ok, thanks. And the code you are having trouble with... does it throw an error? If so, what is the error? — Casey Crookston
Did you try adding it as-is? i.e. insertRow.Add("column3", data[i].column3)? — khan

Catherine Idul Catherine Idul · Accepted Answer · 2019-10-09T07:13:18

After playing around with it, I was able to insert the repeated data into my insertRow.
Here is what I did:

var temp = new BigQueryRow();
var list_data = new List<BigQueryInsertRow>();
for (int j=0; j<data[i].column3.Count; j++)
{
   temp = new BigQueryRow();
   temp.Add("a", data[i].column3[j].a);
   temp.Add("b", data[i].column3[j].b);
   list_data.Add(temp);
}
insertRow.Add("column3", list_data);

Apparently, each column in a record should be named by its own column name without attaching the parent column name which is column3.

Insert row in BigQuery having repeated record column in C#

1 Answers