Creating dataSet with API

Question & Answer
1

Hi! I’m creating the dataset using the API.
I have this error:

Invalid ColumnTag added for column with name Longitude

This is my code:

 .createDataSet({
        AwsAccountId: constants.AWS_DEV_ACCOUNT,
        DataSetId: dataSourceID,
        Name: `${company}_findings`,
        ImportMode: 'SPICE',
        PhysicalTableMap: {
          s3: {
            S3Source: {
              DataSourceArn: dataSource.Arn,
              InputColumns: dataSetColumns,
            },
          },
        },
        LogicalTableMap: {
          s3: {
            Alias: `${company}_findings`,
            Source: { PhysicalTableId: 's3' },
            DataTransforms: [
              {
                CastColumnTypeOperation: {
                  ColumnName: 'Width (mm)',
                  NewColumnType: 'INTEGER',
                },
              },
              {
                CastColumnTypeOperation: {
                  ColumnName: 'Length (mm)',
                  NewColumnType: 'INTEGER',
                },
              },
              {
                CastColumnTypeOperation: {
                  ColumnName: 'Longitude',
                  NewColumnType: 'DECIMAL',
                },
              },
              {
                CastColumnTypeOperation: {
                  ColumnName: 'Latitude',
                  NewColumnType: 'DECIMAL',
                },
              },
              {
                TagColumnOperation: {
                  ColumnName: 'Longitude',
                  Tags: [
                    {
                      ColumnDescription: { Text: 'map' },
                      ColumnGeographicRole: 'LONGITUDE',
                    },
                  ],
                },
              },
              {
                TagColumnOperation: {
                  ColumnName: 'Latitude',
                  Tags: [
                    {
                      ColumnDescription: { Text: 'mapa' },
                      ColumnGeographicRole: 'LATITUDE',
                    },
                  ],
                },
              },
            ],
          },
        },
      })

I don’t understand what is happening here.

Thank you for your time.

2

Hi @Meli - Since Longitude is also a data type in QuickSight, is it possible to change the column name and try again to create the data set?

Regards -Sanjeeb

1 Like
3

I change the name and I have the same error.

4

Hi @Meli - Can you share the error message. In parallel, it will be good if you can raise a ticket to AWS Customer support team to help as they can see the issue and guide you. To submit the ticket, please follow the link - Creating support cases and case management - AWS Support

Also if you can give sample data, source so that we can give a try from our side as well.

Regards -Sanjeeb

1 Like
5

Data that I put on the JSON file on s3

      [
        {
          Country: 'UK',
          Client: 'temp',
          Date: '07/12/2022',
          'Lat geo': 51.495114204314426,
          'Long geo': -2.3681341901435826,
          Altitude: 189.6082916259766,
      ],
    ];

const dataSetColumns = [
      { Name: 'Country', Type: 'STRING' },
     { Name: 'Date', Type: 'STRING' },
      { Name: 'Lat geo', Type: 'STRING' },
      { Name: 'Long geo', Type: 'STRING' },
      { Name: 'Altitude', Type: 'STRING' },
    ];

I create the dataSource with the data inside and s3 object JSON.

const dataSource = await quicksight
      .createDataSource({
        AwsAccountId:AWS_DEV_ACCOUNT,
        DataSourceId: dataSourceID,
        Type: 'S3',
        Name: `${company}_find`,
        DataSourceParameters: {
          S3Parameters: {
            ManifestFileLocation: {
              Bucket: `${S3_BUCKET_PREFIX}${company}`,
              Key: 'dashboard/manifest.json',
            },
          },
        },
      })
      .promise();

