Add live demos

[simonw]

I'm tempted to pull a bunch of different example repos on a schedule and bundle them into the same demo instance.

Could have a recipes.md documentation page that shares the same demos and shows how they were built, using cog somehow.

[simonw]

Maybe cog generates the workflow YAML and the recipes markdown from the same source somehow?

[simonw]

I'm going to put the demos in the README itself, so that they end up on https://datasette.io/ and PyPI.

[simonw]

I have 19 git scraping repos of my own: https://github.com/search?q=topic%3Agit-scraping+user%3Asimonw&type=repositories

Options for demos:

• Not FARA, it's too hard to explain (would be good for CSV though)
• Definitely CA fires https://github.com/simonw/ca-fires-history
• Maybe PGE outages? https://github.com/simonw/pge-outages
• Maybe https://github.com/simonw/cdc-vaccination-history
• https://github.com/simonw/sf-tree-history would be great as a CSV example

[simonw]

And there are repos by other people:

• https://github.com/adolph/getIncidentsGit would be amazing (City of Houston)
• Oh wow look at https://github.com/outages it has a whole bunch!
• https://github.com/dbreunig/511-events-history is tempting, https://511.org/ is SF Bay Area

[simonw]

Here's the 511 demo recipe:

git-history file 511.db events.json --id id --convert '
data = json.loads(content)
if data.get("error"):
    # {"code": 500, "error": "Error accessing remote data..."}
    return
for event in data["Events"]:
    event["id"] = event["extension"]["event-reference"]["event-identifier"]
    # Remove noisy updated timestamp
    del event["updated"]
    # Drop extension block entirely
    del event["extension"]
    # "schedule" block is noisy but not interesting
    del event["schedule"]
    # Flatten nested subtypes
    event["event_subtypes"] = event["event_subtypes"]["event_subtype"]
    if not isinstance(event["event_subtypes"], list):
        event["event_subtypes"] = [event["event_subtypes"]]
    yield event
' --wal

[simonw]

Here's the CA fires demo:

ca-fires-history % git-history file fires.db incidents.json --id UniqueId --convert 'json.loads(content)["Incidents"]'

Can graph the acres burned for a specific fire using this:

/fires/item_version_detail?_item__exact=197&AcresBurned__notblank=1#g.mark=bar&g.x_column=_commit_at&g.x_type=temporal&g.y_column=AcresBurned&g.y_type=quantitative

[simonw]

It's a shame both of these demos need a --convert - what's a good JSON one that doesn't?

[simonw]

PGE is a good demo for that:

git-history file pge.db pge-outages.json --id outageNumber --branch master --wal --ignore lastUpdateTime

Shows --branch and --ignore as well.

Does take about an hour to generate though!

[simonw]

Maybe FARA is good - I can link to the blog entry that explains it, and it's CSV, and the DB is probably quite small. Can maybe find Manafort in it as a demo?

[simonw]

FARA is no good because the FARA_All_Registrants.csv has weird errors that need to be worked around.

[simonw]

https://github.com/simonw/neededge-history/blob/main/v1.xml is interesting. It showed me a bug in the --import command - after applying a fix this worked:

git-history file neededge.db v1.xml --id url --convert '
tree = xml.etree.ElementTree.fromstring(content)
return [site.attrib for site in tree.iter("site")]
' --import xml.etree.ElementTree

Actually it's not interesting, because the data in it never changes - the only column you get is url and there is never a version 2 of anything.

[simonw]

I'm going to try for sf-tree-history since it's the Git scraping demo I enjoy showing people the most, and it might work for a CSV example.

It's only 247 commits, but the first one takes a LONG time because it has ~185,000 records in it.

[simonw]

Yeah I'm going to do the trees one for the CSV example.

So the examples are:

• PG&E because it's interesting and doesn't need convert
• ca-fires because it's quick
• sf-tree-history because it's CSV
• 511 because it's data someone else collected and it's an interesting complex convert example

[simonw]

... but the trees export breaks at simonw/sf-tree-history@3fb63a9 - which is why I added this feature:

