AWS Athena（s3に保存されたファイルをSQLで読み取れる）を試してみた | そう備忘録

AWS Athena（s3に保存されたファイルをSQLで読み取れる）を試してみた

by · 公開済み · 更新済み

AWS Athena

AWS Athenaを試した時の備忘録。

なぜAthenaか

以前にモーションセンサーで１０分毎に人の動きを感知して、その件数をクラウド上のDynamoDBにアップするシステムを作った。

DynamoDBは安価でデータの蓄積には適している反面、取り出し方がちょっとめんどくさい。

以前の記事でも紹介したがテーブルのscanやqueryでデータを取り出すことは出来るのだがプライマリーパーティションキーにはeq（イコール）しか指定できなかったり（フィルターを使えばbetweenは指定可能）プライマリーパーティションキーの条件指定をしないとプライマリーソートキーの条件指定が出来ないなどちょいちょい使い勝手が悪いところがある。

またNOSQLデータベースなので当然だがSQLでデータを取り出すことができない。

蓄積されたデータを解析の為に様々な条件で取り出したいのだが、かといってRDBMSにデータを移行してSQLで取り出すのもオーバースペックで費用もかかる。

という事でAWS Athenaを使ってみることにした。

Athenaの特徴

AthenaはAWSのサービスのひとつでサーバレスでS3に保存されたCSV、JSON、ORC、Parquet形式のファイルをSQLで取り出すことができる。

料金体系はスキャンしたデータ量に応じて計算され１TB＝５USDとなっている。

  • スキャンしたデータ量なのでSQLで取り出したデータ量では無い
  • １０MB未満は１０MB（０．００００５ドル）に切り上げられる
  • 正常に実行されなかったクエリーには課金されない
  • 途中でキャンセルしたらスキャンしたデータ量までの課金となる
  • ファイルの圧縮でデータ量を圧縮すれば料金も圧縮される
  • 特定の列を取り出すクエリーの場合、Parquet形式にすればスキャンするデータ量も減るので料金も節約できる

環境

AWS AthenaはWEBのコンソール上で実行したので基本的にはクライアント環境に依存しない。

しかし一応クライアント環境を記しておく。

OS

Windows10 Home 64Bit

パソコン

DELL G7 15

ブラウザ

Google Chrome バージョン: 84.0.4147.105（Official Build）（64 ビット）

操作手順

CSVファイルでAthenaを試してみる。

JSONでも可能なのだがJSONとCSVではファイルサイズはCSVの方が小さくなるのでAthenaで分析をするのであれば料金的にはCSVの方が有利になる。

大まか手順は以下の通り。

  1. CSVファイルをS3にアップロードする
  2. Athena上でテーブル定義のSQLを実行する
  3. Athena上でSQLを実行する

CSVファイル

CSVファイルの準備

以下の温湿度の情報を格納したCSVファイルを用意した。

"tempkeyindex","clientid","humicheck","humidity","locationid","locationname","max_humi","max_temp","min_humi","min_temp","status","tempcheck","tempdatetime","temperature"
"2020-07-15 14:40:01temperature0001","temperature0001","OK",54.2,"H1F0002","倉庫1F-01",70,40,10,5,"0","OK","2020-07-15 14:40:01",19
"2020-07-15 14:45:04temperature0001","temperature0001","OK",54.8,"H1F0002","倉庫1F-01",70,40,10,5,"0","OK","2020-07-15 14:45:04",19.3
"2020-07-15 14:38:04temperature0001","temperature0001","OK",55.9,"H1F0002","倉庫1F-01",70,40,10,5,"0","OK","2020-07-15 14:38:04",21.4
"2020-07-15 14:36:00temperature0001","temperature0001","OK",55,"H1F0002","倉庫1F-01",70,40,10,5,"0","OK","2020-07-15 14:36:00",19
"2020-07-15 14:47:01temperature0001","temperature0001","OK",55.2,"H1F0002","倉庫1F-01",70,40,10,5,"0","OK","2020-07-15 14:47:01",20.1
"2020-07-15 14:43:04temperature0001","temperature0001","OK",54.9,"H1F0002","倉庫1F-01",70,40,10,5,"0","OK","2020-07-15 14:43:04",19.8
"2020-07-15 14:46:04temperature0001","temperature0001","OK",55.4,"H1F0002","倉庫1F-01",70,40,10,5,"0","OK","2020-07-15 14:46:04",20.4
"2020-07-15 14:39:05temperature0001","temperature0001","OK",55.8,"H1F0002","倉庫1F-01",70,40,10,5,"0","OK","2020-07-15 14:39:05",21.2
"2020-07-15 14:42:03temperature0001","temperature0001","OK",54.9,"H1F0002","倉庫1F-01",70,40,10,5,"0","OK","2020-07-15 14:42:03",19.7
"2020-07-15 12:45:51temperature0001","SKRPZ0002","OK",55.1,"H1F0001","倉庫2F-02",70,40,10,5,"0","OK","2020-07-15 12:45:51",19.2
"2020-07-15 14:44:04temperature0001","temperature0001","OK",54.5,"H1F0002","倉庫1F-01",70,40,10,5,"0","OK","2020-07-15 14:44:04",19
"2020-07-15 14:41:01temperature0001","temperature0001","OK",55.2,"H1F0002","倉庫1F-01",70,40,10,5,"0","OK","2020-07-15 14:41:01",20.3
"2020-07-15 14:48:00temperature0001","temperature0001","OK",54.8,"H1F0002","倉庫1F-01",70,40,10,5,"0","OK","2020-07-15 14:48:00",19.4
"2020-07-15 14:37:04temperature0001","temperature0001","OK",54.2,"H1F0002","倉庫1F-01",70,40,10,5,"0","OK","2020-07-15 14:37:04",20.2

