アプリケーションログの保存先として使用しているHiveにMIMEエンコードされた状態で保存されていたデータがあったので、HiveQLでデコードする方法を考えてみました。HiveQLの関数だけでやろうとするとあまりいい方法が思いつきませんでしたが、とりあえず実現する方法は編み出したのでメモっときます。

はじめに - HiveQLとは

HiveQLとは一言でいうとHive上で使用できるSQLに似たクエリ言語になります。Hiveって何？って方は、こちらのページの説明がわかりやすいと思ったので、リンクさせていただきます。

要点をまとめると、Hive (Apache Hive) とはSQL（厳密にはHiveQLというSQLに近い言語）を使用してHDFS (Hadoop Distributed File System)のデータを集計・分析することを可能にするソフトウェアです。HDFSは複数のコンピューターのハードディスクを一つのストレージのように扱えるスケーラブルな分散型ファイルシステムであり、大規模なデータを格納できますが、データ集計を行う際MapReduceジョブ（各コンピューターに分割された大量のデータを分散処理するためのフレームワーク）を自身で定義（プログラミング）しなければなりません。そこで開発されたのがHiveで、MapReduceの知識がなくてもHiveQLというSQLライクな言語でHadoop上のデータを解析できます！

HiveQLはSQLをベースにしているものの、フルサポートしているわけではないため、一部使用できない構文もあるようです。以下のページにHiveQLの言語レファレンスがございます。

Ref: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

MIMEエンコードについて

MIMEとは、規格上ASCII（7bit文字）のテキストしか使用できないインターネットの電子メールでさまざまなフォーマット（書式）を扱えるようにする規格です。日本語などnon-ASCIIの文字も、MIMEエンコードしASCII文字に変換することでメールで使えるようになります。

メールヘッダー上にASCII以外の文字列を含める場合は、以下のような形式で指定します。

=?文字セット?エンコード方式?エンコードされた文字列?=

文字セット（charset）はUTF-8、エンコード方式（Content-Transfer-Encoding）はBase64でエンコードされたSubjectヘッダー（件名）は例えば以下のような文字列になります。

Subject: =?UTF-8?B?55m76Yyy44GX44Gf44Oh44O844Or44Ki44OJ44Os44K544KS56K66KqN44GX?= =?UTF-8?B?44Gm44GP44Gg44GV44GE?=

今回の例ではこの組み合わせ（=?UTF-8?B?）でエンコードされた文字列をHiveQLを使用してデコードしていきます。

メールデコード処理の流れ

全体の流れとしては以下のようになります：

元の文字列を「=?UTF-8?B」で区切って分割（splitを使用）し、エンコードされた文字列部分を配列に格納する
配列の各要素のエンコードされた文字列をBinaryに変換（unbase64を使用）する
Binaryに変換された各要素をデコードしStringに変換（decodeを使用）する
最後に各要素のStringを連結（concatを使用）する

以下が上記流れを踏んだ完成形のクエリです：

完成形のクエリ

select concat(
       case when length(split(subject,'=\\?UTF-8\\?B\\?')[0])>0 then split(subject,'=\\?UTF-8\\?B\\?')[0] else '' end,
       case when length(split(subject,'=\\?UTF-8\\?B\\?')[1])>0 then decode(unbase64(split(subject,'=\\?UTF-8\\?B\\?')[1]),'UTF-8') else '' end,
       case when length(split(subject,'=\\?UTF-8\\?B\\?')[2])>0 then decode(unbase64(split(subject,'=\\?UTF-8\\?B\\?')[2]),'UTF-8') else '' end
    ) as decoded_subject
  from email_table

次のセクションでどのようにして上記クエリにたどり着いたか解説します。

デコードしてみる

それでは、実際にMIMEエンコードされたデータを上記のデコードしていきましょう。Hive上にあるテーブル（「email_log」とします）の「subject」というカラムに以下のようなString型のデータが格納されている前提で、上記流れに従ってデコードをします。

=?UTF-8?B?55m76Yyy44GX44Gf44Oh44O844Or44Ki44OJ44Os44K544KS56K66KqN44GX?= =?UTF-8?B?44Gm44GP44Gg44GV44GE?=

まずsplitで「=?UTF-8?B?」という文字列で区切って分割します

hive> select split(subject,'=\\?UTF-8\\?B\\?') 
        from email_log;
+--------------------------------------------------------------------------------------------+
|split(subject, =\?UTF-8\?B\?, -1)                                                           |
+--------------------------------------------------------------------------------------------+
|[, 55m76Yyy44GX44Gf44Oh44O844Or44Ki44OJ44Os44K544KS56K66KqN44GX?= , 44Gm44GP44Gg44GV44GE?=] |
+--------------------------------------------------------------------------------------------+

0番目の要素: Null
1番目の要素: 55m76Yyy44GX44Gf44Oh44O844Or44Ki44OJ44Os44K544KS56K66KqN44GX?=
2番目の要素: 44Gm44GP44Gg44GV44GE?=

配列の各要素（本例では1要素目と2要素目）にはBase64エンコードされた文字列がセットされているので、unbase64でBase64エンコードされた文字列をBinaryに変換します

