BigQueryで正規表現を使いこなす

BigQueryの正規表現関数一覧

• REGEXP_CONTAINS: 文字列が正規表現を部分一致する場合に、trueを返します。
• REGEXP_EXTRACT / [REGEXPREPLACE[](https://cloud.google.com/bigquery/docs/reference/standard-sql/stringfunctions?hl=ja#regexp_replace): 文字列が正規表現を部分一致する部分の文字列を返します。一致しない場合は、NULLを返します。
• REGEXPEXTRACTALL: 正規表現と一致する文字列内のすべての部分文字列の配列を返します。
• REGEXP_INSTR: 文字列の正規表現に適合する箇所のインデックスを返却する
• REGEXP_REPLACE: 正規表現と一致する文字列内のすべての部分文字列を指定した文字列で置き換えます。

具体例

URLの構成要素を正規表現で分解し取得する

URLを分析するには、必要な情報だけを抜き出さないといけない。 正規表現もゴリゴリ使うので、基本的な分解を行うUDFを作っておくことで分析が楽になるかも？

    CREATE TEMP FUNCTION PARSE_QUERY_TO_JSON_STRING(q STRING)
    RETURNS STRING
    LANGUAGE js AS
        """
        var ret = {}
        var params = q.split("&")
        params.forEach(row => {
            var keyValue = row.split("=")
            ret[keyValue[0]] = keyValue[1]
        })
        return JSON.stringify(ret);
        """
    ;

    CREATE TEMP FUNCTION PARSE_QUERY(q STRING)
    RETURNS ARRAY<STRUCT<key STRING, value STRING>>
    LANGUAGE js AS
        """
        var ret = []
        var params = q.split("&")
        params.forEach(row => {
            var keyValue = row.split("=")
            ret.push({
            "key" : keyValue[0],
            "value": keyValue[1]
            })
        })
        return ret;
        """
    ;

    CREATE TEMP FUNCTION PARSE_URL (url STRING) AS (
      STRUCT(
        url AS original,
        REGEXP_EXTRACT(url, r'^([^\?]*)\??') as url,
        REGEXP_EXTRACT(url, r'^([a-zA-Z]+)://') as protocol,
        REGEXP_EXTRACT(url, r'(?:[a-zA-Z]+://)?([a-zA-Z0-9-.]+)/?') as host,
        REGEXP_EXTRACT(url, r'(?:[a-zA-Z]+://)?(?:[a-zA-Z0-9-.]+)/{1}([a-zA-Z0-9-./]+)') as path,
        REGEXP_EXTRACT(url, r'\?(.*)') as query,
        PARSE_QUERY(REGEXP_EXTRACT(url, r'\?(.*)')) as queries,
        PARSE_QUERY_TO_JSON_STRING(REGEXP_EXTRACT(url, r'\?(.*)')) as queries_json,
        REGEXP_EXTRACT(url, r'#(.*)') as ref
      )
    );

    WITH data AS (
    SELECT "https://game.yonayona.info/apex/tournaments/20?tab=teams" AS url
    )
    SELECT
    PARSE_URL(url) AS parsed_url
    FROM data

SNS投稿の文字列の構成要素を正規表現で分解し取得する

SNSの投稿には、メンションやハッシュタグなど、特定の記号を使うことで特殊な意味を持たせる記法が存在する。 今回は、下記のように、文字列を渡すと「メンションのリスト」と「ハッシュタグのリスト」を返却するUDFを作成した。

CREATE TEMP FUNCTION PARSE_SNS_TEXT (text STRING) AS (
  STRUCT(
    REGEXP_EXTRACT_ALL(text, r'@(\S+)') AS mention_list,
    REGEXP_EXTRACT_ALL(text, r'(?:^|\s)[＃#]{1}(\S+)') AS hashtag_list
  )
);

WITH org AS (
  SELECT "@mention テキスト本文テキスト本文テキスト本文テキスト本文 #hashtag_1 #hashtag_2 #ハッシュタグ #😊" AS text
)
SELECT
  PARSE_SNS_TEXT(text)
FROM org