---
name: add-athena-table-from-logs
description: Create an AWS Athena external table over a Humand service's S3 log archive by sampling a real `.json.gz` file from S3, classifying the JSON schema by logger family (Pino vs Logstash), and emitting a `CREATE EXTERNAL TABLE` statement with partition projection. Use when the user wants to query a service's S3 logs (`s3://humand-logs-{env}/ECS/...`) in Athena and the table doesn't exist yet, when sampling FluentBit-shipped logs, or when the user mentions Athena tables, partition projection, JsonSerDe, or log schema mappings.
---

# /add-athena-table-from-logs — Create Athena Table from S3 Logs

Creates an Athena external table over a Humand service's S3 log archive. Born from the bamboo-webservice slow-query investigation (May 2026), where reading logs in Athena required building the Logstash-family DDL from scratch.

## Prerequisites

Before running any command, ensure the user is logged in to AWS with the **`AthenaAnalytics`** role. This role has the required Athena and S3 read permissions. Without it, both CLI and console queries will fail with access errors.

## Tooling: prefer the Athena console

Run every Athena query in the [Athena console](https://us-east-1.console.aws.amazon.com/athena/home?region=us-east-1#/query-editor), not the AWS CLI. The CLI's `start-query-execution` is asynchronous (returns an execution ID, then you must poll `get-query-execution` and `get-query-results` separately) and requires a writable `OutputLocation` bucket. The console is synchronous-feeling, shows errors inline, and keeps query history automatically. Use the CLI only for the S3 sampling step (Phase 2) and for any future automation needs.

In the console, set the database to `humand_logs_prod` in the left panel before running queries.

## Workflow Overview

```
Phase 0: Inputs       → service name, env, cluster
Phase 1: Pre-check    → verify table doesn't already exist
Phase 2: Sample       → download a real log file from S3
Phase 3: Classify     → Pino (Node.js) or Logstash (Java) family
Phase 4: DDL          → compose CREATE EXTERNAL TABLE from the matching template
Phase 5: Smoke-test   → verify the table returns rows
Phase 6: Hand off     → report table name, location, and family
```

---

## Phase 0: Inputs

Ask the user:

1. **Service name** — e.g. `humand-bamboo-webservice`, `humand-app`
2. **Environment** — `dev`, `stg`, or `prd` (determines bucket: `humand-logs-<env>`)
3. **ECS cluster** — defaults to `public-prd-services` for `prd`; ask if unsure

Derive:
- `TABLE_NAME` = `<service_without_humand_prefix>_logs_<env>` (e.g. `bamboo_logs_prd`, `app_logs_prd`)
- `S3_PREFIX` = `s3://humand-logs-<env>/ECS/<cluster>/<service>`

---

## Phase 1: Pre-check

Run in the Athena console (database `humand_logs_prod`) before doing anything else:

```sql
SHOW TABLES IN humand_logs_prod LIKE '<table_name>';
```

> **Note:** The Athena database name is `humand_logs_prod` (with `prod`), not `humand_logs_prd`.

If the table already exists: stop. Show its current DDL with `SHOW CREATE TABLE <table_name>` and ask the user what they want to do (add columns, recreate, or use as-is).

---

## Phase 2: Sample a real file

List recent files scoped to a known date prefix (avoid `--recursive` on the full prefix — it is very slow on large buckets):

```bash
aws s3 ls s3://humand-logs-<env>/ECS/<cluster>/<service>/<yyyy>/<mm>/<dd>/ | tail -5
```

Then drill into a specific hour:

```bash
aws s3 ls s3://humand-logs-<env>/ECS/<cluster>/<service>/<yyyy>/<mm>/<dd>/<hh>/
```

Download one file. The easiest approach is to **download it from the S3 console in your browser** and use it directly — `aws s3 cp` does **not** reliably auto-decompress in all environments.

If you prefer CLI, download and decompress explicitly:

```bash
aws s3 cp s3://humand-logs-<env>/ECS/<cluster>/<service>/<year>/<month>/<day>/<hour>/<file>.json.gz \
  /tmp/sample.json.gz
zcat /tmp/sample.json.gz > /tmp/sample.json
```

Inspect the top-level keys:

```bash
head -1 /tmp/sample.json | jq -r 'keys[]' | sort -u
```

> **If you downloaded via browser**, macOS may already decompress the `.gz` — run `head -1` and `jq` directly on the downloaded file without `zcat`.

---

## Phase 3: Classify by logger family

Match the key set against the two known families:

| Family | Discriminating keys |
|--------|---------------------|
| **Pino (Node.js)** | `time`, `pid`, `hostname`, `path`, `method`, `responsetime`, `status`, `requestid`, top-level `user` object |
| **Logstash (Java)** | `@timestamp`, `@version`, `level_value`, `logger_name`, `thread_name` — and dotted JSON paths like `http.in.url`, `dd.trace_id` present as top-level keys |

If neither pattern matches exactly: scan all keys with `head -20 /tmp/sample.json | jq -r 'keys[]' | sort -u`, build a column list incrementally, and document the new family so the next person classifies faster.

---

## Phase 4: Compose the DDL

Replace `<table_name>`, `<env>`, `<cluster>`, `<service>` in the matching template. Keep `projection.year.range` at least 2 years ahead of the current year.

### Pino family (Node.js — app, backoffice, worker, event_handler, public_api, desert_eagle, deagle_firehose)

```sql
CREATE EXTERNAL TABLE `<table_name>`(
  `date` string COMMENT 'from deserializer',
  `level` string COMMENT 'from deserializer',
  `time` bigint COMMENT 'from deserializer',
  `pid` int COMMENT 'from deserializer',
  `hostname` string COMMENT 'from deserializer',
  `message` string COMMENT 'from deserializer',
  `user` struct<id:bigint,instanceid:bigint,employeeinternalid:string> COMMENT 'from deserializer',
  `botapp` struct<id:string,instanceid:bigint,botappimpersonateuserid:bigint> COMMENT 'from deserializer',
  `session` struct<id:string,scope:string,tokentype:string> COMMENT 'from deserializer',
  `requestid` string COMMENT 'from deserializer',
  `method` string COMMENT 'from deserializer',
  `path` string COMMENT 'from deserializer',
  `responsetime` int COMMENT 'from deserializer',
  `status` int COMMENT 'from deserializer',
  `devicetype` string COMMENT 'from deserializer',
  `humanddeviceversion` string COMMENT 'from deserializer',
  `formattedbody` string COMMENT 'from deserializer',
  `formattedheaders` string COMMENT 'from deserializer',
  `container_id` string COMMENT 'from deserializer',
  `container_name` string COMMENT 'from deserializer',
  `source` string COMMENT 'from deserializer',
  `ecs_cluster` string COMMENT 'from deserializer',
  `ecs_task_arn` string COMMENT 'from deserializer',
  `ecs_task_definition` string COMMENT 'from deserializer')
PARTITIONED BY (
  `year` string,
  `month` string,
  `day` string,
  `hour` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'ignore.malformed.json'='true')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://humand-logs-<env>/ECS/<cluster>/<service>'
TBLPROPERTIES (
  'compressionType'='gzip',
  'projection.day.digits'='2',
  'projection.day.range'='1,31',
  'projection.day.type'='integer',
  'projection.enabled'='true',
  'projection.hour.digits'='2',
  'projection.hour.range'='0,23',
  'projection.hour.type'='integer',
  'projection.month.digits'='2',
  'projection.month.range'='1,12',
  'projection.month.type'='integer',
  'projection.year.range'='2024,2028',
  'projection.year.type'='integer',
  'storage.location.template'='s3://humand-logs-<env>/ECS/<cluster>/<service>/${year}/${month}/${day}/${hour}/',
  'use.null.for.invalid.data'='true')
```

### Logstash family (Java/Spring Boot — bamboo, rocket, and future Java services)

JSON keys with `@` prefixes (`@timestamp`) and dotted paths (`http.in.url`, `dd.trace_id`) are illegal as raw Athena column names. Each one requires a `mapping.<flat_col>='<json.path>'` entry in `SERDEPROPERTIES`.

> **Important:** Java services do not yet share a standardised log schema. The base template below is the universally-safe minimum (encoder fields, common MDC ids, Datadog, ECS/container metadata); each service appends its own extension block for service-specific fields. Always also perform Step 3 to catch un-documented fields — extra columns in the DDL that don't exist in the JSON are harmless (`NULL`), but missing columns silently drop data.

#### Step 1: start from the Logstash base template

This template is runnable as-is for any Java service. Service-specific fields just won't appear until you splice in their extension block (Step 2). Replace `<table_name>`, `<env>`, `<cluster>`, `<service>` and keep `projection.year.range` at least 2 years ahead of the current year.

> **Do not add `--` comments inside the DDL.** Athena's Hive DDL parser rejects `--` line comments in `CREATE EXTERNAL TABLE` statements — they cause cryptic parse errors. Use the insertion points in Step 2 to splice extension blocks directly; do not annotate the DDL with placeholder comments before running it.

**Insertion points when splicing Step 2 blocks:**
- **Columns block** → insert immediately after `request_id` and before `dd_trace_id`.
- **Mappings block** → insert immediately after `mapping.request_id` and before `mapping.dd_trace_id`.

```sql
CREATE EXTERNAL TABLE `<table_name>`(
  `date` string COMMENT 'from deserializer',
  `at_timestamp` string COMMENT 'from deserializer',
  `at_version` string COMMENT 'from deserializer',
  `level` string COMMENT 'from deserializer',
  `level_value` int COMMENT 'from deserializer',
  `logger_name` string COMMENT 'from deserializer',
  `thread_name` string COMMENT 'from deserializer',
  `message` string COMMENT 'from deserializer',
  `stack_trace` string COMMENT 'from deserializer',
  `request_id` string COMMENT 'from deserializer',
  `dd_trace_id` string COMMENT 'from deserializer',
  `dd_span_id` string COMMENT 'from deserializer',
  `dd_service` string COMMENT 'from deserializer',
  `dd_env` string COMMENT 'from deserializer',
  `dd_version` string COMMENT 'from deserializer',
  `container_id` string COMMENT 'from deserializer',
  `container_name` string COMMENT 'from deserializer',
  `source` string COMMENT 'from deserializer',
  `ecs_cluster` string COMMENT 'from deserializer',
  `ecs_task_arn` string COMMENT 'from deserializer',
  `ecs_task_definition` string COMMENT 'from deserializer')
PARTITIONED BY (
  `year` string,
  `month` string,
  `day` string,
  `hour` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'ignore.malformed.json'='true',
  'mapping.at_timestamp'='@timestamp',
  'mapping.at_version'='@version',
  'mapping.request_id'='requestId',
  'mapping.dd_trace_id'='dd.trace_id',
  'mapping.dd_span_id'='dd.span_id',
  'mapping.dd_service'='dd.service',
  'mapping.dd_env'='dd.env',
  'mapping.dd_version'='dd.version')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://humand-logs-<env>/ECS/<cluster>/<service>'
TBLPROPERTIES (
  'compressionType'='gzip',
  'projection.day.digits'='2',
  'projection.day.range'='1,31',
  'projection.day.type'='integer',
  'projection.enabled'='true',
  'projection.hour.digits'='2',
  'projection.hour.range'='0,23',
  'projection.hour.type'='integer',
  'projection.month.digits'='2',
  'projection.month.range'='1,12',
  'projection.month.type'='integer',
  'projection.year.range'='2024,2028',
  'projection.year.type'='integer',
  'storage.location.template'='s3://humand-logs-<env>/ECS/<cluster>/<service>/${year}/${month}/${day}/${hour}/',
  'use.null.for.invalid.data'='true')
```

#### Step 2: splice in the service-specific extension

Find the entry below for your service. Paste the **columns block** at the columns insertion point and the **mappings block** at the mappings insertion point (see Step 1). If your service isn't listed, skip to Step 3 to build the extension from scratch.

##### humand-bamboo-webservice (`bamboo_logs_prd`)

User-facing REST API server with outgoing gRPC calls (`grpc.out.*` prefix from `bamboo-webservice` internals).

Columns:
```sql
  `user_id` string COMMENT 'from deserializer',
  `user_instance_id` string COMMENT 'from deserializer',
  `http_in_type` string COMMENT 'from deserializer',
  `http_in_url` string COMMENT 'from deserializer',
  `http_in_method` string COMMENT 'from deserializer',
  `http_in_status_code` string COMMENT 'from deserializer',
  `http_in_duration_millis` string COMMENT 'from deserializer',
  `grpc_out_type` string COMMENT 'from deserializer',
  `grpc_out_method` string COMMENT 'from deserializer',
  `grpc_out_status_code` string COMMENT 'from deserializer',
  `grpc_out_duration_millis` string COMMENT 'from deserializer',
```

Mappings:
```sql
  'mapping.user_id'='user.id',
  'mapping.user_instance_id'='user.instanceId',
  'mapping.http_in_type'='http.in.type',
  'mapping.http_in_url'='http.in.url',
  'mapping.http_in_method'='http.in.method',
  'mapping.http_in_status_code'='http.in.statusCode',
  'mapping.http_in_duration_millis'='http.in.durationMillis',
  'mapping.grpc_out_type'='grpc.out.type',
  'mapping.grpc_out_method'='grpc.out.method',
  'mapping.grpc_out_status_code'='grpc.out.statusCode',
  'mapping.grpc_out_duration_millis'='grpc.out.durationMillis',
```

##### humand-rocket-service (`rocket_logs_prd`)

Camunda external-task worker with a custom `CustomLogstashEncoder`. The flat `method`/`statusCode`/`elapsedTime`/`requestBody`/`responseBody` keys come from the gRPC interceptors in `humand-backend-java-libraries`.

Columns:
```sql
  `trace_id` string COMMENT 'from deserializer',
  `method` string COMMENT 'from deserializer',
  `status_code` string COMMENT 'from deserializer',
  `elapsed_time` string COMMENT 'from deserializer',
  `request_body` string COMMENT 'from deserializer',
  `response_body` string COMMENT 'from deserializer',
  `user_id` string COMMENT 'from deserializer',
  `user_instance_id` string COMMENT 'from deserializer',
  `http_in_type` string COMMENT 'from deserializer',
  `http_in_method` string COMMENT 'from deserializer',
  `http_in_url` string COMMENT 'from deserializer',
  `http_in_status_code` string COMMENT 'from deserializer',
  `http_in_duration_millis` string COMMENT 'from deserializer',
  `http_in_query_params` string COMMENT 'from deserializer',
  `http_in_data` string COMMENT 'from deserializer',
  `http_in_body` string COMMENT 'from deserializer',
  `external_task_type` string COMMENT 'from deserializer',
  `external_task_execution_id` string COMMENT 'from deserializer',
  `external_task_topic_name` string COMMENT 'from deserializer',
  `external_task_worker_id` string COMMENT 'from deserializer',
  `external_task_process_definition_id` string COMMENT 'from deserializer',
  `external_task_process_definition_key` string COMMENT 'from deserializer',
  `external_task_process_instance_id` string COMMENT 'from deserializer',
  `external_task_business_key` string COMMENT 'from deserializer',
  `external_task_duration_millis` string COMMENT 'from deserializer',
  `external_task_error_class` string COMMENT 'from deserializer',
  `external_task_retries` string COMMENT 'from deserializer',
  `external_task_variables` string COMMENT 'from deserializer',
```

Mappings:
```sql
  'mapping.trace_id'='traceId',
  'mapping.status_code'='statusCode',
  'mapping.elapsed_time'='elapsedTime',
  'mapping.request_body'='requestBody',
  'mapping.response_body'='responseBody',
  'mapping.user_id'='user.id',
  'mapping.user_instance_id'='user.instanceId',
  'mapping.http_in_type'='http.in.type',
  'mapping.http_in_method'='http.in.method',
  'mapping.http_in_url'='http.in.url',
  'mapping.http_in_status_code'='http.in.statusCode',
  'mapping.http_in_duration_millis'='http.in.durationMillis',
  'mapping.http_in_query_params'='http.in.queryParams',
  'mapping.http_in_data'='http.in.data',
  'mapping.http_in_body'='http.in.body',
  'mapping.external_task_type'='externalTask.type',
  'mapping.external_task_execution_id'='externalTask.executionId',
  'mapping.external_task_topic_name'='externalTask.topicName',
  'mapping.external_task_worker_id'='externalTask.workerId',
  'mapping.external_task_process_definition_id'='externalTask.processDefinitionId',
  'mapping.external_task_process_definition_key'='externalTask.processDefinitionKey',
  'mapping.external_task_process_instance_id'='externalTask.processInstanceId',
  'mapping.external_task_business_key'='externalTask.businessKey',
  'mapping.external_task_duration_millis'='externalTask.durationMillis',
  'mapping.external_task_error_class'='externalTask.errorClass',
  'mapping.external_task_retries'='externalTask.retries',
  'mapping.external_task_variables'='externalTask.variables',
```

> When Java logging is standardised, this section collapses: fields shared by all services move into the base template, and each per-service block shrinks toward empty. Until then, treat duplication between blocks as honest pressure on the standardisation effort.

#### Step 3: catch un-documented fields

Even with a service-specific extension block, new fields slip in over time. Before running the DDL:

1. Scan the sampled JSON for top-level keys across multiple message types — at least one with `requestId`, one with `http`, one with errors/exceptions, and any service-specific concept:

    ```bash
    grep -m 3 '"requestId"' /tmp/sample.json | jq -r 'keys[]' | sort -u
    grep -m 3 '"http"'      /tmp/sample.json | jq -r 'keys[]' | sort -u
    grep -m 3 '"stack_trace"' /tmp/sample.json | jq -r 'keys[]' | sort -u
    ```

2. Read the service's `logger/` package (look for `addKeyValue(...)` and MDC keys) and any shared logging library it depends on (e.g. `humand-backend-java-libraries`'s `LogKeys.java`).

