舊版 SQL 中的使用者定義函式

本文件詳述如何在舊版 SQL 查詢語法中使用 JavaScript 使用者定義函式。建議使用的 BigQuery 查詢語法是標準 SQL。如要瞭解標準 SQL 中的使用者定義函式,請參閱標準 SQL 的使用者定義函式

BigQuery 舊版 SQL 支援以 JavaScript 編寫的使用者定義函式 (UDF)。UDF 與 MapReduce 中的「Map」函式類似:使用單一資料列做為輸入,然後產生零個以上的資料列做為輸出。輸出的結構定義可能會和輸入不同。

如要瞭解標準 SQL 中的使用者定義函式,請參閱標準 SQL 的使用者定義函式

UDF 範例

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

返回頁首

UDF 結構

function name(row, emit) {
  emit(<output data>);
}

BigQuery UDF 會在資料表的個別列或 subselect 查詢結果上執行。UDF 有兩種形式參數:

  • row:輸入列。
  • emit:BigQuery 用來收集輸出資料的掛鉤。emit 函式採用一種參數:一個代表單列輸出資料的 JavaScript 物件。您可以多次呼叫 emit 函式 (例如在迴圈中),藉此輸出多列資料。

以下程式碼範例顯示基本 UDF。

function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

註冊 UDF

您必須針對自己的函式註冊名稱,以便從 BigQuery SQL 叫用該函式。您註冊的名稱不需和該函式在 JavaScript 中使用的名稱相同。

bigquery.defineFunction(
  '<UDF name>',  // Name used to call the function from SQL

  ['<col1>', '<col2>'],  // Input column names

  // JSON representation of the output schema
  [<output schema>],

  // UDF definition or reference
  <UDF definition or reference>
);

輸入資料欄

輸入資料欄名稱必須符合輸入資料表或子查詢中資料欄的名稱 (或別名,如果適用)。

對於屬於記錄的輸入資料欄,您必須在輸入資料欄清單中指定要從該記錄存取的分葉欄位。

舉例來說,如果您有一筆記錄儲存了某人的姓名和年齡:

person RECORD REPEATED
  name STRING OPTIONAL
  age INTEGER OPTIONAL

姓名和年齡的指定碼會是:

['person.name', 'person.age']

在沒有姓名或年齡的情況下使用 ['person'] 會產生錯誤。

結果輸出會符合結構定義;結果會顯示 Javascript 物件陣列,其中每一個物件都有「name」和「age」屬性。例如:

[ {name: 'alice', age: 23}, {name: 'bob', age: 64}, ... ]

輸出結構定義

您必須將 UDF 所產生記錄的結構定義或結構以 JSON 表示法提供給 BigQuery。結構定義可以包含任何支援的 BigQuery 資料類型,包括巢狀記錄。支援的類型指定碼如下:

  • 布林值
  • 浮動值
  • 整數
  • 記錄
  • 字串
  • 時間戳記

以下程式碼範例顯示輸出結構定義中的記錄語法。每個輸出欄位都需要 nametype 屬性。巢狀欄位也必須含有 fields 屬性。

[{name: 'foo_bar', type: 'record', fields:
  [{name: 'a', type: 'string'},
   {name: 'b', type: 'integer'},
   {name: 'c', type: 'boolean'}]
}]

每個欄位可以含有支援下列值的 mode 選用屬性:

  • nullable:這是預設值,可以省略。
  • required:如果指定此值,則指定的欄位必須設為某個值。
  • repeated:如果指定這個值,則指定的欄位必須是陣列。

傳送至 emit() 函式的資料列必須符合輸出結構定義的資料類型。若 emit 函式省略了輸出結構定義中的欄位,則這些欄位將以空值輸出。

UDF 定義或參照

如果您喜歡,可以在 bigquery.defineFunction 中定義 UDF 內嵌。例如:

bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  // The UDF
  function(row, emit) {
    emit({title: decodeURI(row.title),
          requests: row.num_requests});
  }
);

否則,您可以個別定義 UDF,然後在 bigquery.defineFunction 中傳送該函式的參照。例如:

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

處理錯誤

如果在 UDF 處理期間發生例外狀況或錯誤,整個查詢都將失敗。您可以使用 try-catch 區塊來處理錯誤。例如:

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

UDF 與網頁版 UI

您可以透過 BigQuery 網頁版 UI 來新增 UDF,然後在執行查詢時使用這些 UDF。

必備條件

如要使用 BigQuery 網頁版 UI,您的帳戶必須有權存取 Google Cloud Platform 主控台中啟用 BigQuery 的專案。

  1. 如果您之前沒有使用過 GCP 主控台,請前往主控台,接受服務條款,然後建立新專案。

  2. 前往 BigQuery 網頁版 UI

