Search code examples
javascriptgoogle-bigqueryuser-defined-functionsgoogle-geocoding-apiudf

Making API call as part of UDF in BigQuery - possible?

I'm wondering if it would be possible to make a api call to the google maps geocoding api within a UDF in BigQuery?

I have Google analytics geo fields such as 

{
"geoNetwork_continent": "Europe",
"geoNetwork_subContinent": "Eastern Europe",
"geoNetwork_country": "Russia",
"geoNetwork_region": "Novosibirsk Oblast",
"geoNetwork_metro": "(not set)"
},

And would like to make calls to:

https://maps.googleapis.com/maps/api/geocode/json?address=1600+Amphitheatre+Parkway,+Mountain+View,+CA&key=XXXX

Just wondering if i'd be able to use javascript within the UDF to make an api call for each row in BigQuery.

Would be very powerful and avoid me having to do it in R or Python.

Very much a novice on UDF's so just wanted to ask to see if what i'm suggesting is even possible or not (wondering if external api calls in UDF might be some sort of security concern for whatever reason or if just technically not possible).

Any advice much appreciated.

Solution

  • You can now call remote API using remote functions:

    https://cloud.google.com/bigquery/docs/reference/standard-sql/remote-functions

    However, before using this with maps geocoding APIs you have in the example, please check their usage policies, especially the caching and storage policies:

    https://developers.google.com/maps/documentation/geocoding/policies#pre-fetching,-caching,-or-storage-of-content