Memo/AmazonWebServices/Athena

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

Athena


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
    )

ベストプラクティス

  • HIVE形式(<PARTITION_COLUMN_NAME>=<VALUE>)でパーティション分割する。それ以外(例:YYYY/MM/DD)では、検索したい数だけパーティションを手動で追加する必要がある。
    s3:/bucket/prefix/id=<id>/year=<YYYY>/month=<MM>/day=<DD>/file.csv.gz
  • ファイルフォーマット
    • Parquet, ORC, Text files, Avro, CSV, TSV, and JSON
  • 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かかるため、スキャンデータを減らす工夫をしないと、コストが非常に増える。

  • 検索時によく指定する、日付、ホスト名、アプリケーション名等で分割すると良さそう
  • データのパーティション分割 - Amazon Athena
  • HIVE形式のパーティショニングなら自動で対応してくれる。
    s3:/bucket/prefix/id=<id>/year=<YYYY>/month=<MM>/day=<DD>/file.csv.gz
  • MM/DDの部分は、04/01の形式でも、パーティションはint型指定で検索できた。

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2020-04-20 (月) 18:23:24 (74d)