Athena, Redshift Spectrum で CSV 読み込む時の注意点

概要

CSV の読み込みには以下があるが、2重引用符内に , を含むデータがある場合は要注意。

1. Glue Crawler を利用する
2. Athena, Redshift Spectrum から DDL で外部テーブル定義を行う

問題点

Glue Crawler を利用して csv から Glue カタログにテーブルを作成する際は、デフォルトで LazySimpleSerDe が使われる。しかし、以下のような落とし穴があるので注意する。

LazySimpleSerDe は データ内に引用符で囲まれた値がない場合は、この SerDe を使用します。

とのことで、以下のようなデータが CSV ファイルに存在すると適切にデータを読み込むことができません。

sample.csv

column_a,column_b
abc,1000
def,"2,000"

クエリ結果 (Redshift Spectrum からのクエリ結果)
以下のように、適切に読み込まれない。

対応

以下の対応を検討しましょう。

1. OpenCSV Serde で DDL を書く
2. CSV を Parquet に変換してから、Glue Crawler にかける ( ※ 変換掛ける前に Header 情報を削除しておきましょう。)

1の実装例

create_external_table.ddl

CREATE EXTERNAL TABLE IF NOT EXISTS test_csv_serde (
  `column_a` string,
  `column_b` string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  'separatorChar' = ',',
  'quoteChar' = '"',
  'escapeChar' = '\\'
)
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://{YOUR BUCKET NAME}/{YOUR PREFIX NAME}/'
TBLPROPERTIES (
'classification' = 'csv',
'skip.header.line.count' = '1'
)
;

結果
正常に読み込まれている。

まとめ

LazySimpleSerDe には気をつけましよう。

参考