Amazon Redshift Spectrum ソースデータをマニフェストファイルで定義する

はじめに

本日は、ソースデータをS3のフォルダ指定ではなく、ソースデータとなるファイルの一覧である「マニフェストファイル」で定義する方法について解説します。

Amazon Redshift Spectrum、Amazon Athena、AWS Glueでは、ソースデータはS3上のフォルダを指定します。しかし、データファイルが必ずしも種類ごとに１つのフォルダのまとめられていると限らず、１つのフォルダの中に複数のフォーマットが混在した場合は専用のフォルダを作成してデータファイルをコピーする必要があります。Amazon Redshift Spectrum では、さらにソースデータの定義にソースデータとなるファイルの一覧を定義したマニフェストファイルを指定することができます。

• 参考：データベース開発者ガイド - CREATE EXTERNAL TABLE

マニフェストファイルとは

マニフェストファイルは、ソースファイルの一覧を定義するファイルです。RedshiftのCOPYコマンドを実行する際のデータソース指定にも利用されるJSON形式のファイルです。しかし、Redshift Spectrumでは必須のパラメタが異なりますので別の形式と考えたほうが良いでしょう。

Amazon Redshift Spectrum マニフェストファイルの例

Amazon S3 からロードする各ファイルのURLとファイルサイズ (バイト単位) を指定します。URLにはバケット名およびファイルの完全オブジェクトパスが含まれます。マニフェストに指定するファイルの場所は異なるバケットでもかまいませんが、すべてのバケットは Amazon Redshiftクラスターと同じリージョンに置かれている必要があります。ファイルが2回リストされている場合、ファイルは2回スキャンされます。

{
"entries": [
{"url":"s3://mybucket-alpha/custdata.1", "meta": { "content_length": 5956875 } },
{"url":"s3://mybucket-alpha/custdata.2", "meta": { "content_length": 5997091 } },
{"url":"s3://mybucket-beta/custdata.1", "meta": { "content_length": 5978675 } }
]
}

テーブルを定義してクエリを実行する

データは以下のようなtsvファイルで、ファイルの先頭行はヘッダ行です。

Row ID Order ID Order Date Ship Date Ship Mode Customer ID Customer Name Segment Country City State Region Product ID Category Sub-Category Product Name Sales Quantity Discount Profit
6667 JP-2015-1735881 2015/5/2 2015/5/6 通常配送 田葵-21535 田幡 葵 小規模事業所 焼津 静岡 日本 中部地方 家電-電話-10002868 家電 電話機 モトローラ 信号ブースター, 大容量パック 34372.8 6 0.4 -20059.2
6668 JP-2017-1950692 2017/12/27 2017/12/31 通常配送 蔵真-13090 蔵前 真弓 消費者 岸和田 大阪 日本 関西地方 事務用-文房-10001218 事務用品 文房具 Elite カッター ナイフ, 業務用 12170 5 0 425
：
：

ソースデータのフォルダ構成は以下の通りです。

s3://my_bucket/orders_jp_files
├── orders_jp.manifest
└── orders_jp_files
├── orders_jp_001.tsv
├── orders_jp_002.tsv
└── orders_jp_003.tsv

$ aws s3 ls s3://my_bucket/orders_jp_files/orders_jp_files/
2018-01-24 20:14:48 0
2018-01-25 18:30:42 2498 orders_jp_001.tsv
2018-01-25 18:30:43 2487 orders_jp_002.tsv
2018-01-25 18:30:44 2494 orders_jp_003.tsv

今回は、そのうちorders_jp_001.tsv(3332レコード)とorders_jp_003.tsv(3334レコード)のファイルのみを対象としますので、マニフェストファイル(orders_jp.manifest)は以下のように記載します。

{
"entries": [
{"url":"s3://my_bucket/orders_jp_files/orders_jp_files/orders_jp_001.tsv", "meta": { "content_length": 2498 } },
{"url":"s3://my_bucket/orders_jp_files/orders_jp_files/orders_jp_003.tsv", "meta": { "content_length": 2494 } }
]
}

LOCATION設定にマニフェストファイルのURLを定義します。

--DROP TABLE cm_user_db.orders_jp_files;
CREATE EXTERNAL TABLE cm_user_db.orders_jp_files(
row_id bigint,
order_id varchar(255),
order_date varchar(255),
ship_date varchar(255),
ship_mode varchar(255),
customer_id varchar(255),
customer_name varchar(255),
segment varchar(255),
country varchar(255),
city varchar(255),
state varchar(255),
region varchar(255),
product_id varchar(255),
category varchar(255),
sub_category varchar(255),
product_name varchar(255),
sales double precision,
quantity bigint,
discount double precision,
profit double precision)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION
's3://my_bucket/orders_jp_files/orders_jp.manifest'
TABLE PROPERTIES (
'CrawlerSchemaDeserializerVersion'='1.0',
'CrawlerSchemaSerializerVersion'='1.0',
'UPDATED_BY_CRAWLER'='orders_jp',
'averageRecordSize'='214',
'classification'='csv',
'columnsOrdered'='true',
'compressionType'='none',
'delimiter'='\t',
'objectCount'='1',
'recordCount'='10770',
'sizeKey'='2304877',
'skip.header.line.count'='1',
'typeOfData'='file')
;