hive> select unbase64( split(subject,'=\\?UTF-8\\?B\\?')[1] ),
             unbase64( split(subject,'=\\?UTF-8\\?B\\?')[2] )
        from email_log;
+----------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------+
|unbase64(split(subject, =\?UTF-8\?B\?, -1)[1])                                                                                          |unbase64(split(subject, =\?UTF-8\?B\?, -1)[2])                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------+
|[E7 99 BB E9 8C B2 E3 81 97 E3 81 9F E3 83 A1 E3 83 BC E3 83 AB E3 82 A2 E3 83 89 E3 83 AC E3 82 B9 E3 82 92 E7 A2 BA E8 AA 8D E3 81 97]|[E3 81 A6 E3 81 8F E3 81 A0 E3 81 95 E3 81 84]                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------+

decodeで（UTF-8をcharsetとして指定）してBinaryをStringへと変換します

hive> select decode( unbase64(split(subject,'=\\?UTF-8\\?B\\?')[1]),'UTF-8' ),
             decode( unbase64(split(subject,'=\\?UTF-8\\?B\\?')[2]),'UTF-8' )
        from email_log;
+-------------------------------------------------------------+-------------------------------------------------------------+
|decode(unbase64(split(subject, =\?UTF-8\?B\?, -1)[1]), UTF-8)|decode(unbase64(split(subject, =\?UTF-8\?B\?, -1)[2]), UTF-8)|
+-------------------------------------------------------------+-------------------------------------------------------------+
|登録したメールアドレスを確認し                             　   |てください                                                　  |
+-------------------------------------------------------------+-------------------------------------------------------------+

配列の1要素目と2要素目の文字列（3でMIMEデコードされた状態）をconcatで連結します

hive> select concat(
             decode(unbase64(split(subject,'=\\?UTF-8\\?B\\?')[1]),'UTF-8'),
             decode(unbase64(split(subject,'=\\?UTF-8\\?B\\?')[2]),'UTF-8')
             ) as decoded_subject
        from email_log;
+------------------------------------------+
|decoded_subject                           |
+------------------------------------------+
|登録したメールアドレスを確認してください 　   |
+------------------------------------------+

これでデコードができましたね！

ちなみに、実データには以下のような英文字だけでそもそもエンコードされてないデータだったり、

Welcome to ABC!

以下のように英文字で始まり途中から=?UTF-8?B?でエンコードされているような変化球もありました。

ABC =?UTF-8?B?44Gu44Ko44Oz44K/44O844OX44Op44Kk44K644ON44OD?= =?UTF-8?B?44OI44Ov44O844Kv44Gr44GU5Y+C5Yqg44GP44Gg44GV44GE?=

このようなデータも考慮し分岐処理を入れると以下のような感じになります（前述の完成形のクエリと同じ）

hive> select concat(
        case when length(split(subject,'=\\?UTF-8\\?B\\?')[0])>0 then split(subject,'=\\?UTF-8\\?B\\?')[0] else '' end,
        case when length(split(subject,'=\\?UTF-8\\?B\\?')[1])>0 then decode(unbase64(split(subject,'=\\?UTF-8\\?B\\?')[1]),'UTF-8') else '' end,
        case when length(split(subject,'=\\?UTF-8\\?B\\?')[2])>0 then decode(unbase64(split(subject,'=\\?UTF-8\\?B\\?')[2]),'UTF-8') else '' end
      ) as decoded_subject
     from email_log
+---------------------------------------------------+
|decoded_subject                                    |
+---------------------------------------------------+
|登録したメールアドレスを確認してください　　　　　　　　|
|Welcome to ABC!                                    |
|ABC のエンタープライズネットワークにご参加ください     |
+---------------------------------------------------+

※0番目の要素は=?UTF-8?B?で始まらない、エンコードされてない文字列なのでデコード処理は不要

ちなみに、上記クエリは=?UTF-8?B?で区切られた要素数が最大3個までという前提になってます。もし件名が長い場合は要素数が3より多くなり、クエリで返される文字列が途切れたものになってしまいます。その場合は、以下のようにN番目の要素までクエリを拡張する必要があります。

select concat(
       case when length(split(subject,'=\\?UTF-8\\?B\\?')[0])>0 then split(subject,'=\\?UTF-8\\?B\\?')[0] else '' end,
       case when length(split(subject,'=\\?UTF-8\\?B\\?')[1])>0 then decode(unbase64(split(subject,'=\\?UTF-8\\?B\\?')[1]),'UTF-8') else '' end,
       case when length(split(subject,'=\\?UTF-8\\?B\\?')[2])>0 then decode(unbase64(split(subject,'=\\?UTF-8\\?B\\?')[2]),'UTF-8') else '' end,
       case when length(split(subject,'=\\?UTF-8\\?B\\?')[3])>0 then decode(unbase64(split(subject,'=\\?UTF-8\\?B\\?')[3]),'UTF-8') else '' end,
       ...
       case when length(split(subject,'=\\?UTF-8\\?B\\?')[N])>0 then decode(unbase64(split(subject,'=\\?UTF-8\\?B\\?')[N]),'UTF-8') else '' end
    ) as decoded_subject
  from email_table

可変長にするクエリが思いつきませんでした。可変長にするいいアイデアがあれば教えてほしいですorz