Build Google BigQuery "Stored Procedures" With Google Cloud SQL - Part 4


Part one described the Google Cloud SQL database the sample application uses for the BigQuery "stored procedure" technique.

Part two described important details about the BigQuery project behind the solution.

Part three gave an overview of the Google Apps Script solution engineering.

Part four here will explain the JavaScript objects behind the Google Apps Script solution.

Download the five Google Apps Script component files and scripts for the MySQL stored procedure and the MySQL user-defined function from the GitHub repository.

This diagram

1. Solution file / object diagram
shows how the component solution files and objects fit together. At line 177 of the file, the runQuery() function
2. The runQuery() function
declares and initializes a getSPStringObject class

3. The getSPStringObject class
object. The object ultimately returns a finished BigQuery query string, based on the web form control picks of the user. This object declaration passes SPName as an argument in the object initialization. At line 178, function runQuery() of passes paramArray[] - the array with the user's web page control picks - to the getSPStringObject and receives the finished BigQuery query string. In getSPStringObject, line 17 declares a dbConnectionObject class object to build a connection to the Cloud SQL database. We'll look at this class shortly. Line 18 builds the "pipe" to the Cloud SQL database, and the function at line 25 formally queries the Cloud SQL database, returning the result set as localQueryString. The function at line 25 - queryString() - is publically visible, but it uses a JavaScript closure to hide the function code. In this function, line 27 converts parameter paramArray from an array to a comma-delimited string. Line 30 runs the Cloud SQL database query and receives the result set. The while-loop at line 45 parses the one-row / one-column result set into one string variable, and line 49 returns that string variable to line 178 in the runQuery() function of

In getSPStringObject, line 17 declares a dbConnectionObject

4. The dbConnectionObject class
to build a connection object to the Cloud SQL database. The function uses values in the project Script properties

5. File -> Project properties -> Script properties
as a hash table. To create a Script properties value for a Google Apps Script solution, drill down to

     File -> Project properties -> Script properties

and proceed. This sample solution uses these properties


(base Google Drive Spreadsheet URL)


(Cloud SQL database instance IP address)


(Cloud SQL database instance password)


(BigQuery project ID)


(Cloud SQL database password)


(Cloud SQL database name)

and with the getProperty function, the solution code retrieves the Script properties values from lines 5 to 8 in Figure 4 above, as shown in this

     var db = PropertiesService.getScriptProperties().getProperty('db');


At line 186, the function runQuery() declares and initializes a queryBQProject

6. The queryBQProject class
class object, passing the assembled BigQuery project querystring as an argument. At line 10, the queryBQProject object sets a BigQuery projectID variable at line 10 with a value retrieved from the solution Script properties. At line 28, the object returns a two-element array. The line 186 runQuery() function call to the queryBQProject object receives that two-element array, and lines 188 and 189 place the array elements into the




variables respectively. At this point, the function, and the rest of the solution, matches the older version described in the earlier article.

The technology showcased in this article shows that with creative use of Cloud SQL, we can build software that provides BigQuery stored procedure functionality. More generally, the technology proves that Google Apps Script can front-end multiple Google Cloud technologies.