top of page
検索
  • 執筆者の写真toshiki nishio

S3上のcsvファイルをデータソースとしてGlue Data catalogを作った時にハマったポイント


はじめに

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する方法があるようです)


  1. Glue Crawlerで作る方法

  2. 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では同様の現象が発生せず参照可能なようです。



一行目、1列目の項目の値がcsvファイルと異なる


一行目、1列目の項目の値が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や空白が入っていると参照時エラーとなる。








閲覧数:200回0件のコメント

最新記事

すべて表示

データ状態により異なるSQLを実行させたい

はじめに 近頃担当する業務は夜間バッチでのデータ更新処理が多く、特にDWH的にテーブル再構築(TRUNCATE/INSERT)のパターンを多く使用しています。 その中でSQLで処理を組み上げる時、エラー処理などで条件分岐で異なるSQLを実行したくなる事は珍しくありません。 多くのシステムでは呼び出し側でSQLの実行結果を参照し、次に実行するSQLを選択/実行していると思います。 また、SQLだけで

コメント


bottom of page