新增 UDF

  1. 按一下 [COMPOSE QUERY] 按鈕。

  2. 按一下 [UDF Editor] 分頁標籤以新增 UDF。

  3. 複製下列程式碼並貼到「UDF Editor」文字區域:

    // The UDF
    function urlDecode(row, emit) {
      emit({title: decodeHelper(row.title),
            requests: row.num_requests});
    }
    
    // Helper function for error handling
    function decodeHelper(s) {
      try {
        return decodeURI(s);
      } catch (ex) {
        return s;
      }
    }
    
    // UDF registration
    bigquery.defineFunction(
      'urlDecode',  // Name used to call the function from SQL
    
      ['title', 'num_requests'],  // Input column names
    
      // JSON representation of the output schema
      [{name: 'title', type: 'string'},
       {name: 'requests', type: 'integer'}],
    
      urlDecode  // The function reference
    );
    

在網頁版 UI 中執行查詢

  • 按一下 [Query Editor] 分頁標籤。

  • 複製下列查詢並貼到「Query Editor」(查詢編輯器) 文字區域。

    #legacySQL
    SELECT requests, title
    FROM
      urlDecode(
        SELECT
          sum(requests) AS num_requests
        FROM
          [fh-bigquery:wikipedia.pagecounts_201504]
        WHERE language = 'fr'
        GROUP BY title
      )
    WHERE title LIKE '%ç%'
    ORDER BY requests DESC
    LIMIT 100
    

    上述查詢會尋找 2015 年 4 月之後最常造訪且標題中含有軟音符字元 (ç) 的法文 Wikipedia 文章。

  • 按一下 [RUN QUERY] (執行查詢) 按鈕。查詢結果會顯示在該按鈕下方。

從 Cloud Storage 參照程式碼

在上述範例中,您直接將 UDF 新增到 BigQuery 網頁版 UI。另外,您也可以將 JavaScript 程式碼的一部分或完整內容儲存在 Google Cloud Storage 中。不管 UDF 程式碼位在何處,您都必須在程式碼中利用 bigquery.defineFunction 叫用來註冊每個 UDF。bigquery.definefunction 叫用可在網頁版 UI 或在遠端程式碼資源中提供。遠端來源檔的副檔名必須是 ".js"。

舉例來說,您可以在不同的檔案中維護第三方程式庫、您自己的 UDF 程式碼,以及 UDF 註冊函式呼叫。這些檔案在查詢中會當成個別的外部資源載入。

在 BigQuery 網頁版 UI 中參照外部 UDF

  1. 按一下文字區域下方的 [Show Options] (顯示選項) 按鈕。

  2. 按一下「UDF Source URI」(UDF 來源 URI) 標題旁的 [Edit] (編輯) 按鈕。

  3. 針對每一個遠端來源檔,按一下 [Add UDF Source URI] (新增 UDF 來源 URI) 按鈕,然後輸入 Google Cloud Storage URI。

    如果您想要使用外部 UDF 嘗試執行前一個 URL 解碼範例,請將 bigquery-sandbox-udf/url_decode.js 貼到 URI 欄位。完成這些步驟後,請確定已移除「UDF Editor」(UDF 編輯器) 的內容。

  4. 按一下 [OK] (確定) 按鈕。

接著,您可以切換到「Query Editor」(查詢編輯器) 分頁,並遵循上述範例的相同步驟,在查詢中使用 UDF。

使用外部 UDF 時,您通常也可以在「UDF Editor」(UDF 編輯器) 文字區域中新增額外的 JavaScript 程式碼,前提是這些額外的 UDF 已在網頁版 UI 的 defineFunction 區塊或在外部檔案中完成註冊。

您也可以使用 bq 指令列工具來參照儲存在 Cloud Storage 中的 UDF。詳情請參閱 UDF 和 bq 指令列工具一文。

返回頁首

UDF 和 bq 指令列工具

