aster.cloud aster.cloud
  • /
  • Platforms
    • Public Cloud
    • On-Premise
    • Hybrid Cloud
    • Data
  • Architecture
    • Design
    • Solutions
    • Enterprise
  • Engineering
    • Automation
    • Software Engineering
    • Project Management
    • DevOps
  • Programming
    • Learning
  • Tools
  • About
  • /
  • Platforms
    • Public Cloud
    • On-Premise
    • Hybrid Cloud
    • Data
  • Architecture
    • Design
    • Solutions
    • Enterprise
  • Engineering
    • Automation
    • Software Engineering
    • Project Management
    • DevOps
  • Programming
    • Learning
  • Tools
  • About
aster.cloud aster.cloud
  • /
  • Platforms
    • Public Cloud
    • On-Premise
    • Hybrid Cloud
    • Data
  • Architecture
    • Design
    • Solutions
    • Enterprise
  • Engineering
    • Automation
    • Software Engineering
    • Project Management
    • DevOps
  • Programming
    • Learning
  • Tools
  • About
  • Data

Extending BigQuery Functions Beyond SQL With Remote Functions, Now In Preview

  • aster.cloud
  • May 23, 2022
  • 4 minute read

Today we are announcing the Preview of BigQuery Remote Functions. Remote Functions are user-defined functions (UDF) that let you extend BigQuery SQL with your own custom code, written and hosted in Cloud Functions, Google Cloud’s scalable pay-as-you-go functions as a service.  A remote UDF accepts columns from BigQuery as input, performs actions on that input using a Cloud Function, and returns the result of those actions as a value in the query result. With Remote Functions, you can now write custom SQL functions in Node.js, Python, Go, Java, NET, Ruby, or PHP. This ability means you can personalize BigQuery for your company, leverage the same management and permission models without having to manage a server.

 


Partner with aster.cloud
for your next big idea.
Let us know here.



From our partners:

CITI.IO :: Business. Institutions. Society. Global Political Economy.
CYBERPOGO.COM :: For the Arts, Sciences, and Technology.
DADAHACKS.COM :: Parenting For The Rest Of Us.
ZEDISTA.COM :: Entertainment. Sports. Culture. Escape.
TAKUMAKU.COM :: For The Hearth And Home.
ASTER.CLOUD :: From The Cloud And Beyond.
LIWAIWAI.COM :: Intelligence, Inside and Outside.
GLOBALCLOUDPLATFORMS.COM :: For The World's Computing Needs.
FIREGULAMAN.COM :: For The Fire In The Belly Of The Coder.
ASTERCASTER.COM :: Supra Astra. Beyond The Stars.
BARTDAY.COM :: Prosperity For Everyone.

In what type of situations could you use remote functions?

Before today, BigQuery customers had the ability to create user defined functions or UDFs in either SQL or javascript that ran entirely within BigQuery. While these functions are performant and fully managed from within BigQuery, customers expressed a desire to extend BigQuery UDFs with their own external code. Here are some examples of what they have asked for:

  • Security and Compliance: Use data encryption and tokenization services from the Google Cloud security ecosystem for external encryption and de-identification. We’ve already started working with key partners like Protegrity and CyberRes Voltage on using these external functions as a mechanism to merge BigQuery into their security platform, which will help our mutual customers address strict compliance controls.
  • Real Time APIs: Enrich BigQuery data using external APIs to obtain the latest stock price data, weather updates, or geocoding information.
  • Code Migration: Migrate legacy UDFs or other procedural functions written in Node.js, Python, Go, Java, .NET, Ruby or PHP.
  • Data Science: Encapsulate complex business logic and score BigQuery datasets by calling models hosted in Vertex AI or other Machine Learning platforms.
Read More  The Business Value Of Cloud SQL: How Companies Speed Up Deployments, Lower Costs And Boost Agility

Getting Started

Let’s go through the steps to use a BigQuery remote UDF.

Setup the BigQuery Connection:
1. Create a BigQuery Connection 
a. You may need to enable the BigQuery Connection API

Deploy a Cloud Function with your code:
1. Deploying your Cloud Function
a. You may need to enable Cloud Functions API
b. You may need to enable Cloud Build APIs

2. Grant the BigQuery Connection service account access to the Cloud Function
a. One way you can find the service account is by using the bq cli show command

 

bq show --location=US --connection  $CONNECTION_NAME

 

