__________________
This three part article shows how to set up a Google BigQuery project, how to front-end that project with a sample ASP.net application available for download as a GitHub repository, and how the engineering behind that sample application works.
Part one explained how to set up a Google Cloud Platform account, and how to build a basic BigQuery project.
Part two here first describes the sample application user experience, and then focuses on application configuration and the security set-up.
Part three will explain the engineering behind the application.
I want to say thank you to Eyal Peled, of Google, for taking time to answer some questions I had about BigQuery.
__________________
To show BigQuery in action, I
built BigQueryDemoApp, a C# ASP.net application based on the .Net 4.5
framework. I will first describe the BigQuery user experience, and then I'll explore the code. Download the complete application here as a Dropbox share.
BigQueryDemoApp queries the
zbp11totals table I created in Part 1. The first page
 |
| 1. BigQueryDemoApp main page |
has seven dropdowns. The Number of Quantiles dropdown stays hidden by default; its visiblity depends on the value picked in the Function for Select Clause dropdown. "First Zip Digit"
filters the first character of the ZIP column:
 |
| 2. First Zip Digit dropdown |
The "Employee Count" dropdown filters the EMP column, or mid-March Employee count:
 |
| 3. Employee Count dropdown |
The "Q1 Payroll" dropdown filters the QP1, or Total First Quarter Payroll, column:
 |
| 4. Q1 Payroll dropdown |
The "Total Annual
Payroll" dropdown
 |
| 5. Total Annual Payroll dropdown |
filters the AP, or Total Annual
Payroll. The "Total Establishment
Count" dropdown
 |
| 6. Total Establishment Count dropdown |
filters the EST, or Total Number of
Establishments column. The "Function for Select
Clause" dropdown
 |
| 7. Function for Select Clause dropdown |
picks the statistical function the
BigQuery will use for its calculation. Lastly, when the user picks
"QUANTILES EMPLOYEE COUNT" in the function dropdown, the "Number
of Quantiles" dropdown
 |
| 8. Number of Quantiles dropdown |
together with two labels becomes visible. This
column ranges from two to one hundred and it sets the number of quantiles returned by the quantiles function.
The first five dropdowns
default to "ANY" and the function dropdown defaults to standard
deviation. After setting the dropdown picks, the user clicks
"SUBMIT". The "CLOSE APP" button closes the application at
any time.
This shows an example single value result set
 |
| 9. Single value result set |
This shows a quantiles result set
 |
| 10. Quantiles result set |
If BigQuery can't
calculate a value based on the dropdown picks, it returns zero records
and the application shows an information message to explain
that result.
A Google BigQuery application
needs client ID and client secret values to access the Google servers.
BigQueryDemoApp pulls these values from a JSON file stored as a file within the solution. I built this file with one of the Google Developers Console tools. Note that the example ASP.net application available for download here as a GitHub repository does not have the required JSON credentials file. Logged in with my Google developer account, I clicked on the
BigQueryDemoApp project name here:
 |
| 11. Pick BigQueryDemoApp here to get the OAuth credentials file |
Then, I drilled down to
APIs & auth ->
Credentials
here:
 |
| 12. Get the login credentials for the BigQuery application - first step |
Note that I erased the Client ID, Client secret, Email address and Redirect URIs values from images twelve, thirteen, and fourteen. Then, I clicked
CREATE NEW CLIENT ID
to open the Create Client ID
page. I picked Installed application and Other
 |
| 13. Create Client ID window |
and then I clicked CREATE NEW CLIENT ID:
 |
| 14. Create Client ID |
Next I clicked Download
JSON in the "Client ID for native application" section to download the credentials file. The
original JSON extension file name started with "client_secret_"
followed by a lot of random characters, so I changed the filename to
"client_secrets.json" to make everything easier. Finally, I moved this
file to the application root directory, and I added the file to the
BigQueryDemoApp project node root using the Visual Studio Solution Explorer.
The last configuration steps for the application involve NuGet packages. In the Visual Studio Package Manager Console
Tools -> Library Package Manager -> Package Manager Console
I ran these NuGet commands
- Install-Package
Google.Apis.Bigquery.v2 -Pre
- Install-Package Google.Apis.Authentication.OAuth2 -Version
1.2.4696.27634
- Install-Package DotNetOpenAuth.OAuth2.Client.UI -Version 4.3.4.13329
- Uninstall-Package
Microsoft.Bcl.Async -Force
- Install-Package Microsoft.Bcl.Async -Version 1.0.16
to install the libraries the
application needs. When I ran the application in Visual Studio, this authorization / permission window opened
 |
| 15. Authorize Google BigQuery in the active Google account |
and I clicked Accept.
This completed the front-end application configuration. Part three will explain the engineering behind that application.