Front-end Google BigQuery with a Google Spreadsheet Part 1 - Set up a Google Cloud Platform Account / BigQuery

_________________________

This five part article shows how to set up a Google BigQuery project, and how to front-end that project with a sample Google Docs Spreadsheet web application. This application will work in all browsers and platforms, including the Apple iPad and iPhone. The article also explains the application engineering and configuration. Download the two application component files from the GitHub repository.

Part one here will explain how to set up a Google Cloud Platform account, and how to build a basic BigQuery project.

Part two will describe the application user experience.

Part three will explain the application engineering.

Part four will start with a brief introduction to the Google Docs script editor. Then, it will show how to tie the application code to both the Google Docs Spreadsheet and the BigQuery project.

Part five will show how to configure security for the sample application, and how to distribute the sample application to users.

I want to say thank you to Google Developer Advocate / Software Engineer Felipe Hoffa. At the Google Cloud Platform Roadshow here in Los Angeles, he took the time to discuss Google Apps Script development with me, and answered my questions about the associated security machinery.
____________________

In an earlier series of articles, I showed how to set up a BigQuery project and front-end it with an ASP.net application. The customer liked the original ASP.net solution I built but then wanted a front-end that would work on an iPad. Google does not yet have BigQuery Objective C libraries available, but does offer Google Apps Script. With this tool, I built a web app over a Google Docs Spreadsheet. This web app communicates with the Google BigQuery project. Even better, the web app writes to that spreadsheet. An iPad can certainly see a web app through a browser, so the Google Docs Spreadsheet became the solution. This article focuses on the basic technology behind that original Google Apps Script solution.
_______________

In Front-end Google BigQuery with an ASP.net 4.5 application - Part 1, I showed how to set up a Google Cloud Platform account, and how to build a basic BigQuery project. Since then, enough steps, screens, and forms have changed to require a complete rebuild.

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

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 Google Docs Spreadsheet application we'll explore uses this dataset for its queries. I will show how to load a sample CSV file into that 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 toward 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 "lexical-emblem-651" 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 expanded "APIS & AUTH" and I clicked APIs 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. The application software will need a similar configuration which I will explain in part three.

In Figure 5, I clicked "Billing & Settings" at the lower left to open this page:

6. Set up project billing

Here, I clicked "Enable Billing" as shown. This opened a page to set up the billing machinery for the "lexical-emblem-651" project. Google Billing sends a confirmation email to the customer contact email 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 expanded "BIG DATA" and 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 typed the name of the new dataset. I called it "BigQueryDemoAppDS". The new dataset appeared to the left of the page here:

9. Drill down to the BigQueryDemoAppDS dataset

When I hovered the mouse over the dataset, a blue plus appeared; I clicked this plus to open a new popup to begin the data load process.

10. Create and Import data

First, Dataset ID defaulted to

BigQueryDemoAppDS

and for the Table ID, I typed

zbp11totals

Next, I picked source file "zbp11totals.txt", a CSV file. After that, the popup wanted the source data schema, like this

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:

11. 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 describe the application user experience.