Run an Athena Query With AWS SDK v3 and Read the Results

// athena-query.ts
// Run an Athena SQL query with AWS SDK for JavaScript v3: start it, poll until it finishes,
// then page through GetQueryResults and return typed rows plus the bytes scanned.
import { randomUUID } from "node:crypto";
import { setTimeout as sleep } from "node:timers/promises";
import {
  AthenaClient,
  GetQueryExecutionCommand,
  paginateGetQueryResults,
  StartQueryExecutionCommand,
  StopQueryExecutionCommand,
  type ColumnInfo,
  type QueryExecution,
} from "@aws-sdk/client-athena";

const athena = new AthenaClient({}); // Region from AWS_REGION or the profile

export interface QueryOptions {
  workgroup?: string; // default "primary"
  database?: string;
  catalog?: string; // default "AwsDataCatalog"
  outputLocation?: string; // s3://bucket/prefix/ ; not needed if the workgroup sets one
  params?: string[]; // values for the ? placeholders, as SQL literals: "'2027-03-01'", "42"
  timeoutMs?: number;
}

export type Cell = string | number | boolean | null;
export interface QueryResult {
  queryId: string;
  columns: { name: string; type: string }[];
  rows: Record<string, Cell>[];
  scannedBytes: number;
  engineMs: number;
}

export class AthenaQueryError extends Error {
  queryId: string;
  state: string;
  retryable: boolean;
  constructor(message: string, queryId: string, state: string, retryable: boolean) {
    super(message);
    this.name = "AthenaQueryError";
    this.queryId = queryId;
    this.state = state;
    this.retryable = retryable;
  }
}

/** Convert Athena's string cells using the column type from ResultSetMetadata. */
function convert(value: string | undefined, col: ColumnInfo): Cell {
  if (value === undefined) return null; // SQL NULL has no VarCharValue
  switch (col.Type) {
    case "tinyint":
    case "smallint":
    case "integer":
    case "float":
    case "real":
    case "double":
      return Number(value);
    case "bigint":
    case "decimal": // keep exact: parse later with BigInt or a decimal library if you need to
      return value;
    case "boolean":
      return value === "true";
    default:
      return value; // varchar, date, timestamp, arrays, maps and rows stay strings
  }
}

async function waitForQuery(queryId: string, timeoutMs: number): Promise<QueryExecution> {
  const started = Date.now();
  let delay = 250;
  for (;;) {
    const { QueryExecution: qe } = await athena.send(new GetQueryExecutionCommand({ QueryExecutionId: queryId }));
    const state = qe?.Status?.State;
    if (qe && (state === "SUCCEEDED" || state === "FAILED" || state === "CANCELLED")) return qe;
    if (Date.now() - started > timeoutMs) {
      await athena.send(new StopQueryExecutionCommand({ QueryExecutionId: queryId })); // stop paying for the scan
      throw new AthenaQueryError(`timed out after ${timeoutMs} ms`, queryId, "CANCELLED", true);
    }
    await sleep(delay + Math.random() * delay * 0.2);
    delay = Math.min(delay * 2, 5000); // 250 ms, 500 ms, 1 s ... capped at 5 s
  }
}

export async function runQuery(sql: string, opts: QueryOptions = {}): Promise<QueryResult> {
  const { QueryExecutionId: queryId } = await athena.send(
    new StartQueryExecutionCommand({
      QueryString: sql,
      ClientRequestToken: randomUUID(), // idempotent: an SDK retry can't start the query twice
      WorkGroup: opts.workgroup ?? "primary",
      QueryExecutionContext: opts.database ? { Database: opts.database, Catalog: opts.catalog ?? "AwsDataCatalog" } : undefined,
      ResultConfiguration: opts.outputLocation ? { OutputLocation: opts.outputLocation } : undefined,
      ExecutionParameters: opts.params?.length ? opts.params : undefined,
    }),
  );
  if (!queryId) throw new Error("StartQueryExecution returned no QueryExecutionId");

  const qe = await waitForQuery(queryId, opts.timeoutMs ?? 5 * 60_000);
  const state = qe.Status?.State ?? "UNKNOWN";
  if (state !== "SUCCEEDED") {
    const e = qe.Status?.AthenaError;
    const reason = e?.ErrorMessage ?? qe.Status?.StateChangeReason ?? state;
    throw new AthenaQueryError(`${state}: ${reason}`, queryId, state, e?.Retryable ?? false);
  }

  const columns: ColumnInfo[] = [];
  const rows: Record<string, Cell>[] = [];
  let first = true;
  for await (const page of paginateGetQueryResults({ client: athena, pageSize: 1000 }, { QueryExecutionId: queryId })) {
    if (!columns.length) columns.push(...(page.ResultSet?.ResultSetMetadata?.ColumnInfo ?? []));
    for (const row of page.ResultSet?.Rows ?? []) {
      const cells = row.Data ?? [];
      // For SELECT, the first row of the first page repeats the column names.
      if (first) {
        first = false;
        if (cells.every((c, i) => c.VarCharValue === columns[i]?.Name)) continue;
      }
      const record: Record<string, Cell> = {};
      columns.forEach((col, i) => {
        record[col.Name ?? `col${i}`] = convert(cells[i]?.VarCharValue, col);
      });
      rows.push(record);
    }
  }

  return {
    queryId,
    columns: columns.map((c) => ({ name: c.Name ?? "", type: c.Type ?? "" })),
    rows,
    scannedBytes: qe.Statistics?.DataScannedInBytes ?? 0,
    engineMs: qe.Statistics?.EngineExecutionTimeInMillis ?? 0,
  };
}

