Front-end Google BigQuery with an ASP.net 4.5 application - Part 1

__________________

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 here explains how to set up a Google Cloud Platform account, and how to build a basic BigQuery project.

Part two will first describe the sample application user experience, and will then focus 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.
__________________
Google Cloud Platform offers a wide range of products that move computing from an ownership model to a rental model. In the Platform product line, Google BigQuery handles large data volumes and queries. Although BigQuery lacks some features of products like Oracle or SQL Server, it's perfect for many of the problems traditionally solved by these products, without the hassles. It avoids the hardware and dedicated DBA overhead, at prices competitive with more "established" RDBMS tools. Part one here explains how to set up and load data into a Google BigQuery account.

Relational database products traditionally group tables, stored procedures, etc. in "databases." In contrast, BigQuery groups tables in a user-defined "dataset" within a user-defined project. Note that a dataset has only tables, not the other machinery RDBMS products provide. The ASP.net application we'll explore in Part 2 uses this dataset for its queries. While BigQuery provides some sample datasets, I will show how to load a sample CSV file into the dataset. Note that for user-sourced data, BigQuery requires project billing activation.

I started at the Google BigQuery page and I signed in with a Google account. Just above the black up arrow towards the top of the page I clicked "Go To My Console". 

1. Set up Google Cloud Platform - first page

To set up the BigQuery project for this article, I clicked "CREATE PROJECT" in the Console

2. Create Project

to open this window:

3. Set up the new BigQuery project

Cloud Platform maps billing data to the paying customer at the project level. I named the project "BigQueryDemoApp", I read / agreed to the terms, and I clicked Create. The Developers Console auto-generated "glass-mantra-589" as the Project ID. I could have changed this value, but I left it as is. This Project ID value will become important later. The Project Dashboard opened next:

4. Project Dashboard

At the left, I clicked "APIs & auth" to open the list of Google APIs I could add to the BigQueryDemoApp project:

5.  Add one or more Google APIs to the Google Cloud Platform project

Finally, I toggled off all defaulted picks except BigQuery API, although I could have picked other APIs. In Figure 5, I clicked "Settings" at the upper left to open this page:

6. Set up project billing

Here, I clicked "Enable Billing" as indicated. This opened a page to set up the billing machinery for the "glass-mantra-589" project. Google Billing sends an email address confirmation email to the customer contact address entered in this step. With appropriate changes, we can use this approach to configure a project with any Google Cloud Platform product.

Next, I loaded a data file into BigQueryDemoApp. BigQuery accepts CSV or JSON files at this step. I used zip file "Complete ZIP Code Totals File" from the U.S.Census Bureau for the data. It unzips into CSV file "zbp11totals.txt". This 3 meg CSV file has about 39 K rows - enough to show the basics of BigQuery without hitting the structural limits or price points. At the Project Dashboard, I clicked "BigQuery" to the left of the black arrow here:

7. To load the data, first pick BigQuery at the Developers Console

I clicked the indicated blue down arrow here:

8. Pick the BigQueryDemoApp project

and in the popup, I picked Create new dataset. This opens a popup where I type the name of the new dataset. I called it "BigQueryDemoAppDS". The new dataset appears to the left of the page here:

9. Drill down to the BigQueryDemoAppDS dataset

When I mouse-hovered over the dataset, a blue plus appeared; I clicked this plus to open a new popup to handle the data load process. First, I typed the name of the BigQueryDemoAppDS target table: "zbp11totals". Next, I picked source file "zbp11totals.txt". After that, the popup wanted the source data schema, in this form:

name:string, age:integer

and fortunately, the Census Bureau provided the source ZIP Code Totals data file layout here. This layout maps to a format compatible with BigQuery:

ZIP:string, NAME:string, EMPFLAG:string, EMP_NF:string, EMP:integer, QP1_NF:string, QP1:integer, AP_NF:string, AP:integer, EST:integer, CITY:string, STABBR:string, CTY_NAME:string

