遇見零壹魔王

前置作業

參考 : https://www.dotblogs.com.tw/noncoder/2020/12/16/bigquery

圖解名詞

命名空間

using Google.Apis.Auth.OAuth2;
using Google.Apis.Bigquery.v2;
using Google.Apis.Bigquery.v2.Data;
using Google.Apis.Services;
using System.Security.Cryptography.X509Certificates;

主要程式

string serviceAccountEmail = "xxxxxx@xxxxxx-xxxxx.iam.gserviceaccount.com";
string rootPath = System.IO.Directory.GetCurrentDirectory();
string fileName = Path.Combine(rootPath, "xxxxxx-xxxxx-xxxxxxxxxxxx.p12");
var certificate = new X509Certificate2(fileName, "xxxxxxxxxx", X509KeyStorageFlags.MachineKeySet);

ServiceAccountCredential credential = new ServiceAccountCredential(
   new ServiceAccountCredential.Initializer(serviceAccountEmail)
   {
       Scopes = new[] { BigqueryService.Scope.DevstorageReadOnly, BigqueryService.Scope.Bigquery }
   }.FromCertificate(certificate));

var service = new BigqueryService(new BaseClientService.Initializer()
{
    HttpClientInitializer = credential,
    ApplicationName = "BigQuery API Test",
});

var newTb = new Table();
string tableId = "test_20211123";

/*

後續日期可按照yyyyMMdd累加

string tableId = "test_20211124";

string tableId = "test_20211125";

這樣之後可以透過以下方式跨Talbe查詢

WHERE (_table_suffix >= '20211123') AND(_table_suffix <= '20211125')

*/
newTb.Id = tableId;
newTb.TableReference = new TableReference();
newTb.TableReference.TableId = tableId;
newTb.TableReference.ProjectId = "資源ID";
newTb.TableReference.DatasetId = "資料集ID";

newTb.Kind = "bigquery#table";
newTb.Schema = new TableSchema();
newTb.Schema.Fields = new List<TableFieldSchema>();

var f1 = new TableFieldSchema();
f1.Mode = "NULLABLE";
f1.Name = "TitleName";
f1.MaxLength = 100;
f1.Type = "STRING";
newTb.Schema.Fields.Add(f1);

var f2 = new TableFieldSchema();
f2.Mode = "NULLABLE";
f2.Name = "Code";
f2.Type = "STRING";
newTb.Schema.Fields.Add(f2);

var f3 = new TableFieldSchema();
f3.Mode = "NULLABLE";
f3.Name = "TimesCount";
f3.Type = "STRING";
newTb.Schema.Fields.Add(f3);
var tableTask = service.Tables.Insert(newTb, "資源ID", "資料集ID");
var tableTaskResult = tableTask.Execute();