Memo/AmazonWebServices/Athena

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

Athena


Apache Parquet型式: 列指向、バイナリフォーマット


SendGridのActivityを検索


データ型とクエリ


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')」で一つに纏める。否定は「NOT(regexp_like(...))」
    • ユニークな個数を調べる場合「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-10-16 (金) 14:37:38 (6d)