How to cast column to DATE type with boto3 create_dataset request from s3 datasource

Question & Answer
, ,
1

I created several data sources from s3 with manifest files, then I am trying to create dataset with API request boto3 create_data_set and faced with some issues

  1. with s3 source aws allows to specify in InputFields only ‘STRING’ but in doc they show more
    ‘InputColumns’: [
    {
    ‘Name’: ‘string’,
    ‘Type’: ‘STRING’|‘INTEGER’|‘DECIMAL’|‘DATETIME’|‘BIT’|‘BOOLEAN’|‘JSON’
    },
    Because if I specify INTEGER or DATETIME, throws an error, and says that only STRING type allowed for s3, this is very strange
  2. so in LogicalTable section, I am trying to cast column which has values ‘2020-02-10’, and I need to have this column type DATE, but from docs it is available only DATETIME, and I tried this:
    ‘CastColumnTypeOperation’: {
    ‘ColumnName’: ‘dateColumn’,
    ‘NewColumnType’: ‘DATETIME’,
    ‘Format’: ‘yyyy-mm-dd’
    }
    and gets this result:
    image

Can someone help to fix it, and get DATE in format ‘YYYY-MM-DD’ please?

2

Hi Ivan, QuickSight team is looking into this and will update the next steps.

3

Hi Ivan,

  1. This is a limitation for S3, we note this in the InputColumn section of the documentation: For files that aren't JSON, only STRING data types are supported in input columns.
  2. yyyy-mm-dd is not a supported format, you should use yyyy-MM-dd instead. Since it is a datetime you will get something like 2020-02-10T:00:00:00.000Z