C# parse object into Google BigQuery row

Question

I'm very new with Google Big Query and I have a hard time inserting a row into a table.

For example I have a table:

iso2 [string|required]
names [record|required]
names.name [string|required]
names.language [string|required]
names.official [boolean|required]

And I have a class:

class Country{
   string Iso2 {get;set;}
   List<CountryName> Names {get;set;}

   class CountryName{
      string Name {get;set;}
      string Language {get;set;}
      bool Official {get;set;
   }
}

And I create a country

var country = new Country(){
   Iso2 = "nl",
   Names = new Country.CountryName(){
      Name = "Nederland",
      Language = "nl",
      Official = true
   }
}

BigQueryClient client = BigQueryClient.Create(projectId);
//How do I insert this country into the table?

So how do I convert an object so it can be inserted into Google BigQuery

You need to perform that conversion yourself. There's an example of insertion at googleapis.dev/dotnet/Google.Cloud.BigQuery.V2/latest/…. Have you tried creating a BigQueryInsertRow based on your object? What happened? Have you already created the table? What does the schema look like? — Jon Skeet
@JonSkeet Yes. I can use var json = JsonConvert.SerializeObject(country); var dictionary = JsonConvert.DeserializeObject<Dictionary<string, object>>(json); var row = new BigQueryInsertRow(); That works fine for normal columns. Except for record columns. — NLAnaconda
I probably wouldn't go via JSON to be honest. It shouldn't be too hard to write your own converter, which should be able to handle record columns (I believe) - have you tried that? — Jon Skeet

rmesteves rmesteves · Accepted Answer · 2020-02-13T09:10:44

You can find here some official snippets of C# code regarding BigQuery SDK. In the file TableInsertRows.cs you can find the following example:

using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryTableInsertRows
{
    public void TableInsertRows(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id",
        string tableId = "your_table_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        BigQueryInsertRow[] rows = new BigQueryInsertRow[]
        {
            // The insert ID is optional, but can avoid duplicate data
            // when retrying inserts.
            new BigQueryInsertRow(insertId: "row1") {
                { "name", "Washington" },
                { "post_abbr", "WA" }
            },
            new BigQueryInsertRow(insertId: "row2") {
                { "name", "Colorado" },
                { "post_abbr", "CO" }
            }
        };
        client.InsertRows(datasetId, tableId, rows);
    }
}

I hope it helps

C# parse object into Google BigQuery row

1 Answers