メモ書きです
- $AWSACCOUNT:AWSアカウントです。自身の情報に置き換えて読んでください
- リージョンも置き換えて読んでください。
今回の構成
QSからAthenaに接続
QSで設定するデータセットとデータソース
データセットとデータソース作成をコマンドで行う
Athenaクエリ結果
今回使うテーブルへのselect * はこんな感じ。
SELECT * FROM se2.se2_in0
12月のみ抽出クエリ。このあと、QSのカスタムクエリでこちらのクエリを使う
SELECT * FROM se2.se2_in0 where year = 2017 AND month = 12
データソース作成
作成するデータソースの定義となる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でデータソース作成
$ 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"
}
データソース確認
作成したデータソースの確認
$ 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の画面から作成したデータソース確認
データセット作成
作成するデータセットの定義となるJSON
昼夜というカラム名で、以下の計算フィールドを追加
"ifelse(hour >= 18,\"夜\", hour <= 17, \"昼\", null)"
以下のカスタムクエリを追加
"SELECT * FROM se2.se2_in0 where year = 2017 AND month = 12"
{
"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でデータセット作成
$ 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"
}
データセット確認
作成したデータセットの確認
$ 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の画面から作成したデータセット確認
