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
shows how the component solution files and objects fit together. At line 177 of the Code.gs file, the runQuery() function
declares and initializes a 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 Code.gs 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 Code.gs.
In getSPStringObject, line 17 declares a dbConnectionObject
to build a connection object to the Cloud SQL database. The function uses values in the project 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
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');
example.
At line 186, the Code.gs function runQuery() declares and initializes a queryBQProject
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 Code.gs 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
queryResults
and
rows
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.
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 |
 |
| 2. The runQuery() function |
 |
| 3. The getSPStringObject class |
In getSPStringObject, line 17 declares a dbConnectionObject
 |
| 4. The dbConnectionObject class |
 |
| 5. File -> Project properties -> Script properties |
File -> Project properties -> Script properties
and proceed. This sample solution uses these properties
|
|
spreadSheetURL
|
(base
Google Drive Spreadsheet URL)
|
|
|
address
|
(Cloud
SQL database instance IP address)
|
|
|
userPwd
|
(Cloud
SQL database instance password)
|
|
|
projectID
|
(BigQuery
project ID)
|
|
|
user
|
(Cloud
SQL database password)
|
|
|
db
|
(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');
example.
At line 186, the Code.gs function runQuery() declares and initializes a queryBQProject
 |
| 6. The queryBQProject class |
queryResults
and
rows
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.