均一過去產生表格的方法,將每一段 sub-query存成一個 python變數,一層包裹一層,最後傳到 Bigquery執行。
//python
import junyi_toolsbq = junyi_tools.bqfirst_part = """
SELECT
...
FROM
table_name1
WHERE
....
"""%(parameter1)second_part = """
SELECT
...
FROM
table_name2
WHERE
....
"""%(parameter2)third_part = """
SELECT
...
FROM
(%s)
LEFT JOIN
(%s)
...
"""%(first_part, second_part)final_part = """
SELECT
*
FROM
current_table
UNION ALL
SELECT
*
FROM
third_part
"""bq.query_to_table(final_part)
在重構的過程中有幾個方向
- 單一語言:純粹用 SQL來產生表格,因為現在的 Standard SQL已經有許多類似物件導向的功能。
- Function化:不論在 Data pipeline或是手動要更新表格,都只需要程式碼相關的 Function,就會執行一樣的流程。
- 避免重複寫入:如果表格已經更新到最新,就算重複執行 data pipeline也不應該產生重複資料。
- 產生表格或更新最新資料:如果表格不存在,就創建表格;表格已經存在的話就更新未更新的部分
單一語言
原本使用 python變數來儲存 Query,改用 With(Bigquery: 利用 With把巢狀結構function化),有一樣的效果,同時不需要使用 python。
過去會需要用 python是受限於工具只能使用 python,但又需要執行 SQL程式碼,但這個問題隨著均一開始使用 Airflow而消失。
Function化
使用 Stored Procedure的功能去包裝程式碼,需要執行時只要 call procedure即可(Bigquery:利用 Procedure讓程式碼隨時可以呼叫)。
使用 Stored Procedure 還有一個好處,在 Data pipeline相關的工具中可以直接呼叫。
避免重複寫入
- 判斷目前資料中最新一筆的 timestamp,只新增該 timestamp以後的資料 (BigQuery:利用 Insert來更新資料表)
- 同時最後可以對資料做一個 SELECT DISTINCT *來將重複資料完全去除(BigQuery:利用 SELECT DISTINCT來去除重複資料)
- 往上追資料來源,可能是 GA 或是後端的資料,長期來說會希望後端改善,短期來說可以先建立外部資料與內部資料之間的中間層當做 buffer(文章待補)
產生表格或更新最新資料
- 利用 CREATE TABLE IF NOT EXIST,如果表格不存在,就先創造其 Schema(BigQuery:利用 Create or replace table創建表格)
- 當表格已經存在的情況下,判斷目前資料中最新一筆的 timestamp,只新增該 timestamp以後的資料 (BigQuery:利用 Insert來更新資料表)
- 如果表格原本不存在,會在步驟1創建表格,那麼 timestamp會是空值,就要設為資料的最早起源時間。