LiteLLM + Amazon QuickSight 数据可视化配置手册

litellm_settings:
  cache: true
  store_audit_logs: true
  success_callback: ["s3_v2"]
  s3_callback_params:
    s3_bucket_name: os.environ/S3_BUCKET_NAME
    s3_region_name: os.environ/AWS_DEFAULT_REGION
    s3_path: litellm-logs
    s3_endpoint_url: https://s3.amazonaws.com

{
  "id": "chatcmpl-xxx",
  "model": "bedrock/us.anthropic.claude-opus-4-6-v1",
  "status": "success",
  "response_time": 3.07,
  "response_cost": 0.0022495,
  "metadata": {
    "user_api_key_alias": "claudecode",
    "user_api_key_auth_metadata": {
      "tags": ["user:cloudcode-user1", "project:game1"]
    }
  },
  "cost_breakdown": {
    "input_cost": 0.0001595,
    "output_cost": 0.00209
  },
  "request_tags": ["user:cloudcode-user1", "project:game1"]
}

CREATE EXTERNAL TABLE IF NOT EXISTS litellm_logs (
  id STRING,
  litellm_call_id STRING,
  trace_id STRING,
  call_type STRING,
  cache_hit STRING,
  stream STRING,
  status STRING,
  custom_llm_provider STRING,
  startTime DOUBLE,
  endTime DOUBLE,
  response_time DOUBLE,
  model STRING,
  metadata STRUCT<
    user_api_key_alias: STRING,
    user_api_key_spend: DOUBLE,
    user_api_key_user_id: STRING,
    user_api_key_request_route: STRING,
    requester_ip_address: STRING,
    user_agent: STRING,
    user_api_key_auth_metadata: STRUCT<
      tags: ARRAY<STRING>
    >
  >,
  messages ARRAY<STRUCT<role: STRING, content: STRING>>,
  response STRUCT<
    choices: ARRAY<STRUCT<
      finish_reason: STRING,
      message: STRUCT<content: STRING, role: STRING>
    >>,
    usage: STRUCT<
      completion_tokens: INT,
      prompt_tokens: INT,
      total_tokens: INT
    >
  >,
  response_cost DOUBLE,
  cost_breakdown STRUCT<
    input_cost: DOUBLE,
    output_cost: DOUBLE,
    total_cost: DOUBLE
  >,
  request_tags ARRAY<STRING>,
  model_group STRING,
  api_base STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'ignore.malformed.json' = 'true',
  'case.insensitive' = 'true'
)
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://<your-bucket>/litellm-logs/'
TBLPROPERTIES ('has_encrypted_data'='false');

CREATE OR REPLACE VIEW litellm_logs_flat AS
SELECT
  id,
  status,
  custom_llm_provider,
  startTime,
  response_time,
  model,
  -- 时间字段
  from_unixtime(CAST(startTime AS BIGINT)) AS request_time,
  date(from_unixtime(CAST(startTime AS BIGINT))) AS request_date,
  hour(from_unixtime(CAST(startTime AS BIGINT))) AS request_hour,
  day_of_week(from_unixtime(CAST(startTime AS BIGINT))) AS request_day_of_week,
  -- 用户维度
  metadata.user_api_key_alias,
  metadata.requester_ip_address AS metadata_ip,
  metadata.user_agent AS metadata_user_agent,
  -- 从 tags 提取用户和项目
  regexp_extract(
    array_join(filter(metadata.user_api_key_auth_metadata.tags, t -> t LIKE 'user:%'), ','),
    'user:(.*)', 1
  ) AS tag_user,
  regexp_extract(
    array_join(filter(metadata.user_api_key_auth_metadata.tags, t -> t LIKE 'project:%'), ','),
    'project:(.*)', 1
  ) AS tag_project,
  -- 响应
  response.choices[1].finish_reason AS finish_reason,
  response.choices[1].message.content AS response_content,
  response.usage.completion_tokens AS usage_completion_tokens,
  response.usage.prompt_tokens AS usage_prompt_tokens,
  response.usage.total_tokens AS usage_total_tokens,
  -- 费用
  response_cost,
  cost_breakdown.input_cost,
  cost_breakdown.output_cost,
  cost_breakdown.total_cost,
  request_tags,
  model_group,
  api_base
FROM litellm_logs

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "s3:ListAllMyBuckets",
      "Resource": "arn:aws:s3:::*"
    },
    {
      "Effect": "Allow",
      "Action": ["s3:GetObject", "s3:GetBucketLocation", "s3:ListBucket"],
      "Resource": [
        "arn:aws:s3:::<your-bucket>",
        "arn:aws:s3:::<your-bucket>/*"
      ]
    }
  ]
}

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "ec2:CreateNetworkInterface",
        "ec2:ModifyNetworkInterfaceAttribute",
        "ec2:DeleteNetworkInterface",
        "ec2:DescribeSubnets",
        "ec2:DescribeSecurityGroups",
        "ec2:DescribeNetworkInterfaces",
        "ec2:DescribeVpcs"
      ],
      "Resource": "*"
    }
  ]
}

aws quicksight create-vpc-connection \
  --aws-account-id <ACCOUNT_ID> \
  --vpc-connection-id litellm-vpc \
  --name litellm-vpc \
  --subnet-ids subnet-xxx subnet-yyy \
  --security-group-ids sg-xxx \
  --role-arn arn:aws:iam::<ACCOUNT_ID>:role/service-role/aws-quicksight-service-role-v0 \
  --region us-east-1

aws ec2 authorize-security-group-ingress \
  --group-id <aurora-sg-id> \
  --protocol tcp --port 5432 \
  --source-group <aurora-sg-id>

aws quicksight create-data-source \
  --aws-account-id <ACCOUNT_ID> \
  --data-source-id litellm-athena \
  --name "litellm-athena" \
  --type ATHENA \
  --data-source-parameters '{"AthenaParameters":{"WorkGroup":"primary"}}' \
  --region us-east-1

aws quicksight create-data-source \
  --aws-account-id <ACCOUNT_ID> \
  --data-source-id litellm-aurora \
  --name "litellm-aurora" \
  --type POSTGRESQL \
  --data-source-parameters '{
    "PostgreSqlParameters": {
      "Host": "<aurora-cluster-endpoint>",
      "Port": 5432,
      "Database": "litellm"
    }
  }' \
  --credentials '{
    "SecretArn": "arn:aws:secretsmanager:us-east-1:<ACCOUNT_ID>:secret:<secret-name>"
  }' \
  --vpc-connection-properties '{
    "VpcConnectionArn": "arn:aws:quicksight:us-east-1:<ACCOUNT_ID>:vpcConnection/litellm-vpc"
  }' \
  --ssl-properties '{"DisableSsl": false}' \
  --region us-east-1

# 创建 Dataset（以 DailyTagSpend 为例）
aws quicksight create-data-set \
  --aws-account-id <ACCOUNT_ID> \
  --data-set-id litellm-daily-tag-spend \
  --name "LiteLLM Daily Tag Spend" \
  --import-mode SPICE \
  --physical-table-map '{
    "table": {
      "RelationalTable": {
        "DataSourceArn": "arn:aws:quicksight:us-east-1:<ACCOUNT_ID>:datasource/litellm-aurora",
        "Schema": "public",
        "Name": "LiteLLM_DailyTagSpend",
        "InputColumns": [
          {"Name": "tag", "Type": "STRING"},
          {"Name": "date", "Type": "STRING"},
          {"Name": "model", "Type": "STRING"},
          {"Name": "spend", "Type": "DECIMAL"},
          {"Name": "api_requests", "Type": "INTEGER"},
          {"Name": "successful_requests", "Type": "INTEGER"},
          {"Name": "failed_requests", "Type": "INTEGER"}
        ]
      }
    }
  }' \
  --permissions '[{
    "Principal": "arn:aws:quicksight:us-east-1:<ACCOUNT_ID>:user/default/<your-user>",
    "Actions": ["quicksight:DescribeDataSet","quicksight:PassDataSet","quicksight:UpdateDataSet","quicksight:DeleteDataSet","quicksight:DescribeIngestion","quicksight:ListIngestions","quicksight:CreateIngestion"]
  }]' \
  --region us-east-1

# 设置每日刷新（UTC 00:00 = 北京时间 08:00）
aws quicksight create-refresh-schedule \
  --aws-account-id <ACCOUNT_ID> \
  --data-set-id litellm-daily-tag-spend \
  --schedule '{
    "ScheduleId": "daily-refresh",
    "ScheduleFrequency": {
      "Interval": "DAILY",
      "TimeOfTheDay": "00:00"
    },
    "RefreshType": "FULL_REFRESH"
  }' \
  --region us-east-1

aws quicksight update-data-source-permissions \
  --data-source-id <id> \
  --grant-permissions '[{"Principal":"<user-arn>","Actions":[...]}]'