Hi,
I believe one can run Athena queries against a MongoDB instance using the DocumentDb connector. I've setup a connection and Athena able to read the collections within my database.
When trying to run any query, I get an error like the one below. This happens even when I use the "Preview Table" query. One thing I noticed straight away in the error message is that, even though I type my collection name with a starting capital letter (e.g. User) in my query, it's getting reported back in the error in all lowercase (e.g. "user").
My example table name
User
Here is a sample query (fake db and collection names used):
SELECT * FROM "mydb"."User" limit 10;
Here is an example error message:
NOT_SUPPORTED: Table has no columns: mongo_db:LambdaTableHandle{tableName=TableName{schemaName=mydb, tableName=user}}
This query ran against the "xxx" database, unless qualified by the query.
My first guess, based on the above error, is that Athena is lowercasing the collection name, and in so doing, not finding the correct collection.
Does anyone know:
- If this is the case - i.e. that table names are lowercased?
- If there are settings to change this - i.e. not to lowercase the table name?
- If there is anything I can try
I'd appreciate any help or guidance!
Thanks!
For anyone else stumbling upon this, I've found this: https://github.com/awslabs/aws-athena-query-federation/issues/463#issuecomment-897628798
I'll try option 1 as option 2 is not a viable option in my situation. I'll post the results here (if I can figure it out) :)