基于 Grafana 实现云资源成本及使用量的可视化分析

sudo nano /etc/yum.repos.d/grafana.repo

[grafana]
name=grafana
baseurl=https://packages.grafana.com/oss/rpm
repo_gpgcheck=1
enabled=1
gpgcheck=1
gpgkey=https://packages.grafana.com/gpg.key
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt

sudo yum install grafana

sudo systemctl start grafana-server

sudo systemctl status grafana-server

sudo systemctl enable grafana-server.service

sudo grafana-cli plugins install grafana-athena-datasource
sudo systemctl restart grafana-server

s3://demo-cur-report/grafana/demo-cur-report/crawler-cfn.yml

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AthenaQueryAccess",
            "Effect": "Allow",
            "Action": [
                "athena:ListDatabases",
                "athena:ListDataCatalogs",
                "athena:ListWorkGroups",
                "athena:GetDatabase",
                "athena:GetDataCatalog",
                "athena:GetQueryExecution",
                "athena:GetQueryResults",
                "athena:GetTableMetadata",
                "athena:GetWorkGroup",
                "athena:ListTableMetadata",
                "athena:StartQueryExecution",
                "athena:StopQueryExecution"
            ],
            "Resource": [
                "*"
            ]
        },
        {
            "Sid": "GlueReadAccess",
            "Effect": "Allow",
            "Action": [
                "glue:GetDatabase",
                "glue:GetDatabases",
                "glue:GetTable",
                "glue:GetTables",
                "glue:GetPartition",
                "glue:GetPartitions",
                "glue:BatchGetPartition"
            ],
            "Resource": [
                "*"
            ]
        },
        {
            "Sid": "AthenaQueryResultAccess",
            "Effect": "Allow",
            "Action": [
                "s3:GetBucketLocation",
                "s3:GetObject",
                "s3:ListBucket",
                "s3:ListBucketMultipartUploads",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload",
                "s3:PutObject"
            ],
            "Resource": [
                "arn:aws-cn:s3:::grafana-athena-query-results",
                "arn:aws-cn:s3:::grafana-athena-query-results/*"
            ]
        },
        {
            "Sid": "AthenaS3Access",
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws-cn:s3:::demo-cur-report",
                "arn:aws-cn:s3:::demo-cur-report/*"
            ]
        }
    ]
}

SELECT * FROM demo_cur_report LIMIT 100;

SELECT 
  bill_payer_account_id,
  line_item_usage_account_id,
  DATE_FORMAT((line_item_usage_start_date),'%Y-%m-%d') AS day_line_item_usage_start_date, 
  CASE 
    WHEN line_item_line_item_type IN ('Usage') THEN 'OnDemand'
    WHEN line_item_line_item_type IN ('Fee','RIFee','DiscountedUsage') THEN 'ReservedInstance' 
  END AS case_purchase_option,
  SPLIT_PART(SPLIT_PART(reservation_reservation_a_r_n,':',6),'/',2) AS split_reservation_reservation_a_r_n,
  SPLIT_PART(line_item_usage_type ,':',2) AS split_line_item_usage_type_instance_type,
  SPLIT_PART(SPLIT_PART(line_item_usage_type ,':',2), '.', 1) AS split_line_item_usage_type_instance_family,
  CASE product_region
    WHEN NULL THEN 'Global'
    WHEN '' THEN 'Global'
    ELSE product_region
  END AS case_product_region,
  line_item_line_item_type,
  SUM(TRY_CAST(line_item_usage_amount AS DOUBLE)) AS sum_line_item_usage_amount,
  SUM(TRY_CAST(reservation_unused_quantity AS DOUBLE)) AS sum_reservation_unused_quantity,
  SUM(TRY_CAST(line_item_normalized_usage_amount AS DOUBLE)) AS sum_line_item_normalized_usage_amount,
  SUM(TRY_CAST(reservation_unused_normalized_unit_quantity AS DOUBLE)) AS sum_reservation_unused_normalized_unit_quantity,
  SUM(CAST(reservation_effective_cost AS DECIMAL(16,8))) AS sum_reservation_effective_cost,
  SUM(CAST(line_item_unblended_cost AS DECIMAL(16,8))) AS sum_line_item_unblended_cost
FROM 
  ${table_name} 
WHERE 
  ${date_filter} 
  AND product_product_name = 'Amazon Elastic Compute Cloud'
  AND line_item_operation LIKE '%RunInstance%'
  AND line_item_line_item_type IN ('Usage','Fee','RIFee','DiscountedUsage')
  AND product_product_family NOT IN ('Data Transfer')
GROUP BY 
  bill_payer_account_id,
  line_item_usage_account_id,
  DATE_FORMAT((line_item_usage_start_date),'%Y-%m-%d'),
  4, -- refers to case_purchase_option
  SPLIT_PART(SPLIT_PART(reservation_reservation_a_r_n,':',6),'/',2),
  SPLIT_PART(line_item_usage_type ,':',2),
  SPLIT_PART(SPLIT_PART(line_item_usage_type ,':',2), '.', 1),
  8, -- refers to case_product_region
  line_item_line_item_type
ORDER BY 
  day_line_item_usage_start_date,
  split_line_item_usage_type_instance_type,
  sum_line_item_unblended_cost DESC;