I'm using Redshift serverless and attempting to connect to an RDS (mysql) database. I have crawled the RDS successfully with AWS Glue and can see the RDS tables in both the Glue catalog and in Redshift however when I attempt a basic SELECT on any of the tables I get the following error
ERROR: Invalid DataCatalog response for external table "mydb"."mytable": Cannot deserialize table. Missing mandatory field 'InputFormat' in response from external catalog. Add missing field to table and retry. [ErrorId: 1-64e37792-0f7d2f505a1f1836577cd2ec]
The error seems to imply I'm missing the InputFormat and see that field is missing in my table definition but I don't understand why the crawler would not have populated this field. I'm not sure if this a catalog problem or a Redshift permissions issue.
I have not tried changing the table definition because I want the definition to come from the crawler. I have been able to connect Redshift and RDS directly by creating an external schema def but I want RDS to be in the catalog.
CREATE EXTERNAL SCHEMA myschema
FROM MYSQL
DATABASE 'mydb'
URI 'myuri'
IAM_ROLE 'MYROLE'
SECRET_ARN 'MYARN';