Athena: Missing columns from DDB data source

Question

Hi,

I'm using a DynamoDB connector in Athena to be able to query a DDB table and also use the data in QuickSight. I was able to set up the data source and query the table fine for the most part, but I've noticed that there are several columns missing in Athena. I've verified that they exist in DDB, but when I try to query in Athena (e.g. `SELECT column_name FROM table_name`), it says that the column cannot be resolved.

I'd appreciate any tips/pointers -- thank you!

Answer

I have been struggling with this one myself. I achieved solving my problem by crawling the DynamoDB table with AWS Glue and let AWS Glue infer the schema.

1. In AWS Glue, create a new database with its `Location URI` or `Location` set to the value `dynamo-db-flag` - use this exact value; otherwise, Athena won't see the future table.

2. In AWS Glue, create a crawler:
  - Select DynamoDB as the data source. 
  - For the table name, give the same table name as your dynamodb table. 
  - For the target database, choose the database you just created.
  - Run the crawler.

3. In AWS Glue, after the Crawler ran successfully, verify the table's schema, which the crawler just populated. Edit the table in Glue to add two additional table properties (Table > Edit Table Property > Table Properties): 
  - `sourceTable` is the exact name of your dynamodb table, matching case. E.g. if my dynamodb table is named `DbUsersTableXDXDXDXDX-SZSZSZSZSZSZSZ`, `sourceTable` property's value has to be `DbUsersTableXDXDXDXDX-SZSZSZSZSZSZSZ`.
  - `columnMapping` defines column name mappings. In AWS Glue catalog, column names should be in lowercase. So, this entry is necessary.  E.g. if your table has 3 column: `id`, `email`, `createdAt`, the value of `columnMapping` is `id=id,email=email,createdat=createdAt`, (Glue column name) in the left side of each mapping (`=`).

4. After saving the changes, go to Amazon Athena, for the DataSource `DynamoDB` (from the dynamodb connector), select the new database you created in (1), the table should be here.

Answer

Have you tried:
https://docs.aws.amazon.com/quicksight/latest/user/troubleshoot-athena-column-not-found.html

Athena: Missing columns from DDB data source

Relevant content