How to call BQ stored procedure in GCP Dataform?

Question

I am planning to call a BQ stored procedure in Dataform using some constant values as argument.

I'm showing my code here, and also the error I am getting. Please help.

includes/constants_mfg_ccrel.js:

const domain_name = 'mf'
const transformation_job_grouping = 'ccr_cnf_seq1'
const run_id = 'manual__2024-04-19T03:00:22.571539+00:00'
const tgt_proj_id = 'ttc-mf'
const ssdaa_proj_id = 'ttc-ss'
const bq_conf_ds = 'mf_conf'

module.exports = {domain_name, transformation_job_grouping, run_id, tgt_proj_id, ssdaa_proj_id, bq_conf_ds};

includes/transformation_table_load_proc_call.js:

function Procedure_Call(transformation_job_grouping, run_id) {
    return `CALL '${constants_mfg_ccrel.tgt_proj_id}.${constants_mfg_ccrel.bq_conf_ds}.transformation_table_load_proc'(${transformation_job_grouping}, ${run_id})`;
}
    
module.exports = { Procedure_Call };

definitions/ccr_cnf.sqlx:

config {
    type: "operations",
    tags: ["ccr_cnf_master", "SW"]
}

${transformation_table_load_proc_call.Procedure_Call(`'${constants_mfg_ccrel.transformation_job_grouping}'`, `'${constants_mfg_ccrel.run_id}'`)};

This is the error I got:

Syntax error: Unexpected string literal 'ttc-mf.mf_conf.transformation_table_lo...' at [2:10]

Answer 1

When we look at your code, we find:

function Procedure_Call(transformation_job_grouping, run_id) {
    return `CALL '${constants_mfg_ccrel.tgt_proj_id}.${constants_mfg_ccrel.bq_conf_ds}.transformation_table_load_proc'(${transformation_job_grouping}, ${run_id})`;
}

Your JavaScript function named Procedure_Call retuns the STRING:

CALL '<SOMEVALUE>'(<SOME_VALUE>, <SOME_VALUE>)

Looking at the first parameter you are supplying, it is a string (it surrounded by single quotes). When we compare this to the syntax of the BigQuery CALL statement found here, we find that the first parameter should be the name of a stored procedure and not a string.

Try and re-code your logic to be:

function Procedure_Call(transformation_job_grouping, run_id) {
    return `CALL ${constants_mfg_ccrel.tgt_proj_id}.${constants_mfg_ccrel.bq_conf_ds}.transformation_table_load_proc(${transformation_job_grouping}, ${run_id})`;
}