Hi
We are trying to use Boto3 SDK for QuickSight - we got the PhysicalTableMap part of the input. But we are not able to give LogicalTableMap(we are only able to give DataTransforms for one table, we need to give for multiple tables and ‘Source’ part of LogicalTableMap for joints, if some one can share a sample json of LogicalTableMap={} with data for multiple parts especially RenameColumnOperation and Sources. Any help will be highly appreciated.
Hi,
Go through this document in complete detail 
Regards,
Naveed Ain
I have already checked the document, it doesn’t specify how to give details for multiple tables.
1 Like
@Sumit_Jaiswal ,
If you already have a dataset with multiple tables in QuickSight, use the describe dataset ( describe-data-set — AWS CLI 2.15.8 Command Reference ) to get an understanding of dataset itself .
You can then replicate the same as part of your code when creating a new dataset.
Kind regards,
Koushik
That I am aware, we have moved using boto3 from our DEV to UAT and then PROD.
I am trying to create, one with taking details from some other source for e.g excle or CSV and create required JSON, we took data from existing Dataset through Descibe and tried - for PhysicalTableMap we are able to do it, for LogicalTableMap it not taking even though its same format. Do you have any LogicalTableMap which is not take from describe dataset but created manually with multiple joints and multile table rename. Please share, if its handy , Thanks
The describe dataset provides you the details of how it works in QuickSight. That would be the same syntax what you would have to follow when you are manually trying to build your own.
Example1 RenameColumnOperation
Example 2
Based on the below, if the datasource is a file which has been uploaded, this operation is not supported.
I am trying to create, one with taking details from some other source for e.g excle or CSV
This is where exactly I ran into problem, my LogicalTableMap is exactly like one in PIC(I am using boto3 and not AWS CLI).
error - botocore.errorfactory.InvalidParameterValueException: An error occurred (InvalidParameterValueException) when calling the CreateDataSet operation: LogicalTableMap must have a single root
Even the attached PIC it has multi root. But when we download from describe dataset and build another one with create dataset it works, thats baffling
Below is the dict/json for LogicalTableMap - I am getting this error - botocore.errorfactory.InvalidParameterValueException: An error occurred (InvalidParameterValueException) when calling the CreateDataSet operation: LogicalTableMap must have a single root.
When used run for one alias its works but it multiple get error. Can anyone let me know what needs to be fixed.
{“Ei353j8QxuUdxWYyDwkQWOATIGKXzl8ySxkc”: {“Alias”: “fact_p2p_cycle_time_rt”, “DataTransforms”: [{“RenameColumnOperation”: {“ColumnName”: “org_id”, “NewColumnName”: “org_id”}}, {“RenameColumnOperation”: {“ColumnName”: “check_id”, “NewColumnName”: “check_id”}}, {“RenameColumnOperation”: {“ColumnName”: “invoice_id”, “NewColumnName”: “invoice_id”}}, {“RenameColumnOperation”: {“ColumnName”: “po_header_id”, “NewColumnName”: “po_header_id”}}, {“RenameColumnOperation”: {“ColumnName”: “vendor_id”, “NewColumnName”: “vendor_id”}}, {“RenameColumnOperation”: {“ColumnName”: “check_number”, “NewColumnName”: “Check Number”}}, {“RenameColumnOperation”: {“ColumnName”: “check_date”, “NewColumnName”: “Check Date”}}, {“RenameColumnOperation”: {“ColumnName”: “check_amount”, “NewColumnName”: “Check Amount”}}, {“RenameColumnOperation”: {“ColumnName”: “period_num”, “NewColumnName”: “Period Number”}}, {“RenameColumnOperation”: {“ColumnName”: “period_year”, “NewColumnName”: “Period Year”}}, {“RenameColumnOperation”: {“ColumnName”: “period_name”, “NewColumnName”: “Period Name”}}, {“RenameColumnOperation”: {“ColumnName”: “payment_due_date”, “NewColumnName”: “Payment Due Date”}}, {“RenameColumnOperation”: {“ColumnName”: “invoice_type_lookup_code”, “NewColumnName”: “invoice_type_lookup_code”}}, {“RenameColumnOperation”: {“ColumnName”: “invoice_num”, “NewColumnName”: “Invoice Number”}}, {“RenameColumnOperation”: {“ColumnName”: “invoice_amount”, “NewColumnName”: “Invoice Amount”}}, {“RenameColumnOperation”: {“ColumnName”: “invoice_date”, “NewColumnName”: “Invoice Date”}}, {“RenameColumnOperation”: {“ColumnName”: “voucher_date”, “NewColumnName”: “voucher_date”}}, {“RenameColumnOperation”: {“ColumnName”: “vendor_name”, “NewColumnName”: “Vendor Name”}}, {“RenameColumnOperation”: {“ColumnName”: “po_type”, “NewColumnName”: “Po Type”}}, {“RenameColumnOperation”: {“ColumnName”: “po_date”, “NewColumnName”: “Po Date”}}, {“RenameColumnOperation”: {“ColumnName”: “po_number”, “NewColumnName”: “Po Number”}}, {“RenameColumnOperation”: {“ColumnName”: “po_release_number”, “NewColumnName”: “Po Release Number”}}, {“RenameColumnOperation”: {“ColumnName”: “receipt_date”, “NewColumnName”: “Receipt Date”}}, {“RenameColumnOperation”: {“ColumnName”: “receipt_number”, “NewColumnName”: “Receipt Number”}}, {“RenameColumnOperation”: {“ColumnName”: “po_creation_date”, “NewColumnName”: “Po Creation Date”}}, {“RenameColumnOperation”: {“ColumnName”: “inv_podt”, “NewColumnName”: “inv_podt”}}, {“RenameColumnOperation”: {“ColumnName”: “inv_to_rept”, “NewColumnName”: “inv_to_rept”}}, {“RenameColumnOperation”: {“ColumnName”: “inv_to_voc”, “NewColumnName”: “inv_to_voc”}}, {“RenameColumnOperation”: {“ColumnName”: “rec_to_chk”, “NewColumnName”: “rec_to_chk”}}, {“RenameColumnOperation”: {“ColumnName”: “inv_to_chk”, “NewColumnName”: “inv_to_chk”}}, {“RenameColumnOperation”: {“ColumnName”: “chk_to_due”, “NewColumnName”: “chk_to_due”}}, {“RenameColumnOperation”: {“ColumnName”: “rcpt_30d”, “NewColumnName”: “rcpt_30d”}}, {“RenameColumnOperation”: {“ColumnName”: “payments_ontime”, “NewColumnName”: “payments_ontime”}}],“Source”:{“PhysicalTableId”: “Ei353j8QxuUdxWYyDwkQWOATIGKXzl8ySxkc”}},“JReuzGZT8nB4AxOqyxbKGS9ISn0dpJW4ZSAQ”: {“Alias”: “dim_ap_notes”, “DataTransforms”: [{“RenameColumnOperation”: {“ColumnName”: “note_id”, “NewColumnName”: “Note Id”}}, {“RenameColumnOperation”: {“ColumnName”: “report_header_id”, “NewColumnName”: “Report Header Id”}}, {“RenameColumnOperation”: {“ColumnName”: “org_id”, “NewColumnName”: “Org Id (Dim Ap Notes)”}}, {“RenameColumnOperation”: {“ColumnName”: “invoice_num”, “NewColumnName”: “Invoice Num (Dim Ap Notes)”}}, {“RenameColumnOperation”: {“ColumnName”: “entered_date”, “NewColumnName”: “Entered Date”}}, {“RenameColumnOperation”: {“ColumnName”: “notes_detail”, “NewColumnName”: “Notes Detail”}}, {“RenameColumnOperation”: {“ColumnName”: “is_deleted_flg”, “NewColumnName”: “is_deleted_flg”}}, {“RenameColumnOperation”: {“ColumnName”: “source_app_id”, “NewColumnName”: “Source App Id”}}, {“RenameColumnOperation”: {“ColumnName”: “dw_load_id”, “NewColumnName”: “Dw Load Id”}}, {“RenameColumnOperation”: {“ColumnName”: “dw_insert_date”, “NewColumnName”: “Dw Insert Date”}}, {“RenameColumnOperation”: {“ColumnName”: “dw_update_date”, “NewColumnName”: “Dw Update Date”}}],“Source”:{“PhysicalTableId”: “JReuzGZT8nB4AxOqyxbKGS9ISn0dpJW4ZSAQ”}},“jfv66Uxr0Z4jOAflaHEMH2FKcnNwvrii3Ehg”: {“Alias”: “dim_ap_suppliers”, “DataTransforms”: [{“RenameColumnOperation”: {“ColumnName”: “vendor_id”, “NewColumnName”: “Vendor Id (Dim Ap Suppliers)”}}, {“RenameColumnOperation”: {“ColumnName”: “vendor_name”, “NewColumnName”: “Vendor Name (Dim Ap Suppliers)”}}, {“RenameColumnOperation”: {“ColumnName”: “vendor_name_alt”, “NewColumnName”: “Vendor Name Alt”}}, {“RenameColumnOperation”: {“ColumnName”: “vendor_type_lookup_code”, “NewColumnName”: “Vendor Type Lookup Code”}}, {“RenameColumnOperation”: {“ColumnName”: “enabled_flag”, “NewColumnName”: “Enabled Flag”}}, {“RenameColumnOperation”: {“ColumnName”: “employee_id”, “NewColumnName”: “Employee Id”}}, {“RenameColumnOperation”: {“ColumnName”: “terms_id”, “NewColumnName”: “Terms Id”}}, {“RenameColumnOperation”: {“ColumnName”: “invoice_currency_code”, “NewColumnName”: “Invoice Currency Code”}}, {“RenameColumnOperation”: {“ColumnName”: “payment_currency_code”, “NewColumnName”: “Payment Currency Code”}}, {“RenameColumnOperation”: {“ColumnName”: “start_date_active”, “NewColumnName”: “Start Date Active”}}, {“RenameColumnOperation”: {“ColumnName”: “end_date_active”, “NewColumnName”: “End Date Active”}}, {“RenameColumnOperation”: {“ColumnName”: “party_id”, “NewColumnName”: “Party Id”}}, {“RenameColumnOperation”: {“ColumnName”: “vendor_category”, “NewColumnName”: “Vendor Category”}}, {“RenameColumnOperation”: {“ColumnName”: “created_by”, “NewColumnName”: “created_by”}}, {“RenameColumnOperation”: {“ColumnName”: “last_updated_by”, “NewColumnName”: “last_updated_by”}}, {“RenameColumnOperation”: {“ColumnName”: “creation_date”, “NewColumnName”: “creation_date”}}, {“RenameColumnOperation”: {“ColumnName”: “last_update_date”, “NewColumnName”: “last_update_date”}}, {“RenameColumnOperation”: {“ColumnName”: “num_1099”, “NewColumnName”: “num_1099”}}, {“RenameColumnOperation”: {“ColumnName”: “type_1099”, “NewColumnName”: “type_1099”}}, {“RenameColumnOperation”: {“ColumnName”: “receipt_required_flag”, “NewColumnName”: “receipt_required_flag”}}, {“RenameColumnOperation”: {“ColumnName”: “inspection_required_flag”, “NewColumnName”: “inspection_required_flag”}}, {“RenameColumnOperation”: {“ColumnName”: “is_deleted_flg”, “NewColumnName”: “is_deleted_flg (dim_ap_suppliers)”}}, {“RenameColumnOperation”: {“ColumnName”: “source_app_id”, “NewColumnName”: “Source App Id (Dim Ap Suppliers)”}}, {“RenameColumnOperation”: {“ColumnName”: “dw_load_id”, “NewColumnName”: “Dw Load Id (Dim Ap Suppliers)”}}, {“RenameColumnOperation”: {“ColumnName”: “dw_insert_date”, “NewColumnName”: “Dw Insert Date (Dim Ap Suppliers)”}}, {“RenameColumnOperation”: {“ColumnName”: “dw_update_date”, “NewColumnName”: “Dw Update Date (Dim Ap Suppliers)”}}],“Source”:{“PhysicalTableId”: “jfv66Uxr0Z4jOAflaHEMH2FKcnNwvrii3Ehg”}},“t3QmJfER5ora7v2chuxmIr5lCpNfvU651vJH”: {“Alias”: “dim_op_unit”, “DataTransforms”: [{“RenameColumnOperation”: {“ColumnName”: “business_group_id”, “NewColumnName”: “Business Group Id”}}, {“RenameColumnOperation”: {“ColumnName”: “organization_id”, “NewColumnName”: “Organization Id”}}, {“RenameColumnOperation”: {“ColumnName”: “op_unit_name”, “NewColumnName”: “Operating Unit”}}, {“RenameColumnOperation”: {“ColumnName”: “set_of_books_id”, “NewColumnName”: “Set Of Books Id”}}, {“RenameColumnOperation”: {“ColumnName”: “ledger_name”, “NewColumnName”: “Ledger Name”}}, {“RenameColumnOperation”: {“ColumnName”: “ledger_short_name”, “NewColumnName”: “Ledger Short Name”}}, {“RenameColumnOperation”: {“ColumnName”: “ledger_currency_code”, “NewColumnName”: “Ledger Currency Code”}}, {“RenameColumnOperation”: {“ColumnName”: “legal_entity_name”, “NewColumnName”: “Legal Entity Name”}}, {“RenameColumnOperation”: {“ColumnName”: “attribute1”, “NewColumnName”: “Attribute1”}}, {“RenameColumnOperation”: {“ColumnName”: “attribute2”, “NewColumnName”: “Attribute2”}}, {“RenameColumnOperation”: {“ColumnName”: “attribute3”, “NewColumnName”: “Attribute3”}}, {“RenameColumnOperation”: {“ColumnName”: “attribute4”, “NewColumnName”: “Attribute4”}}, {“RenameColumnOperation”: {“ColumnName”: “attribute5”, “NewColumnName”: “Attribute5”}}, {“RenameColumnOperation”: {“ColumnName”: “is_deleted_flg”, “NewColumnName”: “is_deleted_flg (dim_op_unit)”}}, {“RenameColumnOperation”: {“ColumnName”: “source_app_id”, “NewColumnName”: “Source App Id (Dim Op Unit)”}}, {“RenameColumnOperation”: {“ColumnName”: “dw_load_id”, “NewColumnName”: “Dw Load Id (Dim Op Unit)”}}, {“RenameColumnOperation”: {“ColumnName”: “dw_insert_date”, “NewColumnName”: “Dw Insert Date (Dim Op Unit)”}}, {“RenameColumnOperation”: {“ColumnName”: “dw_update_date”, “NewColumnName”: “Dw Update Date (Dim Op Unit)”}}],“Source”:{“PhysicalTableId”: “t3QmJfER5ora7v2chuxmIr5lCpNfvU651vJH”}}}
Hello @Sumit_Jaiswal, my apologies for the delayed response. Is this an issue you are still facing? My first concern is are you trying to join a csv dataset with a dataset from your database? I’m not certain that is possible and if it is, if it would function correctly. I know some documentation was linked above, but I will link another page of boto3 sdk documentation here that may be helpful. Make sure you are following that format correctly and ensure that if you are joining datasets, you are providing that information within this section:
'Source': {
'JoinInstruction': {
'LeftOperand': 'string',
'RightOperand': 'string',
'LeftJoinKeyProperties': {
'UniqueKey': True|False
},
'RightJoinKeyProperties': {
'UniqueKey': True|False
},
'Type': 'INNER'|'OUTER'|'LEFT'|'RIGHT',
'OnClause': 'string'
},
'PhysicalTableId': 'string',
'DataSetArn': 'string'
}
You may also want to consider creating each dataset individually and then running a join between them once each has imported properly. Let me know if you have any further questions on this topic.
If you still cannot resolve this after working through these docs, I would recommend filing a case with AWS Support where we can dive into the details to help you further. Here are the steps to open a support case. I hope this helps!
I’ve just had this same problem, and the same very unhelpful error message “LogicalTableMap must have a single root”
The problem turns out to be that the “LogicalTableMap” isn’t just a list/map of datasets. It’s a list/map of datasets that need to create a single chain … a chain with a single-root.
To overcome this the format of the payload needs to resemble …
- physical-table-map:
- physical-table
- logical-table-map:
- dataset-1
- join-dataset-1:
- left: physical-table
- right: dataset-1
- dataset-2
- join-dataset-2:
- left: join-dataset-1
- right: dataset-2
Only once you master the single-chain of logical-tables will you avoid the rather unhelpful “single root” error message.
Hope this helps.