Posted at

HIVEのクエリでデリミタで区切られた文字列の前後を入れ替える

某データを見ていたら、政令指定都市の場合に市区町村名が

千種区, 名古屋市

などと区, 市の順に並べて格納されており、正規化に難儀したので備忘録メモ。


解決したい課題・制約など


  • 市区町村データの中に区名, 市名という具合にデリミタで区切られて逆順になっているレコードがある

  • それらをHIVEのクエリの中でデリミタを取り除き通常の順に並び替えたい

  • それ以外は普通に市町村名となっているのでそれらに影響を与えないこと

  • HIVEのバージョンは1.1.0


解決例

HIVEのregexp_replaceが普通に後方参照を扱えるので、デリミタ前後をグループ化してキャプチャし$2$1として入れ替える。

デリミタは今回の場合カンマ,で、直後にスペースが入る可能性があったので,? *とした。


名古屋市千種区でテスト

hive> select regexp_replace('千種区, 名古屋市', '^([^,]+),? *([^,]*)$', '$2$1');

OK
名古屋市千種区

正規表現の該当部分
該当した文字列
意味

^

先頭

([^,]+)
千種区
カンマ(,)以外の文字1文字以上。$1で参照される

,? *
,
デリミタの指定。カンマ(,)と、その後に空白が続くかもしれない

([^,]*)
名古屋市
カンマ(,)以外の文字0文字以上、存在しないかもしれない。$2で参照される

$

末尾

$1千種区$2名古屋市になるので、$2$1名古屋市千種区になる。


通常の市町村名の場合はそのままの値が戻ることを確認。

hive> select regexp_replace('春日井市', '^([^,]+),? *([^,]*)$', '$2$1');

OK
春日井市

正規表現の該当部分
該当した文字列
意味

^

先頭

([^,]+)
春日井市
カンマ(,)以外の文字1文字以上。$1で参照される

,? *

デリミタの指定。カンマ(,)と、その後に空白が続くかもしれない

([^,]*)

カンマ(,)以外の文字0文字以上、存在しないかもしれない。$2で参照される

$

末尾

$2が空になるので、$2$1春日井市になる。


参考