/** Athena SQL pricing: $5 per TB scanned, rounded up per MB, 10 MB minimum (us-east-1, September 2026). */
export function estimateCostUsd(scannedBytes: number): number {
  const mb = Math.max(10, Math.ceil(scannedBytes / 1024 ** 2));
  return (mb / 1024 ** 2) * 5;
}

// run-athena.ts
// Usage: AWS_PROFILE=analytics AWS_REGION=us-east-1 npx tsx run-athena.ts 2027-03-01
import { AthenaQueryError, estimateCostUsd, runQuery } from "./athena-query.js";

const day = process.argv[2] ?? new Date().toISOString().slice(0, 10);
if (!/^\d{4}-\d{2}-\d{2}$/.test(day)) throw new Error("pass a date as YYYY-MM-DD");

const sql = `
  SELECT elb_status_code, count(*) AS requests, approx_percentile(target_processing_time, 0.95) AS p95_seconds
  FROM alb_logs
  WHERE day = ? AND elb_status_code >= ?
  GROUP BY elb_status_code
  ORDER BY requests DESC
  LIMIT 20`;

try {
  const result = await runQuery(sql, {
    workgroup: "analytics",
    database: "weblogs",
    params: [`'${day}'`, "500"], // string parameters need single quotes; numbers don't
    timeoutMs: 120_000,
  });
  console.table(result.rows);
  const mb = (result.scannedBytes / 1024 ** 2).toFixed(1);
  console.log(`${result.rows.length} rows, ${mb} MB scanned (~$${estimateCostUsd(result.scannedBytes).toFixed(4)}), ${result.engineMs} ms, id ${result.queryId}`);
} catch (err) {
  if (err instanceof AthenaQueryError) {
    console.error(`Query ${err.queryId} ${err.state}${err.retryable ? " (retryable)" : ""}: ${err.message}`);
    process.exit(1);
  }
  throw err;
}

npm install @aws-sdk/client-athena
npm install --save-dev tsx typescript @types/node
npm pkg set type=module

AWS_PROFILE=analytics AWS_REGION=us-east-1 npx tsx run-athena.ts 2027-03-01
# ┌─────────┬─────────────────┬──────────┬─────────────┐
# │ (index) │ elb_status_code │ requests │ p95_seconds │
# ├─────────┼─────────────────┼──────────┼─────────────┤
# │ 0       │ 502             │ 1841     │ 0.012       │
# │ 1       │ 504             │ 97       │ 29.87       │
# │ 2       │ 500             │ 12       │ 0.431       │
# └─────────┴─────────────────┴──────────┴─────────────┘
# 3 rows, 8412.6 MB scanned (~$0.0401), 3120 ms, id 5f1c2a0e-8d3b-4c7e-9a61-2b4f0e7d9c13

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "RunQueriesInWorkgroup",
      "Effect": "Allow",
      "Action": [
        "athena:StartQueryExecution",
        "athena:GetQueryExecution",
        "athena:GetQueryResults",
        "athena:StopQueryExecution"
      ],
      "Resource": "arn:aws:athena:us-east-1:123456789012:workgroup/analytics"
    },
    {
      "Sid": "ReadTableDefinitions",
      "Effect": "Allow",
      "Action": [
        "glue:GetDatabase",
        "glue:GetTable",
        "glue:GetPartitions"
      ],
      "Resource": [
        "arn:aws:glue:us-east-1:123456789012:catalog",
        "arn:aws:glue:us-east-1:123456789012:database/weblogs",
        "arn:aws:glue:us-east-1:123456789012:table/weblogs/*"
      ]
    },
    {
      "Sid": "ReadSourceData",
      "Effect": "Allow",
      "Action": [
        "s3:GetBucketLocation",
        "s3:ListBucket",
        "s3:GetObject"
      ],
      "Resource": [
        "arn:aws:s3:::acme-alb-logs",
        "arn:aws:s3:::acme-alb-logs/*"
      ]
    },
    {
      "Sid": "WriteAndReadQueryResults",
      "Effect": "Allow",
      "Action": [
        "s3:GetBucketLocation",
        "s3:ListBucket",
        "s3:GetObject",
        "s3:PutObject",
        "s3:AbortMultipartUpload"
      ],
      "Resource": [
        "arn:aws:s3:::acme-athena-results",
        "arn:aws:s3:::acme-athena-results/*"
      ]
    }
  ]
}