Unable to query dynamodb table in Athena using DynamoDB Connector

0

I have tried to switch the workgroup to AmazonAthenaPreviewFunctionality in the console but the page refreshes and just falls back to the primary group. Hence the query fails.

Then I tried the command line option. I get the error: "SYNTAX_ERROR: line 1:19: Table awsdatacatalog.default.lambda-dynamodb-stream does not exist"

It seems earlier the start-query-execution API had an option to specify the catalog in its query-execution-context but not anymore. What am I missing here?

bash $ aws athena start-query-execution --query-string "select value from \"lambda-dynamodb-stream\" where id = 4" --region us-east-1 --query-execution-context Database=default --work-group AmazonAthenaPreviewFunctionality
{
    "QueryExecutionId": "aa7341a1-24dc-4afb-8ca6-e553f0b4de20"
}
bash $ aws athena get-query-execution --query-execution-id aa7341a1-24dc-4afb-8ca6-e553f0b4de20
{
    "QueryExecution": {
        "QueryExecutionId": "aa7341a1-24dc-4afb-8ca6-e553f0b4de20",
        "Query": "select value from \"lambda-dynamodb-stream\" where id = 4",
        "StatementType": "DML",
        "ResultConfiguration": {
            "OutputLocation": "s3://<BUCKET-NAME>/results/aa7341a1-24dc-4afb-8ca6-e553f0b4de20.csv"
        },
        "QueryExecutionContext": {
            "Database": "default"
        },
        "Status": {
            "State": "FAILED",
            "StateChangeReason": "SYNTAX_ERROR: line 1:19: Table awsdatacatalog.default.lambda-dynamodb-stream does not exist",
            "SubmissionDateTime": 1588915234.426,
            "CompletionDateTime": 1588915235.633
        },
        "Statistics": {
            "EngineExecutionTimeInMillis": 858,
            "DataScannedInBytes": 0,
            "TotalExecutionTimeInMillis": 1207,
            "QueryQueueTimeInMillis": 309,
            "ServiceProcessingTimeInMillis": 40
        },
        "WorkGroup": "AmazonAthenaPreviewFunctionality"
    }
}
asked 3 years ago294 views
1 Answer
0
Accepted Answer

"SYNTAX_ERROR: line 1:19: Table awsdatacatalog.default.lambda-dynamodb-stream does not exist"

You are referencing the Glue Catalog in your query, that will not work for your DynamoDB table. You have to create a new data source; refer to part 2 of this doc : https://docs.aws.amazon.com/athena/latest/ug/connect-to-a-data-source-lambda.html

On Athena console You have to create a new data source to query your DynamoDB :

  • Choose DynamoDB, and pick your Lambda function.
  • Choose a catalog name: ("ddb" for example) => it's this name that you need to specify when querying your DynamoDB tables. In this case : select value from ddb.default.lambda-dynamodb-stream

Please note that if you don't have a table defined in the Glue catalog that matches your DynamoDB table name, then the schema will be inferred by the DynamoDB Connector, as described here (https://github.com/awslabs/aws-athena-query-federation/blob/master/athena-dynamodb/README.md#setting-up-databases--tables-in-glue)

answered 3 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