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 Answer
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"
            }
        ]
    }
    .....
]
answered a year 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