Athenaでダミーテーブルを作ってクエリを動かす方法

目的

Athenaで巨大なテーブル(例えば、数百GBとか）に対してクエリを動かすまえに、意図通りに動くかどうか確認したいので、

ダミーテーブルを作ってクエリを動かす方法を備忘録として残します

方法

たとえば、IPとUserAgentの構成しているテーブルをダミーテーブルとして作りたい場合は、つぎのように記述します

WITH
-- ここからダミーテーブル
log AS (
  SELECT tbl.ip, tbl.ua
  FROM UNNEST(ARRAY[
    CAST(
      ROW('127.0.255.255', 'Mozilla/5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Mobile/15A372 Safari/604.1') AS ROW(ip VARCHAR, ua VARCHAR)
    ),
    CAST(
      ROW('127.3.0.0', 'Mozilla/5.0 (Linux; Android 4.0.3; SC-02C Build/IML74K) AppleWebKit/537.31 (KHTML, like Gecko) Chrome/26.0.1410.58 Mobile Safari/537.31') AS ROW(ip VARCHAR, ua VARCHAR)
    ),
    CAST(
      ROW('127.0.255.255', 'Mozilla/5.0 (iPhone; CPU iPhone OS 11_3 like Mac OS X) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Mobile/15A372 Safari/604.1') AS ROW(ip VARCHAR, ua VARCHAR)
    )
  ]) t(tbl)
)
-- ここまでダミーテーブル

-- ダミーテーブルの中身を確認
select * from log

これをAthenaで実行すると、以下の結果が返ってきます

	ip	ua
1	127.0.255.255	Mozilla/5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Mobile/15A372 Safari/604.1
2	127.3.0.0	Mozilla/5.0 (Linux; Android 4.0.3; SC-02C Build/IML74K) AppleWebKit/537.31 (KHTML, like Gecko) Chrome/26.0.1410.58 Mobile Safari/537.31
3	127.0.255.255	Mozilla/5.0 (iPhone; CPU iPhone OS 11_3 like Mac OS X) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Mobile/15A372 Safari/604.1

あとはこのダミーテーブルをつかえば、あとはこんな感じにIP毎のアクセスカウントなどを求めるなんてこともできます

WITH
-- ここからダミーテーブル
log AS (
  SELECT tbl.ip, tbl.ua
  FROM UNNEST(ARRAY[
    CAST(
      ROW('127.0.255.255', 'Mozilla/5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Mobile/15A372 Safari/604.1') AS ROW(ip VARCHAR, ua VARCHAR)
    ),
    CAST(
      ROW('127.3.0.0', 'Mozilla/5.0 (Linux; Android 4.0.3; SC-02C Build/IML74K) AppleWebKit/537.31 (KHTML, like Gecko) Chrome/26.0.1410.58 Mobile Safari/537.31') AS ROW(ip VARCHAR, ua VARCHAR)
    ),
    CAST(
      ROW('127.0.255.255', 'Mozilla/5.0 (iPhone; CPU iPhone OS 11_3 like Mac OS X) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Mobile/15A372 Safari/604.1') AS ROW(ip VARCHAR, ua VARCHAR)
    )
  ]) t(tbl)
)
-- ここまでダミーテーブル

-- IP毎のアクセスカウントを求める
select ip, count(*) access_count from log group by ip

	ip	access_count
1	127.0.255.255	2
2	127.3.0.0	1