1 Answer
- Newest
- Most votes
- Most comments
0
Thank you for posting your question, you can use the following API calls to create calculated fields:
Step 1: get the parameters required for update_data_set API call using describe_data_set API call
import json
import boto3
client = boto3.client('quicksight')
response = client.describe_data_set(
AwsAccountId='AWS Account ID',
DataSetId='DatasetID'
)
print(response)
Step 2: Get the PhysicalTableMap and LogicalMap for the existing Dataset from the response in step 1
import json
import boto3
client = boto3.client('quicksight')
response = client.update_data_set(
AwsAccountId='Account ID',
DataSetId='DatsetID',
Name='Web and Social Media Analytics',
PhysicalTableMap = {
"String": {
"S3Source": {
"DataSourceArn": "arn:aws:quicksight:us-east-1:<AccountID>:datasource/<DatsetID>",
"UploadSettings": {
"Format": "CSV",
"StartFromRow": 1,
"ContainsHeader": True,
"TextQualifier": "DOUBLE_QUOTE",
"Delimiter": ","
},
"InputColumns": [
{
"Name": "ColumnId-1",
"Type": "STRING"
},
{
"Name": "ColumnId-2",
"Type": "STRING"
},
{
"Name": "ColumnId-3",
"Type": "STRING"
},
{
"Name": "ColumnId-4",
"Type": "STRING"
},
{
"Name": "ColumnId-5",
"Type": "STRING"
},
{
"Name": "ColumnId-6",
"Type": "STRING"
},
{
"Name": "ColumnId-7",
"Type": "STRING"
},
{
"Name": "ColumnId-8",
"Type": "STRING"
},
{
"Name": "ColumnId-9",
"Type": "STRING"
},
{
"Name": "ColumnId-10",
"Type": "STRING"
},
{
"Name": "ColumnId-11",
"Type": "STRING"
},
{
"Name": "ColumnId-12",
"Type": "STRING"
},
{
"Name": "ColumnId-13",
"Type": "STRING"
},
{
"Name": "ColumnId-14",
"Type": "STRING"
},
{
"Name": "ColumnId-15",
"Type": "STRING"
},
{
"Name": "ColumnId-16",
"Type": "STRING"
},
{
"Name": "ColumnId-17",
"Type": "STRING"
},
{
"Name": "ColumnId-18",
"Type": "STRING"
},
{
"Name": "ColumnId-19",
"Type": "STRING"
}
]
}
}
},
LogicalTableMap = {
"String": {
"Alias": "Web and Social Media Analytics",
"DataTransforms": [
{
"RenameColumnOperation": {
"ColumnName": "ColumnId-1",
"NewColumnName": "Date"
}
},
#adding a calculated field, you can use a string for the column identifier
{
"CreateColumnsOperation": {
"Columns": [
{
"ColumnName": "maxVisits2",
"ColumnId": "<columID string identifier string>",
"Expression": "max({Website Visits})"
}
]
}
}
,
{
"CastColumnTypeOperation": {
"ColumnName": "Date",
"NewColumnType": "DATETIME",
"Format": "M/d/yyyy"
}
},
{
"RenameColumnOperation": {
"ColumnName": "ColumnId-2",
"NewColumnName": "New visitors SEO"
}
},
{
"CastColumnTypeOperation": {
"ColumnName": "New visitors SEO",
"NewColumnType": "INTEGER"
}
},
{
"RenameColumnOperation": {
"ColumnName": "ColumnId-3",
"NewColumnName": "New visitors CPC"
}
},
{
"CastColumnTypeOperation": {
"ColumnName": "New visitors CPC",
"NewColumnType": "INTEGER"
}
},
{
"RenameColumnOperation": {
"ColumnName": "ColumnId-4",
"NewColumnName": "New visitors Social Media"
}
},
{
"CastColumnTypeOperation": {
"ColumnName": "New visitors Social Media",
"NewColumnType": "INTEGER"
}
},
{
"RenameColumnOperation": {
"ColumnName": "ColumnId-5",
"NewColumnName": "Return visitors"
}
},
{
"ProjectOperation": {
"ProjectedColumns": [
"Date",
"New visitors SEO",
"New visitors CPC",
"New visitors Social Media",
"Return visitors",
"Twitter mentions",
"Twitter followers adds",
"Twitter followers cumulative",
"Mailing list adds ",
"Mailing list cumulative",
"Website Pageviews",
"Website Visits",
"Website Unique Visits",
"Mobile uniques",
"Tablet uniques",
"Desktop uniques",
"Free sign up",
"Paid conversion",
"Events",
"maxVisits",
"minVisits",
"minmaxdiff",
"percntileAgg",
"maxVisits2"
]
}
}
],
"Source": {
"PhysicalTableId": "<ID>"
}
}
},
ImportMode = "SPICE"
)
print(response)
I have used boto3 in this example but you can use any API from the supported list.
Relevant content
- asked 2 years ago
- Accepted Answerasked 6 months ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated a year ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 6 months ago