すべてのカラムが正しく標示され、レコード数も２つのファイルのレコード数に一致しています。

cmdb=> select * from cm_user_db.orders_jp_files;
row_id | order_id | order_date | ship_date | ship_mode | customer_id | customer_name | segment | country | city | state | region | product_id | category | sub_category | product_name | sales | quantity | discount | profit
--------+-----------------+------------+------------+-------------------+-------------+---------------+--------------+---------+--------+-------+----------+----------------------+----------+----------------+---------------------------------------+---------+----------+----------+---------
1 | JP-2016-1000099 | 2016/11/8 | 2016/11/8 | 即日配送 | 谷大-14605 | 谷奥 大地 | 消費者 | 千歳 | 北海道 | 日本 | 北海道 | 家具-本棚-10004817 | 家具 | 本棚 | Dania キャビネット, 従来型 | 16974 | 3 | 0.4 | -1986
2 | JP-2017-1001016 | 2017/10/7 | 2017/10/10 | ファースト クラス | 飯真-14980 | 飯沼 真 | 消費者 | 豊田 | 愛知 | 日本 | 中部地方 | 事務用-アプ-10001193 | 事務用品 | アプライアンス | フーバー ミキサー, シルバー | 52224 | 8 | 0 | 25584
3 | JP-2015-1001113 | 2015/8/18 | 2015/8/21 | ファースト クラス | 笹大-16015 | 笹淵 大輔 | 消費者 | 浜松 | 静岡 | 日本 | 中部地方 | 事務用-バイ-10002621 | 事務用品 | バインダー | カーディナル バインダー, エコノミー | 3319.2 | 6 | 0.4 | 211.2
：
：
29 | JP-2014-1013528 | 2014/11/27 | 2014/11/29 | ファースト クラス | 大由-11455 | 大郷 由美 | 小規模事業所 | 西条町 | 広島 | 日本 | 中国地方 | 事務用-画材-10002727 | 事務用品 | 画材 | BIC 鉛筆削り, メタル | 4237.8 | 3 | 0.3 | -1276.2
30 | JP-2014-1014041 | 2014/8/13 | 2014/8/18 | 通常配送 | 加翼-13420 | 加川 翼 | 大企業 | 日野 | 滋賀 | 日本 | 関西地方 | 家電-付属-10003172 | 家電 | 付属品 | サンディスク ルータ, リサイクル | 33888 | 2 | 0 | 9824
(20 rows)

マニフェストファイルのcontent_lengthは正確に

ちょっとハマってしまったのが、マニフェストファイルのcontent_lengthです。この値がファイルのサイズと一致しないと以下のエラーが表示されます。

cmdb=> select * from cm_user_db.orders_jp_files;
ERROR: S3 Query Exception (Fetch)
DETAIL:
-----------------------------------------------
error: S3 Query Exception (Fetch)
code: 15001
context: Task failed due to an internal error.
HTTP response error code: 416 Message: InvalidRange The requested range is not satisfiable
x-amz-request-id: B8840FF59F4D51C4
x-amz-id-2: PRUxEpkAXImhaZMoFSIoaeBmM+zUN4Li1VOjN7/nTijOdsBPPd7rZAlQUVs583LrVSQ1zwkW/xc=

query: 1893650
location: dory_util.cpp:686
process: query0_86 [pid=2908]
-----------------------------------------------

他のサービスからソースデータの見え方

Amazon Redshift Spectrumで定義した外部テーブルは、他の外部テーブルと同様にGlueデータカタログに登録されますので、Amazon Athena や AWS Glueからテーブルを確認することができます。しかし、Amazon Athena や AWS Glueからテーブルデータの中身は参照できませんでした。これらのクエリエンジンではマニフェストファイルをデータファイルとして認識できないようです。

最後に

Amazon Athenaでは要望が多かったこの機能ですが、その後登場したAmazon Redshift Spectrumではマニフェストファイルを利用して定義できるように設計されています。マニフェストファイルは同じリージョンであれば良いので、柔軟に既存のS3上のファイルに対してピンポイントでクエリを実行できるようになります。今回はファイル数が少ないので手で書きましたが、本番環境ではマニフェストファイルをAWSCLIやAPIを利用して自動生成したほうが良いでしょう。