BigQuery教學｜基礎必備語法｜免費BigQuery資料導入

BigQuery需要使用到SQL語言來做為資料庫的查詢 ，另外免費用量GCP提供1TB/月 。如果你對BigQuery還不熟悉，還沒有完成串接GA4與BigQuery的使用，詳細可以查看這篇 「BigQuery與GA4基礎串接、必要觀念」。

本篇文章 – 持續更新最新SQL查詢用法，其中包刮「資料日期、資料事件、資料參數、來源媒介、電子商務資料、電子商務項目」。如果你是要 查看PYTHON實務應用視覺化報表，也可以查看這一篇 。

也有提供免費的SQL免費公開的DATASET，可以幫助我們在沒有大量資料的情況下，練習SQL的寫法。

BigQuery 匯入免費資料集

如果你的BigQuery還沒有任何資料，或是你覺得目前資料太少，你可以照著以下步驟，GCP有提供公開的免費資料。

點選新增

公開資料集

在BigQuery的公開資料集中點選你要的資料；尋找一下有GA4的免費公開資料，並且匯入。

完成總表

匯入完成，從左邊選單選取GA4的資料集，再點選其中一個GA4資料表，即可看到從外部匯入的現成資料。後續就可以直接利用這個表作為查詢。

查詢、資料儲存要收費嗎

BigQuery每個月提供我們1TB的查詢用量 。雖然論查詢量來說不多，因為當你查詢月的資料，基本上一下子就好幾GB的查詢量；不過基本上還是可以做滿多練習的。如果你的查詢量很大，你也可以開多個帳戶使用BigQuery；另外只要你帳戶還沒綁定帳單，基本上都不用擔心GCP跟你收費。

如何儲存當前查詢語法

在BigQuery的上方選單中，點選「查詢儲存」。

輸入你要儲存的資料表名稱

可以看到左側BigQuery過往的專案查詢，有剛剛的資料表名稱。

也可以看到剛剛的資料表名稱已經變更，你剛所輸入的內容。

配置查詢設定

配置查詢設定，可以讓我們調整當使用SQL查詢時，當前資料表的資料流向。例如暫存一張資料表，後續需要我們手動儲存，或是先設定好資料叢集、資料表，未來的查詢都會直接匯入至那一張表中。

點選BigQuery選單上方的「查詢設定」。

如果你希望未來的BigQuery資料，都會直接寫入另外一張資料表。你需要再新建一個新的資料叢集、資料表。你可以透過上方BigQuery的範例圖直接設定，下面也會有另外一個方式教你建立。

完成後，未來所查詢的資料，都會在這個資料叢集的資料表中。其中也有分成不同模式的寫入設定，要特別注意這一點。

建立資料叢集的方式2

點選BigQuery資料叢集旁邊的選單。詳見上方圖例。

輸入你要的資料及名稱，以及你的資料地區。要注意的是如果你是使用公開資料，有些資料只能使用指定地區。因此如果你在練習階段，會建議直接使用預設的「多地區」，或是看你的資料地區在哪，只接選擇對應的地區。完成後按建立。

於BigQuery中剛建立的資料叢集，再點選他的選項，「建立資料表」，就可以新增新的資料表在這邊。後續再依照這一段落最一開始的設定，設定對應資料叢集、資料表。即可將資料直接匯入至這張新的資料表中。

另存當前資料表

BigQuery 提供以上方式，可以進行我們儲存查詢完後的結果。

點選BigQuery資料表，可以將當前資料表，再開一個新的資料叢集，並且再新增一個資料表。

輸入資料集名稱、資料表名稱。

完成後你可以看到選單，已經有對應的資料叢集、資料表。

如何共享BigQuery的查詢結果

這個設定應該跟Looker Studio差不多，複製完後點選「取得連結」，對方就可以打開BigQuery的專案。

**對方需要使用有登入的Google 帳戶進行打開哦!

BigQuery資料結構說明

使用SQL查詢BigQuery時，你一定要先了解資料表下的結構，因為這會關乎在撰寫SQL時，需要使用的語法。 如果要使用BigQuery查詢 「ctrl + enter」 ，可以做為SQL的執行快捷鍵。

BigQuery -多元輸出功能

BigQuery支援 Looker Studio的串接，可以再由LS直接製作視覺化報表。

同時BiqQuery也支援Python，透過操作Google Colab引入相關數據分析會使用的套件，例如Pandas, Pgywalker , Matplotlib ，也可以直接在PYTHON應用程式中做視覺報表圖。

匯出至GoogleSheet

點選透過試算表探索。

資料已經從BigQuery引入至Gsheet。目前的資料還無法做直接引用，如果你直接引用會有錯誤顯示。如下圖。

引用Google Sheet資料

點選擷取。

建立一個新工作表。

Google Sheet會開啟一張工作表，讓你可以進行後續的BigQuery數據運算。不過不建議把超過1萬筆以上的資料，導入Google Sheet做處理；會非常的緩慢。

