AWS AthenaでINSERTするときに困ったこと

AWS Athenaを分析ツール（読み込み）として使ってみた系の記事はたくさんあるのですが、書き込み（INSERT）に関する記事が充実していないために調べるのに苦労したので、まとめておきたいと思います。

以下が直面した問題です。

• 問題１： CSVにnullを書き込むと「\N」という文字列が出力されてしまう
• 問題２： CSVが必ずgzipで圧縮され.gzファイルになった状態でS3に保存されてしまう

問題１

CSVにnullを書き込むと「\N」という文字列が出力されてしまう

例えば、CSVの出力を空文字 ” にしたい場合、nameもageも文字列型の場合は以下のようにすれば問題なく期待通りの結果になります。

INSERT INTO profile
SELECT
  'P001' AS profile_id,
  '' AS name,
  '' AS age
;

出力されるCSV ->

P001,,

では次に、ageが数値型の場合はどうすればよいでしょうか。「0」を設定したのでは「0」という文字列が出力されてしまうので null を設定するこになると思いますが、デフォルトでは「\N」という文字列になってしまい困りました。

INSERT INTO profile
SELECT
  'P001' AS profile_id,
  '' AS name,
  null AS age
;

出力されるCSV ->

P001,,\N

日本語では解説記事が見当たらなかったので、stack overflow を漁るとそれらしい設定を見つけることができました。

https://stackoverflow.com/questions/43263038/empty-string-is-not-treated-as-null-in-hive

それに従い、Create文に serialization.null.format プロパティを設定したところ、\N が空文字 ” になってくれました。

解決！

CREATE EXTERNAL TABLE `profile`(
  `profile_id` string, 
  `name` string, 
  `age` int)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://test-stg/profile'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'serialization.null.format'='')  # これを追加

問題２

CSVが必ずgzipで圧縮され.gzファイルになった状態でS3に保存されてしまう

.csv ファイルのまま圧縮せずにS3に保存できるようなプロパティはサポートされていないようです。残念！

以下の記事から少し時間が経っているので、サポートされたりしていないかと淡い期待をしながら公式HPを漁ってみましたが見つかりませんでした。無念！

https://stackoverflow.com/questions/56545753/aws-s3-athena-without-gz-compression

ではどうすれば良いかというとINSERT文にこだわらなければ、それらしいことが無理やりできます。

AthenaにはSELECT文を実行した際の結果をCSV出力する機能がコンソール画面で設定できるので、それをプログラムから利用します。ざっくりとですがPythonでAthenaを操作する手順を以下に列挙します。

1. AWSコンソールの画面上でSELECT文を実行した際に出力されるログ（CSV）の出力先に「s3://athena-log-stg」を設定する（このディレクトリ配下に「クエリID.csv」のような形式でSELECTの結果が保存される）
2. Pythonのboto3などでSELECT文を投げる（実行した際の「クエリID」が返却される）
3. boto3でS3の「s3://athena-log-stg/クエリID.csv」をPythonが動いているサーバーにダウンロードする
4. クエリID.csvを.tsvに変換したい場合は、この時点で行うと良い
5. boto3で上記のCSVファイル（およびTSV）をS3にアップロードする（INSERTした際にできる????.csv.gzファイルが格納されているディレクトリに置けばOK）

なんとか、どうにか、圧縮されていない生のCSVを保存することができました。解決！

Pythonコード自体は難しくないので上記の手順で実装すれば割と簡単に実現できると思います。

まとめ

AthenaのINSERTでは他のAWSの方法で実現できていたうちの一部が実現できないこともあるのではないかと、ちょっと不安な面もありましたが、意外と問題なく対応できそうだと分かりました。他にもイレギュラーな場面に遭遇した場合は記事にしたいと思います。ではでは。