I proceeded through the Create and Import steps, and at Advanced Options as shown here I set the "Header rows to skip" value to 1.

10. Configure the BigQuery data import

I accepted the other default values and I clicked Submit.

This completed the Google Cloud account and BigQuery project set-up. Part two will cover the sample application that front-ends that project.

Front-end Google BigQuery with an ASP.net 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 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.

Front-end Google BigQuery with an ASP.net 4.5 application - Part 3

__________________

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 first described the sample application user experience, and then focused on application configuration and the security set-up.

Part three here explains 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.
__________________

Now, we'll look at the C# code for this application. Since BigQuery is not ODBC compliant, I could not use Object Relational Mapping for this application. Instead, the application used an object created from a custom class - BQObject - to communicate with the BigQuery Project. I made the BQObject code available here as a Github repository share.

In the BQObject class, method returnService() returns a BigqueryService object. The using statement opens the client_secrets JSON file. The MapPath function dynamically maps the virtual path of this file to its physical path, which means the application can go in any directory. Use the Project Name value from the New Project window (Part 1 / image 3) for the ApplicationName variable in the Service block of this method.

The using statement block talks with the Google authentication servers to set the credential object, based on the client_secrets.json file opened in the using statement. In the AuthorizeAsync method call, the second parameter "scopes" tells the authentication servers the specific Google Cloud services the credential will cover. Here, I set it to BigqueryService.Bigquery, to match the API I picked in Part 1, Figure 5. The next block creates and initializes the BigQuery service, which the returnService method ultimately returns.

The returnDT() method queries the BigQuery dataset I built in part one. It returns a C# datatable. The method runs the queryParam parameter as its query against the BigQueryDemoAppDS dataset. The BigQuery response has a JSON structure with metadata that will help parse the response data set. I set the QueryResponse object "response" to the JobsResource object "j" Query method. In the Query method, I used the project name string - in this case "glass-mantra-589" - from the Google Cloud Platform project setup as the second parameter.

The JSON response object metadata includes the result set column names. The for loop in returnDT() extracts them from the JSON response object Schema property and uses them for the column names of the datatable object the returnDT() method will return. In the response object, the column headers might have non-alphanumeric characters. BigQuery can only handle alphanumeric characters in column aliases. Therefore, this application must simulate non-alphanumeric characters, and the replace functions here handle the mappings in the for loop.

The foreach loop pulls the data values from the JSON response object into the datatable object the method returns. It iterates through the response Rows property one row at a time. A BigQuery function returns null if it can't calculate a value. In the loop, the first if-block looks for one row and one column in the result set. If the data value is null, the block places a one-column row, with an information message, in the datatable.

The second if-block handles a quantiles function result set that returned a NULL result set - in other words, no calculated values. The JSON response object will have a null value in the second column and this block places a two-column row, with appropriate messages, in the datatable.

The third if-block handles all single-value result sets and a multiple-value result set from a quantiles function call. For a multiple-value result set, this if-block places the row values, one column at a time, into the datatable. Finally, returnDT() returns the finished datatable.

The sample application has one form - Default.aspx. See the code-behind here as a GitHub repository. We'll look at this code now.

The user sets the payroll dropdown values in the form. A value of 1 maps to 1000 to match the original Census Bureau file values. On the form, gridview GV1 shows the result sets. It handles one or more rows and shows an appropriate message if a BigQuery calculation returns null.

If DDLQuery, the function dropdown, has "QUANTILES EMPLOYEE COUNT" visible, the Page_Load method first sets DDLQuantiles, the quantiles dropdown, visible. The method also shows the associated labels. This line hides DDLQuantiles and the labels for other DDLQuery picks. Next, the method loads DDLQuantiles with text / value pairs from 2 to 100.

The calcStats() method takes the paramString parameter when the btnSubmit_Click() method calls it. The paramString parameter is the assembled query to run against the BigQueryDemoAppDS dataset. The calcStats() method first creates localBQObject, a BQObject object. In a try-catch block, calcStats() calls the localBQObject.returnDT() method, passing paramString as the argument. It sets the GV1 datasource to this call. If the try block throws an error, the catch block formats the error text and shows it in a JavaScript alert.

