QlikでAmazon S3から最新のファイルだけ取得したいんだけど…

こんにちは！Yokoです。
今朝の社内ミーティングで、
「九州は大変だよね。九州行の観光客のキャンセルが相次ぎ、これから繁忙期だというのに、大打撃だ」
っていう話になりました。
熊本に行くのは難しいけど（もっとも、行っても迷惑になるだけ）、断層のラインとは無関係なエリアは経済を停滞させないためにも、敢えて行ってあげるほうがいいと思いますよねえ。
今月行ってみようかな😄

さて、今回ですが、Amazon S3にデータを格納してもらうことなっているという環境で悩ましいのは、ファイルを置いてくれる人のデータリテラシーだったりします。
ファイルの命名ルールを決めたり、古いファイルは別フォルダに移動しておくなどと事前に決めておいても、そのルールが守られず、日次更新がエラーになってしまう…なんて、あるあるですよね😆
そんな時、少し冗長性を持たせたスクリプトにしておく方法として、「とにかく最新のファイルを取ってくる」というスクリプトを作ってみます。

今回使うデータのディレクトリ構成

今回、サンプルのデータとして、以下のようなディレクトリに売上明細ファイルを配置してもらうことを想定して、ロードスクリプトを作成してみます。

■　ディレクトリ　　<バケット名>/blog/foodmart/

格納されるファイルはこんな形式です。

Amazon S3 Metadata V2コネクタで新しい接続を作る

Amazon S3のオブジェクトから構成情報を取得するには、Amazon S3 Matadata V2コネクタを使用します。

Amazon S3 Matadata V2コネクタで新しい接続を作成します。
指定箇所は次の4つです。

• Region　　　　東京であれば、Asia Pacific(Tokyo)
• Bucket Name　S3のバケット名
• Access Key　　指定されたアクセスキー
• Secret Key　　 指定されたシークレットアクセスキー

あとは[接続テスト]で接続が確認できたら、Nameに接続名を任意に指定します。

ファイル名の取得はコピペで行こう

接続から、S3 Matadataの接続をスクリプトに追加します。
そうしますと、スクリプトエディタに、LIB CONNECT TO ‘…’; というコネクションが追加されますので、ファイルリストを取得する以下のスクリプト（filelist:以下）をコピペします。

ファイルリストを取得

LIB CONNECT TO 'BLOG:Amazon_S3_Metadata_connection';

filelist:
LOAD 
	SubField(Key,'/',3) as file_name, 	//ディレクトリの３つめにファイル名
	Num(Right(SubField(SubField(Key,'/',3),'.',1),8)) as date,	//ファイル名の右8桁が年月日
	LastModified as updated_at
Where SubField(Key,'/',3) Like 'sales_detail*.csv';	//取り込むファイルの絞り込み
SELECT Key,
	LastModified
// 	ETag,
// 	Size,
// 	OwnerId,
// 	StorageClass
FROM ListObjects
WITH PROPERTIES (
prefix='blog/foodmart/',	//ディレクトリ（バケット名不要）
maxResults=''
);

環境に合わせて修正する箇所は以下のとおりです。

■5行め　SubField(Key,’/’,3) as file_name, (ディレクトリのファイル名の場所を指定)
■6行め　Num(Right(SubField(SubField(Key,’/’,3),’.’,1),6)) as date,　（ファイル名の日付を取得）
■8行め　Where SubField(Key,’/’,3) Like ‘sales_detail*.csv’; 　（取り込むファイルの絞り込み）
■17行目　prefix=’blog/foodmart/’, 　（ディレクトリを指定）

以上でファイル名のリストが取得されます。

最新の一つのファイルに絞り込む

読み込まれたファイルリストから最新のファイル名を取得します。
最新のファイルは「ファイル名」から取得可能な場合とファイルの更新日時から取得する場合があると思います。
前者の場合は4行めの Max(date) as date を使い、後者の場合は5行めのファイル更新日時 Timestamp(Max(updated_at)) as updated_at を使いましょう。
ここではファイルの更新日時で最新ファイルのファイル名取得できるようにしています。

//最新の一つのファイルに限定
Inner Join(filelist)
Load 
//	Max(date) as date,		//ファイル名の日付Max
	Timestamp(Max(updated_at)) as updated_at	//更新日時のMax    
Resident filelist;

Let varFileName = Peek('file_name',0,'filelist');

Drop Table filelist;

filelistテーブルはファイル名が最新の1行だけになりますので、以下にてファイル名を取得します。

Let varFileName = Peek('file_name',0,'filelist');

最新データを取り込む

取り込むファイル名が確定しましたので、以下のスクリプトを追加します。
ここでは仮に、BLOGスペースのAmazon_S3_V2_liner-notesという接続で、blog/foodmart配下のファイルを取得する設定です。

Data:
LOAD
	*
FROM [lib://BLOG:Amazon_S3_V2_liner-notes/blog/foodmart/$(varFileName)]
(txt, utf8, embedded labels, delimiter is ',', msq);

リロードした結果は以下のとおりです。
最新のファイルだけが取り込まれていますね！

まとめ

Amazon S3に任意のディレクトリから最新のファイルのみを取得する方法を解説しました。
この方法であれば、ファイル名に若干のゆれがあったとしても、スクリプトを工夫して、ゆる〜くしてあげれば、不用意なリロードエラーを防げますよね。
最後に全体のスクリプトをつけておきます。

LIB CONNECT TO 'BLOG:Amazon_S3_Metadata_connection';

filelist:
LOAD 
	SubField(Key,'/',3) as file_name, 	//ディレクトリの３つめにファイル名
	Num(Right(SubField(SubField(Key,'/',3),'.',1),8)) as date,	//ファイル名の右8桁が年月日
	LastModified as updated_at
Where SubField(Key,'/',3) Like 'sales_detail*.csv';	//取り込むファイルの絞り込み
SELECT Key,
	LastModified
// 	ETag,
// 	Size,
// 	OwnerId,
// 	StorageClass
FROM ListObjects
WITH PROPERTIES (
prefix='blog/foodmart/',	//ディレクトリ（バケット名不要）
maxResults=''
);

//最新の一つのファイルに限定
Inner Join(filelist)
Load 
//	Max(date) as date,		//ファイル名の日付Max
	Timestamp(Max(updated_at)) as updated_at	//更新日時のMax    
Resident filelist;

Let varFileName = Peek('file_name',0,'filelist');

Drop Table filelist;

Data:
LOAD
	*
FROM [lib://BLOG:Amazon_S3_V2_liner-notes/blog/foodmart/$(varFileName)]
(txt, utf8, embedded labels, delimiter is ',', msq);

ではまた！