Google BigQuery API Client Sample Code for C#.NET

Introduction

I am trying to stream data to Google Big Query (BQ) but found no useful example code for developer using .NET Client library (https://developers.google.com/api-client-library/dotnet/apis/bigquery/v2).

After I went through some pain and dug inside the code, I have successfully written the script to stream data to BQ.

Pre-requisite

Get ready, sign up for GB

https://developers.google.com/bigquery/sign-up

**** NOTE****

User service account for this example

1. https://developers.google.com/bigquery/authorization#service-accounts-server
2. Make sure you generated a PKCS12 (p12) certificate file for your application.

Install BQ client library from nuGet

https://www.nuget.org/packages/Google.Apis.Bigquery.v2/

Using the Code

I have created the following sample:

1. Read Dataset and Data Table
2. Read Rows in the Table
3. Batch insert rows into GB

var ApplicationName = "APP1";       // GB Application Name
var ProjectID = "PROJECT1";         // GB Project ID
var DataSet = "DS1";                // GB DataSet ID
var TableName = "TBL1";             // GB Table ID

// Refer To https://developers.google.com/bigquery/authorization#service-accounts-server
var serviceAccountEmail = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX@developer.gserviceaccount.com";
var PrivateKeyFileName = @"XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX-privatekey.p12";
var PrivateKeyPassword = "notasecret";
var certificate = new X509Certificate2(PrivateKeyFileName, PrivateKeyPassword, X509KeyStorageFlags.Exportable);

//==============================================================================================
#region Setup credential
ServiceAccountCredential credential = new ServiceAccountCredential(
   new ServiceAccountCredential.Initializer(serviceAccountEmail)
   {
       Scopes = new[] { BigqueryService.Scope.Bigquery }
   }.FromCertificate(certificate));
#endregion
//==============================================================================================

//==============================================================================================
#region Create the service.
var service = new BigqueryService(new BaseClientService.Initializer()
{
    HttpClientInitializer = credential,
    ApplicationName = ApplicationName
});
#endregion
//==============================================================================================

//==============================================================================================
#region Read Dataset and DataTable

var datasetRequest = service.Datasets.List(ProjectID);
DatasetList datasetList = datasetRequest.Execute();

// Get list of Dataset
foreach (var item in datasetList.Datasets)
{
    var ProjectDatasetID = item.Id;
    
    // Project : DataSetID
    Console.WriteLine(ProjectDatasetID);
    
    // Get list of tables
    var tablelist = service.Tables.List(ProjectDatasetID.Split(':')[0], 
    ProjectDatasetID.Split(':')[1]).Execute().Tables;
    foreach (var tbl in tablelist)
    {
        Console.WriteLine(tbl.Id);
    }
}
#endregion
//==============================================================================================

//==============================================================================================
// Read Record
var rows = service.Tabledata.List(ProjectID, DataSet, TableName).Execute().Rows.Take(100);

foreach (var item in rows)
{
    Console.WriteLine(item.F[0].V + " " + item.F[1].V); // Read first and second columns
}
//==============================================================================================

//==============================================================================================
// Insert Record
var d = new TableDataInsertAllRequest();
d.Rows = new List<tabledatainsertallrequest.rowsdata>();
d.Kind = "bigquery#tableDataInsertAllRequest";

// Check @ https://developers.google.com/bigquery/streaming-data-into-bigquery for InsertId usage
var r = new TableDataInsertAllRequest.RowsData();
r.InsertId = "RowOne";
r.Json = new Dictionary<string, object="">();
r.Json.Add("id", "ID1");
r.Json.Add("Created", 
	DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture));
d.Rows.Add(r);

r = new TableDataInsertAllRequest.RowsData();
r.InsertId = "RowTwo";
r.Json = new Dictionary<string, object="">();
r.Json.Add("id", "ID2");
r.Json.Add("Created", 
	DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture));
d.Rows.Add(r);

var requestResponse = service.Tabledata.InsertAll(d, ProjectID, DataSet, TableName).Execute();

//==============================================================================================