Amazon Athena：S3 上的无服务器 SQL

什么是 Amazon Athena

设置 Athena：工作组和输出位置

# Create a workgroup with an encrypted output location
aws athena create-work-group \
  --name analytics-team \
  --configuration '{
    "ResultConfiguration": {
      "OutputLocation": "s3://athena-results-123/analytics-team/",
      "EncryptionConfiguration": {"EncryptionOption": "SSE_S3"}
    },
    "EnforceWorkGroupConfiguration": true,
    "PublishCloudWatchMetricsEnabled": true,
    "BytesScannedCutoffPerQuery": 10737418240
  }'

运行您的第一个查询

-- Query total sales by month from partitioned S3 data
SELECT
  year,
  month,
  SUM(order_total) AS monthly_revenue
FROM my_db.curated_sales
WHERE year = '2024'
GROUP BY year, month
ORDER BY month;

-- CTAS: materialise result as Parquet for reuse
CREATE TABLE my_db.monthly_revenue
WITH (format = 'PARQUET', external_location = 's3://my-data-lake-123/curated/monthly_revenue/')
AS
SELECT year, month, SUM(order_total) AS revenue
FROM my_db.curated_sales
GROUP BY year, month;

成本优化：分区裁剪

-- EXPENSIVE: No partition filter -> scans all data
SELECT * FROM my_db.curated_sales WHERE customer_id = '12345';

-- CHEAP: Partition filter applied -> scans only Jan 2024
SELECT * FROM my_db.curated_sales
WHERE year = '2024' AND month = '01' AND customer_id = '12345';

成本优化：列式格式

-- After converting raw CSV to Parquet:
-- CSV version: 500 GB table, query scans 500 GB -> $2.50
-- Parquet version: same data compressed to 50 GB,
--   query reads only 2 columns -> scans ~2 GB -> $0.01

-- Verify table format in Glue Catalogue
SHOW CREATE TABLE my_db.curated_sales;

使用数据源连接器执行联合查询

-- Federated query: join S3 Parquet with live RDS table
SELECT s.order_id, s.total, c.email
FROM my_db.curated_sales s
JOIN rds_lambda.prod_db.customers c
  ON s.customer_id = c.id
WHERE s.year = '2024' AND s.month = '01';

Athena 与 QuickSight 的集成

优化 Athena 查询性能

-- Use approx_distinct for fast cardinality estimate
SELECT
  year,
  approx_distinct(customer_id) AS approx_unique_customers
FROM my_db.curated_sales
WHERE year = '2024'
GROUP BY year;

控制 Athena 的访问权限

# Minimum IAM policy for an Athena analyst
{
  "Effect": "Allow",
  "Action": [
    "athena:StartQueryExecution",
    "athena:GetQueryExecution",
    "athena:GetQueryResults",
    "athena:StopQueryExecution",
    "glue:GetDatabase",
    "glue:GetTable",
    "glue:GetPartitions",
    "s3:GetObject",
    "s3:PutObject"
  ],
  "Resource": "*"
}

保存查询结果和计划查询

# Start an Athena query via CLI and retrieve results
QUERY_ID=$(aws athena start-query-execution \
  --query-string 'SELECT COUNT(*) FROM my_db.curated_sales WHERE year=2024' \
  --work-group analytics-team \
  --query 'QueryExecutionId' --output text)

# Wait and fetch results
aws athena get-query-results --query-execution-id $QUERY_ID

Athena 与 Redshift：选择合适的工具

快速检查

课程回顾