3. Add any field not yet covered to the column list and (if it contains a `.` or `@`) to the mappings. Then update Step 2 above with the new entry so the next person finds it.

---

## Phase 5: Smoke-test

Run in the Athena console (not the CLI) after creating the table, scoped to a recent populated partition:

```sql
SELECT *
FROM <table_name>
WHERE year = '<yyyy>' AND month = '<mm>' AND day = '<dd>' AND hour = '<hh>'
LIMIT 5;
```

- If zero rows and you're confident the partition is populated: check `LOCATION` (no trailing slash, no `${year}/...` in the path) and verify the `storage.location.template` matches the actual S3 path structure.
- If a SerDe error appears on a Logstash table: check that every dotted / `@`-prefixed JSON key has a `mapping.*` entry in `SERDEPROPERTIES`.
- If only nulls appear for mapped columns: the JSON key in the `mapping.*` value is case-sensitive — verify against the real log file with `jq`.

---

## Phase 6: Hand off

Tell the user:
- Table name and database (`humand_logs_prod.<table_name>`)
- Logger family detected
- S3 location
- Athena query-history link: https://us-east-1.console.aws.amazon.com/athena/home?region=us-east-1#/query-editor/history

---

## Pitfalls

- **`aws s3 cp` does not reliably auto-decompress** (Phase 2): behaviour is environment-dependent. Use browser download (macOS decompresses automatically) or decompress explicitly with `zcat`. Do not assume the downloaded file is plain JSON.
- **Database name is `humand_logs_prod`** (with `prod`), not `humand_logs_prd`. Using the wrong name causes query failures with no obvious error message.
- **Log in with role `AthenaAnalytics`**: without it all Athena and S3 operations fail with access denied.
- **`use.null.for.invalid.data='true'`**: without this, a single malformed JSON row in a partition causes the entire partition query to fail with a SerDe error.
- **`projection.year.range` is finite**: queries for years outside the range silently return zero rows. Set the upper bound at least 2 years ahead of the current year.
- **Logstash services have service-specific fields**: the templates are starting points. Always check the service's `logger/` package and shared libraries (e.g. `humand-backend-java-libraries`) for `addKeyValue(...)` calls and MDC keys before finalising the DDL.
- **No `--` line comments inside `CREATE EXTERNAL TABLE`**: Athena's Hive DDL parser rejects them and the resulting error message does not point at the comment line. Keep all annotations in the surrounding prose, not in the DDL body.
- **Athena lowercases identifiers**: at query time `responseTime` is `responsetime`, `requestId` is `requestid`. The `mapping.*` preserves original JSON casing for SerDe resolution only.
- **Reserved words**: `user`, `date`, `timestamp`, `session` must be double-quoted in queries: `"user".id`, `"date"`.
- **LOCATION must have no trailing slash and no partition placeholders**: `s3://bucket/ECS/cluster/service` — not `s3://bucket/.../service/` and not `s3://bucket/.../service/${year}/...`. The partition projection handles the path expansion automatically via `storage.location.template`.

## Verification

- [ ] `SHOW TABLES IN humand_logs_prod` lists the new table
- [ ] Smoke-test query returns at least one row for a known-good partition
- [ ] `SELECT COUNT(*)` bounded to a single hour completes in under 10 seconds
- [ ] `dd_trace_id` (Logstash) or `requestid` (Pino) is non-null in returned rows
