Querying newline-delimited JSON logs using AWS Athena#

The log format#

My logs are shipped to S3 using Vector. It actually creates a huge number of tiny gzipped files in my S3 bucket, each one representing just a small number of log lines.

The contents of one of those files looks like this:

{"event":{"provider":"app"},"fly":{"app":{"instance":"0e286551c30586","name":"dc-team-52-simon-46d213"},"region":"sjc"},"host":"0ad1","log":{"level":"info"},"message":"subprocess exited, litestream shutting down","timestamp":"2022-09-27T20:34:37.252022967Z"} {"event":{"provider":"app"},"fly":{"app":{"instance":"0e286551c30586","name":"dc-team-52-simon-46d213"},"region":"sjc"},"host":"0ad1","log":{"level":"info"},"message":"litestream shut down","timestamp":"2022-09-27T20:34:37.253080674Z"} {"event":{"provider":"runner"},"fly":{"app":{"instance":"0e286551c30586","name":"dc-team-52-simon-46d213"},"region":"sjc"},"host":"0ad1","log":{"level":"info"},"message":"machine exited with exit code 0, not restarting","timestamp":"2022-09-27T20:34:39.660159411Z"}

This is newline-delimited JSON. Here’s the first of those lines pretty-printed for readability:

1
{
2
  "event": {
3
    "provider": "app"
4
  },
5
  "fly": {
6
    "app": {
7
      "instance": "0e286551c30586",
8
      "name": "dc-team-52-simon-46d213"
9
    },
10
    "region": "sjc"
11
  },
12
  "host": "0ad1",
13
  "log": {
14
    "level": "info"
15
  },
16
  "message": "subprocess exited, litestream shutting down",
17
  "timestamp": "2022-09-27T20:34:37.252022967Z"
18
}

The challenge: how to teach Athena how to turn those files into a table I can run queries against?

Defining an Athena table#

This was by far the hardest thing to figure out.

To run queries in Athena, you first need to create an external table that tells it how to read the data in your S3 bucket.

I was hoping I could just create a table with a single column full of JSON, and then run queries to extract the data I wanted.

I couldn’t figure out how to do that, so instead I figured out how to create a table that matched the schema of my JSON logs.

In the end, this example in the Athena docs helped me crack it.

Here’s my eventual solution:

1
CREATE EXTERNAL TABLE fly_logs (
2
  timestamp string,
3
  host string,
4
  fly struct<
5
    app: struct<
6
      instance: string,
7
      name: string
8
    >,
9
    region: string
10
  >,
11
  log struct<
12
    level: string
13
  >,
14
  message string
15
)
16
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
17
STORED AS INPUTFORMAT
18
  'org.apache.hadoop.mapred.TextInputFormat'
19
OUTPUTFORMAT
20
  'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
21
LOCATION 's3://datasette-cloud-fly-logs/'

I ran that in the Athena query editor at https://us-east-1.console.aws.amazon.com/athena/home?region=us-east-1#/query-editor

As you can see, the key thing here is the section that defines the columns. I’m defining them to exactly match the structure of my JSON logs.

Because my logs include nested objects, I had to use struct< ... > syntax to define some of the columns.

Confusingly, at the base of the column definition columns are defined using column_name type - but within a struct< that changes to column_name: type. If you mix these formats up you get a confusing error message like this one:

FAILED: ParseException line 1:164 missing : at 'string' near '<EOF>'

Querying the table#

Once the table is defined, running queries is pretty easy. I started with this one:

1
select * from fly_logs limit 10;

A count worked too:

1
select count(*) from fly_logs;

To query nested objects, I used . syntax like this:

1
select timestamp, host, fly.app.name, log.level, message
2
from fly_logs_two limit 10;

Finally, to search for a specific error message in the logs I ran this query:

1
select
2
  timestamp, host, fly.app.name, log.level, message
3
from
4
  fly_logs_two
5
where
6
  message like '%sqlite3.OperationalError: no such table: _public_tables%'
7
limit 100;

Generating the configuration using GPT-3#

Manually converting that JSON into a CREATE EXTERNAL TABLE definition is tedious and error-prone.

It turns out you can instead use the OpenAI GPT-3 language model to do that work for you!

Using the playground I tried the following prompt:

1
write an AWS Athena create table statement for querying this JSON data:
2

3
{
4
  "event": {
5
    "provider": "app"
6
  },
7
  "fly": {
8
    "app": {
9
      "instance": "0e286551c30586",
10
      "name": "dc-team-52-simon-46d213"
11
    },
12
    "region": "sjc"
13
  },
14
  "host": "0ad1",
15
  "log": {
16
    "level": "info"
17
  },
18
  "message": "subprocess exited, litestream shutting down",
19
  "timestamp": "2022-09-27T20:34:37.252022967Z"
20
}

GPT-3 responded:

1
CREATE EXTERNAL TABLE IF NOT EXISTS logs (
2
  event struct<provider:string>,
3
  fly
4
  struct<app:struct<instance:string.name:string>,region:string>
5
  host string,
6
  log struct<level:string>,
7
  message string,
8
  timestamp string
9
)
10
ROW FORMAT SERDE
11
  'org.openx.data.jsonserde.JsonSerDe'
12
LOCATION 's3://bucket/path/to/json/data/';