QuickSightでAthenaと接続するデータソースとデータセット作成をコマンドで行う

メモ書きです

* $AWSACCOUNT:AWSアカウントです。自身の情報に置き換えて読んでください
* リージョンも置き換えて読んでください。

## 今回の構成

#### QSからAthenaに接続

![QS_Athena-ページ2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/ba3c02ad-8ed2-2a64-a293-3179af39966d.png)

#### QSで設定するデータセットとデータソース

データセットとデータソース作成をコマンドで行う

![QS_Athena-ページ1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/10a08f1a-3490-6583-088a-27e03794605f.png)

#### Athenaクエリ結果

今回使うテーブルへのselect * はこんな感じ。

```sql:
SELECT * FROM se2.se2_in0
```

![スクリーンショット 0003-06-11 9.30.51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/af52db26-92c4-a7f5-22ab-f65de1d5b0c9.png)

12月のみ抽出クエリ。このあと、QSのカスタムクエリでこちらのクエリを使う

```sql:
SELECT * FROM se2.se2_in0 where year = 2017 AND month = 12
```

![スクリーンショット 0003-06-11 9.02.35.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/3b4366a5-b5b4-c763-0cab-49665559e215.png)

## データソース作成

#### 作成するデータソースの定義となるJSON

```json:
{
    "AwsAccountId": "$AWSACCOUNT",
    "DataSourceId": "Tmp-0611-Data-Source",
    "Name": "Tmp 0611 Data Source",
    "Type": "ATHENA",
    "DataSourceParameters": {

        "AthenaParameters": {
            "WorkGroup": "primary"
        }
    },
    "Permissions": [
        {
            "Principal": "arn:aws:quicksight:us-east-1:$AWSACCOUNT:user/default/uehara",
            "Actions": [
                "quicksight:UpdateDataSourcePermissions",
                "quicksight:DescribeDataSource",
                "quicksight:DescribeDataSourcePermissions",
                "quicksight:PassDataSource",
                "quicksight:UpdateDataSource",
                "quicksight:DeleteDataSource"

            ]
        }
    ]
}

```

#### データソース作成

作成したJSONを元にcreate-data-sourceでデータソース作成

```bash:
$ aws quicksight create-data-source --cli-input-json file://create-data-source.json
{
    "Status": 202,
    "Arn": "arn:aws:quicksight:ap-northeast-1:$AWSACCOUNT:datasource/Tmp-0611-Data-Source",
    "DataSourceId": "Tmp-0611-Data-Source",
    "CreationStatus": "CREATION_IN_PROGRESS",
    "RequestId": "b735d88f-35cf-496f-8047-1e53d54e4491"
}

```

#### データソース確認

作成したデータソースの確認

```bash:
$ aws quicksight describe-data-source --aws-account-id $AWSACCOUNT --data-source-id 'Tmp-0611-Data-Source'
{
    "Status": 200,
    "DataSource": {
        "Arn": "arn:aws:quicksight:ap-northeast-1:$AWSACCOUNT:datasource/Tmp-0611-Data-Source",
        "DataSourceId": "Tmp-0611-Data-Source",
        "Name": "Tmp 0611 Data Source",
        "Type": "ATHENA",
        "Status": "CREATION_SUCCESSFUL",
        "CreatedTime": "2021-06-11T08:49:32.169000+09:00",
        "LastUpdatedTime": "2021-06-11T08:49:34.113000+09:00",
        "DataSourceParameters": {
            "AthenaParameters": {
                "WorkGroup": "primary"
            }
        },
        "SslProperties": {
            "DisableSsl": false
        }
    },
    "RequestId": "dc0d3fc9-2d59-431c-97d2-98215f9b7b88"
}
```

#### QSの画面から作成したデータソース確認

![スクリーンショット 0003-06-11 8.52.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/20170ff7-af2b-5056-47e6-2d69812c8e4b.png)

![スクリーンショット 0003-06-11 8.52.46.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/0b2f90c0-1578-7715-181c-89c08a0dd1c4.png)

## データセット作成

#### 作成するデータセットの定義となるJSON

昼夜というカラム名で、以下の計算フィールドを追加
>"ifelse(hour >= 18,\"夜\", hour <= 17, \"昼\", null)"

以下のカスタムクエリを追加
>"SELECT * FROM se2.se2_in0 where year = 2017 AND month = 12"

