サクッとめのathenaの使い方 #Bash

athenaではパーティションを区切って使用する使い方が主ですが、自動的にはパーティションを認識してくれないので、通常の外部テーブル作成時にパーティションを指定し、その後、逐次一つ一つのディレクトリに対して明示的にパーティションであることを示す必要があります。

1.外部テーブルをパーティションありきで作成

CREATE EXTERNAL TABLE spectrum.ext (
xxxxxxx varchar,
xxx integer,
xxxx varchar,
xxx varchar,
xxx varchar,
xxxxx varchar,
xx varchar,
xxxxxxx varchar,
xxxxxxx varchar
)
partitioned by (year integer, month char(02), day char(02), hour char(02))    <-ここが大事
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://<jsonファイルの格納先ディレクトリパス>/'
;

2.データをパーティション分割する

ここでも言われていますが、パーティション構造を自動的に認識してくれないので
パーティション一つ一つに対して改めて認識させてあげる必要があります。
実際の運用時には、lambdaで一時間おきにパーティションを一つ一つ認識させてあげるような関数を組む必要があるでしょう。

alter tableコマンド

 ALTER TABLE spectrum.ext ADD PARTITION (year='2019',month='01',day='26',hour='01') location 's3://<jsonファイルの格納先ディレクトリパス>/2019/01/26/01';

alter tableコマンドを実行しない場合

dev=# select * from spectrum.ext;
 accuracy | dir | last | lat | lon | speed | ts | userid | version | year | month | day | hour
----------+-----+------+-----+-----+-------+----+--------+---------+------+-------+-----+------
(0 rows)

alter tableコマンド実行後(alter table発行した部分しか検索されない)

dev=# select * from spectrum.ext;
      accuracy      | dir  | last  |     lat     |     lon      |       speed        |            ts            |     userid      | version | year | month | day | hour
--------------------+------+-------+-------------+--------------+--------------------+--------------------------+-----------------+---------+------+-------+-----+------
 33.374000549316406 |  169 | true  | 35.0950276  | 136.95031747 | 0                  | 2019-01-26T10:06:59+0900 | 357568060947996 | 1       | 2019 | 01    | 26  | 01
 72.81599426269531  |  -19 | true  | 35.11207287 | 137.01985959 | 0.5600000023841858 | 2019-01-26T10:07:22+0900 | 357568061692278 | 1       | 2019 | 01    | 26  | 01
 74.33300018310547  | -139 | true  | 35.1645601  | 136.90719514 | 0                  | 2019-01-26T10:07:16+0900 | 357568061420175 | 1       | 2019 | 01    | 26  | 01
 27.305999755859375 |  168 | false | 35.0950265  | 136.95031362 | 0                  | 2019-01-26T10:07:19+0900 | 357568060947996 | 1       | 2019 | 01    | 26  | 01
 31.856998443603516 |  168 | true  | 35.09502495 | 136.95030966 | 0                  | 2019-01-26T10:07:39+0900 | 357568060947996 | 1       | 2019 | 01    | 26  | 01
 77.36699676513672  | -177 | true  | 35.1644496  | 136.90732399 | 0                  | 2019-01-26T10:07:55+0900 | 357568061420175 | 1       | 2019 | 01    | 26  | 01
 47.027000427246094 |  169 | true  | 35.09502424 | 136.95030807 | 0                  | 2019-01-26T10:07:59+0900 | 357568060947996 | 1       | 2019 | 01    | 26  | 01
 78.88399505615234  | -179 | true  | 35.16452855 | 136.90722368 | 0                  | 2019-01-26T10:08:15+0900 | 357568061420175 | 1       | 2019 | 01    | 26  | 01
・・・

3.パーティション認識

s3に格納されている既存ファイルに対して、Redshift Spectrumがパーティションを認識するスクリプトを作成しました。

#!/bin/sh -x

DB="dev"
HOST="xxxxxx-redshiftcluster-1xxxxxxxxx.xxxxxxxxxx.ap-northeast-1.redshift.amazonaws.com"
BUCKET="s3://xxx-transfer-log"
USER="xxxxxxx"
export PGPASSWORD="xxxxxxx"

year=2019

for month  in `seq  -f %02g 1 2`;do
        for day  in `seq  -f %02g 1 31`;do
                for hour in `seq  -f %02g 0 23`;do
                        ALTER_TABLE+="ALTER TABLE spectrum.schema ADD IF NOT EXISTS PARTITION (year='$year',month='$month',day='$day',hour='$hour') location '$BUCKET/$year/$month/$day/$hour';"
                done
        done
done

psql -h $HOST -U $USER -d $DB -p 5439 <<_EOD

        create external schema spectrum
        from data catalog
        database 'dev'
        iam_role 'arn:aws:iam::xxxxxxxxxxxx:role/xxxxxx-RedshiftSpectrumRole-1xxxxxxxxxxx'
        create external database if not exists;

        CREATE EXTERNAL TABLE spectrum.schema (
        xxxxxxxx varchar,
        xxx integer,
        xxxx varchar,
        xxx varchar,
        xxx varchar,
        xxxxx varchar,
        xx varchar,
        xxxxxx varchar,
        xxxxxx varchar
        )
        partitioned by (year integer, month char(02), day char(02), hour char(02))
        ROW FORMAT SERDE
          'org.openx.data.jsonserde.JsonSerDe'
        STORED AS INPUTFORMAT
          'org.apache.hadoop.mapred.TextInputFormat'
        OUTPUTFORMAT
          'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
        LOCATION
          '$BUCKET'
        ;

        $ALTER_TABLE
_EOD