Front-end Google BigQuery with an 4.5 application - Part 2


This three part article shows how to set up a Google BigQuery project, how to front-end that project with a sample 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# 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 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


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


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