How to save PageSpeed Insights results to Google BigQuery?

Question

I have a table of urls in BigQuery on which I would like to perform a check of PageSpeed Insights score (or even include the whole response from API in the BigQuery table). I tried to use UDFs for this purpuse, but for now no luck with this. Is there a way of getting the response from:

https://www.googleapis.com/pagespeedonline/v2/runPagespeed?url=https://google.com/&strategy=mobile&key=yourAPIKey

to BigQuery table?

Answer 1

You cannot make API calls from BigQuery UDFs for several reasons. See here for more details about that.

Although there are a few ways to achieve what you want to do, I'd recommend using a Cloud Dataflow pipeline:

1. Read your BigQuery using BigQueryIO.Read source
2. In your ParDo, call the API you want
3. Write your results back to BigQuery using the BigQueryIO.Write sink