Missing Data in Athena Resultset using javascript SDK v3.0

0

Below is a sample javascript sdk v3 Athena query that uses a prepared statement and parameters that are passed to the query

const { AthenaClient } = require("@aws-sdk/client-athena");
const REGION = 'us-east-1';
const athenaClient = new AthenaClient({region: REGION});
module.exports = {athenaClient};
const tableName = 'employees';
const sqlString = "SELECT firstname, lastname, state FROM " +  tableName + " WHERE  " +
    "zipcode = ? AND " + 
    "companyname = ?";
    
const queryExecutionInput = {
    QueryString: sqlString,
    QueryExecutionContext: {
        Database: 'sample-employee',
        Catalog: 'awscatalogname'
    },
    ResultConfiguration: {
        OutputLocation: 's3://athena-query-bucket'
    },

    WorkGroup: 'primary',
    ExecutionParameters: ["12345", "Test 1"]
}

const queryExecutionId = await athenaClient.send(new StartQueryExecutionCommand(queryExecutionInput));

const command = new GetQueryExecutionCommand(queryExecutionId);
const response = await athenaClient.send(command);

const state = response.QueryExecution?.Status?.State;

if(state === QueryExecutionState.QUEUED || state === QueryExecutionState.RUNNING) {
    await setTimeout(this.config.pollInterval); //wait for pollInterval before calling again.
    return this.waitForQueryExecution(queryExecutionId);
} else if(state === QueryExecutionState.SUCCEEDED) {
    const resultParams = { QueryExecutionId: response.QueryExecution.QueryExecutionId, MaxResults: this.config.maxResults};

    const getQueryResultsCommand:any = new GetQueryResultsCommand(resultParams);

    const resp = await athenaClient.send(getQueryResultsCommand);

    console.log("GetQueryResultsCommand : ", resp.ResultSet.ResultSetMetadata.ColumnInfo);

    console.log("GetQueryResultsCommand : ", resp.ResultSet.Rows);


} else if(state === QueryExecutionState.FAILED) {
    throw new Error(`Query failed: ${response.QueryExecution?.Status?.StateChangeReason}`);
} else if(state === QueryExecutionState.CANCELLED) {
    throw new Error("Query was cancelled");
}

This table has about 50 records that match this query. When the query is run this is what is returned for all 50 records.

{
    "ResultSetMetadata":
    {
        "Rows":
        [
            {
                "Data":
                [
                    {
                        "VarCharValue": "firstname"
                    },
                    {
                        "VarCharValue": "lastname"
                    },
                    {
                        "VarCharValue": "state"
                    }
                ]
            }
        ]
    }
}

Only the column names are listed but no data from these columns.

I see the exact same issue when I try it using the CLI as well

aws athena start-query-execution --query-string "SELECT firstname, lastname, state FROM employees WHERE zipcode = CAST(? as varchar) AND  companyname = CAST(? as varchar)" 
--query-execution-context "Database"="sample-employee" 
--result-configuration "OutputLocation"="s3://athena-query-bucket/" 
--execution-parameters "12345"  "Test 1"

aws athena get-query-execution --query-execution-id "<query-execution-id>"

aws athena get-query-results --query-execution-id "<query-execution-id>"

FYI ColumnInfo in the ResultSetMetadata object has been removed to keep the json simple

{
    "ResultSetMetadata":
    {
        "Rows":
        [
            {
                "Data":
                [
                    {
                        "VarCharValue": "firstname"
                    },
                    {
                        "VarCharValue": "lastname"
                    },
                    {
                        "VarCharValue": "state"
                    }
                ]
            }
        ]
    }
}

So, not exactly sure what I might be doing wrong. Any help/pointers on this would be great. We are currently running Athena engine version 2.

  • Does that query return any results if run directly in AWS Console?

1 Antwort
0

yeah it does. I have about 50 records that match the where clause and I can see it in the Athena Query console. When I run my sdk code, I also get 50 records, but it only has the column names like shown below when using the sdk.

[
    {
        "Data":
        [
            {
                "VarCharValue": "firstname"
            },
            {
                "VarCharValue": "lastname"
            },
            {
                "VarCharValue": "state"
            }
        ]
    },
    {
        "Data":
        [
            {
                "VarCharValue": "firstname"
            },
            {
                "VarCharValue": "lastname"
            },
            {
                "VarCharValue": "state"
            }
        ]
    }
    .....
]
beantwortet vor einem Jahr

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