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

______________________

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


Part two here will briefly describe important details about the BigQuery project behind the solution.

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

Part four 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.
________________

The solution described in this specific article will use the BigQuery project described in this earlier article. For the solution here, name the BigQuery project

     BigQuerySPDemoAppDS


and create one table called

     zbp11totals


in it. Load it with data from zip file "Complete ZIP Code Totals Filefrom the U.S. Census BureauThe earlier article has more details about how to do all this, but remember to name this BigQuery project here "BigQuerySPDemoAppDS".
_______________

Part two here briefly described important details about the BigQuery project behind the solution. Part three will give an overview of the Google Apps Script solution engineering.

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

______________________

To analyze large data volumes, Google BigQuery is a great tool. Through 
Google Apps Scripts, we can easily build universal web applications to front-end BigQuery. The JavaScript engineering behind these web applications certainly works well enough, but a major pain point remains: BigQuery does not handle stored procedures.

This article describes a sample Google Apps Script solution that front-ends both BigQuery and Cloud SQL, using Cloud SQL to simulate BigQuery stored procedures. In this approach, a JavaScript function takes a Cloud SQL / MySQL stored procedure name and an array of stored procedure arguments as parameters. The function returns a BigQuery result set in a JavaScript array. Of course, when / if Google provides a BigQuery stored procedure feature, it will have a completely different user / developer experience and completely different engineering. However, this new approach works really well and it makes BigQuery front‑end development much easier.

This article focuses on a solution with the same front-end user experience as the one I described in this earlier article. However, this new solution scraps the earlier dynamic SQL approach. This article and sample solution show that a Google Apps Scripts application can front-end and integrate multiple Google Cloud products, and at the same time mirror the web form behavior on a Google Drive file.

This article builds on two articles I published here at BitVectors. The
first article shows how to build a Google Apps Script application to front-end BigQuery. The second article shows how to build a Google Apps Script application to front-end Google Cloud SQL.

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

Part two will briefly describe important details about the BigQuery project behind the solution.

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

Part four 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.
________________

First, set up a Cloud SQL instance as described here. Note that the necessary procedures and steps to do this might have diverged somewhat from what I described earlier, and some of the web setup forms and panels might now look and work somewhat differently. However, the overall ideas and concepts have stayed the same.

In the Cloud SQL instance, create a database named "DEMO". Next, point a MySQL workbench to the Cloud SQL instance, and configure that workbench as needed. With that workbench, first build stored procedure usp_return_BigQuery_SP in the DEMO database. This stored procedure takes one comma-delimited string parameter holding seven comma-delimited values. Later sections of this article will explain these values. This stored procedure has a simple goal: starting with the input parameter, extract the comma-delimited values and build a BigQuery query string that will query the BigQuery project associated with the sample solution. The stored procedure input parameter

     COMMA_DELIMITED_PARAM

will have these example formats

3,10 AND 100,100 AND 1000,ANY,0 AND 9, ,2

3,10 AND 100,ANY,ANY,10000 AND 100000,54,4

which map to this structure:

FIRST_ZIP_DIGIT
EMP
QP1
AP
EST
QUANTILES_VALUE
     DDL_QUERY_VALUE

In this list, the last item, DDL_QUERY_VALUE, spans an integer value range of 1 to 4 [1 . . 4]. A value of 4 maps to the BigQuery Quantiles function, called "Quantiles Employee Count" in this application. The Quantiles function requires a quantiles value as a separate integer. In this list, item six, QUANTILES_VALUE, covers this requirement. Here, parsed item six (the quantiles value) will have a non-blank value only if parsed item seven (the drop down list query value) has a value of 4, because 4 maps to the BigQuery Quantiles function as "Quantiles Employee Count" in the web page dropdown. The value for QUANTILES_VALUE stays blank for any other value for DDL_QUERY_VALUE. The first five parameter items can have a value of "ANY".

Stored procedure usp_return_BigQuery_SP first declares a number of VARCHAR variables. Then, it uses MySQL string functions to parse input parameter COMMA_DELIMITED_PARAM into those declared VARCHAR variables. The stored procedure begins to assemble the finished BigQuery query string at line 78. The line 87 CASE statement maps the DDL_QUERY_VALUE value to a BigQuery function within the BigQuery query string, covering a quantiles function pick at lines 98 and 99. Line 108 adds the "FROM" clause to the query string. The CASE blocks between lines 134 and 168 place the first five variable values in the query string. The blocks will place a value in the query string only if a variable has a value different from "ANY". This way, "ANY" can wildcard the variable values.

The CASE blocks use the CONCAT string function to add the variable values to the assembled query string. Starting with the EMP variable block, the CONCAT functions call user-defined function endOfQueryString. As the CASE blocks build the query string, this user-defined function takes the assembled query string as a parameter, returning " AND " if the query string does not end with "WHERE ".


At line 170, the stored procedure adds a ";" character and returns the assembled BigQuery query string.

_______________

Part one here explained the Cloud SQL / MySQL part of the solution. Part two will describe important details about the BigQuery project for the solution.

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

______________________

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 here has an overview of the Google Apps Script solution engineering.

Part four 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 .

________________

T he engineering for this solution closely matches the engineering described in the earlier article. Therefore, we'll focus on the differences between the solutions and the innovations in this solution.


Each solution must build a BigQuery query string based on web form user picks, send that query string to a BigQuery project, receive the result set, and write the result set on the original web page. Additionally, they must mirror that result set and user web form control picks on a Google Spreadsheet. While the earlier approach builds a dynamic SQL BigQuery query string in its application .HTML file, this solution gathers the web form user picks in an array. Then, it transforms this array into a comma-delimited string and sends this string as a parameter to a Cloud SQL database stored procedure. It receives a finished BigQuery query string as a stored procedure result set. Next, it queries the BigQuery project with this query string, receives a BigQuery result set, and writes the result set on the web form, mirroring everything on the associated Google Spreadsheet. The rest of this article will focus on the differences described in the highlighted text above.

First, the new application reversions the old BigQueryDemoApp.html file as BigQuerySPDemoApp.html.  The new .html file scraps all code that built the original queryString variable. Instead, the submitData() function of this file uses jQuery to gather all the web form control pick data values, placing them in array paramArray[]. This array will become the basis for the call to Cloud SQL stored procedure usp_return_BigQuery_SP. In parallel, submitData() gathers the web form control pick text values, placing them in array headerArray[]. The solution will write these values on the spreadsheet, to show the web form control picks of the user. At line 208, submitData() calls returnFormParams() of file Code.gs with both these arrays as arguments. Lastly, the .html file scraps the old endOfQueryString() function.

The solution then runs function returnFormParams() of the Code.gs file. At line 126, the function writes the headerArray[] array values on the spreadsheet in row four and creates / initializes variable SPName with text for a call to stored procedure usp_return_BigQuery_SP. The variable SPName text includes the "(?)" text to account for the parameter the stored procedure requires. Then, returnFormParams() calls runQuery() with parameters SPName and paramArray. Although returnFormParams() hard‑wired the stored Cloud SQL / MySQL stored procedure it calls, its engineering could dynamically pick a stored procedure based on one or more values in paramArray[]. A hash table, for example, would work. Finally, returnFormParams() calls function runQuery(), with two parameters: SPName and paramArray[].
_______________

Part three here gave an overview of the  Google Apps Script solution engineering. Part four will explain the JavaScript objects behind that solution.



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 Code.gs 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 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

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




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
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.