[筆記]C#取得Google BigQuery資料

2020-12-16

1017
0
.net C#
2021-11-24

C# 使用 Google.Apis.Bigquery.v2 取得Google BigQuery資料

前置作業

複製 [電子郵件] 備用 (填入程式的serviceAccountEmail 變數中)
新增金鑰

選擇P12

私密金鑰密碼 只會出現一次要妥善保存

NuGet安裝項目

命名空間

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

主要程式

static void Main(string[] args)
{
    //Google Cloud Platform > [API和服務] > [憑證] > [服務帳戶]中的電子郵件
    string serviceAccountEmail = "填入電子郵件";
    
    //console專案bin資料夾的路徑
    string basePath = AppDomain.CurrentDomain.BaseDirectory;
 
    //此範例直接把憑證放在bin資料夾內所在在這邊組憑證的完整路徑
    string fileName = $"{basePath}憑證的檔名.p12";

    //建立憑證物件(Google Cloud Platform > [API和服務] > [憑證] > [服務帳戶]中建立憑證時會給"私密金鑰密碼"只會給一次要妥善保存)
    var certificate = new X509Certificate2(fileName, "私密金鑰密碼", X509KeyStorageFlags.Exportable);

    ServiceAccountCredential credential = new ServiceAccountCredential(
           new ServiceAccountCredential.Initializer(serviceAccountEmail)
           {
                   //BigqueryReadonly這個Scope一定要加不然後面在Execute時會因為沒有權限回傳403拒絕存取
                   Scopes = new[] { BigqueryService.Scope.DevstorageReadOnly, 
                                    BigqueryService.Scope.BigqueryReadonly }
           }.FromCertificate(certificate));

    // Create the service.
    var service = new BigqueryService(new BaseClientService.Initializer()
        {
             HttpClientInitializer = credential,
             ApplicationName = "BigQuery Sample",
        });
    
    //建立任務
    var jobs = service.Jobs;

    //要在BigQuery中做查詢的SQL語法
    string query = "SELECT 欄位 FROM [ProjectId:DataSets.Table] LIMIT 要撈幾筆資料";

    //建立Query請求物件
    QueryRequest queryRequest = new QueryRequest();
            queryRequest.Query = query;

            QueryResponse response = jobs.Query(queryRequest, "填入ProjectId").Execute();
            foreach (TableRow row in response.Rows)
            {
                List<string> list = new List<string>();
                foreach (var field in row.F)
                {
                    list.Add(field.V?.ToString());
                }
                Console.WriteLine(String.Join("\t", list));
            }

    
}

補充1. (2021/04/23更新)

在透過BigqueryService 查詢資料時，我們會把查詢語法assign到QueryRequest物件的Query屬性中

而查詢語法有分為#legacySQL與#StandardSQL兩種
通常我們在Google BigQuery平台上做查詢使用的是#StandardSQL
但Google.Apis.Bigquery.v2套件預設卻是使用#legacySQL
如果要改用#StandardSQL
請將QueryRequest的UseLegacySql屬性設定為false

範例:

QueryRequest queryRequest = new QueryRequest();
queryRequest.UseLegacySql = false; //將QueryRequest的UseLegacySql屬性設定為false
queryRequest.Query = query;

補充2. (2021/04/23更新)

跨日期查詢及使用UNNEST範例(#StandardSQL)

SELECT
user_pseudo_id,
event_timestamp,
param.value.string_value
device.category,
geo.region
FROM `xxx-12abc.analytics_123456789.events_*`, 
UNNEST(event_params) AS param
WHERE 
(_table_suffix >= '20210101') AND (_table_suffix <= '20210111')
AND event_name = 'page_view' 
AND param.key = 'page_location'