Invoking BigQuery Stored Procedures in Google Cloud Workflows with Runtime Args
This article focuses on how to invoke a BigQuery stored procedure with specified parameters using an input operation provided as a JSON structure in a Google Cloud Workflow.
Google Cloud Workflows
Google Cloud Workflows is a fully managed, serverless service that helps you create and coordinate workflows that run multiple GCP services in a defined order. Cloud Workflows enables you to build complex, event-driven, and stateful workflows using a simple YAML syntax.
BigQuery Stored Procedures
BigQuery Stored Procedures, introduced in BigQuery version 1.9, allow you to define and run procedural logic in BigQuery. Stored procedures can be used for:
- Performing data validation
- Performing data transformation
- Encapsulating reusable code
Integrating Google Cloud Workflows with BigQuery Stored Procedures
Cloud Workflows can interact with BigQuery using its API. Users can execute a query or a stored procedure, fetch metadata, and manage datasets and tables.
Runtime Args and Input Operations
Google Cloud Workflows accept arguments and input through its well-structured YAML definition. Users can pass runtime arguments and provide input operations as JSON data structures, which can be used by activities during the workflow execution.
For this example, consider a simple BigQuery Stored Procedure:
CREATE PROCEDURE `project.dataset.sp_multiply`(IN p1 INT64, IN p2 INT64, OUT result INT64)
BEGIN
SET result = p1 \* p2;
END;
This stored procedure multiplies the two input parameters p1 and p2 and returns the result through an output parameter result.
Google Cloud Workflow YAML Definition
main:
params: [ &inputInput ]
steps:
- initialize:
assign:
- input: $.params.inputInput
- callMyStoredProcedure:
call: googleapis.bigquery.v2.jobs.query
args:
projectsId: ${"projects/" + google_project}
body:
configuration:
query:
query: CALL project.dataset.sp_multiply(@p1, @p2, @result);
parameters:
- name: p1
parameterType:
type: INT64
parameterValue:
value: ${input.p1}
- name: p2
parameterType:
type: INT64
parameterValue:
value: ${input.p2}
result: result
The workflow contains a structured action for the Google APIs BigQuery jobs.query method, which calls the stored procedure and passes the required parameters using the parameters array.
params is used for inputs and the entire input payload is used for the input parameter. params can have a specific name, which will later be referenced while calling the stored procedure.
- Google Cloud Workflows can easily be integrated with BigQuery Stored Procedures.
- An input operation can be provided as a JSON structure for a stored procedure's input parameters.
- Users can pass runtime arguments to the Cloud Workflow through a YAML definition.