はじめに
S3ファイルをデータソースとして Glue Data catalogを作った時のハマりポイントを共有します。その前にGlueとはなんぞやといったことから紹介していきます。
Glueとは？
抽出（Extract）、変換（Transform）、ロード（Load）（略称：ETL）を行う完全マネージド型のサービス。
Extract - 外部の情報源からデータを抽出
Transform - 抽出したデータをビジネスでの必要に応じて変換・加工
Load - 最終的ターゲット（すなわちデータウェアハウス）に変換・加工済みのデータをロード
Glueを使うと何が嬉しいの？
GlueにはData catalogというものがあります。これを作るとAmazon Athena、Amazon EMR、Amazon Redshift SpectrumといったAWSサービスでクエリを利用できるようになります。Data catalogはS3やRedshiftなどをデータソースとして設定することができ、S3にファイルが置かれたらそのデータをRedshiftやAthenaで参照することができるような世界を実現できます。
Glue Data catalogの作り方
私がよく利用するのは、以下のような作り方です。（他にもAthenaからCreate tableする方法があるようです）
Glue Crawlerで作る方法
Redshift spectrumのCREATE EXTERNAL TABLEで作る方法
1. Glue Crawlerで作る方法
GlueにはCrawlerという機能があります。Crawlerはデータソースを参照しデータの形式とスキーマを推論して、データカタログを作ってくれます。データカタログを明示的に作らずとも、クローラーで作ってくれるため短時間でシステム構築したい場合に有効ではないかと思います。
2. Redshift spectrumの外部テーブルで作る方法
以下のようにRedshift spectrumの機能で、外部スキーマと外部テーブルを使って静的なテーブルを作る方法です。
create external schema if not exists bi_spectrum from data catalog
database 'database_name'
iam_role '${ROLE_REDSHIFT_ARN}'
create external database if not exists;
CREATE EXTERNAL TABLE bi_spectrum.ALL_TABLE_NAME (
file_name_shikibetu VARCHAR(1),
file_name VARCHAR(32),
insert_time TIMESTAMP
)
PARTITIONED BY (eigyo_dt date)
STORED AS parquet
LOCATION 's3://${SPECTRUM_BUCKET}/table_name/';ハマりポイント
では、ここから私がハマったポイントについて紹介していきます。
Data catalogを作る方法として、クローラーとRedshift spectrumの外部テーブルで作る方法を紹介しましたが、両方Data Catalogを作るという点は同じなのでこれから紹介するハマりポイントはどちらも当てはまると思われます。
データ参照時にcsvのダブルクォートが表示される。
以下画像のようにダブルクォートが表示される場合、 Serdeシリアル化ライブラリとSerde パラメータを設定することで解決できます。
設定内容は以下の通り。
Serde シリアル化ライブラリ：
org.apache.hadoop.hive.serde2.OpenCSVSerde
Serde パラメータ
escapeChar : \
quoteChar : "
separatorChar : ,
この設定はGlueのコンソールから設定することができます。
Redshift spectrumのCREATE EXTERNAL TABLEで作る場合は以下のように、指定することで上記のSerdeの設定が可能です。
CREATE EXTERNAL TABLE .users_from_athena(
id varchar(255),
name varchar(255),
created_at varchar(255)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'escapeChar'='\\',
'quoteChar'='\"',
'separatorChar'=',')
STORED AS TEXTFILE
LOCATION 's3:///users_from_athena/'
TABLE PROPERTIES (
'classification'='csv',
'skip.header.line.count'='1',
'typeOfData'='file')
;HIVE_BAD_DATA
Athenaで参照時に以下のようなエラーが出た場合は、Data catalogのスキーマで数値データ型と定義されている項目にNULLや空白が入っている可能性が高いです。
HIVE_BAD_DATA: Error reading field value: Cannot convert value of type String to a LONG value
以下のようにデータ型をstringにする方法が 公式ドキュメント で紹介されています。stringにすることでエラー回避可能です。stringのためそのままでは数値計算できないため、数値計算をしたい場合は使う側でキャストが必要になります。
数値データ型として定義された列の空値または null 値を認識せず、string として残します。回避策の 1 つは、null 値を string とした列を作成してから、CAST を使用してクエリのフィールドを数値データ型に変換して、null の場合にデフォルト値の 0 を指定することです。詳細については、AWS ナレッジセンターの「Athena で CSV データをクエリすると、『HIVE_BAD_DATA: フィールド値の解析エラー』というエラーが表示されます」を参照してください。
Athenaで参照時に上記エラーが発生しますが、Redshiftでは同様の現象が発生せず参照可能なようです。
一行目、１列目の項目の値がcsvファイルと異なる
一行目、１列目の項目の値がcsvファイルとRedshiftで表示した場合で異なるという現象が発生しました。（下記表の通り、csvとAthenaでは「20160508」、Redshiftでは「20160」）
csvでの値
年月 | 項目1 |
20160508 | xxxx |
20160509 | xxxx |
20160510 | xxxx |
Redshiftでの表示
年月 | 項目1 |
20160 | xxxx |
20160509 | xxxx |
20160510 | xxxx |
Athenaでの表示
年月 | 項目1 |
20160508 | xxxx |
20160509 | xxxx |
20160510 | xxxx |
この現象はデータソースのファイルがUTF8-BOM付きだったことが原因だったようです。（UTF8に文字コード変換することで解決。）データソースとするファイルがUTF8かどうかは気をつけましょう。
こちらのAWSブログで記載の通り、GlueはS3上のファイルがUTF-8であることを想定しているため、UTF8に変換する必要があります。
Q14 文字エンコードの縛りはありますか？A14.AWS Glueとして特に文字コードの規定があるわけではありませんが、PySparkをベースにしているため、ジョブ処理時には文字列がUTF-8である事を想定しています。JDBCドライバ経由でRDBを読み取った場合はJDBCドライバ内で文字コードがUTF-8に変更されるものもあります。UTF-8以外のファイル（S3上）は読み取る際に文字コードをUTF-8変換する必用があります。
まとめ
色々なハマりポイントを紹介して来ましたが、S3上のcsvファイルをデータソースとして Data Catalogを作る方法としてクローラーを使う場合と、Redshift spectrumを使った場合では以下のようなメリットがあると感じました。以下を踏まえて、どちらを選択するかの方針を決定するといいかもしれません。
Glue Data Catalogを作る方式毎の特徴
方式 | メリット |
クローラー | クローラーに任せて、大量のIFのデータカタログを短時間で構築可能 |
Redshift spectrum | テーブル定義の型定義を静的に決定できる。 |
また、データを参照するサービスとしてRedshiftとAthenaを紹介しましたが、参照サービス毎で型定義をする時に気を付けるべきポイントが違うこともわかりました。
型定義時に気をつけるべきポイント
AWS サービス | 制限事項 |
Redshift | ー |
Athena | 数値型項目を参照時、値にNULLや空白が入っていると参照時エラーとなる。 |
Comments