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"
    }
}
gefragt vor 4 Jahren571 Aufrufe
1 Antwort
0
Akzeptierte Antwort

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

beantwortet vor 4 Jahren

Du bist nicht angemeldet. Anmelden um eine Antwort zu veröffentlichen.

Eine gute Antwort beantwortet die Frage klar, gibt konstruktives Feedback und fördert die berufliche Weiterentwicklung des Fragenstellers.

Richtlinien für die Beantwortung von Fragen