どうも、こんにちは。tokitoです。今回は、AWSでデータカタログ化をしようとした際に、うまくいかなかったこと、それの解決方法などを書いていこうと思います。作成するにあたって問題に当たった時、Glueのデータカタログ化などについての記事の内容が少なく苦戦したので、もし問題に当たった時、自分の問題点・解決方法を参照して解決していってください！
作成したもの
構成図
指定のS3バケットに、インポートされるCSVファイルを、自動でデータカタログ化してAthenaでクエリを実行できるようにするものが作成したいと思っていました。しかし、作成するにあたって様々な問題にぶち当たりました。
Crawlerの挙動確認
とりあえず、S3に作成したテストデータに対して、手動でデータカタログ化するものを作成。ここでは、Crawlerの挙動・Athenaでの出力結果などを表示させようとしていました。
今回、作成するプログラムの元データのサンプル1です。上図、構成図のa.csvなどにあたる部分です。
サンプルデータ1
name,age,work,hobby
"tokito","18","student","baseball"
"tom","20","student","soccer"
"Brown","27","doctor","tennis"
"Johnes","62","teacher","walking"
上記のcsvを、crawlerを実行。作成したテーブルDDLは以下のどうり。Serde serialization libは、クローラ作成時のLazySimpleSerDe。
見る感じ読み込まれた型は、想定どうり。
この結果をAthenaで確認したところ、bigint型のageカラムのみ表示されず、表示されているほかのカラムに関しても、ダブルクォーテーションがついており微妙な感じ。。。
Serde serialization libを、OpenCSVSerdeに変更した結果。クエリが実行できるようになった。
ここまでは、一般的なCSVファイルをCrawlerでデータカタログ化しているのみである。次からは、調べてみても的確な回答が出てこない問題に直面しました。
問題発生
自分は、次に２点のことが発生した場合に、Crawler・Athenaがどのような挙動をするのかを調べました。
・Crawlerを実行する階層に、新しくファイルが追加されたときどのような挙動をするか？
・カラム型ごとに、””(ダブルコーテーションの中のデータが、nullの場合)のときどのような挙動をするか？
上記２点を調査しました。すると、Athena上でクエリが実行できない状況に陥ってしまう問題が発生しました。原因を追求していくと、下記２点が原因ではないかということがわかりました。
・Crawlerの設定変更(Serdeの変更など)をしても、テーブルのみにしか変更点がいきわたらないため、パーティションごとに設定がことなる状況になってしまうため、エラーが発生する。
・OpenCSVSerdeを使用すると、int型のカラムのデータがnullの場合、OpenCSVSerdeの特性上、Athenaでクエリが実行できないため、エラーが発生する。
１つ目のの問題に関しては、今回のブログで再現することができませんでした。（多分、容量の大きいファイルでないとできないのかも。）また、１つ目の問題は原因がわからなさ過ぎて、classmethodに問い合わせしました（笑）。２つ目に関しては、AWS公式ドキュメントを参照することで原因を特定しました。
そして、今回問題が発覚した時のサンプルデータです。
サンプルデータ2
name,age,work,hobby
"tokito","18","student","baseball"
"tom","20","student","soccer"
"Brown","27","doctor","tennis"
"Johnes","","teacher","walking"
解決方法
・Crawlerの設定変更(Serdeの変更など)をしても、テーブルのみにしか変更点がいきわたらないため、パーティションごとに設定がことなる状況になってしまうため、エラーが発生する。
こちらに関しては、Crawlerの設定項目が問題でした。
Crawler -> Edit -> Step4 Set output and schedulingのAdvance optionsの中にある、赤枠のUpdate all new and existing partitions with metadata from the tableのチェックボックスに、チェックを入れて、再度crawlerを実行。すると、Athenaでクエリを実行してもエラーが出力されることはなくなりました。（テストデータで、再現することができなかったのでクエリが正常にする様子などの画像はありません。）
・OpenCSVSerdeを使用すると、int型のカラムのデータがnullの場合、OpenCSVSerdeの特性上、Athenaでクエリが実行できないため、エラーが発生する。
結論から言うと、この問題を根本的から解決することはできませんでした。AWSのドキュメントを参照すると、OpenCSVSerdeを使用した際、int型のカラムがnullの場合、String型判定されてデータカタログ化されてしまう為、Athenaでクエリを実行するとエラーが返ってきてしまいます。
しかし、データカタログ化できないと困ります。ここで、自分の発見した回避策を２つご紹介します。
＃OpenCSVSerdeは使用するが、すべてのカラムをString型にする
このようにすると、nullのカラムがあっても参照自体はできます。この方法をとるのが、データカタログ化する際の基本設計な気がします。(自分が調べた中では、この方法で対応しているものが多かったイメージ。)
この方法では、最初にテーブルのカラムをすべてStringにする必要があります。Glue crawlerは、作成時の設定ではcrawlerを実行する度に、Crawler元のファイルを解析しカラム型を再度付け直してしまうため、その設定をCrawlerを実行する度にカラム型を変更する設定から、新しいカラムが追加されたときにのみ追加されたカラムの型を設定するように変更します。
Crawler -> Edit -> Step4 Set output and schedulingのAdvance optionsの中にある、赤枠のAdd new columns onlyに変更します。変更することで、Crawler元にファイルが追加され、新たにcrawlerを実行しても、テーブルDDLの設定を変更する必要がなくなります。
ここまでの手順が終わると、Crawlerで作成したデータカタログのテーブル設定を行っていきます。先に伝えておきますが、この下の手順はデータカタログ化するテーブルが少ないときに推奨する方法です。複数個のテーブルを作成し、データを書き換える際には、Lambdaを使用してSerde・カラム型を変更することを推奨します。
Glue -> Data Catalog -> Table -> (クローラ先のテーブル名) -> Edit Schema
カラムを選択して、Editを押す。
Data typeを、Stringに変更してSaveを実行。そして、再度Crawlerを実行し、Athenaでクエリを実行すると。。。
クエリを正常に実行することができました。ただ、この時Athenaで特定のカラムに関して集計クエリを実行する際は、キャストしてから実行する必要があります。
＃RegexSerdeを使用して、Crawlerを実行する
上記の方法をとると、OpenCSVSerdeの時とは異なりカラムの型による制限なく、データカタログ化することができます。しかし、このRegexSerdeとは、正規表現を使用してデータカタログ化するものです。なので、CSVに対して使用する際には、「,」このカンマが重要になるため一部問題になることがあります。要するに、CSVのデータにカンマが含まれる場合は、Athenaでクエリを実行するとき、一列データが吹き飛んでしまうことがあります。
この方法では、まずこれまで使用してきたOpenCSVSerdeではなく、RegexSerdeを使用するためその設定を変更しないといけません。
Glue -> Data Catalog -> Table -> (クローラ先のテーブル名) -> Actions -> Edit table
Serialization libを、org.apache.hadoop.hive.serde2.RegexSerDeに変更。
Serde parametersに、Key: input.regex、 Value: 正規表現を入力 。
Serde parametersに入力する、正規表現はcsvの形式によって臨機応変に作成する。自分のテストデータの場合は、\”?([^,\”?]*)\”?, をカラム数に合わせて入力しています。多分、正規表現の意味が分からない人のために簡単に説明します(書いている本人が、わかってなかったのでわかりにくいとは思いますが…)。CSVを例に挙げると、
カラム名,カラム名,カラム名
"a","b","1"上記のような形で、データが保管されている。１行目のヘッダー部分を読み飛ばすと、データは”***”,”***”,”***”という形式で保管されている。正規表現では、このデータ部の形が、ここで設定する正規表現とマッチした際に、うまい具合にデータをカタログ化するということである。
設定項目を更新したら、更新内容を保存して、再度crawlerを実行。すると、Athenaでクエリを実行すると。。。
クエリが実行することができます。ちなみに、正規表現のセットが甘いとクエリにかかる時間が１回５分ぐらいになることがあるので、運用する際にはお気を付けを。(Athenaは時間に対する課金ではなく、読み込みデータ量での課金なので問題はないと思いますが。)
ここまで回避策を２つ記載しましたが、どちらを選択するかは、テーブルの設計しだいですね。。。そのテーブルに対して、集計クエリを多用する場合は、RegexSerdeを使用することで毎回キャストする負担を減らすことができる。カラム内に、カンマ(,)を含む場合は、OpenCSVSerdeを用いた方法を用いる。このように、どちらの方法を用いるかは、ケースバイケースだと思います。ちなみに、どちらの方法もLambdaか手動で、テーブルDDLを変更しないといけません。複数テーブルあるのであれば、LambdaでテーブルDDLを書き換える方が便利だと思います。
最後に
今回は、自分がGlueを使用してデータカタログ作成する際に詰まった点について記載していきました。Glueは今回触るまで聞いたことのないサービスですが、触ってみた結果、非常に便利なサービスであるなと感じました。まだまだ、自分はGlueについて知って浅いですが、この記事でCSVのデータをほかの形式に変換することなく、カタログ化したいんだよなという問題が解決すればとても良いなと感じています。
参考にしたサイトなど
・AWS公式ドキュメント
Crawler作成・Cloudformation作成のドキュメント
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/glue-best-practices.html
OpenCSVSerdeのドキュメント
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/csv-serde.html