S3 にあるダブルクォーテーション引用符の CSV データを Glue のクローラで検出し、Athena でクエリ実行する

こんにちは!! 筧( @TakaakiKakei )です。

皆さんは Athena で ダブルクォーテーション引用符の CSV データを扱う際に、うまくテーブル作成ができなかった経験はありませんか？ 先日、当該データを Athena で分析する際に少し引っかかったので、作成方法の1つを紹介したいと思います。

それでは早速やっていくっ！！

目標

Athena で対象の CSV データをクエリした際に以下のように表示することが目標です。

実践

CSV データを用意

以下のような CSV データを用意します

• ファイル名：price-list.csv
• ファイルの中身：

"id","name","price"
"1"," apple","100"
"2","orange","150"
"3","grape","300"
"4","peach","250"
"5","banana","90"

CSV データを S3 に保存

以下のような S3 バケットとフォルダを作成して、用意した CSV データをアップロードします

• バケット名： demo-athena-csv-data-XXXXXXXXXXXX （末尾の X は自身の AWS アカウント ID）
• フォルダ名： test-data
• ファイル： 先ほど作成した price-list.csv

Glue と Athena で処理

S3 バケットに保存した CSV データを Glue でクローラ実行後、Athena でそのまま表示させます。 最初にうまくいかないパターンを確認後、Glue でテーブルの設定変更を行い、うまくいくパターンを確認します。

AWS Glue でクローラの追加

AWS Glue で以下のようなクローラを追加します

主な設定内容

• クローラの名前：demo-crawler-for-price-list
• Crawler source type： DataStores
• データストア：S3
• インクルードパス：s3://demo-athena-csv-data-XXXXXXXXXXXX/test-data
• IAMロールの選択：IAMロールを作成する（AWSGlueServiceRole-demo-crawler-for-price-list）
• 頻度：オンデマンド
• データベース：detabase-price-list-csv

クローラの実行

作成したクローラを実行します

テーブルの確認

クローラの実行完了後、対象のテーブルの内容を確認します

データの確認

作成したテーブルを選択して、Athena でデータの確認をしてみましょう。

id や price に値がなく、name の値もダブルクォーテーション引用符がついたままになっています（失敗）

作成したテーブルを修正

id や price に値が表示され、name の値のダブルクォーテーション引用符を削除して表示するにはどのようにすれば良いでしょうか。 以下の公式ドキュメントによれば、OpenCSVSerDe を使えばうまく表示できそうです。

CSV を処理するための OpenCSVSerDe | AWS

データに含まれる値が二重引用符 (") で囲まれている場合は、Athena で OpenCSV SerDe を使用して値を逆シリアル化できます。次のセクションでは、STRING データ型でのこの SerDe の動作に注目してください。

公式ドキュメント記載を参考にして、作成したテーブルの Serde シリアル化ライブラリと Serde パラメータを修正します。

修正内容

• Serde シリアル化ライブラリを org.apache.hadoop.hive.serde2.OpenCSVSerde に変更
• Serde パラメータから field.delim : , を削除
• Serde パラメータに escapeChar : \ quoteChar : " separatorChar : , を追加

作成したテーブルを選択して、データの再確認

Athena でデータの確認をしてみると、以下のように目標の表示ができました！

下記、黄色枠について追記しました¹

"id","name","price"
"1","apple","100"
2,"orange","150"
"3",grape,"300"
"4","peach",250

"id",name,"price"
"1",apple,"100"
"2",orange,"150"
"3",grape,"300"
"4","peach","250"

最後に

補足になりますが、AWS Glue を用いる際は文字コードにも注意しましょう。

[AWS Black Belt Onine Seminar] AWS Glue | AWS Solutions Architect ブログ

A14.AWS Glueとして特に文字コードの規定があるわけではありませんが、PySparkをベースにしているため、ジョブ処理時には文字列がUTF-8である事を想定しています。JDBCドライバ経由でRDBを読み取った場合はJDBCドライバ内で文字コードがUTF-8に変更されるものもあります。UTF-8以外のファイル（S3上）は読み取る際に文字コードをUTF-8変換する必用があります。

上記の記載があるので、扱うデータが UTF-8 以外の文字コードの場合は、文字コードを変更するスクリプトを Lambda で事前に実行しておくなどして、 UTF-8 に変更しておくことをおすすめします。 実際に今回利用した OpenCSVSerDe の場合、AWS Glue のテーブルのプロパティでは、文字コードが SJIS のデータをエンコーディングがすることは難しいように見受けれました。

以上！筧( @TakaakiKakei )でした！