Click here to Skip to main content
Click here to Skip to main content

Google BigQuery API Client Sample Code for C#.NET

, 12 Jun 2014
Rate this:
Please Sign up or sign in to vote.
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, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

LM Heah

Malaysia Malaysia
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 13 Jun 2014
Article Copyright 2014 by LM Heah
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid