Quicksight: join datasets using api

Hi.

yes you can You create a new dataset with the CreateDataSet API. The creation parameter LogicalTableMap allows you to combine and JOIN existing datasets Arn.

An example would be:

{
    "AwsAccountId": "123456789012",
    "DataSetId": "hoge0425dataset1111111",
    "Name": "hoge0425dataset1111111",
    "PhysicalTableMap": {},
    "LogicalTableMap": {
        "hoge0425b": {
            "Alias": "hoge0425b",
            "DataTransforms": [
                {
                    "RenameColumnOperation": {
                        "ColumnName": "key1",
                        "NewColumnName": "key1[hoge0425b]"
                    }
                }
            ],
            "Source": {
                "DataSetArn": "arn:aws:quicksight:ap-northeast-1:123456789012:dataset/599be6e0-c054-4d56-91cb-eb3b3a24a25d"
            }
        },
        "hoge0425a": {
            "Alias": "hoge0425a",
            "Source": {
                "DataSetArn": "arn:aws:quicksight:ap-northeast-1:123456789012:dataset/a1e4803a-5ea6-4191-ac14-7bf4a434e695"
            }
        },
        "hoge0425c": {
            "Alias": "Intermediate Table",
            "DataTransforms": [
                {
                    "ProjectOperation": {
                        "ProjectedColumns": [
                            "key1",
                            "val1",
                            "key1[hoge0425b]",
                            "val2"
                        ]
                    }
                }
            ],
            "Source": {
                "JoinInstruction": {
                    "LeftOperand": "hoge0425a",
                    "RightOperand": "hoge0425b",
                    "Type": "LEFT",
                    "OnClause": "key1 = {key1[hoge0425b]}"
                }
            }
        }
    },
    "ImportMode": "SPICE",
    "FieldFolders": {},
    "Permissions": [
        {
            "Principal": "arn:aws:quicksight:ap-northeast-1:123456789012:user/default/hoge-iwasa.takahito/hoge-iwasa.takahito",
            "Actions": [
                "quicksight:PassDataSet",
                "quicksight:CreateIngestion",
                "quicksight:DeleteRefreshSchedule",
                "quicksight:UpdateRefreshSchedule",
                "quicksight:UpdateDataSet",
                "quicksight:DeleteDataSetRefreshProperties",
                "quicksight:CreateRefreshSchedule",
                "quicksight:DescribeDataSetPermissions",
                "quicksight:ListIngestions",
                "quicksight:UpdateDataSetPermissions",
                "quicksight:DescribeIngestion",
                "quicksight:PutDataSetRefreshProperties",
                "quicksight:DeleteDataSet",
                "quicksight:DescribeDataSet",
                "quicksight:CancelIngestion",
                "quicksight:DescribeRefreshSchedule",
                "quicksight:ListRefreshSchedules",
                "quicksight:DescribeDataSetRefreshProperties"
            ]
        }
    ],
    "DataSetUsageConfiguration": {
        "DisableUseAsDirectQuerySource": false,
        "DisableUseAsImportedSource": false
    }
}

Here, hoge0425a and hoge0425b are existing datasets. hoge0425c is the new combined dataset.

You can also try creating a combined dataset from the console and see how the LogicalTableMap is constructed with the DesribeDataSet API.