Define the BigQuery remote UDF:
1. Create the remote UDFs definition within BigQuery 
a. One way to find the endpoint name is to use the gCloud cli functions describe command

 

gcloud functions describe $FUNCTION_NAME

 

Use the BigQuery remote UDF in SQL:
1. Write a SQL statement as you would calling a UDF 
2. Get your results!

How remote functions can help you with common data tasks

Let’s take a look at some examples of how using BigQuery with remote UDFs can help accelerate development and enhance data processing and analysis.

Encryption and Decryption

As an example, let’s create a simple custom encryption and decryption Cloud Function in Python.

The encryption function can receive the data and return an encrypted base64 encoded string.

In the same Cloud Function, the decryption function can receive an encrypted base64 encoded string and return the decrypted string. A data engineer would be able to enable this functionality in BigQuery.

The Cloud Function receives the data and determines which function you want to invoke. The data is received as an HTTP request. The additional userDefinedContext fields allow you to send additional pieces of data to the Cloud Function.

Read More  Top 5 Takeaways From Data Cloud Summit ‘22

 

def remote_security(request):
   request_json = request.get_json()
   mode = request_json['userDefinedContext']['mode']
   calls = request_json['calls']
   not_extremely_secure_key = 'not_really_secure'
   if mode == "encryption":
       return encryption(calls, not_extremely_secure_key)
   elif mode == "decryption":
       return decryption(calls, not_extremely_secure_key)
   return json.dumps({"Error in Request": request_json}), 400

 

The result is returned in a specific JSON formatted response that is returned to BigQuery to be parsed.

 

def encryption(calls,not_extremely_secure_key):
   return_value = []
   for call in calls:
       data = call[0].encode('utf-8')
       cipher = AES.new(
           not_extremely_secure_key.encode('utf-8')[:16],
           AES.MODE_EAX
       )
       cipher_text = cipher.encrypt(data)
       return_value.append(
           str(base64.b64encode(cipher.nonce + cipher_text))[2:-1]
       )
   return json.dumps({"replies": return_value})

 

This Python code is deployed to Cloud Functions where it awaits to be invoked.

Let’s add the User Defined Function to BigQuery so we can invoke it from a SQL statement. The additional user_defined_context is what is sent to Cloud Functions as additional context in the request payload so you can use multiple remote functions mapped to one endpoint.

 

CREATE OR REPLACE FUNCTION `<project-id>.demo.decryption` (x STRING) RETURNS STRING REMOTE WITH CONNECTION `<project-id>.us.my-bq-cf-connection` OPTIONS (endpoint = 'https://us-central1-<project-id>.cloudfunctions.net/remote_security', user_defined_context = [("mode","decryption")])

 

Once we’ve created our functions, users with the right IAM permissions can use them in SQL on BigQuery.

 

If you’re new to Cloud Functions, be aware that there are very minimal delays known as “cold starts”.

The neat thing is you can call APIs as well, which is how our partners at Protegrity and Voltage enable their platforms to perform encryption and decryption of BigQuery data.

 

Calling APIs to enrich your data

Users, such as data analysts, can use the user defined functions created easily without needing other tools and moving the data out of BigQuery.

You can enrich your dataset with many more APIs, for example, the Google Cloud Natural Language API to analyze sentiment on your text without having to use another tool.

Read More  Helping European Education Providers Navigate Privacy Assessments

 

def call_nlp(calls):
   return_value = []
   client = language_v1.LanguageServiceClient()
   for call in calls:
       text = call[0]
       document = language_v1.Document(
           content=text, type_=language_v1.Document.Type.PLAIN_TEXT
       )
       sentiment = client.analyze_sentiment(
           request={"document": document}
       ).document_sentiment
       return_value.append(str(sentiment.score))
   return_json = json.dumps({"replies": return_value})
   return return_json

 

Once the Cloud Function is deployed and the remote UDF definition is created on BigQuery, you are able to invoke the NLP API and return the data from it for use in your queries.

 

Custom Vertex AI endpoint

Data Scientists can integrate Vertex AI endpoints and other APIs, all from the SQL console for custom models.

Remember, the remote UDFs are meant for scalar executions.

You are able to deploy a model to a Vertex AI endpoint, which is another API, and then call that endpoint from Cloud Functions.

 

