iT邦幫忙

2022 iThome 鐵人賽

DAY 20
1
AI & Data

[GCP BigQuery] - 探索資料倉儲,開啟你的數位轉型之旅系列 第 20

Day 20: 優化你的BigQuery 查詢,提高查詢效能並節省費用 (下) (實作)

  • 分享至 

  • xImage
  •  

https://ithelp.ithome.com.tw/upload/images/20221004/20152529Zjt12tR2yF.png

前言:

我們在 Day 07 曾提到,BigQuery 的基本架構可分為查詢和儲存。因此,本篇要來介紹幾個方法,不僅能夠幫助我們提升查詢的效能、降低費用,甚至還能減少故障。

為了看到查詢量的差異,我們繼續使用 bigquery-public-data.crypto_band.logsbigquery-public-data.crypto_band.block_events,分別有 306,385,922 筆 和 3,285,547,037 筆,因此也會有額外的費用。

大家也可以試試看把這段語法貼到 BigQuery查詢介面,體驗一下右上角顯示的查詢量,但是但是,記得不要按執行阿~ 以免被收取額外的費用!

希望大家能夠應用以下的查詢技巧到工作中,做一個聰明的使用者!

避免使用 SELECT *:

使用 SELECT *:

SELECT
  *
FROM
  `bigquery-public-data.crypto_band.logs`

https://ithelp.ithome.com.tw/upload/images/20221004/20152529znAYDZao5o.png

選取特定欄位,可以發現明顯查詢量少了快 5 倍!

SELECT
  txhash, log_index
FROM
  `bigquery-public-data.crypto_band.logs`

https://ithelp.ithome.com.tw/upload/images/20221004/20152529gAscG4HfwY.png

使用 partitioned 查詢:

partitioned 前:

SELECT block_timestamp, event_type
FROM `bigquery-public-data.crypto_band.block_events`
WHERE block_timestamp between '2020-11-01' and '2020-11-02';

https://ithelp.ithome.com.tw/upload/images/20221004/20152529WMv1STmyCQ.png

partitioned 後,從原本的 GB查詢量變為 MB!

SELECT block_timestamp, event_type
FROM `bigquery-public-data.crypto_band.block_events`
WHERE block_timestamp_truncated between '2020-11-01' and '2020-11-02';

https://ithelp.ithome.com.tw/upload/images/20221004/201525294WL1yrLJRE.png

使用 cluster 查詢:

因為沒有找到使用 cluster 的公共數據集,這裡就先自己創立一個 cluster 的 table!

先在你的專案底下建立一個名為 crypto 的資料集:

https://ithelp.ithome.com.tw/upload/images/20221004/20152529sK5wxz0Qj2.png

cluster 前:

CREATE TABLE `crypto.block_events` AS
SELECT block_timestamp_truncated, event_type
FROM `bigquery-public-data.crypto_band.block_events`
WHERE block_timestamp_truncated between '2020-11-01' and '2020-11-02';

SELECT block_timestamp_truncated, event_type
FROM `crypto.block_events`
WHERE event_type = 'resolve';

https://ithelp.ithome.com.tw/upload/images/20221004/20152529SIJ1kM0JRx.png

cluster 後,從原本的 GB查詢量變為 MB!

CREATE TABLE `crypto.block_events_cluster` 
CLUSTER BY event_type AS
SELECT block_timestamp_truncated, event_type
FROM `bigquery-public-data.crypto_band.block_events`
WHERE block_timestamp_truncated between '2020-11-01' and '2020-11-02';
 
SELECT block_timestamp_truncated, event_type
FROM `crypto.block_events_cluster`
WHERE event_type = 'resolve';

https://ithelp.ithome.com.tw/upload/images/20221004/20152529dbmjZalrYf.png

https://ithelp.ithome.com.tw/upload/images/20221004/2015252950JxTYC4th.png

*註: 1) cluster 一開始的查詢量明明顯示是 126.74 MB,但我們細看可以發現 其實只花了1.06 MB ! 這是因為 cluster 是在執行的時候才去做 cluster,因此在一開始查詢的時候,BigQuery無法去預估查詢量,因此只好以最大查詢量去估計。

註: 2) BigQuery 存在低消限制,雖然我們只有查詢1.06 MB的量,但是還是被收取了 10 MB 的查詢費用。

Summary:

今天介紹的方法不只可以提高性能,也能減少查詢量,進而減少費用!

  1. 避免使用 SELECT *
  2. 使用 partitioned 查詢
  3. 使用 cluster 查詢

Reference:

https://cloud.google.com/bigquery/docs/best-practices-performance-overview?hl=zh-cn


上一篇
Day 19: 優化你的BigQuery 查詢,提高查詢效能 (上) (實作) (可選)
下一篇
Day 21: 優化你的BigQuery 查詢,Partitioned 和 Clustered 的使用時機和限制
系列文
[GCP BigQuery] - 探索資料倉儲,開啟你的數位轉型之旅30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言