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