GCP的Billing Export與分析(一) - 透過BigQuery查詢與分析GCP帳務資料
GCP提供雲端服務平台之相關產品,在計價上,GCP提供Billing檔案的匯出,從GCP web console設定,可匯出至BigQuery、Cloud Storage與PubSub幾個目的儲存體,本次跟大家介紹BigQuery的匯出部分,並藉由BigQuery的查詢呈現您想要的報表。
GCP Billing export to Bigquery
在您的Billing Account的設定頁面中,可以透過下面的介紹設定好Billing Data的匯出動作...
首先,進入Google Cloud Billing頁面後,可以在BigQuery export的項目中設定匯出Billing資料至某個您具備權限的project下的BigQuery dataset中...
設定完成後,BigQuery就會多一個dataset並且自動產生一個table,該table的名稱規則為:gcp_billing_export_$billing_account_id
原則上設定完後,Billing的資料會等約2個工作天才會完整的進來,如果沒看到全部的資料,請不要擔心^^
建立報表
下面是透過BigQuery來取得某個期間的每日各項目加總(日期自行修改)的查詢語句。結合上述的專案帳務資料的匯出,可以得到所指定日期內每一天的所有項目費用紀錄,可以讓您方便了解每項資源在每天的使用上,在各專案中的比重約為多少...
SQL:
SELECT t0.product ,strftime_utc_usec(t0.start_time,'%Y%m%d') as date ,
round(SUM(t0.cost),6) AS cost_total FROM (SELECT * FROM [your-project-id:daily_billing.gcp_billing_export_$billing_account_id] WHERE _PARTITIONTIME >= TIMESTAMP('20170401') AND _PARTITIONTIME <= TIMESTAMP('20170620') ) AS t0 GROUP EACH BY t0.product,date ORDER BY date,cost_total DESC,t0.product; |
查詢結果如下:
上面以日為觀察區間,我們也可以使用月為區間觀察,透過下面的SQL可以了解每個產品的用量,佔本月的比重(日期自行修改)
SQL:
SELECT t0.product ,round(SUM(t0.cost),6) AS cost_total
FROM (SELECT * FROM [your-project-id:daily_billing.gcp_billing_export_$billing_account_id] WHERE _PARTITIONTIME >= TIMESTAMP('20170206') AND _PARTITIONTIME <= TIMESTAMP('20170511') ) AS t0 GROUP EACH BY t0.product ORDER BY cost_total DESC,t0.product; |
查詢結果如下:
進一步,我們想透過往前推三個月各個月的金額加總(日期自行修改)做比較,可以使用下面的SQL來做分析...
SQL:
SELECT t0.product,strftime_utc_usec(t0.start_time,'%Y%m') as date,
round(SUM(t0.cost),6) AS cost_total FROM (SELECT * FROM [your-project-id:daily_billing.gcp_billing_export_$billing_account_id] WHERE _PARTITIONTIME >= TIMESTAMP('20170401') AND _PARTITIONTIME <= TIMESTAMP('20170630') and project.id =$project_id ) AS t0 GROUP EACH BY t0.product,date ORDER BY date,t0.product,cost_total DESC; |
查詢結果如下:
最後,如果想了解您每個project每個月的花費(日期自行修改),可以透過下面的SQL來查詢...
SQL:
SELECT strftime_utc_usec(t0.start_time,'%Y%m') as date,t0.project.id,
round(SUM(t0.cost),6) AS cost_total FROM (SELECT * FROM [your-project-id:daily_billing.gcp_billing_export_$billing_account_id] WHERE _PARTITIONTIME >= TIMESTAMP('20170201') AND _PARTITIONTIME <= TIMESTAMP('20170630') and billing_account_id =$billing_account_id ) AS t0 GROUP EACH BY date,t0.project.id ORDER BY date,t0.project.id; |
查詢結果如下:
作者:宜禎
感謝分享
回覆刪除Play Online Game – No Download - Topcleo
回覆刪除Play Online Game – 예스카지노 No Download. Play Online. 우리카지노 Let's Play online now. Topcleo is a modern, multiplayer free to play game. The free online game is built around