The dataSet creation

  const dataSet = await quicksight
      .createDataSet({
        AwsAccountId:AWS_DEV_ACCOUNT,
        DataSetId: dataSourceID,
        Name: `${company}_find`,
        ImportMode: 'SPICE',
        PhysicalTableMap: {
          s3: {
            S3Source: {
              DataSourceArn: dataSource.Arn,
              InputColumns: dataSetColumns,
            },
          },
        },
        LogicalTableMap: {
          s3: {
            Alias: `${company}_findings`,
            Source: { PhysicalTableId: 's3' },
            DataTransforms: [
         
              {
                CastColumnTypeOperation: {
                  ColumnName: 'Long geo',
                  NewColumnType: 'DECIMAL',
                },
              },
              {
                CastColumnTypeOperation: {
                  ColumnName: 'Lat geo',
                  NewColumnType: 'DECIMAL',
                },
              },
              {
                TagColumnOperation: {
                  ColumnName: 'Long geo',
                  Tags: [
                    {
                      ColumnDescription: { Text: 'map' },
                      ColumnGeographicRole: 'LONGITUDE',
                    },
                  ],
                },
              },
              {
                TagColumnOperation: {
                  ColumnName: 'Lat geo',
                  Tags: [
                    {
                      ColumnDescription: { Text: 'map' },
                      ColumnGeographicRole: 'LATITUDE',
                    },
                  ],
                },
              },
            ],
          },
        },
      })
      .promise();

This is the error’s message

Error: Invalid ColumnTag added for column with name Long geo

6

Hi @Meli - Is it possible to create the data set from UI and then describe the data set to validate the logical table map definition. I am trying to replicate it at my end but my account do not have S3 access. In parallel, can you raise a ticket to AWS Customer support as well so that they can quickly check the issue by screen share. To raise the request, please follow the link - Creating support cases and case management - AWS Support

Hi @Max - Any advise on this. The scripts looks ok to me in high level, any quick check on this?

Regards - Sanjeeb

7

On the UI, this is working

8

Hi @Meli - Thank you. Can you list the data sets and get the arn of this data set and then describe the data set to understand the logical and physical map file descriptions.

Regards - Sanjeeb

9

I can describe it…This is the error:

InvalidParameterValueException: The data set type is not supported through API yet

1 Like
10

Hi @Meli - Can you please raise a ticket to AWS Customer support team so that they can analyze and provide the root cause of the problem.

Regards - Sanjeeb

11

Sorry, I confused the dataSetId.

This is the result.:

