CODEWITHSUNDEEP
mongodbamazon-athenaamazon-quicksightaws-documentdb
ModX
ModX

Reputation: 879

Error while fetching data from mongo to QuickSight using Athena (COLUMN_NOT_FOUND Error)

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?

Upvotes: 0

Views: 162

Answers (1)

ModX
ModX

Reputation: 879

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:

  • full refresh: LIMIT 0
  • preview request: LIMIT 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!

Upvotes: 0

Related Questions