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?