The btnSubmit_Click() method runs when the user clicks the submit button. In the queryString variable, it builds the SELECT statement the application runs against the BigQueryDemoAppDS dataset, based on the control values the user picked. The switch statement maps the selected value of the DDLQuery dropdown to the correct BigQuery function. BigQuery doesn't like spaces and certain other characters in column aliases. Therefore, in the column name aliases in the switch statement cases, I used underscores for spaces and I mapped the characters I wanted over to Unicode equivalents like this:

    $ <-> x24
    ( <-> x28
    ) <-> x29
     = <-> x3d
   ^ <-> x5e

The BQObject returnDT() method maps the Unicode strings back to the intended characters. Note that BigQuery expects to see a

{dataset name}.{table name}

table name syntax in the FROM clause. As the btnSubmit_Click() method builds the query string, method endOfQueryString() adds " AND" to the end of query string.

The foreach loop pulls non-default values from the DropDownList controls, and adds them to queryString. The endOfQueryString function first adds " AND" to queryString. For the ZIP dropdown list DDLZIP, the loop adds a "ZIP LIKE" clause that looks at the first character in the ZIP column. For the other dropdowns, it adds a BETWEEN clause. If the user never changed the value of a specific dropdown from its default "ANY" value, the loop ignores that dropdown.

If the user left all the dropdown values unchanged, queryString would end with "WHERE ". This would crash BigQuery, so the last if statement in btnSubmit_click() substrings "WHERE " out of queryString if necessary. Finally, btnSubmit_Click() calls calcStats to fill the GV1 gridview control.

The DDLQueryClause_SelectedIndexChanged method hides or unhides the Quantiles dropdown DDLQuantiles, and the associated labels. If the user picks the quantiles function in that dropdown, this method sets these three controls visible, or hidden otherwise.

BigQueryDemoApp shows how to easily front-end a BigQuery dataset with an ASP.net application. BigQuery is a powerful, flexible product that offers an affordable way to crunch large data volumes.

Control iApp behaviors and properties with an XCode animation object

__________________

iApp developers have complete control over the properties and behaviors of both built-in XCode library objects and objects based on custom and derived classes. With CABasicAnimation objects, iApp developers can easily make an iApp come alive through animation of any CALayer property - color, position, opacity, rotation/orientation, etc. However, a direct combination of these tools - a CABasicAnimation of a CALayer that directly controls other class properties within that iApp - becomes more of a challenge. Fortunately, XCode can handle this. I built BSD, an iApp available for sale at iTunes that relies on this idea and to highlight the solution, I built a simple iApp called DV (for Dependent Variables), available as a GitHub repository. Using XCode 5.1 and targeting iOS 7.1, DV has a ViewController class with most of the machinery of the iApp. DV also has the AppDelegate class, and customView, a simple custom class. I did not use the Interface Builder to build DV; the ViewController viewDidLoad method dynamically builds all view controller objects DV needs.

DV has a simple user experience. The first X Position label shows the states of the top two squares; the second X position label reflects the lower square. The Rotation Angle applies to all the squares.

Initialized DV animations

When the user taps START, the top two squares rotate and slide to the right and the lower square rotates and slides to the left.

DV running.
The top square translation / rotation
animations directly control the
location and angle of the
other squares

The top square animations continuously
update the Rotation Angle and first
X Position label values 

The X Position label and Rotation Angle labels continuously update to show the latest instantaneous values. The PAUSE / RESUME button toggles, and when the animations finish, the RESET button enables.

DV animations completed

The button interlocks make intuitive sense to the user.

