AWS CUR 使用 ClickHouse S3 外表

背景

在调研 ClickHouse S3 外表功能的过程中，我们发现 ClickHouse 可以支持查询 .gz 格式的数据文件。这启发了我们，我们计划通过 S3 外表功能来简化 AWS CUR 的报告投递路径，此文档将描述基于 ClickHouse S3 外表的 AWS CUR 查询方案。

方案说明

本方案包含两个 AWS 账号，我们在此不假设一定是由 master account 提供 AWS CUR，所以整个方案中有两个不同的账号：

提供 AWS CUR 的账号

提供 ClickHouse 服务的账号

接下来，将分别描述这两个账号需要提供的资源和基础设施

提供 AWS CUR 的账号

用来存放 AWS CUR 的 S3 bucket

要接收账单报告，服务中须有一个 S3 bucket。在账单控制台中设置成本和使用情况报告时，可以选择目前已有的 S3 bucket 或创建一个新的 bucket。S3 bucket 需要信任提供 ClickHouse 服务中的一个 IAM Role，在 bucket policy 中编写相应的 resource-based policy（允许跨账号读取桶内资源）。

参考官方文档：Setting up an Amazon S3 bucket for Cost and Usage Reports

定期投递的 AWS CUR

确认 S3 bucket 创建完后，登录 Billing and Cost Management 控制台，在导航窗格中，选择 Cost & Usage Reports，选择要编辑的报告，然后选择 Edit report (编辑报告)。

参考官方文档：Editing your Cost and Usage Reports configuration

提供 ClickHouse 服务的账号

提供 ClickHouse 服务以及所需的计算资源

可能是一个或一组 nomad client 用于部署 ClickHouse 服务，相关的基础设施还包括大小合适的 EBS 卷，用于存储 ClickHouse 的元数据

用于跨账号访问 S3 bucket 的 IAM Role

该 IAM Role 需要被存放 AWS CUR 的 S3 bucket 信任，允许该 IAM Role 跨账号读取 S3 bucket 中的数据。该 IAM Role 可以是机器级别的 IAM Role，附加在运行 ClickHouse 服务的 nomad client 上，也可以是服务级别的 IAM Role，让 ClickHouse 通过 Assume 操作获取。

需要在该账号上配置 identity-based policy（此 IAM Role 允许访问跨账号的 S3 bucket 内的资源）。

测试方案

1、创建报告以及用于接收报告的 S3 bucket

在提供 AWS CUR 报告的账号上登录 Billing and Cost Management 控制台后，选择 Cost & Usage Reports，点击创建报告。

需勾选"包括资源 ID"

可以选择已有存储桶，也可以创建存储桶。

验证策略，此处的策略使得 S3 bucket 信任 AWS 的 billingreports 服务

点击保存后，控制台会自动向 S3 bucket 添加如下策略：

{
  "Version": "2008-10-17",
  "Id": "Policy1335892530063",
  "Statement": [
    {
      "Sid": "Stmt1335892150622",
      "Effect": "Allow",
      "Principal": {
        "Service": "billingreports.amazonaws.com"
      },
      "Action": [
        "s3:GetBucketAcl",
        "s3:GetBucketPolicy"
      ],
      "Resource": "arn:aws:s3:::shiyou-labs-aws-cur-test",
      "Condition": {
        "StringEquals": {
          "aws:SourceArn": "arn:aws:cur:us-east-1:121828301502:definition/*",
          "aws:SourceAccount": "121828301502"
        }
      }
    },
    {
      "Sid": "Stmt1335892526596",
      "Effect": "Allow",
      "Principal": {
        "Service": "billingreports.amazonaws.com"
      },
      "Action": [
        "s3:PutObject"
      ],
      "Resource": "arn:aws:s3:::shiyou-labs-aws-cur-test/*",
      "Condition": {
        "StringEquals": {
          "aws:SourceArn": "arn:aws:cur:us-east-1:121828301502:definition/*",
          "aws:SourceAccount": "121828301502"
        }
      }
    }
  ]
}

2、编写 ClickHouse IAM Role 的权限

在提供 ClickHouse 服务的账号上配置 IAM Role 跨账号访问 S3 bucket 的权限策略，此处添加的是 identity-based policy，授予 ClickHouse 使用的 IAM Role 只读权限

{
    "Statement": [
        {
          "Effect": "Allow",
          "Action": [
            "s3:ListBucket",
          ],
          "Resource": [
            "arn:aws:s3:::shiyou-labs-aws-cur-test",
          ]
        },
        {
          "Effect": "Allow",
          "Action": [
            "s3:GetObject*",
          ],
          "Resource": "arn:aws:s3:::shiyou-labs-aws-cur-test/*"
        }  
    ]
}

3、编写存放报告的 S3 bucket 的 bucket policy

在存放 AWS CUR 的账号的 S3 bucket policy 里添加权限，允许 ClickHouse 跨账号读取桶内资源（新增以下代码中 Statement 内容，但不覆盖原有策略，此处添加的是 resource-based policy，授予 ClickHouse 使用的 IAM Role 只读权限

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                    "AWS": "arn:aws:iam::565898613640:role/x86_iam_role"
            },
            "Action": "s3:ListBucket",
            "Resource": "arn:aws:s3:::shiyou-labs-aws-cur-test"
        },
        {
            "Effect": "Allow",
            "Principal": {
                    "AWS": "arn:aws:iam::565898613640:role/x86_iam_role"
            },
            "Action": "s3:GetObject*",
            "Resource": "arn:aws:s3:::shiyou-labs-aws-cur-test/*"
        } 
    ]
}

4、验证 ClickHouse 跨账号查询 S3 bucket

通过 Superset 执行以下语句，查询 S3 外表内容

select c1
from 
s3('https://shiyou-labs-aws-cur-test.s3.ap-southeast-1.amazonaws.com/aws-cur-reports-00001.csv.gz'
,'TabSeparated','c1 String')

查询结果