---
title: "BigQueryでUDFを使用する | grasys blog"
url: "https://blog.grasys.io/post/higuchi/udf-bigquery"
description: "BigQueryはUDF(ユーザー定義の関数)をサポートしています。 UDFはJavaScriptとSQL式で記述する事が出来ます。 サポートされる UDF 言語 今回はデータ型がSTRINGのカラムにJSON形式のデータを入れているテーブルから、 UDFを使用してJSON形式のデータをパースして取得してみたいと思い…"
---

# BigQueryでUDFを使用する

-   ![](/_astro/noicon.CTHOhNiB_1HMs6A.webp)[higuchi](/authors/higuchi/)
-   公開日：2018年1月9日
-   カテゴリー：[Tech](/categories/tech/)
-   タグ：[#BigQuery](/tags/bigquery/)

![BigQueryでUDFを使用する](/_astro/og-image.DDSiu2Ny_XHpDd.webp)

BigQueryはUDF(ユーザー定義の関数)をサポートしています。  
UDFはJavaScriptとSQL式で記述する事が出来ます。

[サポートされる UDF 言語](https://cloud.google.com/bigquery/sql-reference/user-defined-functions?hl=ja#supported-udf-languages)

今回はデータ型がSTRINGのカラムにJSON形式のデータを入れているテーブルから、 UDFを使用してJSON形式のデータをパースして取得してみたいと思います。

使用するテーブルとデータは下記の通りです。

## テーブル定義

| name | type |
| --- | --- |
| id | INTEGER |
| user_name | STRING |
| profile | STRING |

## テーブル内容

| id | user_name | profile |
| --- | --- | --- |
| 1 | taro | {“age”:29, “address”:{“country”:81, “pref”:“北海道”}} |
| 2 | jiro | {“age”:24, “address”:{“country”:81, “pref”:“東京”}} |

## 取得したい形式

| id | user_name | age | country | pref |
| --- | --- | --- | --- | --- |
| 1 | taro | 29 | 81 | 北海道 |
| 2 | jiro | 24 | 81 | 東京 |

## 標準SQLの関数でデータを取得する場合

上記のテーブルとデータであれば、UDFを使用せずに標準SQLの関数を 使用して取得する事もできます。

Plain textcontent\_copy

```
bq query \
  --use_legacy_sql=false \
  "SELECT
     id,
     name,
     age,
     JSON_EXTRACT_SCALAR(address, '$.country') AS country,
     JSON_EXTRACT(address, '$.pref') AS pref
   FROM (
         SELECT
           id,
           name,
           JSON_EXTRACT_SCALAR(profile, '$.age') AS age,
           JSON_EXTRACT(profile, '$.address') AS address
         FROM DATASET.TABLE
```

## UDF内でJavaScriptの標準メソッドJSON.prase()を使用して取得する場合

Plain textcontent\_copy

```
bq query \
  --use_legacy_sql=false \
  "CREATE TEMPORARY FUNCTION
     parse(x STRING) RETURNS STRUCT<age INT64, address STRUCT<country INT64, pref STRING>> LANGUAGE js AS
     ' var row = JSON.parse(x); return row; ';
   SELECT
     id, name, profile.address.country, profile.address.pref
     FROM (
       SELECT
         id, name, parse(profile) as profile
       FROM DATASET.TABLE 
   )"
```

UDFの定義部分は下記になります。

Plain textcontent\_copy

```
  CREATE TEMPORARY FUNCTION
    parse(x STRING) RETURNS STRUCT<age INT64, address STRUCT<country INT64, pref STRING>> LANGUAGE js AS
    ' var row = JSON.parse(x); return row; ';
```

parseという関数を定義していて、引数で文字列xを渡すと、その文字列に対してJSON.parse()を実行し、 JSON文字列からオブジェクトに変換され、オブジェクトを返しています。 返されるオブジェクトはSTRUCTで指定してある各メンバー毎のデータ型に変換されます。

## 外部JSライブラリを使用する

UDFは外部JSライブラリを使用する事も出来ます。

標準メソッドであるJSON.parse()を再現しているjson2をincloudして、 json2のJSON.parse()を実行してみたいと思います。 json2はGCS上に配置している想定です。

[json2](https://github.com/douglascrockford/JSON-js)

Plain textcontent\_copy

```
  CREATE TEMPORARY FUNCTION
    parse(x STRING) RETURNS STRUCT<age INT64, address STRUCT<country INT64, pref STRING>> LANGUAGE js AS
    ' var row = JSON.parse(x); return row; ';
  OPTIONS (
    library="gs://bucket/path/to/json2.js",
  )
```

標準SQLの関数では吸収出来ない処理がある場合などは、UDFを使ってみるといいと思います。

## この記事を書いた人

[![](/_astro/noicon.CTHOhNiB_Z24aAgM.webp)](/authors/higuchi/)

### [higuchi](/authors/higuchi/)

higuchiのプロフィールと執筆記事をご覧いただけます。

[プロフィールと記事一覧](/authors/higuchi/)