A Proof-of-Concept of BigQuery

Getting the data into BigQuery

Dealing with Big Data, file size limits are an issue. We used the Unix command line 'split' to break the data file into chunks of the right size, taking care to break files apart on line boundaries rather than the middle of a record. Both Google Cloud Storage (GCS) limits and the BigQuery import limits have to be considered when doing this.

As soon as we had Google’s GCS command line tool gsutil successfully installed, we used it to move the data from Amazon into GCS, which turned out to be the slowest part of the whole process. The total rows took around 12 hours to transfer to GCS. We tried using the parallel upload facility in gsutil but this just led to our EC2 instance becoming I/O bound. With hindsight, we could have spent more time experimenting with Amazon regions, file sizes and parallel uploads to make this process quicker.

With the data in GCS, we next created a very simple text file to represent the schema and used it with the Big Query command line tool (bq) to set up tables in BigQuery. We checked it out first with a small subset of the data, doing a few queries from the BigQuery web console to be sure everything was suitable before we loaded the whole dataset.

The next stage was to complete the transfer from GCS into the newly defined BigQuery database. Because of the problems we ran into with parallel uploads into GCS from our source on Amazon, we didn’t try to use the parallel facility for the load into BigQuery. Later we found we could have done this and probably cut our 8 hour transfer time down to around 15 minutes.

Once we had all the data uploaded we tried a few queries. At the start, these were very slow, perhaps to do with some internal distributions not having happened inside of BigQuery. Initially these queries were taking a few minutes, but the next morning things took around 7 to 10 seconds and this remained reasonably consistent for the remainder of the exercise.

Creating Big Data web analytics using BigQuery and Google Charts

We decided to use Google App Engine to create a simple web front-end to our queries, with Google Charts embedded for interactive visualisation of the output.

Our web pages made RESTful calls to our App which in turn used the Java Big Query API (Python is an option as well) to make RESTful calls to invoke queries on the data. The results were then rendered client-side using the Google Charts libraries. In addition to the interactive charts, we were able to add a simple export mechanism, taking advantage of the fact that BigQuery results are saved into a temporary table which can be accessed via a “job ID”. This makes for a very simple and quick way to export the results of queries into GCS—and hence into other Google tools or as a CSV file. All of this was secured via OAUTH giving us fine-grained control over who could see, access and invoke queries using the App.

We began looking into changing our web app to use asynchronous mechanisms for invoking BigQuery, but ran out of time. That said, it looked pretty straightforward, using the jobID to query for intermediate results. Given the chance again, we would go with an asynchronous approach from start.

Conclusions and benefits

Overall we were impressed with what we were able to achieve in a short amount of time with BigQuery, the APIs and utilities. We were able to experiment with different ways of visualising and querying a very large volume of data with a relatively low investment in terms of time and without needing expensive infrastructure.

For organisations longing to gain insights into very large datasets, BigQuery could be a viable option that requires no purchases of specialised software or additional infrastructure. Even for organisations that already have an enterprise data warehouse, BigQuery is an option for testing out theories, or for other instances where the keepers of the data warehouse place restrictions on use.

Issues and Lessons

We encountered issues during the proof of concept that others can learn from.