Amazon Athena SELECT INSERT や CTASの疑似してみる

はじめに

現在の Amazon Athena は 更新系クエリーはもちろん、SELECT INSERT や CTAS(CREATE TABLE AS)が標準でサポートされていませんので、「参照専用」という印象を持つ方が少なくありません。このような理由もあり、Athena が向いていない処理に「テンポラリテーブルを活⽤した多段のETL処理」といわれています。そこで、今回は最初のクエリーで得られた実行結果に対して更にクエリ処理することで、SELECT INSERT や CTASの疑似にチェレンジしたいと思います。

実現する方針

Amazon Athena でクエリーを実行すると s3_staging_dir に実行結果がS3ファイルとして出力されます。このS3ファイルに対して、テーブル定義を設定してクエリを実行が可能であるかが検証のポイントです。

課題

以下のような形式で出力されます。

"order_date","product_category","product_sub_category","product_name","sales","profit"
"2009/1/1","事務用品","紙","Xerox 1939","15261","-1520"
"2009/1/1","家具","オフィス家具","Eldon Wave Desk Accessories","6548","-1738"
"2009/1/10","テクノロジー","コンピューター周辺機器","IBM 80 Minute CD-R Spindle, 50/Pack","21138","-1059"
"2009/1/10","テクノロジー","コンピューター周辺機器","IBM Active Response Keyboard, Black","76768","-5437"
 :
 :
"2012/9/9","事務用品","ペンと画材","Newell 320","22570","-1579"
"2012/9/9","事務用品","保管と整理","Acco Perma® 3000 Stacking Storage Drawers","105737","5253"

つまり、S3ファイル（実行結果）の形式は以下のとおりです。

• Athena 実⾏リージョンの S3 にヘッダ付き csv で出⼒される
• ヘッダやカラムはダブルクォーテーションで括られている
• CREATE TABLE の際にヘッダ行読み込ませない⾏を指定できない

AthenaのCREATE EXTERNAL TABLE では，Hive DDL の ʻskip.header.line.countʼ オプションによるヘッダ行のスキップ指定ができません。また、カラムデータはダブルクォーテーションで括られている、つまりString型のみとなります。

正しい結果を得られるようにするには、これらの課題をクリアする必要があります。

テーブル定義

カラムは全てString型として定義します。SERDEPROPERTIESのパラメータをCSV形式に合わせて設定します。問題はありますが、これでクエリーが実行できることができるようになりました。

CREATE EXTERNAL TABLE tempdb.orders_daily_summary (
  order_date              string,
  product_category        string,
  product_sub_category    string,
  product_name            string,
  sales                   string,
  profit                  string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'separatorChar' = ',',
'quoteChar' = '\"',
'escapeChar' = '\\'
)
STORED AS TEXTFILE
LOCATION 's3://cm-bucket/xxxx/';

クエリの実行

ステップ１

まずはテーブルとして定義しましたが、orders_daily_summaryテーブルを単純に検索すると下記のようにスキップできなかったヘッダ行が先頭に入っています。

SELECT * FROM tempdb.orders_daily_summary limit 5;

ステップ２

ヘッダ行削除するためにsales(売上)のように数値が入るはずのカラムに文字列が入っている条件指定してヘッダ行を除去しています。以下の例では「sales <> 'sales'」と指定しています。

SELECT * FROM tempdb.orders_daily_summary  WHERE sales <> 'sales' limit 5;

ステップ３

最後にsales(売上)やprofit(利益)の集計ができるようにSTRING型からBIGINT型に型変換しています。参照用のクエリーにこれらの処理を含めると可読性が悪くなるので、型変換やヘッダ削除の処理はWITH句の中で指定しています。

WITH order_daily AS 
    (SELECT order_date,
         product_category,
         product_sub_category,
         product_name,
         cast(sales AS BIGINT) AS sales,
         cast(profit AS BIGINT) AS profit
    FROM tempdb.orders_daily_summary
    WHERE sales <> 'sales')
SELECT
  product_category, 
  product_sub_category, 
  product_name, 
  sum(sales) AS sales, 
  sum(profit) AS profit 
FROM order_daily
GROUP BY 1,2,3
ORDER BY 1,2,3
;

最後に

Amazon Athenaは、SELECT INSERT や CTASが標準でサポートされていませんが、上記のような手法で擬似できることがご理解できたと思います。Athena が向いていない処理と言われていた「テンポラリテーブルを活⽤した多段のETL処理」についても実現可能です。Amazon Athena が「参照専用」ではなく、より幅広い分野で活用して頂けることを祈っています。