【データ基盤構築/BigQuery】URLの表記揺れの名寄せを行う

今回の課題

社内のマスタデータからBigQueryにデータを取り込んだところ、URLに表記揺れがあった。
処理をしやすいように名寄せ処理を行う必要があった。

名寄せ処理が必要なデータを目視で探した

下記のクエリを実行して、データの除去や名寄せが必要なデータを目視でざっと探してみた。

--dateをdate型に変換。page_pathが「/contents/」で始まるURLに絞る。page_pathの「index」「&」「?」文字列以降のデータを削除。
with master as (
    select
        page_title
        , page_path
    from
        `テーブル名`
    where
        regexp_contains(page_path, r'^/contents/') --「/contents/」で始まるURLに絞る。   
)

--page_pathカラムがすべて「/」で終了するように調整。
, master2 as (
    select
        page_title
        , split(page_path, '/') as page_path_array
    from
        master
)

--必要なカラムを抽出
select 
    page_path_array
    , offset
from 
    master2 as m
cross join unnest(m.page_path_array)　as page_path_array
with offset as offset
group by
    page_path_array 
    , offset

上記クエリの解説

• CROSS JOIN UNNESTとSPLIT関数を組み合わせることで、URLを単一の列の複数レコードに分割している。
  ※参考：cross join unnestを利用して、カンマ区切りのデータを単一の列の複数レコードに分解する
• page_path_array, offsetでGROUP BYすることによって、ユニークなデータを抽出している。こうすることによって、目視で探すデータの数を減らした。
• with ofset as offsetで添え字をoffsetカラムに格納するようにしている。
  ※参考：BigQueryのArrayを理解する

上記のクエリを実行した結果、下記の名寄せ処理の条件の項目のURLを名寄せする必要があると分かった。

名寄せ処理の条件

下記のように名寄せを行った。

1. /contents/から始まるURLに絞り込む（それ以外は除外する）
2. indexが付いているURLを除外（URLの最後にindex.htmlといった記述があるものを除外したい。）
3. パラメータ付きのURLを除外（?~, &~となっているURL）
4. /で終わっていないURLは、/を付け足しする。
5. 上記を除外した後で他に最後のスラッシュ後に余計なものが付与されているデータを除去する。（offsetが4なのに、https://～という文字列が付いているURLがあったので、それを除去したい。）

※除外しきれないURLがあった際、そういったデータはゴミデータとして除外してしまうのもあり。

実装方法

• 名寄せ処理の条件1~4について
  page_pathのindex, &, ?文字列以降のデータを削除した状態から、
  更に/で終っているかどうかを正規表現で判定して/で終わっていなければ/を末尾に付与する。

• 名寄せ処理の条件5について
  1~4を除外した後に、最後の/の後ろにhttps:~が付与されていたので、
  下記クエリの(.*/https:/).*$で除外した。

with master as (
    select
        page_title
       , regexp_replace(normalize(page_path, NFKC), r'(index|&|\?).*|[^a-zA-Z0-9-/:-@-~_]|[ +]|(.*/https:).*$', "") as page_path_fix --データの揺れを調整
    from
        `テーブル名`
    where
        regexp_contains(page_path, r'^/contents/') --「/contents/」で始まるURLに絞り込む。
)

select
    page_title
    , page_path -- 一応、元のpage_pathも保持しておく
    , if(regexp_contains(page_path, r'.*/$'), concat(page_path, ""), concat(page_path, "/")) as page_path_fix -- page_pathカラムが全て「/」で終了するように調整。
from
    master
;

補足

• 全角数字が混在していたので、normalize(page_path, NFKC)で正規化（英数カナすべて統一）した。
  NFKC正規化は、「全角を半角にする」時に役に立つ。（参考：全角英数字を半角に変換【BigQuery】）

• 業務では上記クエリの正規表現で、URLを名寄せできたが、こちらの引用URLの正規表現も使えそう。（引用の方が良さそうなので試してみる）
  ※参考：【BigQuery】正規表現を使ってURLを分解する - ドメイン名｜パス(path)｜utmパラメータ
  

• page_path_fix（名寄せしたURL）とpage_path（名寄せする前のURL）は、
  念のため両方保持しておくのが良さそう。（確実に不要であれば、除去してもOK)

以上で、名寄せ処理が完了した。