Memo/AmazonWebServices/Athena

https://dexlab.net:443/pukiwiki/index.php?Memo/AmazonWebServices/Athena 		 

Athena †

↑

データ型とクエリ †

↑

VALUES: インラインテーブル †

↑

パーティション指定 †

  • 以下のようなパーティションはSQL内では文字列として扱われる 
    s3:/bucket/prefix/id=<id>/dt=<YYYY>-<MM>-<DD>/file.csv.gz
s3:/bucket/prefix/id=<id>/dt=<YYYY>-<MM>-<DD>-<HH>/file.csv.gz
s3:/bucket/prefix/id=<id>/year=<YYYY>/month=<MM>/day=<DD>/file.csv.gz
  • 「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
    • ALBの日付列: time
    • ELBの日付列: timestamp 
      2018-12-24T02:34:48.895006Z
  • 文字列のままで扱う場合: 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
  • current_timezone(): タイムゾーン取得 
    SELECT current_timezone();
-- 結果: UTC

SELECT now() AT TIME ZONE 'Asia/Tokyo';
-- 結果: 2020-07-31 12:56:13.373 Asia/Tokyo

SELECT TIMESTAMP '2018-12-24 02:34:48 UTC' AT TIME ZONE 'Asia/Tokyo';
-- 結果: 2018-12-24 11:34:48.000 Asia/Tokyo
  • 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, ...
  • テーブル内のデータが「{key:val,...」と表示される場合。jsonが文字列として認識しているので、json_extract(blob, key)で参照する 
    SELECT json_extract(BLOB, '$.name') AS 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",
↑

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}'
);
  • クエリ時のエラー 
    GENERIC_INTERNAL_ERROR: Text '2019' could not be parsed: Unable to obtain YearMonth from TemporalAccessor: {Year=2020},ISO of type java.time.format.Parsed
  • 代案: AWSサポートの回答「エラーは再現した。代わりに type = 'integer'を使って」。SQLの変更点だけ抜粋。AWS docに「空のパーティションが多すぎるとパフォーマンス低下の可能性」とあるので、無闇に遠い未来は指定しないほうが良さそう 
    TBLPROPERTIES (
...
  'projection.year.type'='integer',
  'projection.year.range'='2019,2030',
  'projection.year.interval'='1',
  'projection.year.digits'='4',
...
  • 例: 前日のパーティションを指定してクエリ: 
    SELECT *
FROM <table>
WHERE YEAR=YEAR(now())
  AND MONTH=MONTH(now())
  AND DAY=DAY(now() - INTERVAL '1' DAY)
↑

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のカラム名が重複している
  • 解決
    • どちらかのカラム名を変えればOK
↑

Workgroup: クエリのスキャン量上限設定、コスト管理、ユーザ権限管理 †

  • workgroup毎に
    • 1クエリ1TBスキャンあたり、$5かかるので、上限を設定したい
    • 出力s3 bucketや暗号化方法を指定できる
    • 複数のタグを設定し、識別できる。このタグでBilling画面で識別できる。
    • workgroupに割り当てたクエリの設定を上書きできる。
↑

athenacli: athenaの利用に特化したCLI †

  • awscliの欠点
    • 手動で試す場合、非同期なので使いにくい。
    • 結果はS3のファイルに出るため、ダウンロードする手間がかかる。
↑

awscliからの利用 †

実行順序:

  1. start-query-execution: クエリの実行。DDL(CREATE/ALTER TABLE)もSQLもこれ。非同期なのですぐ完了する。idが出る
  2. get-query-execution: 指定idのステータス確認。定期的に実行して完了/エラーまで待つ
  3. get-query-results: 指定idが完了していれば、S3に結果のファイルが出ている
  4. aws s3 cp 等でlocalに結果のcsv等をダウンロード

SQL実行順序:

  1. CREATE TABLE table_name
  2. MSCK REPAIR TABLE table_name
    • これを実行しないと、selectしても結果は空になる。
  3. 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')」で一つに纏める。
    • ユニークな個数を調べる場合「count(distinct l_comment)」の代わりに、「approx_distinct(l_comment)」を使う。ただし、誤差が2.3%出る
    • 「SELECT * 」の代わりに「SELECT customer.c_name」のように明示的にカラム名を指定する
↑

パーティション分割 †

Athenaは1TBのスキャンあたり、1回$5かかる。スキャンデータを減らす工夫をしないと、コストが非常に増えるまた遅い。

  • 検索時によく指定する、日付、ホスト名、アプリケーション名等で分割すると良さそう
  • HIVE形式のパーティショニングなら自動で対応してくれる。 
    s3:/bucket/prefix/id=<id>/year=<YYYY>/month=<MM>/day=<DD>/file.csv.gz
  • MM/DDの部分は、04/01の形式でも、パーティションはint型指定で検索できた。
トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2020-09-15 (火) 19:02:04 (9d)