文字コードはUTF-8で作成している。

humidity、max_humi、max_temp、min_humi、min_temp、temperatureのデータ型はfloatでそれ以外はstringとしている。

CSVファイルのアップロード

S3上にバケット”temp-humi”を作成してその配下にdataフォルダーとresultフォルダー（SQLの実行結果の格納用）を作成した。

上記のCSVはdataフォルダ配下にアップロードした。

CSVファイルのアップロード

Athena

AWSのサービスからAthenaを選択すると始めての時は以下の画面が表示されるので「Get Started」ボタンをクリックする。

Athenaサービスの開始

クエリーの実行結果の格納場所

最初に起動した時に”set up a query result location in Amazon S3″（Amazon S3上にクエリーの実行結果の格納場所を設定せよ）と言われるのでクリックする。

実行結果の格納場所

先程作成した”s3://temp-humi/result/”を指定して「Save」ボタンで保存した。

クエリーの実行結果の格納場所を設定する

テーブル作成

以下のSQLを実行してテーブルを作成する。

CREATE EXTERNAL TABLE IF NOT EXISTS default.temp_humi_table(
  tempkeyindex string,
  clientid string,
  humicheck string,
  humidity float,
  locationid string,
  locationname string,
  max_humi float,
  max_temp float,
  min_humi float,
  min_temp float,
  status string,
  tempcheck string,
  tempdatetime string,
  temperature float
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES ('escapeChar'='\\','quoteChar'='\"','integerization.format'=',','field.delim' = ',') 
LOCATION 's3://temp-humi/data/' 
TBLPROPERTIES ('has_encrypted_data'='false','skip.header.line.count'='1','serialization.encoding'='utf8')

１行目

テーブル名：temp_humi_tableを指定している

２～１５行目

CSVの項目名と型を一致させて指定している

OpenCSVSerde

CSVデータの文字列項目が”（ダブルクォーテーション）で囲まれているので引用符を除いてデータだけを項目として格納するOpenCSVSerdeを指定している。

LazySimpleSerDeを指定すると”（ダブルクォーテーション）も含めて項目の値として格納されてしまう（これでちょっとハマった）

escapeChar

エスケープシーケンスを表す文字列を指定している

quoteChar

引用符の文字（ダブルクォーテーション）を指定している

field.delim

項目と項目の区切り文字

LOCATION

s3上のデータ格納フォルダーを指定している

skip.header.line.count

１行目がヘッダーなのでスキップするために１を指定している

encoding

デフォルトはutf8だが明示的に指定している

実行結果

New query1にSQLを貼り付けて「Run query」ボタンで実行する。

queryが成功すると左のTablesにtemp_humi_tableが表示されて作成された事が分かる。

テーブル作成SQL実行結果

データ取得

検索をしてみる。

以下の検索クエリーを実行する。

select * from temp_humi_table
where humidity >= 55 and
clientid= 'temperature0001'

SQLが実行されて結果が画面下部に表示さた。

ダウンロードアイコンをクリックすればCSV形式でダウンロードする事も可能だ。

SQL実行結果

s3のresultフォルダー

s3のrelustフォルダーにも実行結果が格納されている。

s3のresultフォルダー

以上で今回の記事は終了とする。

最後に

この記事が何処かで誰かの役に立つことを願っている。

尚、当記事中の商品へのリンクはAmazonアソシエイトへのリンクが含まれています。Amazonのアソシエイトとして、当メディアは適格販売により収入を得ていますのでご了承ください。

タグ:

souichirou

やった事を忘れない為の備忘録 同じような事をやりたい人の参考になればと思ってブログにしてます。 主にレゴ、AWS（Amazon Web Services）、WordPress、Deep Learning、RaspberryPiに関するブログを書いています。 仕事では工場に協働ロボットの導入や中小企業へのAI／IoT導入のアドバイザーをやっています。 2019年7月にJDLA（一般社団法人 日本デイープラーニング協会）Deep Learning for GENERALに合格しました。 質問は記事一番下にあるコメントかメニュー上部の問い合わせからお願いします。

おすすめ

質問やコメントや励ましの言葉などを残す

名前、メール、サイト欄は任意です。
またメールアドレスは公開されません。