def predict_classification(calls):
   # Vertex AI endpoint details
   client = aiplatform.gapic.PredictionServiceClient(client_options=client_options)
   endpoint = client.endpoint_path(
       project=project, location=location, endpoint=endpoint_id
   )
   # Call the endpoint for each
   for call in calls:
       content = call[0]
       instance = predict.instance.TextClassificationPredictionInstance(
           content=content,
       ).to_value()
       instances = [instance]
       parameters_dict = {}
       parameters = json_format.ParseDict(parameters_dict, Value())
       response = client.predict(
           endpoint=endpoint, instances=instances, parameters=parameters
       )

 

Try it out today

Try out the BigQuery remote UDFs today!

 

 

By: Christopher Crosbie (Product Manager) and Wei Hsia (Developer Advocate)
Source: Google Cloud Blog


For enquiries, product placements, sponsorships, and collaborations, connect with us at [email protected]. We'd love to hear from you!

Our humans need coffee too! Your support is highly appreciated, thank you!

aster.cloud

Related Topics
  • BigQuery;
  • Cloud Function
  • Data Analytics
  • Encryption
  • Google Cloud
  • Remote Functions
  • SQL
  • Tutorial
  • Vertex AI
You May Also Like
Getting things done makes her feel amazing
View Post
  • Computing
  • Data
  • Featured
  • Learning
  • Tech
  • Technology

Nurturing Minds in the Digital Revolution

  • April 25, 2025
View Post
  • Data
  • Engineering

Hiding in Plain Site: Attackers Sneaking Malware into Images on Websites

  • January 16, 2025
IBM and Ferrari Premium Partner
View Post
  • Data
  • Engineering

IBM Selected as Official Fan Engagement and Data Analytics Partner for Scuderia Ferrari HP

  • November 7, 2024
dotlah-smartnation-singapore-lawrence-wong
View Post
  • Data
  • Enterprise
  • Technology

Growth, community and trust the ‘building blocks’ as Singapore refreshes Smart Nation strategies: PM Wong

  • October 8, 2024
nobel-prize-popular-physics-prize-2024-figure1
View Post
  • Data
  • Featured
  • Technology

They Used Physics To Find Patterns In Information

  • October 8, 2024
goswifties_number-crunching_202405_wm
View Post
  • Data
  • Featured

Of Nuggets And Tenders. To Know Or Not To Know, Is Not The Question. How To Become, Is.

  • May 25, 2024
View Post
  • Data

Generative AI Could Offer A Faster Way To Test Theories Of How The Universe Works

  • March 17, 2024
Chess
View Post
  • Computing
  • Data
  • Platforms

Chess.com Boosts Performance, Cuts Response Times By 71% With Cloud SQL Enterprise Plus

  • March 12, 2024

Stay Connected!
LATEST
  • 1
    Just make it scale: An Aurora DSQL story
    • May 29, 2025
  • 2
    Reliance on US tech providers is making IT leaders skittish
    • May 28, 2025
  • Examine the 4 types of edge computing, with examples
    • May 28, 2025
  • AI and private cloud: 2 lessons from Dell Tech World 2025
    • May 28, 2025
  • 5
    TD Synnex named as UK distributor for Cohesity
    • May 28, 2025
  • Weigh these 6 enterprise advantages of storage as a service
    • May 28, 2025
  • 7
    Broadcom’s ‘harsh’ VMware contracts are costing customers up to 1,500% more
    • May 28, 2025
  • 8
    Pulsant targets partner diversity with new IaaS solution
    • May 23, 2025
  • 9
    Growing AI workloads are causing hybrid cloud headaches
    • May 23, 2025
  • Gemma 3n 10
    Announcing Gemma 3n preview: powerful, efficient, mobile-first AI
    • May 22, 2025
about
Hello World!

We are aster.cloud. We’re created by programmers for programmers.

Our site aims to provide guides, programming tips, reviews, and interesting materials for tech people and those who want to learn in general.

We would like to hear from you.

If you have any feedback, enquiries, or sponsorship request, kindly reach out to us at:

[email protected]
Most Popular
  • Understand how Windows Server 2025 PAYG licensing works
    • May 20, 2025
  • By the numbers: How upskilling fills the IT skills gap
    • May 21, 2025
  • 3
    Cloud adoption isn’t all it’s cut out to be as enterprises report growing dissatisfaction
    • May 15, 2025
  • 4
    Hybrid cloud is complicated – Red Hat’s new AI assistant wants to solve that
    • May 20, 2025
  • 5
    Google is getting serious on cloud sovereignty
    • May 22, 2025
  • /
  • Technology
  • Tools
  • About
  • Contact Us

Input your search keywords and press Enter.