上一篇介紹了 BigQuery UDF 以及為何我們團隊想要在 dbt 上實作，這篇就來介紹是如何透過 macro 來實際實作。

1. 創建 ddl_generator macro：這支 macro 主要是用來產生生成 UDF 的語法，因為每個 UDF 產生的語法都有一大部分重複，所以透過此 macro 就能省去寫重複語法的部分

   #macros/ddl_generator.sql
   {% macro ddl_generator(function_name, type, params, reture_type) %}
       {%- set params_string -%}
       {%- for param in params -%}
         {{ param }}
         {%- if not loop.last -%}, {%- endif -%}
       {%- endfor -%}
     {%- endset -%}
   
     {% set return_string = '' %}
     {% if type == 'FUNCTION' %}
         {% set return_string = 'RETURNS' + return_type %}
       {% endif %}
       CREATE OR REPLACE {{ type }} `joshua-1000.udf.{{ function_name }}` ({{ params_string }}) {{ return_string }} AS 
   {% endmacro %}
   

2. 建立 UDF macro：開發 UDF 需要建立一個 macro，裡面放建立該 UDF 的 sql code，因為我們前面先開發了 ddl_generator，這裡就只需要呼叫 ddl_generator 並填入對應參數

   #macros/udf/parse_datetime.sql
   {% macro parse_datetime(env) %}
       {{ 
           ddl_generator(
               function_name = 'parse_datetime',
               type = 'FUNCTION',
               params = [
                   'timestamp_str STRING'
               ],
               return_type = 'DATETIME'
           ) 
       }}
       (
           PARSE_DATETIME('%Y-%m-%d', timestamp_str)
       )
   {% endmacro %}
   
   {% if env == 'ci' %}
       {% set dataset = 'udf_ci' %}
   {% else %}
       {% set dataset = 'udf' %}
   
   # compile 的結果
   CREATE OR REPLACE FUNCTION `joshua-1000.{{ dataset }}.parse_datetime` (timestamp_str STRING) RETURNS DATETIME(
       PARSE_DATETIME('%Y-%m-%d', timestamp_str)
   )
   

3. 建立 create_udfs macro：傳入要建立的 UDF 名稱，此 macro 會執行對應名稱 UDF macro 中的 sql 語法，來達到在 BigQuery 建立 UDF 的效果

   #macros/create_udfs.sql
   {% macro create_udfs(function_list=[], table_function_list=[], env=none) %}
       {% for function in function_list %}
               {% set macro_func = context.get(function)%}
               # 在 BQ 執行 create function 語法
               {% do run_query(macro_func(env)) %};
       {% endfor %}
       # 因為 table function 有可能用到 function，所以先建立 function
       {% for table_function in table_function_list %}
               {% set macro_func = context.get(table_function)%}
               {% do run_query(macro_func(env)) %};
       {% endfor %}
   {% endmacro %}
   

4. CI/CD 流程：在 CI 和 CD 時使用 git 來取出有異動或新增的 UDF macro 名稱，透過 function_list/table_function_list 傳入 create_udfs macro，在測試及正式環境建立 UDF

   # CI
   dbt run-operation create_udfs --args '{function_list = ['parse_datetime'], env='ci'}'
   # CD
   dbt run-operation create_udfs --args '{function_list = ['parse_datetime']}'
   

透過以上 4 個步驟就能透過 macro 來達到在 dbt 開發 UDF ，但其實這個方法隱含了很多缺點，在下篇文章中會說明 macro 管理 UDF 會有哪些缺點，以及我們團隊後續發現的解法。

參考

• Using dbt to manage user defined functions