• --start-at and --start-after options #26

  [#####-------------------------------]  40/247   16%  01:12:33Error: Commit: 3fb63a99dfab8a75c83d341c67afc9abf484e0c4 - every item must have the --id keys. These items did not:
[
    {
        "1": "9",
        "DPW Maintained": "DPW Maintained",
        "Pinus radiata :: Monterey Pine": "Palm (unknown Genus) :: Palm Spp",
        "10 10th Ave": "97 12th St",
        "Median : Cutout": "Median : Cutout",
        "Tree": "Tree",
        "DPW": "DPW",
        "": "",
        "3": "20",
        "16": "3X3",
        "5992719.40718": "6007166.5822125",
        "2114869.71245": "2109668.7679164",
        "37.7866097734767": "37.7731533608049",
        "-122.468982710496": "-122.418629924651",
        "(37.7866097734767, -122.468982710496)": "(37.7731533608049, -122.418629924651)",
        "11": "19",
        "6": "2",
        "54": "28853"
    },

[simonw]

I'm going to stash the intermediary .db files in an S3 bucket and re-fetch them when each action runs.

[simonw]

Had to use --dialect excel with the trees example because of this strange issue: https://gist.github.com/simonw/f715433af8f8bf699d016ff19d7f542a

[simonw]

Here's the tree recipe that finally worked:

git-history file tree-history.db Street_Tree_List.csv \
  --id TreeID \
  --csv \
  --start-after 3fb63a99dfab8a75c83d341c67afc9abf484e0c4 \
  --dialect excel

tree-history.db was 591M after a sqlite-utils vacuum tree-history.db.

[simonw]

Most exciting demo is still likely to be this one: https://github.com/adolph/getIncidentsGit

git-history file houston.db incidents.json \
  --convert 'return json.loads(content)["ActiveIncidentDataTable"]' \
  --id CallTimeOpened --id Address --id CrossStreet \
  --ignore-duplicate-ids --wal

Could do with a couple of extra conversions, in particular the XCoord and YCoord look like this and would work better as a latitude/longitude:

-95373150,29752989

Also would be neat if columns like IncidentType could be extracted, see:

• Ability to extract columns from item and item_version #41

[simonw]

My big concern at this point is size: three of my four preferred demos are multiple hundreds of MB. I think they're going to work on Cloud Run but it's towards the upper limit of what I'm happy to host there.

[simonw]

Deploying the first version of this with:

datasette publish cloudrun \
  --service git-history-demos \
  /tmp/pge-outages/pge.db \
  /tmp/511-events-history/511.db \
  /tmp/ca-fires-history/fires.db \
  --install datasette-block-robots \
  --install datasette-remote-metadata \
  -m demos/metadata.yml \
  --memory 4Gi

Creating temporary tarball archive of 5 file(s) totalling 656.2 MiB before compression.

So big but maybe not too big?

[simonw]

That deployed fine and it seems snappy enough: https://git-history-demos-j7hipcg4aq-uc.a.run.app/

[simonw]

That change to the metadata.yml was picked up on https://git-history-demos-j7hipcg4aq-uc.a.run.app/pge without needing to re-deploy thanks to datasette-remote-metadata!

[simonw]

I'll configure git-history-demos.datasette.io for this.

[simonw]

https://git-history-demos.datasette.io/ is working now.

[simonw]

I added --public to s3-credentials in https://github.com/simonw/s3-credentials/releases/tag/0.8 so now I can create an S3 bucket to store the built SQLite database files in between GitHub Actions runs.

[simonw]

~ % s3-credentials create git-history-demos --public --create-bucket
Created bucket: git-history-demos
Attached bucket policy allowing public access
Created  user: 's3.read-write.git-history-demos' with permissions boundary: 'arn:aws:iam::aws:policy/AmazonS3FullAccess'
Attached policy s3.read-write.git-history-demos to user s3.read-write.git-history-demos
Created access key for user: s3.read-write.git-history-demos
{
    "UserName": "s3.read-write.git-history-demos",
    "AccessKeyId": "AKIAWXFXAIOZOLWKY4FP",
    "Status": "Active",
    "SecretAccessKey": “…”,
    "CreateDate": "2021-12-07 07:11:48+00:00"
}

Got the bucket and the credentials now. Adding those credentials to this GitHub repository as secrets AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY.

[simonw]

https://git-history-demos.datasette.io/ was just successfully deployed using a manual trigger of the new GitHub Actions workflow! https://github.com/simonw/git-history/runs/4441234271?check_suite_focus=true

[simonw]

I'm not convinced datasette-remote-metadata is working correctly: https://git-history-demos.datasette.io/-/metadata

UPDATE: no it was working fine, see notes on simonw/datasette-remote-metadata#3

[simonw]

Add these to the README and I can close the issue.

[simonw]

git-history-demos.datasette.io hosts three example databases created using this tool:

• pge-outages shows a history of PG&E (the electricity supplier) outages, using data collected in simonw/pge-outages
• ca-fires shows a history of fires in California reported on fire.ca.gov/incidents, from data in simonw/ca-fires-history
• sf-bay-511 has records of San Francisco Bay Area traffic and transit incident data from 511.org, collected in dbreunig/511-events-history

[simonw]

Wrote up part of this as a TIL: https://til.simonwillison.net/github-actions/s3-bucket-github-actions

[simonw]

Also this blog entry: https://simonwillison.net/2021/Dec/7/git-history/