```json:
{
    "AwsAccountId": "$AWSACCOUNT",
    "DataSetId": "Tmp-0611-Data-Set",
    "Name": "Tmp 0611 Data Set",
    "PhysicalTableMap": {
        "AthenaPhysicalTable": {
            "CustomSql": {
                "DataSourceArn": "arn:aws:quicksight:ap-northeast-1:$AWSACCOUNT:datasource/Tmp-0611-Data-Source",
                "Name":"Tmp-CustomSQL",
                "SqlQuery":"SELECT * FROM se2.se2_in0 where year = 2017 AND month = 12",
                "Columns":[
          {
            "Name": "deviceid",
            "Type": "STRING"
          },
          {
            "Name": "uuid",
            "Type": "STRING"
          },
          {
            "Name": "appid",
            "Type": "STRING"
          },
          {
            "Name": "country",
            "Type": "STRING"
          },
          {
            "Name": "year",
            "Type": "STRING"
          },
          {
            "Name": "month",
            "Type": "INTEGER"
          },
          {
            "Name": "day",
            "Type": "INTEGER"
          },
          {
            "Name": "hour",
            "Type": "INTEGER"
          }
        ]
                }
        }
    },
    "LogicalTableMap": {
        "AthenaPhysicalTable": {
            "Source": {
                "PhysicalTableId": "AthenaPhysicalTable"
            },
            "Alias": "Group 1",
            "DataTransforms": [
                   {
                        "CreateColumnsOperation": {
                          "Columns":[
                            {
                            "ColumnName": "昼夜",
                            "ColumnId": "test",
                            "Expression":"ifelse(hour >= 18,\"夜\", hour <= 17, \"昼\", null)"
                          }
                        ]
                        }
                    }
                    ]}},
    "ImportMode": "DIRECT_QUERY",
    "Permissions": [
        {
            "Actions": [
                "quicksight:UpdateDataSetPermissions",
                "quicksight:DescribeDataSet",
                "quicksight:DescribeDataSetPermissions",
                "quicksight:PassDataSet",
                "quicksight:DescribeIngestion",
                "quicksight:ListIngestions",
                "quicksight:UpdateDataSet",
                "quicksight:DeleteDataSet",
                "quicksight:CreateIngestion",
                "quicksight:CancelIngestion"
            ],
            "Principal": "arn:aws:quicksight:us-east-1:$AWSACCOUNT:user/default/uehara"
        }
    ]
}

```

#### データセット

作成したJSONを元にcreate-data-setでデータセット作成

```bash:
$ aws quicksight create-data-set --cli-input-json file://create-data-set.json
{
    "Status": 201,
    "Arn": "arn:aws:quicksight:ap-northeast-1:$AWSACCOUNT:dataset/Tmp-0611-Data-Set",
    "DataSetId": "Tmp-0611-Data-Set",
    "RequestId": "7b323396-c373-4cfd-bf4b-08f85de4ac43"
}

```

#### データセット確認

作成したデータセットの確認

```bash:
$ aws quicksight describe-data-set --aws-account-id $AWSACCOUNT --data-set-id 'Tmp-0611-Data-Set'
{
    "Status": 200,
    "DataSet": {
        "Arn": "arn:aws:quicksight:ap-northeast-1:$AWSACCOUNT:dataset/Tmp-0611-Data-Set",
        "DataSetId": "Tmp-0611-Data-Set",
        "Name": "Tmp 0611 Data Set",
        "CreatedTime": "2021-06-11T08:54:42.203000+09:00",
        "LastUpdatedTime": "2021-06-11T08:54:42.203000+09:00",
        "PhysicalTableMap": {
            "AthenaPhysicalTable": {
                "CustomSql": {
                    "DataSourceArn": "arn:aws:quicksight:ap-northeast-1:$AWSACCOUNT:datasource/Tmp-0611-Data-Source",
                    "Name": "Tmp-CustomSQL",
                    "SqlQuery": "SELECT * FROM se2.se2_in0 where year = 2017 AND month = 12",
                    "Columns": [
                        {
                            "Name": "deviceid",
                            "Type": "STRING"
                        },
                        {
                            "Name": "uuid",
                            "Type": "STRING"
                        },
                        {
                            "Name": "appid",
                            "Type": "STRING"
                        },
                        {
                            "Name": "country",
                            "Type": "STRING"
                        },
                        {
                            "Name": "year",
                            "Type": "STRING"
                        },
                        {
                            "Name": "month",
                            "Type": "INTEGER"
                        },
                        {
                            "Name": "day",
                            "Type": "INTEGER"
                        },
                        {
                            "Name": "hour",
                            "Type": "INTEGER"
                        }
                    ]
                }
            }
        },
        "LogicalTableMap": {
            "AthenaPhysicalTable": {
                "Alias": "Tmp-CustomSQL",
                "DataTransforms": [
                    {
                        "CreateColumnsOperation": {
                            "Columns": [
                                {
                                    "ColumnName": "昼夜",
                                    "ColumnId": "test",
                                    "Expression": "ifelse(hour >= 18,\"夜\", hour <= 17, \"昼\", null)"
                                }
                            ]
                        }
                    }
                ],
                "Source": {
                    "PhysicalTableId": "AthenaPhysicalTable"
                }
            }
        },
        "OutputColumns": [
            {
                "Name": "deviceid",
                "Type": "STRING"
            },
            {
                "Name": "uuid",
                "Type": "STRING"
            },
            {
                "Name": "appid",
                "Type": "STRING"
            },
            {
                "Name": "country",
                "Type": "STRING"
            },
            {
                "Name": "year",
                "Type": "STRING"
            },
            {
                "Name": "month",
                "Type": "INTEGER"
            },
            {
                "Name": "day",
                "Type": "INTEGER"
            },
            {
                "Name": "hour",
                "Type": "INTEGER"
            },
            {
                "Name": "昼夜",
                "Type": "STRING"
            }
        ],
        "ImportMode": "DIRECT_QUERY",
        "ConsumedSpiceCapacityInBytes": 0
    },
    "RequestId": "231c0c8b-cd39-48a6-9706-db0c1225dd0f"
}

```

#### QSの画面から作成したデータセット確認

![スクリーンショット 0003-06-11 9.00.30.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/3003d832-fd9f-4b4c-b54b-5045ef0e2d76.png)

![スクリーンショット 0003-06-11 9.02.00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/6343d47b-abd1-911e-fd1a-f51b750cb424.png)