Athena †
Athena engine v2 †
Apache Parquet型式: 列指向、バイナリフォーマット †
SendGridのActivityを検索 †
データ型とクエリ †
VALUES: インラインテーブル †
パーティション指定 †
- 「year=<YYYY>/month=<MM>/day=<DD>」形式のパーティションで、日付の範囲指定
SELECT *
FROM t
WHERE concat(t.year, t.month, t.day)
BETWEEN date_format(CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo' - INTERVAL '7' DAY, '%Y%m%d')
AND date_format(CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo' - INTERVAL '1' DAY, '%Y%m%d')
ALB/ELBログの分析 †
- 見たいメトリクスが、CloudWatchメトリクスにある場合は、そちらを見た方が良い
- 条件を指定したり、ログにしか無いメトリクスを見たい場合に有効
- ALB/ELBのログの日付フォーマット: iso8601
- 文字列のままで扱う場合: substr()
- 毎時
SELECT
substr(TIME,1,13) || ':00:00Z' AS time_hour,
COUNT(elb) AS request_count
FROM example.alb_logs
WHERE DATE = format_datetime(now() - INTERVAL '1' DAY,'YYYY/MM/dd')
GROUP BY substr(TIME,1,13)
ORDER BY time_hour ASC;
- 毎分
SELECT
substr(TIME,1,16) || ':00Z' AS time_min,
COUNT(elb) AS request_count
FROM example.alb_logs
WHERE DATE = format_datetime(now() - INTERVAL '1' DAY,'YYYY/MM/dd')
GROUP BY substr(TIME,1,16)
ORDER BY time_min ASC;
- 日付へ変換する場合: date_trunc() + from_iso8601_timestamp()
- 毎時
SELECT
date_trunc('hour', from_iso8601_timestamp(TIME)) AS time_hour,
COUNT(elb) AS request_count
FROM example.alb_logs
WHERE DATE = format_datetime(now() - INTERVAL '1' DAY,'YYYY/MM/dd')
GROUP BY date_trunc('hour', from_iso8601_timestamp(TIME))
ORDER BY time_hour ASC;
- 毎分
SELECT
date_trunc('minute', from_iso8601_timestamp(TIME)) AS time_min,
COUNT(elb) AS request_count
FROM example.alb_logs
WHERE DATE = format_datetime(now() - INTERVAL '1' DAY,'YYYY/MM/dd')
GROUP BY date_trunc('minute', from_iso8601_timestamp(TIME))
ORDER BY time_min ASC;
日付と時刻 †
- date/timestamp -> bigint
SELECT
now() AS _now,
YEAR(TIMESTAMP '2018-12-24 02:34:48 UTC') AS _year,
MONTH(TIMESTAMP '2018-12-24 02:34:48 UTC') AS _month,
DAY(TIMESTAMP '2018-12-24 02:34:48 UTC') AS _day,
week(TIMESTAMP '2018-12-24 02:34:48 UTC') AS _week,
HOUR(TIMESTAMP '2018-12-24 02:34:48 UTC') AS _hour,
MINUTE(TIMESTAMP '2018-12-24 02:34:48 UTC') AS _minute,
SECOND(TIMESTAMP '2018-12-24 02:34:48 UTC') AS _second;
-- _now: 2020-08-01 02:03:04.953 UTC
-- _year: 2018
-- _month: 12
-- _day: 24
-- _week: 52
-- _hour: 2
-- _minute: 34
-- _second: 48
- date_parse(): 文字列 -> date/timestamp型へ
SELECT DATE '2018-12-24' AS DATE;
-- 結果: 2018-12-24
SELECT DATE '2018/12/24' AS DATE;
-- 結果: INVALID_CAST_ARGUMENT: Value cannot be cast to date: 2018-12-24
SELECT date_parse('2018-12-24','%Y/%m/%d') AS TIMESTAMP;
-- 結果: 2018-12-24 00:00:00.000
-- iso8601は専用の関数がある。from_iso8601_date()もある
SELECT from_iso8601_timestamp('2018-12-24T02:34:48Z') AS TIMESTAMP;
-- 結果: 2018-12-24 02:34:48.000 UTC
- format_datetime(): 日付文字列へ変換
- 現在時刻を任意文字列へ
-- 月だけ取得
SELECT format_datetime(now(), 'MM')
-- 結果: 07
-- 今日
SELECT format_datetime(now(),'YYYY/MM/dd') AS DATE;
-- 結果: 2020/07/31
-- 1ヶ月前
SELECT format_datetime(now() - INTERVAL '1' MONTH, 'YYYY/MM/dd') AS DATE;
-- 結果: 2020/06/30
- 例: Amazon S3 サーバーアクセスログの形式
SELECT format_datetime(date_parse('06/Feb/2019:00:00:38 +0000', '%d/%b/%Y:%H:%i:%s +0000'),'YYYY-MM-dd HH:mm:ss')
-- 結果: 2019-02-06 00:00:38
- date_trunc(): 日時から指定フィールまで切り捨て
-- 月初を取得
SELECT date_trunc('month', now());
-- 結果: 2020-07-01 00:00:00.000 UTC
SELECT date_trunc('minute', now());
-- 結果: 2020-09-07 04:17:00.000 UTC
SELECT date_trunc('hour', now());
-- 結果: 2020-09-07 04:00:00.000 UTC
クエリサンプル †
- テーブル内のデータが「{key=val,...」と表示される場合(CREATE TABLE STRUCT name: STRING,...)。既にAthenaのデータ型として認識しているので、「column.key」で参照できる
SELECT dataset.name, ...
Partition Projection: パーティション管理の自動化 †
- Amazon Athena を使用したパーティション射影 - Amazon Athena
- パーティションが設定されていない、AWSサービスのログ(CloudTrail, ELB他)で使うと便利
- HIVE形式以外は、「ALTER TABLE」で検索対象のパーティションを都度追加する必要があった。パーティションが整数、日付、列挙値であれば、「CREATE TABLE」時に設定して、自動化できる。
- HIVE形式はクエリー実行前に有効なパーティションを認識させるため、「MSCK REPAIR TABLE <table>;」を実行する必要がある。これの自動化には「where date = 'year=yyyy/month=MM/day=dd'」とSQLを入力せねばならず、Partition Projectionは向いて無さそう。
- Partition Projectionでは実際のデータではなく、「CREATE TABLE」時の設定からパーティションが設定される。
- HIVE形式(s3//bucket/key1=value1/object.gz)のパーティション設定は無視される。
- 過去〜現在までを指定する場合
# 3ヶ月前〜
"projection.date.range" = "NOW-3MONTH,NOW",
# 3年前〜
"projection.date.range" = "NOW-3YEARS,NOW",
- AWS サービスログをクエリする - Amazon Athena
- ALB, NLB, CLB(ELB), CloudFront, CloudTrail, EMR, Global Accelerator, GuardDuty, VPC flow, WAF等のTABLEサンプルがある。これを元にPartition Projectionを設定すれば良さそう。
HIVE形式のパスに projection type dateを設定するとエラー †
HIVE形式で「s3://bucket/year=${year}/month=${month}/day=${day}」とパーティションを分けているとする。
2019〜YYYYまでをパーティションとして認識させたい。
- 2020-08現在、「"projection.year.type" = 'date'」と指定すると、クエリ時にエラーが発生する。最低「yyyy/MM」のように月まで指定が必要
- SQL抜粋
TBLPROPERTIES (
...
'projection.enabled'='true',
"projection.year.type" = 'date',
"projection.year.range" = '2019,NOW',
"projection.year.format" = 'yyyy',
"projection.year.interval" = '1' ,
"projection.year.interval.unit" = 'YEARS',
'projection.month.type'='integer',
'projection.month.range'='1,12',
'projection.month.interval'='1',
'projection.month.digits'='2',
'projection.day.type'='integer',
'projection.day.range'='1,31',
'projection.day.interval'='1',
'projection.day.digits'='2',
'storage.location.template'='s3://bucket/year=${year}/month=${month}/day=${day}'
);
- 代案: AWSサポートの回答「エラーは再現した。代わりに type = 'integer'を使って」。SQLの変更点だけ抜粋。AWS docに「空のパーティションが多すぎるとパフォーマンス低下の可能性」とあるので、無闇に遠い未来は指定しないほうが良さそう
TBLPROPERTIES (
...
'projection.year.type'='integer',
'projection.year.range'='2019,2030',
'projection.year.interval'='1',
'projection.year.digits'='4',
...
Unknown pattern letter †
- 以下の設定で発生。
TBLPROPERTIES (
...
"projection.date.type" = "date",
"projection.date.range" = "2020/07/01,NOW",
"projection.date.format" = "year=yyyy/month=MM/day=dd",
"projection.date.interval" = "1" ,
"projection.date.interval.unit" = "DAYS",
Column repeated in partitioning columns †
- 原因
- パーティションのカラム名と、create tableのカラム名が重複している
Workgroup: クエリのスキャン量上限設定、コスト管理、ユーザ権限管理 †
- workgroup毎に
- 1クエリ1TBスキャンあたり、$5かかるので、上限を設定したい
- 出力s3 bucketや暗号化方法を指定できる
- 複数のタグを設定し、識別できる。このタグでBilling画面で識別できる。
- workgroupに割り当てたクエリの設定を上書きできる。
athenacli: athenaの利用に特化したCLI †
- awscliの欠点
- 手動で試す場合、非同期なので使いにくい。
- 結果はS3のファイルに出るため、ダウンロードする手間がかかる。
awscliからの利用 †
実行順序:
- start-query-execution: クエリの実行。DDL(CREATE/ALTER TABLE)もSQLもこれ。非同期なのですぐ完了する。idが出る
- get-query-execution: 指定idのステータス確認。定期的に実行して完了/エラーまで待つ
- get-query-results: 指定idが完了していれば、S3に結果のファイルが出ている
- aws s3 cp 等でlocalに結果のcsv等をダウンロード
SQL実行順序:
- CREATE TABLE table_name
- MSCK REPAIR TABLE table_name
- これを実行しないと、selectしても結果は空になる。
- SELECT ... from table_name
- 注意点
- SQL,DDLは1回に一つだけ。";"で区切ってもエラーになる。
- 「no viable alternative at input」がよく発生するが、詳細がわからないため解決に苦労する。
- "\"は"\\\\"と書く必要がある。
- 項目の順序も重要
- CSV
- csv.gz ファイルに対応しているので圧縮しておく。
- 元のCSVにダブルクオート付きの場合、「ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'」を指定しないと、ダブルクオートが二重に出力される。
- 例: csv.gz のファイルで、ダブルクオート付き、ヘッダ行は1行
local sql=$(cat << EOD
CREATE EXTERNAL TABLE IF NOT EXISTS
dns (
Date string,
QueryType string,
Client string,
SendReceive string,
Protocol string,
RecordType string,
Query string,
Results string
)
PARTITIONED BY (
year int,
month int,
day int
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'separatorChar' = ',',
'quoteChar' = '\"',
'escapeChar' = '\\\\'
)
STORED AS TEXTFILE
LOCATION 's3://${S3_BUCKET}/${prefix}id=DNS/'
TBLPROPERTIES (
'skip.header.line.count'='1',
'has_encrypted_data'='false'
)
;
EOD
)
ベストプラクティス †
- Athenaと検索対象のS3は同じリージョンにする。
- 異なるリージョンの場合、大きいデータを検索すると、リージョン間の転送が遅く、Athenaの料金よりS3の転送量の料金のほうが高くなる場合もある。
- 小さいデータを検索する場合は、あまり問題にはならなそう。
- マルチアカウント:
- s3 objectのownerによって、他アカウントからのs3:GetObjectが失敗する。CloudTrail/ELB/ALB等のログはs3 object のownerが各サービスに変わっている。この場合、AssumeRoleして、各アカウント内のAthenaからは検索ができる。
- 例: CloudTrail
- HIVE形式(<PARTITION_COLUMN_NAME>=<VALUE>)でパーティション分割する。それ以外(例:YYYY/MM/DD)では、
検索したい数だけパーティションを手動で追加する必要がある。 Partition Projection で自動化できるようになった。
- SQL
- 「LIMIT 10000」のように制限する
- JOINする場合、大きいTABLEを左側に、小さいTABLEを右側に指定する。「FROM big_table, little_table」
- GROUP BYは、カーディナリティ(カラムに含まれている種類)が高い順に指定する。文字列ではなく、数値を指定する。
- LIKEは「regexp_like(l_comment, 'wake|regular|express|sleep|hello')」で一つに纏める。否定は「NOT(regexp_like(...))」
- ユニークな個数を調べる場合「count(distinct l_comment)」の代わりに、「approx_distinct(l_comment)」を使う。ただし、誤差が2.3%出る
- 「SELECT * 」の代わりに「SELECT customer.c_name」のように明示的にカラム名を指定する
パーティション分割 †
Athenaは1TBのスキャンあたり、1回$5かかる。スキャンデータを減らす工夫をしないと、コストが非常に増えるまた遅い。
- 検索時によく指定する、日付、ホスト名、アプリケーション名等で分割すると良さそう
- CREATE TABLEした直後にselectしても何も出ない。以下を実行する必要がある。
|
|