We use Amazon QuickSight to analyze data from MongoDB.
The custom query in our dataset works perfect:
SELECT _id, label, structure, createdAt, deleted
FROM docdb.label_db.labels
We have added a new field imagelink (string) to a couple of mongo documents in labels collection.
But when I updated the query with the new imagelink field:
SELECT _id, label, structure, createdAt, deleted, imageLink
FROM docdb.label_db.labels
the query started failing. I even cannot save it.
Error:
[Simba]AthenaJDBC An error has been thrown from the AWS Athena client. COLUMN_NOT_FOUND: line 2:53: Column 'imagelink' cannot be resolved or requester is not authorized to access requested resources
I tried to run the same queries directly from Athena - the same error appears.
When I run db.labels.find({imageLink: {$exists: true}}) from mongo shell, it correctly shows me the documents with imageLink field.
I have tried to find an answer in AWS documentation, but the only possible reason I see for now - it's a camel case in field name, but it works with another field createdAt.
Has anyone encountered this problem?
Solved! That was a tricky one :)
When I checked recent queries history in Athena, I found two types of requests to mongo. One was a full refresh request (by schedule) and another one was a preview request (during Custom Query update - when I added imageLink field). The last one was failed.
There was a difference in LIMIT clause:
LIMIT 0LIMIT 1000 (QuickSight sets this limit by itself)As we have more than 4000 documents in requested mongo collection, I assumed that documents with newly added imageLink field were not listed in first 1000 documents.
So, after I added imageLink to one of the first documents in labels collection QuickSight was able to fetch the field.
Then I successfully saved Custom Query and did full refresh!