{
    "Status": 200,
    "DataSet": {
        "Arn": "arn:aws:quicksight####:dataset/f14e0f65-9b54-4bfd-90d1-74e7dc3da4cc",
        "DataSetId": "f14e0f65-9b54-4bfd-90d1-74e7dc3da4",
        "Name": "test-eh",
        "CreatedTime": "2023-05-30T09:19:52.185Z",
        "LastUpdatedTime": "2023-06-14T11:10:27.511Z",
        "PhysicalTableMap": {
            "a36b2904-50a0-40ba-a7c7-0fb24c059192": {
                "S3Source": {
                    "DataSourceArn": "arn:aws:quicksight:#####:datasource/ab9926fc-d2c4-4f9a-b1f8-347d724fc073",
                    "UploadSettings": {
                        "Format": "JSON",
                        "StartFromRow": 1,
                        "ContainsHeader": true,
                        "TextQualifier": "DOUBLE_QUOTE",
                        "Delimiter": null
                    },
                    "InputColumns": [
                        {
                            "Name": "Country",
                            "Type": "STRING"
                        },
                 
                        {
                            "Name": "Date",
                            "Type": "STRING"
                        },
                  
                        {
                            "Name": "Latitude",
                            "Type": "STRING"
                        },
                        {
                            "Name": "Longitude",
                            "Type": "STRING"
                        },
                   
                    ]
                }
            }
        },
        "LogicalTableMap": {
            "425f6fa5-192e-4f63-9627-094ce4c7b87f": {
                "Alias": "test-eh",
                "DataTransforms": [
                    {
                        "CastColumnTypeOperation": {
                            "ColumnName": "Date",
                            "NewColumnType": "DATETIME",
                            "Format": "dd/MM/yyyy"
                        }
                    },
            
                    {
                        "CastColumnTypeOperation": {
                            "ColumnName": "Latitude",
                            "NewColumnType": "DECIMAL",
                            "Format": null
                        }
                    },
                    {
                        "CastColumnTypeOperation": {
                            "ColumnName": "Longitude",
                            "NewColumnType": "DECIMAL",
                            "Format": null
                        }
                    },
                   
                 
                    {
                        "TagColumnOperation": {
                            "ColumnName": "Country",
                            "Tags": [
                                {
                                    "ColumnGeographicRole": "COUNTRY"
                                }
                            ]
                        }
                    },
                    {
                        "TagColumnOperation": {
                            "ColumnName": "Latitude",
                            "Tags": [
                                {
                                    "ColumnGeographicRole": "LATITUDE"
                                }
                            ]
                        }
                    },
                    {
                        "TagColumnOperation": {
                            "ColumnName": "Longitude",
                            "Tags": [
                                {
                                    "ColumnGeographicRole": "LONGITUDE"
                                }
                            ]
                        }
                    },
                    {
                        "ProjectOperation": {
                            "ProjectedColumns": [
                                "Country",
                                "Latitude",
                                "Longitude",
                                "Altitude",
                                "Date"
                            
                            ]
                        }
                    }
                ],
                "Source": {
                    "PhysicalTableId": "a36b2904-50a0-40ba-a7c7-0fb24c059192",
                    "DataSetArn": null
                }
            }
        },
        "OutputColumns": [
            {
                "Name": "Country",
                "Description": null,
                "Type": "STRING"
            },
        
            {
                "Name": "Date",
                "Description": null,
                "Type": "DATETIME"
            },
       
     
            {
                "Name": "Latitude",
                "Description": null,
                "Type": "DECIMAL"
            },
            {
                "Name": "Longitude",
                "Description": null,
                "Type": "DECIMAL"
            },
            {
                "Name": "Altitude",
                "Description": null,
                "Type": "DECIMAL"
            },
   
        ],
        "ImportMode": "SPICE",
        "ConsumedSpiceCapacityInBytes": 87619,
        "FieldFolders": {},
        "DataSetUsageConfiguration": {
            "DisableUseAsDirectQuerySource": false,
            "DisableUseAsImportedSource": false
        }
    },
    "RequestId": "7c487877-95f7-427c-91ee-ec20533cd5d5"
}
12

Hi @Meli - Thanks. Can you compare the physical and logical map section to your code and see any differences. Can you update as per this definition and see whether you are able to create the data set or not.

Regards - Sanjeeb

13

Same error… I will write the ticket

1 Like
14

Thank you @Meli . Please keep us update if you will get the resolution from AWS.

Have a great week ahead.

Regards -Sanjeeb

15

@Meli I believe {Name: ‘Long geo’, Type: ‘STRING’ } is your problem…
it must be decimal…

16

I’m changing the type on the LogicalTableMap CastColumnTypeOperation

1 Like
17

The issue was that I needed to add the permissions every time I made the request for every API. Example:

.createDashboard({
AwsAccountId: AWS_DEV_ACCOUNT,
DashboardId: uuid.v4(),
Name: ${company}_dashboard,
SourceEntity: {
SourceTemplate: {
Arn: arn:aws:quicksight:${AWS_ZONE}:${AWS_DEV_ACCOUNT}:template/${templateID},
DataSetReferences: [
{
DataSetArn: dataSetArn,
DataSetPlaceholder: placeholder,
},
],
},
},
Permissions: [
{
Principal: userArn,
Actions: [
‘quicksight:UpdateDashboardPermissions’,
‘quicksight:DescribeDashboard’,
‘quicksight:DescribeDashboardPermissions’,
‘quicksight:UpdateDashboardPublishedVersion’,
‘quicksight:UpdateDashboard’,
‘quicksight:DeleteDashboard’,
‘quicksight:ListDashboardVersions’,
‘quicksight:QueryDashboard’,
];,
},
],
})
.promise();

1 Like
18

Thanks @Meli for share the details… small permission issue lead a big problem. Anyway glad to hear the issue is resolved.

Regards - Sanjeeb

1 Like