Revitalize the collection of PUDL usage metrics

[jdangerx]

Overview

In order to better trace the development of PUDL, the success of our outreach efforts and the effects of our new Superset instance, we need to revitalize the pudl-usage-metrics repository and collect usage metrics from the following sources:

• S3
• Datasette (until retirement)
• Superset
• Zenodo
• Kaggle
• Github

We're interested in the following types of metrics to start:

• how many different IPs are accessing our data via each method?
• what tables and versions of the data are people accessing?

As a first step, we should be able to ETL the logs and metrics from each of these data sources and get a weekly summary that we can look at. As a second step, we want to hook up our metrics to a private Superset dataset and build some dashboards for easy interpretation.

Out of scope

• Google Analytics - we already have a dashboard we can use to look at these metrics!
• Migrating out of CloudSQL to another storage backend
• Setting up a permanent dagster server with sensors and schedules

Infrastructure

The pudl-usage-metrics repository hasn't been maintained for a while. We'll need to get it up to speed to support this development work.

Infrastructure tasks

Beta Give feedback

1. Update packages Move from setup.py to pyproject.toml, switch to using ruff and tox, bump python version and update pre-commit hooks #119
2. The repo was created with an older version of the cheshire template. Update the packaging methodology and pre-commit hooks. Replace setup.py with pyproject.toml and conda for mamba. Move from setup.py to pyproject.toml, switch to using ruff and tox, bump python version and update pre-commit hooks #119
3. Update Python version to 3.12 Move from setup.py to pyproject.toml, switch to using ruff and tox, bump python version and update pre-commit hooks #119
4. Add pudl usage metrics gcp infrastructure pudl#3841
   0 of 7
   cloud superset +2

S3 Logs

Our main programmatic access method. S3 logs are currently mirrored to a GCS bucket. Each request produces one log.

S3 Usage Metrics

Beta Give feedback

1. If config to mirror S3 bucket to GCS is in S3, start with getting existing code to run locally on latest S3 logs
2. Put in devtools and run using a GHA weekly? Or update the usage metrics repo? Decide
3. Clean up pudl-usage-metrics repo: see Revamp pudl-usage-metrics repo #118
4. Configure maximum run concurrency
5. Update existing code and add to pudl-usage-metrics repo
6. Create a Dagster ETL pipeline for S3 usage metrics #140
   8 of 8
   s3 +1
   
7. Automate local S3 ETL in cloud #147
   13 of 13
   github_actions s3 +2
   

Datasette

While we're planning to retire Datasette, it'd still be helpful to understand the history of usage and to see how usage changes during the transition to Superset. The log ETL that exists in pudl-usage-metrics hasn't worked since the transition to fly.io.

fly.io currently doesn't retain logs for a long time so we need to use the https://github.com/superfly/fly-log-shipper fly log shipper to send logs to S3.

It also doesn't log out the IP address of the datasette requests - guessing that the IP currently logged is the load balancer IP. Usually the load balancer includes some sort of "forwarded this request from original IP" information in the headers, so we should be able to extract that somehow. Seems like we can't configure the datasette access logs so we'll need to set it up behind something we can configure, like NGINX.

Datasette Tasks

Beta Give feedback

1. Ship datasette metrics from fly.io using fly-log-shipper #148
   0 of 2
   datasette +1
   
2. write a script to parse logs into timestamp/IP/resource records
3. compare & contrast to old Datasette logs
4. Update datasette ETL to use assets rather than ops, and to conform to the format of the S3 processing

Superset

We're slowly deploying a new data visualization tool! It'll give us a lot of usage information, which we should process and handle. See https://engineering.hometogo.com/monitor-superset-usage-via-superset-c7f9fba79525 for a template.

Superset Tasks

Beta Give feedback

1. Figure out how Superset logs usage metrics and how long usage metrics are retained for.
2. If retention timeline is short, figure out strategy for archiving logs periodically.
3. See https://github.com/hometogo/hometogo-data-code-snippets/tree/main/superset/usage_dashboard

Zenodo

Zenodo API calls return stats on views and downloads for a record at a particular point in time. We should periodically (weekly?) collect stats on all of our archives on Zenodo and archive them for later processing.

Zenodo Tasks

Beta Give feedback

1. Write a script to collect stats for all Zenodo archives and save them to a GCS bucket
2. Write an ETL script to combine and process these archived records

Kaggle

Kaggle collects data on views and downloads through its dataset metadata JSON, accessible through the api.metadata_get(KAGGLE_OWNER, KAGGLE_DATASET) call from the KaggleApi. Like Zenodo, this is data reported at the time of query, so we'll need to archive these metrics to see changes over time.

Kaggle

Beta Give feedback

1. Write a GHA to archive the metadata file daily into a Kaggle Metrics GCS bucket
2. ETL this data, keeping only the relevant metrics fields: ["info.usabilityRatingNullable", "info.totalViews", "info.totalVotes", "info.totalDownloads"]
3. Investigate whether it is possible to pull kernel/notebook metrics as well

Github

Migrate our Github metrics archiving from the business repository, and add it to our ETL.

Github

Beta Give feedback

1. Move Github metric archive out of business repo #161
   2 of 4
   github_actions +1
   
2. ETL this data, keeping only the relevant metrics fields

Reporting and Visualization

Once the data is processed, we'll need to analyze and report on metrics of interest in order to interpret changes in usage and highlight trends of interest.

Some interesting references for Superset usage dashboards can be found here.

Reporting and Summaries

Beta Give feedback

1. Define usage metrics of interest across data sources
2. Write a script to output a weekly usage summary
3. Write a GHA to produce and upload this weekly summary to GCS every week
4. Hook up the database to Superset
5. Create some dashboards

[jdangerx]

We should timebox this to 5h and prioritize getting S3 parquet logs because of the possibility of replacing datasette altogether.

[bendnorman]

I think revamping the pudl-usage-metrics repo will take some work. Maybe we can simplify the task by "disabling" the current metrics in the ETL:

• old datasette logs from the Cloud Run days which are probably still helpful for us
• intake catalog logs which we never really utilized

and integrate just the s3 logs since those are the highest value / most relevant rn. I opened a PR with my janky s3 log download script and notebook.

The ETl generally works like this:

1. Pull some logs from GCS
2. Does some cleaning with pandas and dagster
3. Load the cleaned logs into Cloud SQL postgres

I have a github action that processes the latest logs and loads them to Cloud SQL. Cloud SQL is kind of expensive so it might make more sense to use BQ.

I think it makes sense to create a quick design doc for the usage metrics revamp, given there is a lot we could do.

[e-belfer]

I've updated this issue to be an epic reflecting all our logs and possible workflows, and have tried to structure out smaller steps in the tasklists.

[zaneselvans]

@e-belfer was this issue supposed to get closed by #162?

[e-belfer]

Definitely not!