Athena


DBカラム名にハイフン等が入っている場合、値が表示されない

問題:

解決:

WITH SERDEPROPERTIES (
  "mapping.ses_configurationset"="ses:configuration-set",
  "mapping.ses_source_ip"="ses:source-ip", 
  "mapping.ses_from_domain"="ses:from-domain", 
  "mapping.ses_caller_identity"="ses:caller-identity"
  )

UNNEST(): ネストされた配列のフラット化


縦横変換(pivot/unpivot)

記事:

例: AWS billing (CUR)から、月別/サービス別のコストを抽出。サービスを行、月を列に変換。

WITH t1 AS (
SELECT 
  bill_billing_period_start_date AS TIME,
  'Total' AS product,
  SUM(line_item_blended_cost) AS cost
FROM cost.monthly
WHERE
  bill_billing_period_start_date BETWEEN TIMESTAMP '2023-07-01 00:00:00' AND TIMESTAMP '2023-09-30 23:59:59'
GROUP BY 1
UNION ALL
SELECT 
  bill_billing_period_start_date AS TIME,
  line_item_product_code AS product,
  SUM(line_item_blended_cost) AS cost
FROM cost.monthly
WHERE
  bill_billing_period_start_date BETWEEN TIMESTAMP '2023-07-01 00:00:00' AND TIMESTAMP '2023-09-30 23:59:59'
GROUP BY 1, 2
),
t2 AS (
SELECT
  TIME,
  map_agg(product, cost) AS kv_product
FROM t1
GROUP BY TIME
)
SELECT
  t2.time,
  t2.kv_product['Total'] AS Total,
  t2.kv_product['AmazonEC2'] AS AmazonEC2,
  t2.kv_product['AmazonRDS'] AS AmazonRDS,
  t2.kv_product['AmazonS3'] AS AmazonS3

FROM t2
ORDER BY t2.time

トラブルシューティング

空白行を返す場合:

記事:


JDBC経由でRDS等をデータソースとして登録してAthenaで検索


terraformで管理する

記事:


INSERT INTO: 結果を別s3 bucketに出力

記事:


パフォーマンスチューニング

記事:


クエリサンプル

記事:


$path: 検索対象のs3 objectを表示する

記事:


S3上にパーティション無しで配置したobjectにパーティションを付ける

  1. 検索対象のs3 objectを並べた「symlink.txt」を用意する
  2. symlink.txtをs3 bucketにアップロード。データがあるs3 bucketとは別で良い
  3. create tableで、symlinkを読むオプションを指定
    STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
  4. tableにロードする
    msck repair TABLE <db>.<table>;
  5. queryを実行

Athena engine v2


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


SendGridのActivityを検索


データ型とクエリ

UNDO: 任意のs3 bucketと形式で出力

workgroupで設定した出力s3 bucketの形式はCSV固定。
任意のs3 bucketや指定形式で出力したい場合。


VALUES: インラインテーブル


パーティション指定


ALB/ELBログの分析


日付と時刻


Partition Projection: パーティション管理の自動化


injected型

記事:


HIVE形式のパスに projection type dateを設定するとエラー

HIVE形式で「s3://bucket/year=${year}/month=${month}/day=${day}」とパーティションを分けているとする。
2019〜YYYYまでをパーティションとして認識させたい。


Unknown pattern letter


Column repeated in partitioning columns


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


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


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

ベストプラクティス


パーティション分割

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


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2024-05-28 (火) 19:06:16