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
- https://developers.google.com/bigquery/authorization#service-accounts-server
- 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:
- Read Dataset and Data Table
- Read Rows in the Table
- Batch insert rows into GB
var ApplicationName = "APP1"; var ProjectID = "PROJECT1"; var DataSet = "DS1"; var TableName = "TBL1";
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();
foreach (var item in datasetList.Datasets)
{
var ProjectDatasetID = item.Id;
Console.WriteLine(ProjectDatasetID);
var tablelist = service.Tables.List(ProjectDatasetID.Split(':')[0],
ProjectDatasetID.Split(':')[1]).Execute().Tables;
foreach (var tbl in tablelist)
{
Console.WriteLine(tbl.Id);
}
}
#endregion
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); }
var d = new TableDataInsertAllRequest();
d.Rows = new List<tabledatainsertallrequest.rowsdata>();
d.Kind = "bigquery#tableDataInsertAllRequest";
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();