# til

path: github-actions_ensure-labels.md
topic: github-actions
title: Ensure labels exist in a GitHub repository
url: https://github.com/simonw/til/blob/main/github-actions/ensure-labels.md
created: 2022-09-25T11:28:21-07:00
created_utc: 2022-09-25T18:28:21+00:00
updated: 2022-09-25T11:28:53-07:00
updated_utc: 2022-09-25T18:28:53+00:00
shot_hash: 147e94f19f28e7c13888d03c583014ee
slug: ensure-labels
body:
I wanted to ensure that when [this template repository](https://github.com/simonw/action-transcription) was used to create a new repo that repo would have a specific set of labels.

Here's the workflow I came up with, saved as `.github/workflows/ensure_labels.yml`:

```yaml
name: Ensure labels
on: [push]

jobs:
  ensure_labels:
    runs-on: ubuntu-latest
    steps:
    - name: Create labels
      uses: actions/github-script@v6
      with:
        script: |
          try {
            await github.rest.issues.createLabel({
              ...context.repo,
              name: 'captions'
            });
            await github.rest.issues.createLabel({
              ...context.repo,
              name: 'whisper'
            });
          } catch(e) {
            // Ignore if labels exist already
          }
```
This creates `captions` and `whisper` labels, if they do not yet exist.

It's wrapped in a `try/catch` so that if the labels exist already (as they will on subsequent runs) the error can be ignored.

Note that you need to use `await ...` inside that `try/catch` block or exceptions thrown by those methods will still cause the action run to fail.

The `...context.repo` trick saves on having to pass `owner` and `repo` explicitly.

html:
<p>I wanted to ensure that when <a href="https://github.com/simonw/action-transcription">this template repository</a> was used to create a new repo that repo would have a specific set of labels.</p>
<p>Here's the workflow I came up with, saved as <code>.github/workflows/ensure_labels.yml</code>:</p>
<div class="highlight highlight-source-yaml"><pre><span class="pl-ent">name</span>: <span class="pl-s">Ensure labels</span>
<span class="pl-ent">on</span>: <span class="pl-s">[push]</span>

<span class="pl-ent">jobs</span>:
  <span class="pl-ent">ensure_labels</span>:
    <span class="pl-ent">runs-on</span>: <span class="pl-s">ubuntu-latest</span>
    <span class="pl-ent">steps</span>:
    - <span class="pl-ent">name</span>: <span class="pl-s">Create labels</span>
      <span class="pl-ent">uses</span>: <span class="pl-s">actions/github-script@v6</span>
      <span class="pl-ent">with</span>:
        <span class="pl-ent">script</span>: <span class="pl-s">|</span>
<span class="pl-s">          try {</span>
<span class="pl-s">            await github.rest.issues.createLabel({</span>
<span class="pl-s">              ...context.repo,</span>
<span class="pl-s">              name: 'captions'</span>
<span class="pl-s">            });</span>
<span class="pl-s">            await github.rest.issues.createLabel({</span>
<span class="pl-s">              ...context.repo,</span>
<span class="pl-s">              name: 'whisper'</span>
<span class="pl-s">            });</span>
<span class="pl-s">          } catch(e) {</span>
<span class="pl-s">            // Ignore if labels exist already</span>
<span class="pl-s">          }</span></pre></div>
<p>This creates <code>captions</code> and <code>whisper</code> labels, if they do not yet exist.</p>
<p>It's wrapped in a <code>try/catch</code> so that if the labels exist already (as they will on subsequent runs) the error can be ignored.</p>
<p>Note that you need to use <code>await ...</code> inside that <code>try/catch</code> block or exceptions thrown by those methods will still cause the action run to fail.</p>
<p>The <code>...context.repo</code> trick saves on having to pass <code>owner</code> and <code>repo</code> explicitly.</p>


path: aws_athena-key-does-not-exist.md
topic: aws
title: Athena error: The specified key does not exist
url: https://github.com/simonw/til/blob/main/aws/athena-key-does-not-exist.md
created: 2022-09-27T20:45:49-07:00
created_utc: 2022-09-28T03:45:49+00:00
updated: 2022-09-27T20:45:49-07:00
updated_utc: 2022-09-28T03:45:49+00:00
shot_hash: a8c1236f9396767642c8b18eb29c003e
slug: athena-key-does-not-exist
body:
I was trying to run Athena queries against compressed JSON log files stored in an S3 bucket.

No matter what I tried, I got the following error:

> The specified key does not exist. (Service: Amazon S3; Status Code: 404; Error Code: NoSuchKey; Request ID: 4GHB3YX6DQHYTCPF; S3 Extended Request ID: 0LSAhwbo21RaZ+8/FOgKf1oh+dIkV0WO8DvtYmwQdBzddfILchiSyamFLenD8IOmrN+lDPxKTFP/7my0DKbVvw==; Proxy: null), S3 Extended Request ID: 0LSAhwbo21RaZ+8/FOgKf1oh+dIkV0WO8DvtYmwQdBzddfILchiSyamFLenD8IOmrN+lDPxKTFP/7my0DKbVvw== (Path: s3://my-logs-bucket/my-fly-app/2022-05-27/1653693921-a96e5844-02db-4e3e-9e9a-3eef00910271.log.gz)

This is using the Fly log shipping recipe [described here previously](https://til.simonwillison.net/fly/fly-logs-to-s3).

I couldn't find any search results online for this error in the context of Athena.

After much head scratching... I spotted that the files in my bucket had keys that looked like this:

- `my-fly-app/2022-05-27//1653693921-a96e5844-02db-4e3e-9e9a-3eef00910271.log.gz`

Note that there's a `//` after the date instead of a `/`. But in the error message from Athena the same key is identified as `my-fly-app/2022-05-27/1653693921-a96e5844-02db-4e3e-9e9a-3eef00910271.log.gz` - without the double slash.

It looks like Athena has a bug where it can't read files with `//` in their key!

The fix was to first fix my log shipper so that it wrote files without that prefix. Upgrading to the most recent version in the Fly repo seemed to handle that.

Then I needed to rename all of my existing keys. This wasn't easy: S3 doesn't have a bulk rename operation, so I ended up having to run a script that looked like this:

```bash
aws s3 --recursive mv \
  s3://my-logs-bucket/my-fly-app/2022-08-28// \
  s3://my-logs-bucket/my-fly-app//2022-08-28/

aws s3 --recursive mv \
  s3://my-logs-bucket/my-fly-app//2022-09-23// \
  s3://my-logs-bucket/my-fly-app//2022-09-23/
```
With a command for every single one of my folders that were mis-named.

Having done this, Athena started working against my bucket!

html:
<p>I was trying to run Athena queries against compressed JSON log files stored in an S3 bucket.</p>
<p>No matter what I tried, I got the following error:</p>
<blockquote>
<p>The specified key does not exist. (Service: Amazon S3; Status Code: 404; Error Code: NoSuchKey; Request ID: 4GHB3YX6DQHYTCPF; S3 Extended Request ID: 0LSAhwbo21RaZ+8/FOgKf1oh+dIkV0WO8DvtYmwQdBzddfILchiSyamFLenD8IOmrN+lDPxKTFP/7my0DKbVvw==; Proxy: null), S3 Extended Request ID: 0LSAhwbo21RaZ+8/FOgKf1oh+dIkV0WO8DvtYmwQdBzddfILchiSyamFLenD8IOmrN+lDPxKTFP/7my0DKbVvw== (Path: s3://my-logs-bucket/my-fly-app/2022-05-27/1653693921-a96e5844-02db-4e3e-9e9a-3eef00910271.log.gz)</p>
</blockquote>
<p>This is using the Fly log shipping recipe <a href="https://til.simonwillison.net/fly/fly-logs-to-s3" rel="nofollow">described here previously</a>.</p>
<p>I couldn't find any search results online for this error in the context of Athena.</p>
<p>After much head scratching... I spotted that the files in my bucket had keys that looked like this:</p>
<ul>
<li><code>my-fly-app/2022-05-27//1653693921-a96e5844-02db-4e3e-9e9a-3eef00910271.log.gz</code></li>
</ul>
<p>Note that there's a <code>//</code> after the date instead of a <code>/</code>. But in the error message from Athena the same key is identified as <code>my-fly-app/2022-05-27/1653693921-a96e5844-02db-4e3e-9e9a-3eef00910271.log.gz</code> - without the double slash.</p>
<p>It looks like Athena has a bug where it can't read files with <code>//</code> in their key!</p>
<p>The fix was to first fix my log shipper so that it wrote files without that prefix. Upgrading to the most recent version in the Fly repo seemed to handle that.</p>
<p>Then I needed to rename all of my existing keys. This wasn't easy: S3 doesn't have a bulk rename operation, so I ended up having to run a script that looked like this:</p>
<div class="highlight highlight-source-shell"><pre>aws s3 --recursive mv \
  s3://my-logs-bucket/my-fly-app/2022-08-28// \
  s3://my-logs-bucket/my-fly-app//2022-08-28/

aws s3 --recursive mv \
  s3://my-logs-bucket/my-fly-app//2022-09-23// \
  s3://my-logs-bucket/my-fly-app//2022-09-23/</pre></div>
<p>With a command for every single one of my folders that were mis-named.</p>
<p>Having done this, Athena started working against my bucket!</p>


path: html_video-preload-none.md
topic: html
title: HTML video that loads when the user clicks play
url: https://github.com/simonw/til/blob/main/html/video-preload-none.md
created: 2022-09-29T12:52:21-07:00
created_utc: 2022-09-29T19:52:21+00:00
updated: 2022-09-29T12:56:07-07:00
updated_utc: 2022-09-29T19:56:07+00:00
shot_hash: bdf495be28a7d25e04012833fcc5c584
slug: video-preload-none
body:
Today I figured out how to use the `<video>` tag to show a static thumbnail that gets replaced by the loaded video only when the user clicks play.

This is useful if you are going to show multiple video players on a page at the same time, as I do [on webvid.datasette.io](https://webvid.datasette.io/webvid/videos) ([described here](https://simonwillison.net/2022/Sep/29/webvid/)).

```html
<video controls
  width="400"
  preload="none"
  poster="https://ak.picdn.net/shutterstock/videos/172/thumb/1.jpg?ip=x480"
>
  <source
    src="https://ak.picdn.net/shutterstock/videos/172/preview/stock-footage-furnace-chimney.mp4"
    type="video/mp4"
  >
</video>
```
- `preload="none"` causes the browser to not preload the video until the user hits play
- `poster="..."` provides an image thumbnail to show in the player when it first loads

More information: [MDN guide to the Video element](https://developer.mozilla.org/en-US/docs/Web/HTML/Element/video)

html:
<p>Today I figured out how to use the <code>&lt;video&gt;</code> tag to show a static thumbnail that gets replaced by the loaded video only when the user clicks play.</p>
<p>This is useful if you are going to show multiple video players on a page at the same time, as I do <a href="https://webvid.datasette.io/webvid/videos" rel="nofollow">on webvid.datasette.io</a> (<a href="https://simonwillison.net/2022/Sep/29/webvid/" rel="nofollow">described here</a>).</p>
<div class="highlight highlight-text-html-basic"><pre><span class="pl-kos">&lt;</span><span class="pl-ent">video</span> <span class="pl-c1">controls</span>
  <span class="pl-c1">width</span>="<span class="pl-s">400</span>"
  <span class="pl-c1">preload</span>="<span class="pl-s">none</span>"
  <span class="pl-c1">poster</span>="<span class="pl-s">https://ak.picdn.net/shutterstock/videos/172/thumb/1.jpg?ip=x480</span>"
<span class="pl-kos">&gt;</span>
  <span class="pl-kos">&lt;</span><span class="pl-ent">source</span>
    <span class="pl-c1">src</span>="<span class="pl-s">https://ak.picdn.net/shutterstock/videos/172/preview/stock-footage-furnace-chimney.mp4</span>"
    <span class="pl-c1">type</span>="<span class="pl-s">video/mp4</span>"
  <span class="pl-kos">&gt;</span>
<span class="pl-kos">&lt;/</span><span class="pl-ent">video</span><span class="pl-kos">&gt;</span></pre></div>
<ul>
<li>
<code>preload="none"</code> causes the browser to not preload the video until the user hits play</li>
<li>
<code>poster="..."</code> provides an image thumbnail to show in the player when it first loads</li>
</ul>
<p>More information: <a href="https://developer.mozilla.org/en-US/docs/Web/HTML/Element/video" rel="nofollow">MDN guide to the Video element</a></p>


path: graphql_graphql-fragments.md
topic: graphql
title: GraphQL fragments
url: https://github.com/simonw/til/blob/main/graphql/graphql-fragments.md
created: 2022-09-30T15:47:20-07:00
created_utc: 2022-09-30T22:47:20+00:00
updated: 2022-09-30T15:47:20-07:00
updated_utc: 2022-09-30T22:47:20+00:00
shot_hash: 86ec72a6b67f6a369a28bfd423cb5d8e
slug: graphql-fragments
body:
One of [the scripts](https://github.com/simonw/datasette.io/blob/main/build_directory.py) that builds and deploys [datasette.io](https://datasette.io/) uses a GraphQL query to retrieve information from GitHub about the repositories used for the various Datasette tools and plugins.

That query was [very big](https://gist.github.com/simonw/80ef5a69f147741009d0edea4ef3b6ea) - over 4,000 lines long!

That's because it was shaped like this:
```graphql
{
  repo_0: repository(name: "datasette-query-history", owner: "bretwalker") {
    id
    nameWithOwner
    createdAt
    openGraphImageUrl
    usesCustomOpenGraphImage
    defaultBranchRef {
      target {
        oid
      }
    }
    repositoryTopics(first: 100) {
      totalCount
      nodes {
        topic {
          name
        }
      }
    }
    openIssueCount: issues(states: [OPEN]) {
      totalCount
    }
    closedIssueCount: issues(states: [CLOSED]) {
      totalCount
    }
    releases(last: 1) {
      totalCount
      nodes {
        tagName
      }
    }
  }
  # ...
  repo_137: repository(name: "yaml-to-sqlite", owner: "simonw") {
    id
    nameWithOwner
    createdAt
    openGraphImageUrl
    usesCustomOpenGraphImage
    defaultBranchRef {
      target {
        oid
      }
    }
    repositoryTopics(first: 100) {
      totalCount
      nodes {
        topic {
          name
        }
      }
    }
    openIssueCount: issues(states: [OPEN]) {
      totalCount
    }
    closedIssueCount: issues(states: [CLOSED]) {
      totalCount
    }
    releases(last: 1) {
      totalCount
      nodes {
        tagName
      }
    }
  }
}
```
That block was repeated for every repository - 138 in total!

I figured there was likely a way to do this more efficiently, and it turns out there is: [GraphQL fragments](https://www.apollographql.com/docs/react/data/fragments/).

Here's that example query rewritten to use fragments instead:

```graphql
fragment repoFields on Repository {
  id
  nameWithOwner
  createdAt
  openGraphImageUrl
  usesCustomOpenGraphImage
  defaultBranchRef {
    target {
      oid
    }
  }
  repositoryTopics(first: 100) {
    totalCount
    nodes {
      topic {
        name
      }
    }
  }
  openIssueCount: issues(states: [OPEN]) {
    totalCount
  }
  closedIssueCount: issues(states: [CLOSED]) {
    totalCount
  }
  releases(last: 1) {
    totalCount
    nodes {
      tagName
    }
  }
}
{
  repo_0: repository(name: "datasette-query-history", owner: "bretwalker") {
    ...repoFields
  }
  repo_137: repository(name: "yaml-to-sqlite", owner: "simonw") {
    ...repoFields
  }
}
```
Now each additional repo added to the query is only 3 extra lines of GraphQL, not 30!

html:
<p>One of <a href="https://github.com/simonw/datasette.io/blob/main/build_directory.py">the scripts</a> that builds and deploys <a href="https://datasette.io/" rel="nofollow">datasette.io</a> uses a GraphQL query to retrieve information from GitHub about the repositories used for the various Datasette tools and plugins.</p>
<p>That query was <a href="https://gist.github.com/simonw/80ef5a69f147741009d0edea4ef3b6ea">very big</a> - over 4,000 lines long!</p>
<p>That's because it was shaped like this:</p>
<div class="highlight highlight-source-graphql"><pre>{
  <span class="pl-s">repo_0</span>: <span class="pl-v">repository</span>(<span class="pl-v">name</span>: <span class="pl-s"><span class="pl-pds">"</span>datasette-query-history<span class="pl-pds">"</span></span>, <span class="pl-v">owner</span>: <span class="pl-s"><span class="pl-pds">"</span>bretwalker<span class="pl-pds">"</span></span>) {
    <span class="pl-v">id</span>
    <span class="pl-v">nameWithOwner</span>
    <span class="pl-v">createdAt</span>
    <span class="pl-v">openGraphImageUrl</span>
    <span class="pl-v">usesCustomOpenGraphImage</span>
    <span class="pl-v">defaultBranchRef</span> {
      <span class="pl-v">target</span> {
        <span class="pl-v">oid</span>
      }
    }
    <span class="pl-v">repositoryTopics</span>(<span class="pl-v">first</span>: <span class="pl-c1">100</span>) {
      <span class="pl-v">totalCount</span>
      <span class="pl-v">nodes</span> {
        <span class="pl-v">topic</span> {
          <span class="pl-v">name</span>
        }
      }
    }
    <span class="pl-s">openIssueCount</span>: <span class="pl-v">issues</span>(<span class="pl-v">states</span>: [<span class="pl-c1">OPEN</span>]) {
      <span class="pl-v">totalCount</span>
    }
    <span class="pl-s">closedIssueCount</span>: <span class="pl-v">issues</span>(<span class="pl-v">states</span>: [<span class="pl-c1">CLOSED</span>]) {
      <span class="pl-v">totalCount</span>
    }
    <span class="pl-v">releases</span>(<span class="pl-v">last</span>: <span class="pl-c1">1</span>) {
      <span class="pl-v">totalCount</span>
      <span class="pl-v">nodes</span> {
        <span class="pl-v">tagName</span>
      }
    }
  }
<span class="pl-c">  # ...</span>
  <span class="pl-s">repo_137</span>: <span class="pl-v">repository</span>(<span class="pl-v">name</span>: <span class="pl-s"><span class="pl-pds">"</span>yaml-to-sqlite<span class="pl-pds">"</span></span>, <span class="pl-v">owner</span>: <span class="pl-s"><span class="pl-pds">"</span>simonw<span class="pl-pds">"</span></span>) {
    <span class="pl-v">id</span>
    <span class="pl-v">nameWithOwner</span>
    <span class="pl-v">createdAt</span>
    <span class="pl-v">openGraphImageUrl</span>
    <span class="pl-v">usesCustomOpenGraphImage</span>
    <span class="pl-v">defaultBranchRef</span> {
      <span class="pl-v">target</span> {
        <span class="pl-v">oid</span>
      }
    }
    <span class="pl-v">repositoryTopics</span>(<span class="pl-v">first</span>: <span class="pl-c1">100</span>) {
      <span class="pl-v">totalCount</span>
      <span class="pl-v">nodes</span> {
        <span class="pl-v">topic</span> {
          <span class="pl-v">name</span>
        }
      }
    }
    <span class="pl-s">openIssueCount</span>: <span class="pl-v">issues</span>(<span class="pl-v">states</span>: [<span class="pl-c1">OPEN</span>]) {
      <span class="pl-v">totalCount</span>
    }
    <span class="pl-s">closedIssueCount</span>: <span class="pl-v">issues</span>(<span class="pl-v">states</span>: [<span class="pl-c1">CLOSED</span>]) {
      <span class="pl-v">totalCount</span>
    }
    <span class="pl-v">releases</span>(<span class="pl-v">last</span>: <span class="pl-c1">1</span>) {
      <span class="pl-v">totalCount</span>
      <span class="pl-v">nodes</span> {
        <span class="pl-v">tagName</span>
      }
    }
  }
}</pre></div>
<p>That block was repeated for every repository - 138 in total!</p>
<p>I figured there was likely a way to do this more efficiently, and it turns out there is: <a href="https://www.apollographql.com/docs/react/data/fragments/" rel="nofollow">GraphQL fragments</a>.</p>
<p>Here's that example query rewritten to use fragments instead:</p>
<div class="highlight highlight-source-graphql"><pre><span class="pl-k">fragment</span> <span class="pl-en">repoFields</span> <span class="pl-k">on</span> <span class="pl-c1">Repository</span> {
  <span class="pl-v">id</span>
  <span class="pl-v">nameWithOwner</span>
  <span class="pl-v">createdAt</span>
  <span class="pl-v">openGraphImageUrl</span>
  <span class="pl-v">usesCustomOpenGraphImage</span>
  <span class="pl-v">defaultBranchRef</span> {
    <span class="pl-v">target</span> {
      <span class="pl-v">oid</span>
    }
  }
  <span class="pl-v">repositoryTopics</span>(<span class="pl-v">first</span>: <span class="pl-c1">100</span>) {
    <span class="pl-v">totalCount</span>
    <span class="pl-v">nodes</span> {
      <span class="pl-v">topic</span> {
        <span class="pl-v">name</span>
      }
    }
  }
  <span class="pl-s">openIssueCount</span>: <span class="pl-v">issues</span>(<span class="pl-v">states</span>: [<span class="pl-c1">OPEN</span>]) {
    <span class="pl-v">totalCount</span>
  }
  <span class="pl-s">closedIssueCount</span>: <span class="pl-v">issues</span>(<span class="pl-v">states</span>: [<span class="pl-c1">CLOSED</span>]) {
    <span class="pl-v">totalCount</span>
  }
  <span class="pl-v">releases</span>(<span class="pl-v">last</span>: <span class="pl-c1">1</span>) {
    <span class="pl-v">totalCount</span>
    <span class="pl-v">nodes</span> {
      <span class="pl-v">tagName</span>
    }
  }
}
{
  <span class="pl-s">repo_0</span>: <span class="pl-v">repository</span>(<span class="pl-v">name</span>: <span class="pl-s"><span class="pl-pds">"</span>datasette-query-history<span class="pl-pds">"</span></span>, <span class="pl-v">owner</span>: <span class="pl-s"><span class="pl-pds">"</span>bretwalker<span class="pl-pds">"</span></span>) {
    <span class="pl-k">...</span><span class="pl-v">repoFields</span>
  }
  <span class="pl-s">repo_137</span>: <span class="pl-v">repository</span>(<span class="pl-v">name</span>: <span class="pl-s"><span class="pl-pds">"</span>yaml-to-sqlite<span class="pl-pds">"</span></span>, <span class="pl-v">owner</span>: <span class="pl-s"><span class="pl-pds">"</span>simonw<span class="pl-pds">"</span></span>) {
    <span class="pl-k">...</span><span class="pl-v">repoFields</span>
  }
}</pre></div>
<p>Now each additional repo added to the query is only 3 extra lines of GraphQL, not 30!</p>


path: cocktails_tommys-margarita.md
topic: cocktails
title: Tommy's Margarita
url: https://github.com/simonw/til/blob/main/cocktails/tommys-margarita.md
created: 2022-10-02T12:03:37-07:00
created_utc: 2022-10-02T19:03:37+00:00
updated: 2022-10-02T13:29:00-07:00
updated_utc: 2022-10-02T20:29:00+00:00
shot_hash: 990ce33b65e40356be0035f185b3484c
slug: tommys-margarita
body:
A few years ago I decided to learn how to make some classic cocktails. It is a very rewarding hobby.

Of all of the drinks that I have learned to make, by far the biggest crowd pleaser is the Tommy's margarita. It is surprisingly easy, and is guaranteed to delight guests. It's also a great introduction to cocktail making in general.

![A tasty looking margarita in a moderately fancy cocktail glass](https://static.simonwillison.net/static/2022/tommys-margarita.jpg)

The Tommy's margarita is a San Francisco drink. It was created by [Tommy's Mexican Restaurant](https://www.tommystequila.com/), a charming family Mexican restaurant over in the Richmond district which opened in 1965. They have one of the largest tequila collections in the USA, and they will make you a margarita from any of them.

That's the first lesson of the Tommy's margarita: no tequila is too good for it, and the better the tequila the better the drink.

Ingredients:

- Fresh limes to squeeze (about one per drink)
- A good reposado or añejo tequila
- Agave syrup

The ingredients are simple: freshly squeezed lime juice, agave syrup and a good reposado (rested) or añejo (aged) tequila. I've been using Partida Añejo and it's fantastic. I've also had great results from Patron Añejo (which is more widely available) and 1800 Reposado.

For equipment: a cocktail shaker (I like two tins, not a tin-and-a-glass), a lime squeezer, a strainer and a glass. And plenty of ice.

Also required: jiggers for measuring. Good craft cocktails require accurate measurement. Don't be tempted to eyeball.

The drink is constructed in the shaker. Always start with the cheapest ingredients - that way mistakes are less expensive.

Fill a shaker tin about a third of the way with ice. Any ice will do - save the fancy stuff for drinks that are presented with it (I serve my margaritas without ice). I use about five ice cubes per drink.

Or for a more detailed, expert guide to shaking, consult [this guide on Serious Eats](https://www.seriouseats.com/how-to-shake-a-cocktail-like-a-pro).

But... a chilled glass makes for a better drink. Glasses will chill in the freezer surprisingly quickly - dropping some in the ice tray when you start making the drink (or a few minutes beforehand) is enough to effectively chill them.

Now add the following for each drink. I usually make two at a time.

* 1 floz freshly squeezed lime juice (about one lime)
* 1/2 floz agave syrup
* 2 floz good tequila

Shake vigorously, for about 15 seconds. The trick with shaking is to let the ice do the work, by ensuring the ice is traveling the entire volume of the shaker. Then strain into a chilled glass.

You could add a salt rim to the glass before serving, but I don't do that: this margarita stands on its own. I think the salt spoils the flavor.

And that's it! Once you have the equipment together you can knock out a great drinks in just a couple of minutes.

(This TIL adapted from a team-building workshop I gave at work a few years ago.)

html:
<p>A few years ago I decided to learn how to make some classic cocktails. It is a very rewarding hobby.</p>
<p>Of all of the drinks that I have learned to make, by far the biggest crowd pleaser is the Tommy's margarita. It is surprisingly easy, and is guaranteed to delight guests. It's also a great introduction to cocktail making in general.</p>
<p><a target="_blank" rel="noopener noreferrer nofollow" href="https://camo.githubusercontent.com/4aa3d453749823f951a829c5d0404a6333abd32cd55b877f905c2e846f2de9eb/68747470733a2f2f7374617469632e73696d6f6e77696c6c69736f6e2e6e65742f7374617469632f323032322f746f6d6d79732d6d61726761726974612e6a7067"><img src="https://camo.githubusercontent.com/4aa3d453749823f951a829c5d0404a6333abd32cd55b877f905c2e846f2de9eb/68747470733a2f2f7374617469632e73696d6f6e77696c6c69736f6e2e6e65742f7374617469632f323032322f746f6d6d79732d6d61726761726974612e6a7067" alt="A tasty looking margarita in a moderately fancy cocktail glass" data-canonical-src="https://static.simonwillison.net/static/2022/tommys-margarita.jpg" style="max-width: 100%;"></a></p>
<p>The Tommy's margarita is a San Francisco drink. It was created by <a href="https://www.tommystequila.com/" rel="nofollow">Tommy's Mexican Restaurant</a>, a charming family Mexican restaurant over in the Richmond district which opened in 1965. They have one of the largest tequila collections in the USA, and they will make you a margarita from any of them.</p>
<p>That's the first lesson of the Tommy's margarita: no tequila is too good for it, and the better the tequila the better the drink.</p>
<p>Ingredients:</p>
<ul>
<li>Fresh limes to squeeze (about one per drink)</li>
<li>A good reposado or añejo tequila</li>
<li>Agave syrup</li>
</ul>
<p>The ingredients are simple: freshly squeezed lime juice, agave syrup and a good reposado (rested) or añejo (aged) tequila. I've been using Partida Añejo and it's fantastic. I've also had great results from Patron Añejo (which is more widely available) and 1800 Reposado.</p>
<p>For equipment: a cocktail shaker (I like two tins, not a tin-and-a-glass), a lime squeezer, a strainer and a glass. And plenty of ice.</p>
<p>Also required: jiggers for measuring. Good craft cocktails require accurate measurement. Don't be tempted to eyeball.</p>
<p>The drink is constructed in the shaker. Always start with the cheapest ingredients - that way mistakes are less expensive.</p>
<p>Fill a shaker tin about a third of the way with ice. Any ice will do - save the fancy stuff for drinks that are presented with it (I serve my margaritas without ice). I use about five ice cubes per drink.</p>
<p>Or for a more detailed, expert guide to shaking, consult <a href="https://www.seriouseats.com/how-to-shake-a-cocktail-like-a-pro" rel="nofollow">this guide on Serious Eats</a>.</p>
<p>But... a chilled glass makes for a better drink. Glasses will chill in the freezer surprisingly quickly - dropping some in the ice tray when you start making the drink (or a few minutes beforehand) is enough to effectively chill them.</p>
<p>Now add the following for each drink. I usually make two at a time.</p>
<ul>
<li>1 floz freshly squeezed lime juice (about one lime)</li>
<li>1/2 floz agave syrup</li>
<li>2 floz good tequila</li>
</ul>
<p>Shake vigorously, for about 15 seconds. The trick with shaking is to let the ice do the work, by ensuring the ice is traveling the entire volume of the shaker. Then strain into a chilled glass.</p>
<p>You could add a salt rim to the glass before serving, but I don't do that: this margarita stands on its own. I think the salt spoils the flavor.</p>
<p>And that's it! Once you have the equipment together you can knock out a great drinks in just a couple of minutes.</p>
<p>(This TIL adapted from a team-building workshop I gave at work a few years ago.)</p>


path: aws_athena-newline-json.md
topic: aws
title: Querying newline-delimited JSON logs using AWS Athena
url: https://github.com/simonw/til/blob/main/aws/athena-newline-json.md
created: 2022-10-06T16:35:55-07:00
created_utc: 2022-10-06T23:35:55+00:00
updated: 2022-10-07T07:51:40-07:00
updated_utc: 2022-10-07T14:51:40+00:00
shot_hash: bff09038069ba066cdbe1e832a7f1c6e
slug: athena-newline-json
body:
I've been writing my Fly logs to S3 in newline-delimited JSON format using the recipe described in [Writing Fly logs to S3](https://til.simonwillison.net/fly/fly-logs-to-s3).

I recently needed to run a search against those logs. I decided to use [AWS Athena](https://aws.amazon.com/athena/).

(Scroll to the bottom for a cunning shortcut using GPT-3.)

## The log format

My logs are shipped to S3 using [Vector](https://vector.dev/). 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:

```json
{
  "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"
}
```
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](https://docs.aws.amazon.com/athena/latest/ug/openx-json-serde.html#nested-json-serde-example) in the Athena docs helped me crack it.

Here's my eventual solution:
```sql
CREATE EXTERNAL TABLE fly_logs (
  timestamp string,
  host string,
  fly struct<
    app: struct<
      instance: string,
      name: string
    >,
    region: string
  >,
  log struct<
    level: string
  >,
  message string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
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:

```sql
select * from fly_logs limit 10;
```

![Screenshot showing the results of that query in the Athena console](https://static.simonwillison.net/static/2022/fly-logs.jpg)

A count worked too:

```sql
select count(*) from fly_logs;
```

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

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

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

```sql
select
  timestamp, host, fly.app.name, log.level, message
from
  fly_logs_two
where
  message like '%sqlite3.OperationalError: no such table: _public_tables%'
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](https://simonwillison.net/2022/Jun/5/play-with-gpt3/) I tried the following prompt:

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

{
  "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"
}
```
GPT-3 responded:
```
CREATE EXTERNAL TABLE IF NOT EXISTS logs (
  event struct<provider:string>,
  fly
  struct<app:struct<instance:string.name:string>,region:string>
  host string,
  log struct<level:string>,
  message string,
  timestamp string
)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://bucket/path/to/json/data/';
```

html:
<p>I've been writing my Fly logs to S3 in newline-delimited JSON format using the recipe described in <a href="https://til.simonwillison.net/fly/fly-logs-to-s3" rel="nofollow">Writing Fly logs to S3</a>.</p>
<p>I recently needed to run a search against those logs. I decided to use <a href="https://aws.amazon.com/athena/" rel="nofollow">AWS Athena</a>.</p>
<p>(Scroll to the bottom for a cunning shortcut using GPT-3.)</p>
<h2><a id="user-content-the-log-format" class="anchor" aria-hidden="true" href="#the-log-format"><span aria-hidden="true" class="octicon octicon-link"></span></a>The log format</h2>
<p>My logs are shipped to S3 using <a href="https://vector.dev/" rel="nofollow">Vector</a>. It actually creates a huge number of tiny gzipped files in my S3 bucket, each one representing just a small number of log lines.</p>
<p>The contents of one of those files looks like this:</p>
<p><code>{"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"}</code></p>
<p>This is newline-delimited JSON. Here's the first of those lines pretty-printed for readability:</p>
<div class="highlight highlight-source-json"><pre>{
  <span class="pl-ent">"event"</span>: {
    <span class="pl-ent">"provider"</span>: <span class="pl-s"><span class="pl-pds">"</span>app<span class="pl-pds">"</span></span>
  },
  <span class="pl-ent">"fly"</span>: {
    <span class="pl-ent">"app"</span>: {
      <span class="pl-ent">"instance"</span>: <span class="pl-s"><span class="pl-pds">"</span>0e286551c30586<span class="pl-pds">"</span></span>,
      <span class="pl-ent">"name"</span>: <span class="pl-s"><span class="pl-pds">"</span>dc-team-52-simon-46d213<span class="pl-pds">"</span></span>
    },
    <span class="pl-ent">"region"</span>: <span class="pl-s"><span class="pl-pds">"</span>sjc<span class="pl-pds">"</span></span>
  },
  <span class="pl-ent">"host"</span>: <span class="pl-s"><span class="pl-pds">"</span>0ad1<span class="pl-pds">"</span></span>,
  <span class="pl-ent">"log"</span>: {
    <span class="pl-ent">"level"</span>: <span class="pl-s"><span class="pl-pds">"</span>info<span class="pl-pds">"</span></span>
  },
  <span class="pl-ent">"message"</span>: <span class="pl-s"><span class="pl-pds">"</span>subprocess exited, litestream shutting down<span class="pl-pds">"</span></span>,
  <span class="pl-ent">"timestamp"</span>: <span class="pl-s"><span class="pl-pds">"</span>2022-09-27T20:34:37.252022967Z<span class="pl-pds">"</span></span>
}</pre></div>
<p>The challenge: how to teach Athena how to turn those files into a table I can run queries against?</p>
<h2><a id="user-content-defining-an-athena-table" class="anchor" aria-hidden="true" href="#defining-an-athena-table"><span aria-hidden="true" class="octicon octicon-link"></span></a>Defining an Athena table</h2>
<p>This was by far the hardest thing to figure out.</p>
<p>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.</p>
<p>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.</p>
<p>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.</p>
<p>In the end, <a href="https://docs.aws.amazon.com/athena/latest/ug/openx-json-serde.html#nested-json-serde-example" rel="nofollow">this example</a> in the Athena docs helped me crack it.</p>
<p>Here's my eventual solution:</p>
<div class="highlight highlight-source-sql"><pre>CREATE EXTERNAL TABLE fly_logs (
  <span class="pl-k">timestamp</span> string,
  host string,
  fly struct<span class="pl-k">&lt;</span>
    app: struct<span class="pl-k">&lt;</span>
      instance: string,
      name: string
    <span class="pl-k">&gt;</span>,
    region: string
  <span class="pl-k">&gt;</span>,
  log struct<span class="pl-k">&lt;</span>
    level: string
  <span class="pl-k">&gt;</span>,
  message string
)
ROW FORMAT SERDE <span class="pl-s"><span class="pl-pds">'</span>org.openx.data.jsonserde.JsonSerDe<span class="pl-pds">'</span></span>
STORED <span class="pl-k">AS</span> INPUTFORMAT 
  <span class="pl-s"><span class="pl-pds">'</span>org.apache.hadoop.mapred.TextInputFormat<span class="pl-pds">'</span></span> 
OUTPUTFORMAT 
  <span class="pl-s"><span class="pl-pds">'</span>org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat<span class="pl-pds">'</span></span>
LOCATION <span class="pl-s"><span class="pl-pds">'</span>s3://datasette-cloud-fly-logs/<span class="pl-pds">'</span></span></pre></div>
<p>I ran that in the Athena query editor at <a href="https://us-east-1.console.aws.amazon.com/athena/home?region=us-east-1#/query-editor" rel="nofollow">https://us-east-1.console.aws.amazon.com/athena/home?region=us-east-1#/query-editor</a></p>
<p>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.</p>
<p>Because my logs include nested objects, I had to use <code>struct&lt; ... &gt;</code> syntax to define some of the columns.</p>
<p>Confusingly, at the base of the column definition columns are defined using <code>column_name type</code> - but within a <code>struct&lt;</code> that changes to <code>column_name: type</code>. If you mix these formats up you get a confusing error message like this one:</p>
<blockquote>
<p><code>FAILED: ParseException line 1:164 missing : at 'string' near '&lt;EOF&gt;'</code></p>
</blockquote>
<h2><a id="user-content-querying-the-table" class="anchor" aria-hidden="true" href="#querying-the-table"><span aria-hidden="true" class="octicon octicon-link"></span></a>Querying the table</h2>
<p>Once the table is defined, running queries is pretty easy. I started with this one:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> <span class="pl-k">*</span> <span class="pl-k">from</span> fly_logs <span class="pl-k">limit</span> <span class="pl-c1">10</span>;</pre></div>
<p><a target="_blank" rel="noopener noreferrer nofollow" href="https://camo.githubusercontent.com/3b43d42114c33ab313ed409fb638a0fc1cc2967d40d0afcfe8a354120c5e7ff0/68747470733a2f2f7374617469632e73696d6f6e77696c6c69736f6e2e6e65742f7374617469632f323032322f666c792d6c6f67732e6a7067"><img src="https://camo.githubusercontent.com/3b43d42114c33ab313ed409fb638a0fc1cc2967d40d0afcfe8a354120c5e7ff0/68747470733a2f2f7374617469632e73696d6f6e77696c6c69736f6e2e6e65742f7374617469632f323032322f666c792d6c6f67732e6a7067" alt="Screenshot showing the results of that query in the Athena console" data-canonical-src="https://static.simonwillison.net/static/2022/fly-logs.jpg" style="max-width: 100%;"></a></p>
<p>A count worked too:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> <span class="pl-c1">count</span>(<span class="pl-k">*</span>) <span class="pl-k">from</span> fly_logs;</pre></div>
<p>To query nested objects, I used <code>.</code> syntax like this:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> <span class="pl-k">timestamp</span>, host, <span class="pl-c1">fly</span>.<span class="pl-c1">app</span>.name, <span class="pl-c1">log</span>.<span class="pl-c1">level</span>, message
<span class="pl-k">from</span> fly_logs_two <span class="pl-k">limit</span> <span class="pl-c1">10</span>;</pre></div>
<p>Finally, to search for a specific error message in the logs I ran this query:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span>
  <span class="pl-k">timestamp</span>, host, <span class="pl-c1">fly</span>.<span class="pl-c1">app</span>.name, <span class="pl-c1">log</span>.<span class="pl-c1">level</span>, message
<span class="pl-k">from</span>
  fly_logs_two
<span class="pl-k">where</span>
  message <span class="pl-k">like</span> <span class="pl-s"><span class="pl-pds">'</span>%sqlite3.OperationalError: no such table: _public_tables%<span class="pl-pds">'</span></span>
<span class="pl-k">limit</span> <span class="pl-c1">100</span>;</pre></div>
<h2><a id="user-content-generating-the-configuration-using-gpt-3" class="anchor" aria-hidden="true" href="#generating-the-configuration-using-gpt-3"><span aria-hidden="true" class="octicon octicon-link"></span></a>Generating the configuration using GPT-3</h2>
<p>Manually converting that JSON into a <code>CREATE EXTERNAL TABLE</code> definition is tedious and error-prone.</p>
<p>It turns out you can instead use the OpenAI GPT-3 language model to do that work for you!</p>
<p>Using <a href="https://simonwillison.net/2022/Jun/5/play-with-gpt3/" rel="nofollow">the playground</a> I tried the following prompt:</p>
<pre><code>write an AWS Athena create table statement for querying this JSON data:

{
  "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"
}
</code></pre>
<p>GPT-3 responded:</p>
<pre><code>CREATE EXTERNAL TABLE IF NOT EXISTS logs (
  event struct&lt;provider:string&gt;,
  fly
  struct&lt;app:struct&lt;instance:string.name:string&gt;,region:string&gt;
  host string,
  log struct&lt;level:string&gt;,
  message string,
  timestamp string
)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://bucket/path/to/json/data/';
</code></pre>


