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"
    }
}
질문됨 4년 전571회 조회
1개 답변
0
수락된 답변

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

답변함 4년 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