How to query DynamoDB nested elements from Athena

0

I am trying a query on the sample Movies table provided in the DynamoDB documentation. I have tried several variations of the below but all result in the same SYNTAX error.

A sample of the data is available at: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GettingStarted.Python.02.html

Here is the athena query and the output. The specific error is : Column 'lds."info"."release_date"' cannot be resolved"

bash$ aws athena start-query-execution --query-string "select lds.\"info\".\"release_date\" from \"dynamodb\".\"default\".\"movies\" as lds where year = 1985" --region us-east-1 --work-group AmazonAthenaPreviewFunctionality
{
    "QueryExecutionId": "f1c21259-43ea-4c69-8c42-55ff70998953"
}
bash$ aws athena get-query-execution --query-execution-id f1c21259-43ea-4c69-8c42-55ff70998953
{
    "QueryExecution": {
        "QueryExecutionId": "f1c21259-43ea-4c69-8c42-55ff70998953",
        "Query": "select lds.\"info\".\"release_date\" from \"dynamodb\".\"default\".\"movies\" as lds where year = 1985",
        "StatementType": "DML",
        "ResultConfiguration": {
            "OutputLocation": "s3://<BUCKET-NAME>/results/f1c21259-43ea-4c69-8c42-55ff70998953.csv"
        },
        "QueryExecutionContext": {},
        "Status": {
            "State": "FAILED",
            "StateChangeReason": "SYNTAX_ERROR: line 1:8: Column 'lds.\"info\".\"release_date\"' cannot be resolved",
            "SubmissionDateTime": 1589221630.629,
            "CompletionDateTime": 1589221632.273
        },
        "Statistics": {
            "EngineExecutionTimeInMillis": 998,
            "DataScannedInBytes": 0,
            "TotalExecutionTimeInMillis": 1644,
            "QueryQueueTimeInMillis": 625,
            "ServiceProcessingTimeInMillis": 21
        },
        "WorkGroup": "AmazonAthenaPreviewFunctionality"
    }
}
asked 4 years ago563 views
1 Answer
0
Accepted Answer

You are just missing the double quotes around table alias : bash$ aws athena start-query-execution --query-string "select \"lds\".\"info\".\"release_date\" from \"dynamodb\".\"default\".\"movies\" as \"lds\" where year = 1985" --region us-east-1 --work-group AmazonAthenaPreviewFunctionality

You should also check your table schema. You can do so by executing this query in Athena :

DESCRIBE dynamodb.default.movies

If your column is missing from that schema, then you should :

  • Verify your Glue catalog : you might have created a table named "movies" that have an incorrect schema
  • Either remove that table from Glue catalog, or make sure it gets created with the right schema.

Just for info, your query works for me. I don't have the table defined in Glue Catalog, so the schema is inferred by the connector

Hope this will help

answered 4 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions