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.


The .Net IComparer Interface - Part 4

______________________

Part one described the sample application user experience.

Part two explained the main form engineering of the C# solution.

Part three described the C# solution IComparer sort machinery.

Part four here will explain the C# solution data display form, and wrap up with a brief description of the differences between the C# and VB.net example applications.

Download the sample C# / VB.net software at the GitHub repository.

________________

The btn_load click event method
btn_load_RTB_Click
of CSharp_Demo_App.cs assembles and formats the display text, placing the text in variable recipe_text. Then, it declares and initializes localTextForm1, a textForm1 class instance, at line 230. This method disables the CSharp_Demo_App button controls, passes the recipe_text value to localTextForm1, and finally shows localTextForm1. CSharp_Demo_App sets the localTextForm1 recipe_text property value to recipe_text at line 235.


At line 26


1. The textForm1 class
the textForm1 class load event adds and configures variable localRTB, a RichTextBox form control originally declared and initialized as a private class variable. At line 40, it places the line 21 recipe_text class property value in the localRTB text property. The Close button closes the form and returns focus to CSharp_Demo_App.

Past syntax and a few code differences, the VB.net and C# applications are almost the same. The VB.net ColumnSorter class and code-behind files avoid the namespace / partial class syntax of the C# code. Additionally, the VB.net code-behind files avoid the InitializeComponent() calls found in the C# versions. Beyond this and language syntax differences, these applications are almost exactly the same.
_______________

This article showed that the IComparer Interface makes it easy to offer flexible, dynamic sort features in Windows desktop applications. Developers can focus on the business rules and requirements - IComparer will do the hard work.



The .Net IComparer Interface - Part 2

______________________

Part one described the sample application user experience.

Part two here will explain the main form engineering of the C# solution.

Part three will describe the C# solution IComparer sort machinery.

Part four will explain the C# solution data display form, and wrap up with a brief description of the differences between the C# and VB.net example applications.

Download the sample C# / VB.net software at the GitHub repository.

________________

We'll now look at the code-behind for main form CSharp_Demo_App.cs.

The sample application opens the first form
CSharp_Demo_App.cs
by default, adding and configuring DataGridView object
RecipeDGV
to the form in the form Load event. Although a Visual Studio application can easily fill a DataGridView from a database, this application uses arrays. To handle the column headers, it first builds 2-D string array
columnHeaderArray
at line 49, placing the RecipeDGV column names on the left and the column header text on the right.


1. Set up the RecipeDGV columns in the form load event
At line 63, array
widthArray
has the RecipeDGV column width values. The for-loop at line 67 uses the two arrays to configure the RecipeDGV columns. Line 94 builds 2-D string array
DGVRecordArray
which has the actual data values for RecipeDGV. The for-loop at line 114


2. Build and load the data rows for the
DataGridView in the form
load event
loads RecipeDGV from DGVRecordArray and make final configurations.

The Click event opens the second form and disables the two button controls. In this form, code in the Activated method enables the button controls.

The btn_load_RTB load event method pulls together the records for the second form, opens and shows that form, and disables the CSharp_Demo_App button controls. First, it tests for one or more selected rows in RecipeDGV at line 134. At line 178


3. Place all selected RecipeDGV row values
in a 1-D array, and declare / initialize
a ColumnSorter object
it loops through all selected RecipeDGV rows, placing all cell values in all selected rows into
combinedDGVRecordArray 
1-D string array.The loop looks at one selected row as a "unit", placing those assembled values, as a finished array, in one combinedDGVRecordArray cell. It might seem like a 2-D array structure similar to DGVRecordArray would work better for this, but as we'll see later, the sorting machinery expects a 1-D array. This loop increments variable i to map the selected rows in RecipeDGV to a new cell in combinedDGVRecordArray. Line 203 declares and initializes column_comparer, a new
ColumnSorter
class object. This line first sorts by column two ascending, then by column one descending. We'll look at the ColumnSorter class later. Line 203 hard-coded the column values but we could easily build a Visual Studio application with form controls that a user could then use to set dynamic sort criteria.

Line 205 sorts the combinedDGVRecordArray array.


4. Sort the 1-D array, gather the selected
row data, and show textForm1
The line 213 loop assembles the column two (recipe_name) and column three (recipe_text) values of every combinedDGVRecordArray cell into string variable recipe_text. Note that in this loop, we could easily include values from one, some, or all available columns, in any combination and in any order. Line 216 uses variable
page_separator_string
of lines 161 - 163 to separate the records and line 224 trims the trailing non-record characters. Line 230 declares and initializes a new
textForm1
form as an object. Lines 232 and 233 disable the button controls. Finally, the method loads the assembled recipe_text value in textForm1 and shows it.
_______________

Part 2 here showed how the overall sample C# application engineering works. Part 3 will describe the core sorting machinery in the ColumnSorter class.

The .Net IComparer Interface - Part 3

______________________

Part one described the sample application user experience.

Part two explained the main form engineering of the C# solution.

Part three here will describe the C# solution IComparer sort machinery.

Part four will explain the C# solution data display form, and wrap up with a brief description of the differences between the C# and VB.net example applications.

Download the sample C# / VB.net software at the GitHub repository.

________________

As Part 2 explained, the IComparer expects to sort 1-D arrays. This might seem a little restrictive, but it actually offers great flexibility. The 1-D array can hold elements of any structure or data type. Once we have a clean element type definition, we only need to customize the IComparer object to handle the element types of that 1-D array. This sample application, and Windows applications generally, can use this approach.

In the application, the IComparer object in ColumnSorter sorts arrays of strings based on one or more columns. To make things easier, a ColumnSorter object places the separate list of sort column indices, with ASC / DESC flags, in an object property. In CSharp_Demo_App.cs, line 203 declares and initializes
column_compare
as a new ColumnSorter object. That declaration has a zero-based, comma-delimited character (string) list of column indices - in other words, "0" maps to the first column, "2" maps to the third column, etc. The sort order for the columns defaults to ascending. In the list, a prefixed hyphen
-
in front of a column number character flags descending sort for that column. Note that column 0 - the first column - can also use the hyphen flag character. This works because the "0" (zero) character is a text string. In the list, place the column indices themselves in the record sort order the business rules and / or the user wants to see. At line 203 of CSharp_Demo_App.cs in the sample application, ColumnSorter sorts by column two ascending, then by column one descending. When the ColumnSorter class creates / initializes a new ColumnSorter object, the class constructor code of lines 13 to 22 place the column index array in class property
sort_Columns
as a string array.

Back at CSharp_Demo_App.cs, line 205 calls the Array class static method Sort, with
combinedDGVRecordArray
and
column_comparer
as arguments. The Array class Sort static method
Array.Sort
accepts column_comparer as an argument because one of the overloads will accept an IComparer interface. The ColumnSorter class inherits from the IComparer interface, so it will work here.

In C# / VB.net / etc., the IComparer interface provides a way to compare two objects, returning the result of that comparison. IComparer offers huge flexibility, because we must only place the comparison objects in an array, and provide some code that defines exactly how to compare those objects for sorting. Here, the Array.Sort method uses the IComparer.Compare method to make the comparisons for the sorting. The IComparer.Compare method takes two parameters of type Object and returns an integer based on its comparison machinery. Other parameter types will work for the Compare method, but the Object type offers great flexibility. At lines 34 and 35


1. Cast the input parameters
as string arrays
the Compare method casts the Object parameters as string array variables. The line 41 for-loop


2. Loop through the sort columns
in the IComparer 
handles each individual column index item in the sort_Columns array. This loop looks for a leading sign in the sort_Columns array item, maps that sign, if it exists, to variable sortSign, and if necessary, then trims the sign substring to remove the sign character. The col variable has the actual column index value - this will tell the comparison machinery which array column it will compare in the method parameters. The comparison machinery needs to know the data types of the individual object parameter values it will compare, so it does a little type testing on these values at lines 65 to 69.

The if-block of lines 71 to 118

3. Compare numeric values
in IComparer
handles numeric values. We must handle numeric value comparisons separately from string value comparisons because numeric values, as strings, sort very differently from numeric values as numeric values. The line 78 if-block handles descending sorting. In the if-blocks of lines 88 to 96, if the second numeric value exceeds the first numeric value, the block returns 1; if the first numeric value exceeds the second numeric value, it returns -1. Note that the value comparisons at lines 88 to 96 avoid if-then-else. 
If the IComparer compares two equal values of any type, it returns 0, and this case will fall through to the end of the method. All comparison blocks handle equal values this way, to make them more compact. The else block of lines 98 to 117 handles ascending sorting, with appropriate changes.

The if-block of lines 119 to 166


4. Compare date values
in IComparer
handles datetime comparisons in a similar way. The if-block of lines 168 to 219



5. Compare other data
types in IComparer
handles comparisons of all other types. At line 224, the method returns 0 as a default value


6. Return 0 (zero) for defined
equivalent values
of any type
to cover comparisons of two equal values.

To generalize this approach for all parameter structures / types, start with ColumnSorter.cs and in the parameter objects, figure out how to drill down into the object components that drive the sorting business rules. This will extract the data components on which the sort comparison will operate. Define the actual sort criteria as shown in lines 88 to 96, extending out to lines 78 to 117 to cover ascending and descending sorts. To cover more input parameter types, add more blocks similar to lines 78 to 117. Then, pass the objects into the IComparer as shown at line 24, encapsulating everything as shown in ColumnSorter.cs itself.
_______________

Part 3 here showed how the ColumnSorter class works.the overall sample C# application engineering works. Part 4 will describe how form textForm1 shows the assembled, sorted text.

The .Net IComparer Interface - Part 1

______________________

Desktop / Microsoft Windows Forms application will probably need a way to sort the data presented to the user. If that application presents the data in a DataGridView, sorting can become quite a challenge. This article describes a solution built around the IComparer Interface, focusing on sample C# and VB.net Windows Forms applications. Note that ASP.net also handles the IComparer Interface.

Part one here will describe the application user experience.

Part two will describe the main form engineering of the C# solution.

Part three will describe the C# solution IComparer sort machinery.

Part four will explain the C# solution data display form, and wrap up with a brief description of the differences between the C# and VB.net example applications.

Download the sample C# / VB.net software at the GitHub repository.

________________

I recently built a VB.net Windows Forms application - .NET Framework 4.5 - to front-end a SQL Server 2012 database. The application presents the database result sets in a DataGridView form object. In the application, the user can pick one or more DataGridView rows and click a button that sends data from the picked rows to MS Word. The application, including the DataGridView, preserves the sort order of the original SQL Server stored procedure result set.
 Microsoft explains that "[t]he DataGridView control provides a customizable table for displaying data", with almost infinite flexibility through its huge number of properties, events, and controls. Unfortunately, the DataGridView does not offer native sort order control for selected rows. In the application, this matters because the user needs full ascending / descending sort control, and complete sort column ordering flexibility, for all selected DataGridView rows the application passes to MS Word. MS Word, and by extension Word VBA, can handle all this but the Word VBA object model can become nasty. I tried LINQ, but I never found a way to make this approach work. The IComparer Interface solved the problem. After I finished the first application, I built a second sample application in C# and VB.net to highlight the technique. Part one here will first focus on the sample application user experience - identical for both versions.
________________

The first form


1. The first form
opens with a fourteen row, eight column row set in a DataGridView, highlighting the first row. The user can pick zero or more rows


2. Highlighting DataGridView rows in the first form
and when the user clicks "Load RTB"


3. Second form - DataGridView columns Recipe Name
and Recipe Text of selected rows in first
form placed in a RichTextBox
form textForm1 opens. This form only shows the
RECIPE NAME
and
RECIPE TEXT
column values from the selected DataGridView rows in a Rich Text Box, although we can easily add, remove, and / or change column values in the application code-behind. The Rich Text Box shows these record values separated by
____________
________________________
____________

strings. The application sorted the recipes by the
CATEGORY (ascending)
and then by the
ROW (descending)
columns from DataGridView of the first form. As we'll see, however, we can easily change the sort criteria, again in the application code-behind. To close the second form and return to the first form, the user clicks the Close button. At the first form, the user clicks the Close button to close the application.
_______________

Part 1 here showed how the sample application works. Part 2 will describe the C# application engineering.