Featuring the Metropolitan Museum of Art and the Cloud Vision API
BigQuery is a data warehousing solution provided by Google Cloud. Organisations use data warehouses to gather several sources of data into a single entity, as well as to reshape them into SQL databases with business-oriented schemas.
This allows collaborators of an organization to gain access to multiple sources of analysis-ready data through a unique service, at a few SQL queries away.
Thus, cross-source data analysis is easily enabled.
This type of service comes in to be very useful for any data-driven company function, in particular Business Intelligence Analysts or Data Scientists.
BigQuery also comes with public datasets (eg.
github_repos). What is best is that the list keeps being updated on a regular basis.
If you are new to BigQuery and would like to explore these open data, you can find valuable information here: try BigQuery for free.
In addition, are some pointers to interesting analysis of BigQuery public datasets from Felipe Hoffa:
- All the open source in Github now shared within BigQuery: Analyze all the code!
- These are the real Stack Overflow trends: Use the pageviews
- Who contributed the most to open source in 2017? Let’s analyze Github’s data and find out.
I have personally been working with BigQuery for almost a year as part of my work and here are some learnings I picked up along the way, which you may find useful. To support my statements, I will use public dataset
the_met dataset gathers data from 200k art objects from the Metropolitan Museum of Art of New York. The data consists in object metadata as well as picture representation.
On top of that, all of the images have been annotated thanks to the Cloud Vision API: this API features several pre-trained computer vision models, providing rich visual information (among which image classification, face detection and localisation, Optical Character Recognition).
These annotations are made available in the dataset and contain a lot of nested fields, making of it a great playground to wrangle with data.
The dataset consists of 3 tables:
the_met.imagesfor image url
the_met.objectsfor object metadata
the_met.vision_api_datafor vision api-generated annotations
The common key is the
More information about
the_met is available in this post from Sara Robinson.
How about the tricks?
The tricks described here do not necessarily address complicated cases. They are rather intended to help you write shorter and more efficient queries, often times with overlooked commands.
The topics we will cover are as follows:
- Idempotently split table lines at random
- Shorten your queries with
- Modify arrays inline with
- When SQL cannot handle it, just JS it
- Bonus part on BigQuery ML
Disclaimer: The following examples will be using Standard SQL, which, in general provides more features than BigQuery Legacy SQL. They assume you are already familiar with BigQuery, row aggregation, records, repeated fields and subqueries.
1. Idempotently split table lines at random
When experimenting with Machine Learning and BigQuery data, it may sound useful to be able to randomly split tables, with a given fraction.
It is even better if the query is idempotent: whenever it is ran, no matter how many times, the result will remain the same.
The use case arises when splitting a dataset into Training and Development sets.
For instance, let us perform a 80–20 split of
the_met.objects. The idea lies in hashing a column field present in all rows and unique for each of the rows, eg. with
The arithmetic properties of the integer hashes can then be exploited to discriminate lines idempotently.
For 20% splits, the modulo 5 value can be used. Here, this field could be
The above query returned 80221 lines out of 401596 (ie. 19.97%). Yay!
2. Shorten your queries with
An often overlooked keyword is
EXCEPT. This allows you to query all columns except a subset of them.
It proves useful when the table schema is very furnished, like in
Another example with the image annotations available in
the_met.vision_api_data. Among the available annotations, there are the
What if we were interested in all columns except
faceAnnotations.sorrowLikelihood, would this query work?
In practice, this query is not allowed as it references a nested field in
faceAnnotations needs to be
UNNESTed before referencing.
The above query works but
faceAnnotations are now unnested.
The next section shows how to perform the task while preserving the nested structure, thanks to keyword
ARRAY. In general, maintaining nested structures turns out to be more cost-effective in terms of storage and processing power, compared to fully flattened tables.
3. Modify arrays inline with
ARRAY fields (aka.
REPEATED fields) refer to columns for which one line can have several corresponding values. For instance, in
vision_api_data , one object can correspond to several
You may know about
ARRAY_LENGTH, but have you heard of often overlooked
According to the documentation,
ARRAY(subquery) returns an
ARRAY with one element for each row in a subquery. Let us look at several use cases.
3.1 Filter out a nested field, while keeping the nested structure
Let us tackle the previous example once again, but with a more elegant approach this time.
SELECT AS STRUCT is necessary when querying multiple columns within an
3.2 Filter lines in an ARRAY field
This time, instead of filtering out columns, we will filter out lines within an
ARRAY, based on a condition on a nested column.
Assume we are not interested in faces which are very likely to be under-exposed (ie.
3.3 Enrich an ARRAY with a JOIN ON a nested field
On the top of the face annotations, the Cloud Vision API provides with web detection features, similar to Google Image reverse search.
Let us enrich this schema with matching
the_met.images (image urls of the museum objects). This is target schema:
The corresponding query
and the result
From there you could, for instance, evaluate image retrieval performance of Google’s reverse image search.
4. When SQL cannot handle it, just JS it
For instance, we could be interested in extracting, from
labelAnnotation with the second highest
score, for each
object_id. In StandardSQL, you can select top lines but not the second top line (at least not in a single query).
EDIT: the latter task is actually achievable quite simply. Please refer to the comment section to see how it can be done.
This can be achieved with
Note that UDFs, at the time this post was written, are not supported in BigQuery views.
5. Bonus part on BigQuery ML
BigQuery ML is one the newest features of BigQuery. It allows you to build, train, deploy Machine Learning models only with a few SQL commands. This can save you the time of building data pipelines and perhaps spinning up other services like Google AppEngine.
For the sake of the example, let us see if some of an object’s metadata can be good predictors of whether or not a face is represented on it.
We will rely on the Cloud Vision API for ground-truth labelling of presence of a face:
ARRAY_LENGTH(faceAnnotations)=0 OR faceAnnotations IS NULL
) AS label
We will use a linear logistic regression for this purpose.
Creating a model on BigQuery is as simple as pre-pending your training set query with a few commands.
Provided you have created a dataset named
bqml (if not, create it), the syntax looks like the following:
Notice the mixture of types in the input features:
By default, BigQuery handles this diversity with no problem by one-hot encoding the non-numerical features. Also notice we have used the above-mentioned idempotent splitting trick to train the model on 80% of the data.
After training, let us evaluate the model on the development set (the other 20% of the data) with:
The evaluation query returns
As a side note, the prior distribution is the following:
Given the very low recall score and that the accuracy score barely exceeds the prior distribution, we cannot say the model has learnt from the input features 😊.
Nevertheless, this example can be used as an intro to BigQuery ML.
To improve model performance, we could have looked at string preprocessing and factoring for fields like
classification. Other unstructured description text fields could have been exploited but this goes beyond the current scope of BigQuery ML.
If you are keen to serve Machine Learning in the cloud, but find BigQuery ML to be limiting, you may find my previous article useful.
At the time this post was written, BigQuery ML was available as a beta release.
And that is a wrap! Thank you for reading this far. I really enjoyed sharing those learnings so I hope they will be useful to some.
At Sephora South-East Asia (Sephora SEA), we leverage BigQuery to enable easier data-backed decisions as well as to better understand our customers.
The Data Team at Sephora SEA takes care of internally democratising data as much as possible, in part through SQL trainings on BigQuery.
This article is dedicated to them, as well as to all of our trainees and past graduates.
And as my colleague Aurélien would proudly say:
SELECT thanks FROM you