Error: newColumnsBatch operation is not supported on non-root logical table

Question & Answer
, , ,
1

I got an error Invalid request provided: newColumnsBatch operation is not supported on non-root logical table while deploying Quicksight CDK.

Does anyone know what this mean?

I’m creating a join dataset. Adding the first physical table and logical table works. When adding the second physical table and logical table of the second dataset and the joined dataset, it caused the error.

qs_athena_dataset_joborchestration_raw = quicksight.CfnDataSet(
            self,
            "AthenaDatasetJoborchestrationRaw",
            import_mode="SPICE",
            name=f"{stack_prefix}Joborchestration",
            aws_account_id=account_id,
            data_set_id=f"{stack_prefix}JoborchestrationData",
            data_set_usage_configuration=quicksight.CfnDataSet.DataSetUsageConfigurationProperty(
                disable_use_as_direct_query_source=False,
                disable_use_as_imported_source=False,
            ),
            physical_table_map={
                "edljoblog-table": qs_athena_dataset_edljoblog_physical_table,
                "joborchestration-table": qs_athena_dataset_joborchestration_physical_table,
            },
            logical_table_map={
                "edljoblog-table": qs_athena_dataset_edljoblog_logical_table,
                "joborchestration-table": qs_athena_dataset_joborchestration_logical_table,
                "edljoblog-joborchestration-table": quicksight.CfnDataSet.LogicalTableProperty(
                    alias="edljoblog-joborchestration",
                    source=quicksight.CfnDataSet.LogicalTableSourceProperty(
                        join_instruction=quicksight.CfnDataSet.JoinInstructionProperty(
                            left_operand="joborchestration-table",
                            right_operand="edljoblog-table",
                            on_clause="dataSource = {dataSource[devEDLJobLog]} AND table = tableName",
                            type="LEFT",
                        ),
                    ),
                    data_transforms=[
                        quicksight.CfnDataSet.TransformOperationProperty(
                            project_operation=quicksight.CfnDataSet.ProjectOperationProperty(
                                projected_columns=[
                                    "dataSource",
                                    "date",
                                    "dateTime",
                                    "file_location"               
                                ]
                            )
                        ),
                    ],
                ),
            },
            permissions=qs_dataset_permissions,
        )
2

@pattdf The error message “Invalid request provided: newColumnsBatch operation is not supported on non-root logical table” typically occurs in Amazon QuickSight when attempting to add a new column to a logical table that is not the root table of a dataset. In your case, it seems like the error may be caused by the data transforms specified in the “edljoblog-joborchestration-table” logical table, which includes a project operation that specifies a subset of columns to be projected.

One potential solution to this issue is to modify your data transform to include the additional columns that you need. You could try adding the missing columns to the list of projected_columns in the project operation of the data transform.

Alternatively, you could try creating a separate logical table that includes the additional columns you need, and join it with the “edljoblog-joborchestration” table to create a new logical table that contains all the required columns. This would require updating the join_instruction property to include the new logical table as a source, and may require additional data transforms to ensure that the data is formatted correctly.

2 Likes