3

When trying to automate dataset creation in Quicksight through importing a CSV from an S3 bucket, I get an error when trying to create a data set by calling the createDataSet() method from the Java API:

com.amazonaws.services.quicksight.model.InvalidParameterValueException: Input column Year in physical table PrimaryTable has invalid type. Allowed types for S3 physical table are [String] (Service: AmazonQuickSight; Status Code: 400; Error Code: InvalidParameterValueException; Request ID: 84d3da22-4e4e-45e0-8bbf-9d01975206b0; Proxy: null) The relevant code is as follows, with the sixth inputColumns.add line causing the error:

inputColumns.add(new InputColumn().withName("Column 1").withType(InputColumnDataType.STRING));
inputColumns.add(new InputColumn().withName("Column 2").withType(InputColumnDataType.STRING));
inputColumns.add(new InputColumn().withName("Column 3").withType(InputColumnDataType.STRING));
inputColumns.add(new InputColumn().withName("Column 4").withType(InputColumnDataType.STRING));
inputColumns.add(new InputColumn().withName("Column 5").withType(InputColumnDataType.STRING));
inputColumns.add(new InputColumn().withName("Year").withType(InputColumnDataType.INTEGER)); //*hits the error on this line*
... //more columns with types STRING, INTEGER, and DECIMAL

s3source.setDataSourceArn(QS_BASE_ARN + "datasource/" + sourceName);
s3source.setInputColumns(inputColumns);
uploadSettings.setFormat(FileFormat.CSV.name());
uploadSettings.setContainsHeader(true);
uploadSettings.setDelimiter(",");
s3source.setUploadSettings(uploadSettings);
physicalTable.setS3Source(s3source);
Map<String, PhysicalTable> physicalTableMap = new HashMap<String, PhysicalTable>();
physicalTableMap.put("PrimaryTable", physicalTable);
newDataSet.withAwsAccountId(ACCOUNT_ID).withDataSetId(dataSetId).withName(dataSetName).withPhysicalTableMap(physicalTableMap).withImportMode("SPICE"); //These are all the required parameters for the API request
permissions.add(new ResourcePermission().withPrincipal(QS_BASE_ARN + "user/default/" + username).withActions("quicksight:UpdateDataSetPermissions","quicksight:DescribeDataSet","quicksight:DescribeDataSetPermissions","quicksight:PassDataSet", "quicksight:DescribeIngestion", "quicksight:ListIngestions", "quicksight:UpdateDataSet", "quicksight:DeleteDataSet","quicksight:CreateIngestion","quicksight:CancelIngestion"));
newDataSet.setPermissions(permissions);
try {
   return getClient().createDataSet(newDataSet); //Creates SPICE dataset
} catch (SdkClientException e) {
   throw e;
}

With the getClient() method being:

private static AmazonQuickSight getClient() {
   final AWSCredentialsProvider credsProvider = new AWSCredentialsProvider() {
   @Override
   public AWSCredentials getCredentials() {
      // provide actual IAM access key and secret key here
      return new BasicAWSCredentials(ACCESS_KEY, SECRET_KEY);
   }
   @Override
   public void refresh() {}
   };
   return AmazonQuickSightClientBuilder
      .standard()
      .withRegion(Regions.US_EAST_1.getName())
      .withCredentials(credsProvider)
      .build();
}

The API documentation says that STRING | INTEGER | DECIMAL | DATETIME | BIT | BOOLEAN | JSON are all accepted, however the error message and testing says that only STRING is accepted as a type (API documentation: https://docs.aws.amazon.com/quicksight/latest/APIReference/API_InputColumn.html).

Is this error something caused by the API not accepting the data types it says it does or could it be caused by my setup?

Nick
  • 31
  • 1

1 Answers1

0

I know I'm 18 months late, but I struggled with this as well and finally arrived at a solution. The Physical Table Mapping simply maps your columns for reading into QuickSight, but (unless your S3 source file is JSON), you'll need to create a LogicalTableMap to cast any columns to the desired data type. I used Java SDK V2, but V1 is very similar.

LogicalTableSource logicalTableSource = LogicalTableSource.builder().physicalTableId("PrimaryTable").build();

ArrayList<TransformOperation> dataTransforms = dataTransforms();

LogicalTable logicalTable = LogicalTable.builder().alias("LogicalTableAlias").dataTransforms(dataTransforms)
                .source(logicalTableSource).build();
        
Map<String, LogicalTable> logicalTableMap = new HashMap<String, LogicalTable>();
        logicalTableMap.put("LogicalTableMap", logicalTable);
    CreateDataSetRequest createDataSetRequest = CreateDataSetRequest.builder()
            .awsAccountId(AwsAccountID)
            .dataSetId(dataSetId)
            .name(name)
            .physicalTableMap(physicalTableMap)
            .logicalTableMap(logicalTableMap)
            .permissions(permissions)
            .importMode(DataSetImportMode.SPICE)
            .build();

    // Create data set
    getClient().createDataSet(createDataSetRequest);

With the dataTransforms() method being:

    private static ArrayList<TransformOperation> dataTransforms() {
    ArrayList<TransformOperation> dataTransforms = new ArrayList<TransformOperation>();

    // Cast "Year" column as INTEGER
    CastColumnTypeOperation castYear = CastColumnTypeOperation.builder()
            .columnName("Year").newColumnType(ColumnDataType.INTEGER).build();
    TransformOperation yearTransform = TransformOperation.builder()
            .castColumnTypeOperation(castYear).build();
    dataTransforms.add(yearTransform);
return dataTransforms;
}
Chiara
  • 1
  • 1