BQ UDF 介紹

BigQuery 有個功能是 UDF(User Define Function) ，是用創建自定義函數，並且在 Query 時能夠呼叫。

UDF 的用途為減少重複代碼、封裝複雜邏輯、優化 query 可讀性和維護性等等。UDF 有兩種類型，一個是一般的 function，返回值是單一值；另一個則是 table function，返回的值是一張表。

以下為兩種類型的範例程式碼

# Function
CREATE FUNCTION `joshua-1000.udf.isotime_to_datetime`(timestamp_str STRING, timezone STRING) RETURNS DATETIME
AS(
	DATETIME(PARSE_TIMESTAMP("%Y-%m-%dT%H:%M:%E*SZ", timestamp_str), timezone)
);

# Table Function
CREATE TABLE FUNCTION `joshua-1000.udf.extract_member`(brandid INT)
AS(
	SELECT
		*
	FROM
		`joshua-1000.data_source.members`
	WHERE
		brand_id = brandid
);

UDF 應用場景

像先前的文章提到，Migo 是 B2B 公司，我們團隊常需要處理不同客戶的資料，而不同客戶給的資料格式都不一致，會需要清整資料，以下是幾個常見的清整案例

• 手機號碼統一格式：去除國碼，統一成 09 開頭
• 解析地址：解析出縣市、行政區
• 生日合理性驗證：歲數是否合理，超過 100 歲的資料歲設成空值

透過 UDF ，可以統一清整邏輯和節省每次寫清整邏輯的時間。

為何需要在 dbt 上管理 UDF

我們是採用開一個 repository 來管理這些 UDF 的 sql 程式碼，但開發和使用分別要切換不同 repo 非常麻煩(dbt 和 UDF)，因此我們團隊就希望能夠在 dbt 上管理 UDF，讓開發和使用都在同個 repo 上。

macro 管理 UDF

在 dbt 官方文件查找後，發現 dbt 沒有辦法直接開發 UDF，但在官方文件有列出一篇論壇文章的連結，這篇文章是在介紹說如何用 macro 來開發 UDF，在研究其作法並優化後，我們發展出用 macro 開發 UDF 的用法，下篇文章中會一步步介紹作法。

參考

• Using dbt to manage user defined functions