使用Kiro Dashboard上的User Report 查询Kiro CLI/IDE用量

CREATE EXTERNAL TABLE IF NOT EXISTS kiro_user_report (
  date_col STRING,
  userid STRING,
  client_type STRING,
  chat_conversations INT,
  credits_used DOUBLE,
  overage_cap DOUBLE,
  overage_credits_used DOUBLE,
  overage_enabled BOOLEAN,
  profileid STRING,
  subscription_tier STRING,
  total_messages INT
)
PARTITIONED BY (
  region STRING,
  year STRING,
  month STRING,
  day STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  'separatorChar' = ',',
  'quoteChar' = '\"',
  'escapeChar' = '\\'
)
STORED AS TEXTFILE
LOCATION 's3://kiro-activity-123456789012/AWSLogs/123456789012/KiroLogs/user_report/'
TBLPROPERTIES (
  'skip.header.line.count' = '1',
  'projection.enabled' = 'true',
  'projection.region.type' = 'enum',
  'projection.region.values' = 'us-east-1',
  'projection.year.type' = 'integer',
  'projection.year.range' = '2025,2030',
  'projection.month.type' = 'integer',
  'projection.month.range' = '1,12',
  'projection.month.digits' = '2',
  'projection.day.type' = 'integer',
  'projection.day.range' = '1,31',
  'projection.day.digits' = '2',
  'storage.location.template' = 's3://kiro-activity-123456789012/AWSLogs/123456789012/KiroLogs/user_report/${region}/${year}/${month}/${day}/00'
);

SELECT
  DATE_FORMAT(DATE_PARSE(CONCAT(year, '-', month, '-01'), '%Y-%m-%d'), '%Y-%m') AS billing_month,
  userid,
  subscription_tier,
  CASE
    WHEN subscription_tier = 'PRO' THEN 1000
    WHEN subscription_tier = 'PRO_PLUS' THEN 3000
    WHEN subscription_tier = 'FREE' THEN 50
    ELSE 0
  END AS plan_credits,
  ROUND(SUM(CASE WHEN client_type = 'KIRO_CLI' THEN credits_used ELSE 0 END), 2) AS cli_credits,
  ROUND(SUM(CASE WHEN client_type = 'KIRO_IDE' THEN credits_used ELSE 0 END), 2) AS ide_credits,
  ROUND(SUM(credits_used), 2) AS total_credits,
  CASE
    WHEN SUM(credits_used) > (CASE WHEN subscription_tier = 'PRO' THEN 1000 WHEN subscription_tier = 'PRO_PLUS' THEN 3000 WHEN subscription_tier = 'FREE' THEN 50 ELSE 0 END) THEN 'Y'
    ELSE 'N'
  END AS over_limit
FROM kiro_user_report
GROUP BY
  DATE_FORMAT(DATE_PARSE(CONCAT(year, '-', month, '-01'), '%Y-%m-%d'), '%Y-%m'),
  userid,
  subscription_tier
ORDER BY billing_month DESC, total_credits DESC;

SELECT
  CONCAT(year, '-', month, '-', day) AS date,
  userid,
  ROUND(SUM(CASE WHEN client_type = 'KIRO_CLI' THEN credits_used ELSE 0 END), 2) AS cli_credits,
  ROUND(SUM(CASE WHEN client_type = 'KIRO_IDE' THEN credits_used ELSE 0 END), 2) AS ide_credits,
  ROUND(SUM(credits_used), 2) AS total_credits
FROM kiro_user_report
WHERE year = '2026' AND month = '02' AND
  userid = '843884c8-00c1-7046-e4e8-7ba5acc6cfc7'
GROUP BY year, month, day, userid
ORDER BY day, total_credits DESC;

- 月份
- 用户名（从userid自动关联）
- 订阅Plan
- 本Plan包含的Credit量
- CLI的Credit用量
- IDE的Credit用量
- 总用量
- 是否超出本月Credit量（Y表示超过，N表示未超过）

SELECT
  DATE_FORMAT(DATE_PARSE(CONCAT(r.year, '-', r.month, '-01'), '%Y-%m-%d'), '%Y-%m') AS billing_month,
  u.username,
  r.subscription_tier,
  CASE
    WHEN r.subscription_tier = 'PRO' THEN 1000
    WHEN r.subscription_tier = 'PRO_PLUS' THEN 3000
    WHEN r.subscription_tier = 'FREE' THEN 50
    ELSE 0
  END AS plan_credits,
  ROUND(SUM(CASE WHEN r.client_type = 'KIRO_CLI' THEN r.credits_used ELSE 0 END), 2) AS cli_credits,
  ROUND(SUM(CASE WHEN r.client_type = 'KIRO_IDE' THEN r.credits_used ELSE 0 END), 2) AS ide_credits,
  ROUND(SUM(r.credits_used), 2) AS total_credits,
  CASE
    WHEN SUM(r.credits_used) > (CASE WHEN r.subscription_tier = 'PRO' THEN 1000 WHEN r.subscription_tier = 'PRO_PLUS' THEN 3000 WHEN r.subscription_tier = 'FREE' THEN 50 ELSE 0 END) THEN 'Y'
    ELSE 'N'
  END AS over_limit
FROM kiro_user_report r
LEFT JOIN identity_center_users u ON r.userid = u.userid
GROUP BY
  DATE_FORMAT(DATE_PARSE(CONCAT(r.year, '-', r.month, '-01'), '%Y-%m-%d'), '%Y-%m'),
  u.username,
  r.subscription_tier
ORDER BY billing_month DESC, total_credits DESC;