PYTHON視覺報表應用

原本官方已經寫好PYTHON與當前BigQuery的專案連接，剩下就看個人使用的目地來引入相關套件。

import pands as pd import pygwalker as pyg 這邊我也會推薦你先用 Matplotlib ，也可以製作視覺化報表，只是本文利用pygwalker作為範例，結果都差不多。要注意的是引用pygwalker需要先安裝這個套件，於是上方第二條語句。

後續可直接由Colab 搭配pygwalker 在程式中做出報表，也能匯出圖片、連結、pdf檔案，非常方便。 如果想要學習更多PYTHON製作視覺化報表，或是有哪些視覺化報表的應用，也可以查看這一篇 。

BigQuery-SQL查詢技巧

接下來會有BigQuery最基本的SQL語法，我也會把SQL語言貼在這邊，務必要實際操作，這樣學習才會快速。

*符號

*符號為全選，如果你一開始想要先預覽整張BigQuery資料表，就可以透過以下語法選擇全部資料表的欄位。

SELECT + FROM

SELECT:選擇XX資料欄位。資料欄位就是前一步驟講的資料結構下的欄位名稱。

FROM:使用哪一個表做查詢。

SELECT event_date, event_name,event_params[0].key,event_params[0].value --選擇哪些資料 選擇事件日期, 事件名稱, 事件參數-字串類, 事件參數-值類。

FROM bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_* 從bigquery-public-data.ga4_obfuscated_sample_ecommerce 資料叢集選擇對應欄位。

有一點要注意的是我資料表的 名稱目前是 * 結尾 ，意思是可以後續搭配WHERE指定日期範圍。不然原本應該會是字尾接續日期名稱，這樣的意思就是你只能查詢到這一天的所有資料。因為BigQuery以天數來拆分每一張資料表，也可以讓我們不會一下子把每月的CREDIT使用完。

WHERE

WHERE 條件篩選。

SELECT event_date, event_name,event_params[0].key,event_params[0].value FROM bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_* WHERE _table_suffix BETWEEN '20210120' AND '20210130' AND event_name = 'page_view' --WHERE為篩選資料表的條件 搭配”_table_suffix”這一個條件，以及*字尾的資料表。可以讓我們選取指定日期範圍的資料；當然where也可以使用在其他條件的篩選。

AND / OR

AND / OR 為更多條件的篩選。例如以下範例，當你想要選擇事件名稱只有page_view而且在指定時間範圍，你就可以寫入更多篩選條件。

ORDER BY

ORDER BY為使用哪一個資料欄位做排序，有ASC遞增 / DESC遞減。如果你要查看由大到小的，你就必須在 ORDER BY 的結尾，寫上 DESC。他就會由大到小降冪排序。

LIMIT

LIMIT 限制顯示的資料筆數 。

例如你可以看本周哪一個產品帶來最多業績TOP5 。只需要寫上LIMIT 5 資料就會顯示5筆，同時也能節省SQL的查詢使用量。

SUM

SUM把資料加總。如何把上圖左邊的資料，依照城市(county_name)加總醫院總數範例。

CASE/WHEH

當你想要查詢資料表，設定條件大於多少數值，就給予1，否則給0；並且新增至新的一欄。

符合條件 => 1

不符合條件=>0

如何篩選符合1的欄位

如果你沒有使用Common Table Expressions(CTE)，那麼查詢就會報錯。因此我們要搭配WITH ... AS ...的撰寫。

WITH的存在，為了就是減少過多的條件篩選，同時也能減少SQL的撰寫複雜度。 本次範例透過簡單的CTE方式，可以把資料表篩選完>600的，並且獨立出來成一個欄位。

當然，你也可以不用with (CTE)的表示法，但當你有CASE WHEN 與 WHERE整個語句就會非常複雜，如下。

SELECT *, case when load_weight > 600 then 1 else 0 end as load_weight_greater600 FROM `bigquery-public-data.austin_waste.waste_and_diversion` WHERE case when load_weight > 600 then 1 else 0 end =1 如果資料大量，且要使用的CASE WHEH一多，那使用WHERE的條件就會讓查詢變得很複雜，因此才會需要使用with。

使用範例

查詢PAGE_VIEW與PAGE_LOCATION

從BigQuery查詢GA4的Page_view & page_location。

產品銷售金額TOP10

從BigQuery查詢GA4數據的TOP10產品銷售金額

查詢近7日報表

查詢每日總收益來源 / 媒介

事件總表

透過SQL作為BigQuery的查詢媒介，我們可以在SQL撈取指定的資料，後續再藉由LookerStudio來做視覺報表，或是匯出至Google Sheet，BigQuery也可作為一個免費的數據倉儲站。本文將會持續更新BigQuery-SQL最新使用語法；目前還有很多語法沒有介紹到，但文章內容過於長，因此分次更新，歡迎定期關注!