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

Question

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.

Au

Answer 1

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

Succory

Answer 2

Making network calls from your UDF is not permitted. This is due to both security considerations, as well as the fact that queries are (heavily) sharded for execution in BigQuery. Making per-record outbound network connections from the query nodes would DDOS many targets ;)

Rearmost

Answer 3

I think, currently, API calls within UDF are subject of BigQuery UDF limitations
https://cloud.google.com/bigquery/user-defined-functions#limitations

Lal