AWS have now put a nifty API behind quicksight so we can automatically create users, data-sources, data-sets, analysis, templates and dashboards all through the API.
I will go through the api commands and show some demo’s of the commands
Template Build
So we have test_list_demo analysis which has been built and we want to build a template from this which can then use to create the other dashboards, we need to use the API to get more information of the analysis to be able to create the template programmatically through the API.
The first api call is to list-analysis, i’ve added a little grep on the api call to filter just for the analysis we need
LT-14739:git tonym$ aws quicksight list-analyses --aws-account-id 'xxxxxxxxxxx' --profile test-profile |grep -B 2 -A 2 test_list_demo
"Arn": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:analysis/ce3cf271-43f5-4425-9fc0-b77e76595b85",
"AnalysisId": "ce3cf271-43f5-4425-9fc0-b77e76595b85",
"Name": "test_list_demo",
"Status": "CREATION_SUCCESSFUL",
"CreatedTime": "2021-02-17T12:21:07.629000+00:00",
We also need to get the associated data set for the template creation this can be done by describing the analysis
LT-14739:git tonym$ aws quicksight describe-analysis --analysis-id ce3cf271-43f5-4425-9fc0-b77e76595b85 --aws-account-id 'xxxxxxxxxxx' --profile strata-non-prod-analysis
{
"Status": 200,
"Analysis": {
"AnalysisId": "ce3cf271-43f5-4425-9fc0-b77e76595b85",
"Arn": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:analysis/ce3cf271-43f5-4425-9fc0-b77e76595b85",
"Name": "test_list_demo",
"Status": "CREATION_SUCCESSFUL",
"DataSetArns": [
"arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:dataset/42b208e3-2c8f-41d0-8950-8411799f72ef"
],
"CreatedTime": "2021-02-17T12:21:07.629000+00:00",
"LastUpdatedTime": "2021-03-22T11:39:41.281000+00:00",
"Sheets": [
{
"SheetId": "293057c1-e43d-450c-8c34-33491070b053",
"Name": " "
}
]
},
"RequestId": "547a1990-1e27-4573-9744-3c7c3ba9764d"
}
Now we have the ARN for the analysis and dataset we can create the template, for the template build we have a json file with all the config data in, templateid & name are the name of the template, we use the source analysis ARN from above in the “SourceAnalysis” bit of the json and the “DataSetArn” from the analysis describe this will just us the config to be able to build the template
LT-14739:tony tonym$ cat template_test_list_tony.json
{
"AwsAccountId": "xxxxxxxxxxx",
"TemplateId": "template_test_list_tony",
"Name": "template_test_list_tony",
"SourceEntity": {
"SourceAnalysis": {
"Arn": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:analysis/ce3cf271-43f5-4425-9fc0-b77e76595b85",
"DataSetReferences": [
{
"DataSetPlaceholder": "test_list_demo",
"DataSetArn": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:dataset/42b208e3-2c8f-41d0-8950-8411799f72ef"
}
]
}
},
"VersionDescription": "Version-1"
We create the template using the create-template api call
LT-14739:tony tonym$ aws quicksight create-template --cli-input-json file://template_test_list_tony.json --aws-account-id 'xxxxxxxxxxx' --profile test-profile
{
"Status": 202,
"Arn": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:template/template_test_list_tony",
"VersionArn": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:template/template_test_list_tony/version/1",
"TemplateId": "template_test_list_tony",
"CreationStatus": "CREATION_IN_PROGRESS",
"RequestId": "2fe6b6a8-0fc9-4f3f-b16b-81201fe3aeac"
}
You can view the template using the list-templates api call
LT-14739:tony tonym$ aws quicksight list-templates --aws-account-id '686794321847' --profile test-profile
{
"TemplateSummaryList": [
{
"Arn": "arn:aws:quicksight:eu-west-1:686794321847:template/template_test_list_tony",
"TemplateId": "template_test_list_tony",
"LatestVersionNumber": 1,
"CreatedTime": "2021-03-04T10:40:27.655000+00:00",
"LastUpdatedTime": "2021-03-04T10:40:27.655000+00:00"
}
],
"Status": 200,
"RequestId": "8d042b61-35c2-430b-bebe-9addd6cffb8a"
}
Data Source
So we now want to build a data source which is a RDS connection to a test user which we can then build data-sets from, so we use the create-data-source api call for this so we configure 2 files a config file and credentials file.
With the config file listed below we need to set-up all of the parameters to be able to connect to the RDS instance
LT-14739:tony tonym$ cat training-tony-data-source.json
{
"AwsAccountId": "xxxxxxxxxxx",
"DataSourceId": "pg-test-tony-data",
"Name": "pg-test-tony-data",
"SslProperties": {
"DisableSsl": false},
"Type": "POSTGRESQL",
"DataSourceParameters": {
"PostgreSqlParameters": {
"Host": "pg-test-data.crnc1zkn1m7t.eu-west-1.rds.amazonaws.com",
"Port": 5432,
"Database": "test"
}},
"VpcConnectionProperties": {
"VpcConnectionArn": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:vpcConnection/test-non-prod-a"
},
"Permissions": [
{
"Actions": [
"quicksight:UpdateDataSourcePermissions",
"quicksight:DescribeDataSource",
"quicksight:DescribeDataSourcePermissions",
"quicksight:PassDataSource",
"quicksight:UpdateDataSource",
"quicksight:DeleteDataSource"
],
"Principal": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:user/default/test-RoleV1/tony@test.co.uk"
}
]
}
With the credentials file this is the password details to connect to the user you have configured in our case is standard reports
LT-14739:tony tonym$ cat training-tony-password.json
{
"CredentialPair": {
"Username": "test_user",
"Password": "**********"
}
}
We now run the api command to build the data source
LT-14739:tony tonym$ aws quicksight create-data-source --cli-input-json file://training-tony-data-source.json --credentials file://training-tony-password.json --profile test-profile
{
"Status": 202,
"Arn": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:datasource/pg-training-tony-data",
"DataSourceId": "pg-training-tony-data",
"CreationStatus": "CREATION_IN_PROGRESS",
"RequestId": "fa6419d3-3595-4ab8-9b3e-4c65b6d50762"
}
Data Set
Now we have the data source created we can create a data set to associate with the data source this is done by using the create-data-set api call, with the data set config in this example we are mapping to a query from a view and we need to specify all the columns to be outputted by the view and the data source its to be associated with so you get the arn from the data source added above which you will see in the config.
LT-14739:tony tonym$ cat dataset_tony_list_tony.json
{
"AwsAccountId": "xxxxxxxxxxx",
"DataSetId": "tony_list_tony",
"Name": "tony_list_tony",
"PhysicalTableMap": {
"renewalslisttony": {
"CustomSql": {
"Columns": [
{
"Name": "Date",
"Type": "DATETIME"
},
{
"Name": "Reference",
"Type": "STRING"
},
{
"Name": "Branch",
"Type": "STRING"
},
{
"Name": "Status",
"Type": "STRING"
},
{
"Name": "Fee",
"Type": "DECIMAL"
},
{
"Name": "Balance",
"Type": "DECIMAL"
}
],
"DataSourceArn": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:datasource/pg-test-tony-data",
"Name": "test_list_tony",
"SqlQuery": "select * from test_list_demo"
}
}
},
"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:eu-west-1:xxxxxxxxxxx:user/default/test-RoleV1/tony@test.co.uk"
}
]
}
We run the creation through as follows:
LT-14739:tony tonym$ aws quicksight create-data-set --cli-input-json file://dataset_test_list_tony.json --aws-account-id 'xxxxxxxxxxx' --profile test-profile
{
"Status": 201,
"Arn": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:dataset/test_list_tony",
"DataSetId": "test_list_tony",
"RequestId": "de719298-ce3c-4ae8-8a5d-5fcdb36df8f2"
}
LT-14739:tony tonym$
Dashboard Creation
Now we have the data source, data set and template we can create a dashboard from the template this is done by calling the create-dashboard api call, the config file for the creation is as follows:
LT-14739:tony tonym$ cat dashboard-test-list-tony.json
{
"AwsAccountId": "xxxxxxxxxxx",
"DashboardId": "test_list_tony",
"Name": "test_list_tony",
"SourceEntity": {
"SourceTemplate": {
"Arn": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:template/test_list_tony",
"DataSetReferences": [
{
"DataSetPlaceholder": "test_list_demo",
"DataSetArn": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:dataset/test_list_tony"
}
]
}
},
"VersionDescription": "Version-1"
}
We create the dashboard with the following api call:
LT-14739:tony tonym$ aws quicksight create-dashboard --cli-input-json file://dashboard-test-list-tony.json --aws-account-id 'xxxxxxxxxxx' --profile test-profile
{
"Status": 202,
"Arn": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:dashboard/test_list_tony",
"VersionArn": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:dashboard/test_list_tony/version/1",
"DashboardId": "test_list_tony",
"CreationStatus": "CREATION_IN_PROGRESS",
"RequestId": "5dc1c0a3-8d3b-4d4b-8bc0-d7e67a110890"
}
You add permissions to the dashboard by calling the update-dashboard-permissions api, the config file is:
LT-14739:tony tonym$ cat dashboard-test-list-tony-permissons.json
{
"AwsAccountId": "xxxxxxxxxxx",
"DashboardId": "test_list_tony",
"GrantPermissions": [
{
"Principal": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:user/default/test-RoleV1/tony@test.co.uk",
"Actions": [
"quicksight:DescribeDashboard",
"quicksight:ListDashboardVersions",
"quicksight:UpdateDashboardPermissions",
"quicksight:QueryDashboard",
"quicksight:UpdateDashboard",
"quicksight:DeleteDashboard",
"quicksight:DescribeDashboardPermissions",
"quicksight:UpdateDashboardPublishedVersion"
]
}
]
}
To run call the api as follows:
aws quicksight update-dashboard-permissions --cli-input-json file://dashboard-test-list-tony-permissons.json --aws-account-id 'xxxxxxxxxxx' --profile test-profile
{
"Status": 200,
"DashboardArn": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:dashboard/test_list_tony",
"DashboardId": "test_list_tony",
"Permissions": [
{
"Principal": "arn:aws:quicksight:eu-west-1:xxxxxxxxxxx:user/default/test-RoleV1/test@test.co.uk",
"Actions": [
"quicksight:DescribeDashboard",
"quicksight:ListDashboardVersions",
"quicksight:UpdateDashboardPermissions",
"quicksight:QueryDashboard",
"quicksight:UpdateDashboard",
"quicksight:DeleteDashboard",
"quicksight:DescribeDashboardPermissions",
"quicksight:UpdateDashboardPublishedVersion"
]
}
],
"RequestId": "57254756-a529-49b4-b705-06cc0f1fc892"
}
If you connect to quicksight you will be able to now see the dashboard created with your associated data set 🙂
Useful Links
For the Love of Data 