Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

Google BigQuery API Client Sample Code for C#.NET

0.00/5 (No votes)
12 Jun 2014 2  
Google BigQuery API Client Example 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();

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here