您可以從 Cloud SDK 利用bq 指令列工具指定 --udf_resource 標記,藉此執行含有一或多個 UDF 的查詢。標記的值可以是 Cloud Storage (gs://...) URI 或本機檔案的路徑。您可以重複這個標記來指定多個 UDF 資源檔。

使用以下語法來執行含有 UDF 的查詢:

bq query --udf_resource=<file_path_or_URI> <sql_query>

以下範例執行的查詢會使用儲存在本機檔案的 UDF,以及同樣也是儲存在本機檔案中的 SQL 查詢。

建立 UDF

您也可以將 UDF 儲存在 Cloud Storage 或儲存為本機文字檔。舉例來說,如要儲存 UDF 與網頁版 UI 一節說明的 urlDecode UDF,請建立名稱為 urldecode.js 的檔案,然後在其中貼上下列 JavaScript 程式碼,接著儲存檔案。

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

建立查詢

您也可以將查詢儲存在檔案中,讓您的指令列不會變得太過冗長。舉例來說,您可以建立名稱為 query.sql 的本機檔案,然後將下列 BigQuery 陳述式貼到該檔案。

#legacySQL
SELECT requests, title
FROM
  urlDecode(
    SELECT
      title, sum(requests) AS num_requests
    FROM
      [fh-bigquery:wikipedia.pagecounts_201504]
    WHERE language = 'fr'
    GROUP EACH BY title
  )
WHERE title LIKE '%ç%'
ORDER BY requests DESC
LIMIT 100

儲存檔案後,您就可以在指令列參照該檔案。

執行查詢

在個別檔案中定義 UDF 和查詢後,您可以在指令列中參照它們。舉例來說,以下指令會執行您以 query.sql 檔名儲存的查詢,並參照您所建立的 UDF。

$ bq query --udf_resource=urldecode.js "$(cat query.sql)"

UDF 和 BigQuery API

configuration.query

使用 UDF 的查詢必須包含查詢中使用的 userDefinedFunctionResources 元素,這項元素會提供程式碼或程式碼資源的位置。該元素提供的程式碼必須包含查詢所參照的任何 UDF 的註冊函式叫用。

程式碼資源

查詢設定可以包含 JavaScript 程式碼 blob,以及儲存在 Cloud Storage 中 JavaScript 來源檔的參照。

userDefinedFunctionResource 元素的 inlineCode 區段中,系統會填入內嵌 JavaScript 程式碼 blob。不過,如要在多個查詢中重複使用或參照程式碼,您應該將該程式碼保存在 Cloud Storage,並當成外部資源來參照。

如要從 Cloud Storage 參照 JavaScript 來源檔,請將 userDefinedFunctionResource 元素的 resourceURI 區段設為檔案的 gs:// URI。

查詢設定可以包含多個 userDefinedFunctionResource 元素。每個元素都可以含有 inlineCoderesourceUri 區段。

範例

以下 JSON 範例說明參照兩個 UDF 資源的查詢要求:一個內嵌程式碼的 blob,以及一個要從 Cloud Storage 讀取的 lib.js 檔案。在本範例中,myFuncmyFunc 的註冊叫用是由 lib.js 提供。

{
  "configuration": {
    "query": {
      "userDefinedFunctionResources": [
        {
          "inlineCode": "var someCode = 'here';"
        },
        {
          "resourceUri": "gs://some-bucket/js/lib.js"
        }
      ],
      "query": "select a from myFunc(T);"
    }
  }
}

返回頁首

最佳做法

開發您的 UDF

您可以使用我們的 UDF 測試工具來測試 UDF 並進行偵錯,BigQuery 不會為此向您收費。

預先篩選您的輸入內容

如果可以在將您的輸入內容傳送至 UDF 之前先輕鬆地進行篩選,您的查詢可能就會更快、更便宜。

上述的執行查詢範例是將子查詢當成輸入內容傳送至 urlDecode,而非傳送整個資料表。[fh-bigquery:wikipedia.pagecounts_201504] 資料表約有 56 億列,如果不是對篩選後的子查詢執行 UDE,而是對整份資料表執行 UDF,JavaScript 架構必須處理的列數將多出 21 倍以上。

避免永久的可變動狀態

請勿在 UDF 呼叫中儲存或存取可變動的狀態。以下程式碼範例說明這個情境:

// myCode.js
var numRows = 0;

function dontDoThis(r, emit) {
  emit(rowCount: ++numRows);
}

// The query.
SELECT max(rowCount) FROM dontDoThis(t);

上述範例不會如預期般運作,因為 BigQuery 將查詢分割到許多節點。每個節點都有獨立的 JavaScript 處理環境,其中累積了不同的 numRows 值。

有效率地使用記憶體

JavaScript 處理環境限制了每個查詢可用的記憶體。累積太多本機狀態的 UDF 查詢可能會因記憶體耗盡而失敗。

展開 select 查詢

您必須明確列出要從 UDF 中選取的資料欄。系統不支援 SELECT * FROM <UDF name>(...)

如要檢查輸入列資料的結構,可以使用 JSON.stringify() 來發送字串輸出資料欄:

bigquery.defineFunction(
  'examineInputFormat',
  ['some', 'input', 'columns'],
  [{name: 'input', type: 'string'}],
  function(r, emit) {
    emit({input: JSON.stringify(r)});
  }
);

返回頁首

限制

  • 處理單一資料列時,您的 UDF 輸出的資料量應該約在 5 MB 以內。
  • 每個使用者僅限同時在特定專案中執行約 6 個 UDF 查詢。如果您收到的錯誤說明您已超出並行查詢限制,請稍待片刻,然後再試一次。
  • UDF 可能會逾時,造成查詢無法完成。逾時可能只有短短 5 分鐘,但可能因多種因素而異,包括您的函式佔用多少使用者 CPU 作業時間,以及 JS 函式的輸入和輸出有多大。
  • 查詢工作最多可以有 50 個 UDF 資源 (內嵌程式碼 blob 或外部檔案)。
  • 每個內嵌程式碼 blob 的大小上限為 32 KB。如要使用更大的程式碼資源,請將程式碼儲存在 Cloud Storage,然後將它當成外部資源來參照。
  • 每個外部程式碼資源的大小上限為 1 MB。
  • 所有外部程式碼資源的累積大小上限為 5 MB。

返回頁首

限制

  • 系統不支援 WindowDocumentNode 等 DOM 物件,以及需要這些物件的函式。
  • 系統不支援依賴原生程式碼的 JavaScript 函式。
  • JavaScript 中的位元作業僅處理最重要的 32 位元。
  • 叫用使用者定義函式的查詢由於具有非確定性本質,因此無法使用快取結果。

返回頁首

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