Quicksight API

Hello everyone,

I am currently working on creating datasets using API and I am facing an issue. The problem is that the columns of the dataset I am trying to create can change periodically, and therefore I cannot provide fixed column details while using the CustomSQL API.

Specifically, when I try to create the dataset using the SQL Query “SELECT * FROM sample_table” through the CustomSQL API, I am asked to provide the column details. However, due to the changing nature of the columns, I cannot provide this information.

I am seeking advice and suggestions from the community on how to overcome this issue. Any help or guidance would be greatly appreciated.

Thank you.

1 Like

Hi @Sanjay-dev-ds - Welcome to AWS QuickSight community and thanks for posting the question.
For your problem statement, when there is a change a column underline database, you can do the following approach ( Custom Approach).

  1. Create a business glossary which will be generated dynamically for each table, you can write a python code which will take table name as argument and it will generate the table name and data type and you have to map the data type as per QuickSight data types so that it will sync.
  2. Once the business glossary is created, you can create the physical and logical map files.
  3. Using physical and logical map and custom sqls you can always create your data sets via python program.

This is a bit tricky and a good amount of coding is require to achieve this by boto3 QuickSight API, however it is a interesting problem. In my case we created the business glossary manually but remaining steps are automated.

You can refer the link - Quicksight dataset with API table map - #4 by Sanjeeb2022 for the details.

Regards - Sanjeeb

Hi @Sanjeeb2022,

I was getting errors due to columns of customSql is missing.Is there anyways to solve this.

Changed some details below here to hide sensitive data.

botocore.exceptions.ClientError: An error occurred (ValidationException) when calling the CreateDataSet operation: 2 validation errors detected: Value '{sample_table=PhysicalTable(relationalT
able=null, customSql=CustomSql(dataSourceArn=arn:aws:quicksight:eu-west-2:xxxxxxxx:datasource/db, name=sample_db , sqlQuery=*** REDACTED **, columns=null), s3
Source=null)}’ at ‘physicalTableMap’ failed to satisfy constraint: Map keys must satisfy constraint: [Member must have length less than or equal to 64, Member must have length greater than or
equal to 1, Member must satisfy regular expression pattern: [0-9a-zA-Z-]
]; Value null at ‘physicalTableMap.sample_table.member.customSql.columns’ failed to satisfy constraint: Member must no
t be null

image
Here columns mentioned to be not required

Hi @Sanjay-dev-ds : Please ensure your custom sql output column details should match with physical table column list.

Regards - Sanjeeb

Hi @Sanjeeb2022 , Appreciate for your help on this, But as I mentioned earlier I would not able to give the column details because of no of columns can be increased. Previously we have created datasets in UI but due to number of datasets, I have planned to automate this. I hope you understood this scenario, Thank you

Hi @Sanjay-dev-ds - You may not mention the column names in custom sql but in your logical and physical map you need to mention it, that is why you should have a custom code which will extract the columns from db, data types, change the data types w.r.t QuickSight, generate the Physical and logical map file with custom sql and create the data set. Please refer my first post on the same.

As I said, it is bit tricky and a good amount of custom code is require.

Tagging @Max and @David_Wong for their expert advise as well.

Regards - Sanjeeb

1 Like

@Sanjeeb2022 Thank your help on this.

@Sanjay-dev-ds Hi Sanjay, qq- are you creating sql queries in APi and calling the quicksight Dataset to retrive the data?