In Objective C, a class declares its instance variables in the header file @interface section. All class methods can see and change instance variables inside that class. Additionally, properties of methods within that class can take the values of instance variables inside that class. Therefore, if a CABasicAnimation object can change an instance variable of an object, those changes could drive object behavior in other methods through direct property changes of those objects. A CABasicAnimation object cannot change these instance variable values directly, but through a CADisplayLink object - a native Objective C class - a CABasicAnimation object can update these instance variables indirectly. CADisplayLink works as a timer, synchronized to the screen refresh rate. Each time a CADisplayLink object fires, it calls a selector, or specified method. The selector can then update class instance variables. These instance variables then drive changes in objects with properties controlled by those variables. In this way, the animation indirectly controls those objects. Sample iApp DV relies on this engineering. In the XCode simulator, DV is optimized for iPhone Retina (3.5-inch). The main class, ViewController, has a CADisplayLink object which drives changes in objects both in ViewController and in a derived class instance. To show the flexibility of this approach, the changes in the derived class object happen through a direct call to a class method, and through direct updates to a derived class property.

Method viewDidLoad – the last method of class file ViewController.m - builds and configures the fourteen view controller objects of DV, and places each controller object in its own layer. All controls are instances of Xcode base classes, except for thirdView. Method viewDidLoad initializes three flags

(Code 1)
DV application control flags

at lines 533 to 535 that control the toggling, appearance, and enable/disable status of the control "buttons" at the bottom of the simulator screen; we won’t explore the code behind this button behavior because this article does not focus on that engineering. DV launches with the START enabled, and the squares initialized.

The thirdView object created in the viewDidLoad method of ViewController is an instance of the DV customView class. This class derives from the UIView base class. In customView.m, the drawRect method draws and fills a rectangle based on a CGRect parameter. In the customView class, method cGAffineTransforms uses CGAffineTransforms at lines 31 and 32 to translate and rotate the thirdView object, calling the transforms with CGAffineTransformConcat at line 33 to make them combine correctly.

(Code 2)
customView class transforms

To make the transforms occur instantaneously, the animation block sets both animateWithDuration and delay to 0.

The animation objects in ViewController.m method prmrySquareLayerMainAnim only animate the upper square.

(Code 3)
When DV calls this method, the
objects in this method only
animate the upper square.

When the user taps START, the ViewController.m touchesBegan method launches; lines 216 and 217 in this method configure displayLink, the CADisplayLink variable.

(Code 4)
Configure the CADisplayLink
object in touchesBegan
at lines 216 - 217

A CADisplayLink variable is a timer, firing at the screen’s refresh rate. Each time it fires, it calls a selector callback - in this case, method squareLayerStateChange at line 216. Line 221 calls method prmrySquareLayerMainAnim, to start the primary square animations. Each time displayLink calls selector method squareLayerStateChange, this callback updates ViewController instance variables

• curPos
• curXVal
• curRadAngle

based on the latest instantaneous location and rotation angle of prmryAnimView. For DV, prmryAnimView is the UIView parent of the primary square layer - the top square in DV. Method squareLayerStateChange

(Code 5)
Update the labels;
change second and
third square properties

uses the instance variable values

• curPos
• curXVal
• curRadAngle

to update the first two labels of the application (lines 40 - 41). Then it uses the curXVal and curRadAngle values to translate and rotate the second square, secondaryAnimView (lines 52 – 54), with simple CGAffineTransforms. The squareLayerStateChange method also changes properties in the customView object thirdView, created by viewDidLoad at line 513. At line 68, squareLayerStateChange directly updates the thirdView object xPosVal property with curXVal. To rotate the thirdView object, at line 70 the squareLayerStateChange method calls the cGAffineTransforms method of the thirdView object, with parameter curRadAngle.

To show that a derived class method can use the xPosVal property as a transformed value, the customView class cGAffineTransforms method uses

–(self.xPosVal)

as a negative value in a translate transform (see Code 2 above) to move the object to the left.

Although the examples in DV focused on simple animations, the CADisplayLink technique has huge potential. With this approach, a CABasicAnimation can drive changes to any object property that an XCode statement can see – layer color, sound generator frequency, or UILabel font size, for example. It could even control object creation based on animation object values. It has endless possibilities.