Featuring the Metropolitan Museum of Art and the Cloud Vision API 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. BigQuery 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 Intelligence Analysts or Data Scientists. Business BigQuery also comes with public datasets ( , or ). What is best is that the list keeps being updated on a regular basis. eg. hackernews stackoverflow github_repos 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 . bigquery-public-data.the_met The dataset The 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. the_met On top of that, all of the images have been annotated thanks to the : this API features several pre-trained computer vision models, providing rich visual information (among which image classification, face detection and localisation, Optical Character Recognition). Cloud Vision API 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. Logo Detection request to Cloud Vision with , David (1787) The Death of Socrates The dataset consists of 3 tables: for image url the_met.images for object metadata the_met.objects for vision api-generated annotations the_met.vision_api_data The common key is the . object_id More information about is available in from Sara Robinson. the_met this post 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 **EXCEPT** Modify arrays inline with **ARRAY** When SQL cannot handle it, just JS it Bonus part on BigQuery ML : The following examples will be using Standard SQL, which, in general provides . They assume you are already familiar with BigQuery, row aggregation, records, repeated fields and subqueries. Disclaimer more features than BigQuery Legacy SQL 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 idea lies in hashing a column field present in all rows and unique for each of the rows, with . the_met.objects eg. [FARM_FINGERPRINT](https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#farm_fingerprint) 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 . object_number The above query returned 80221 lines out of 401596 ( 19.97%). Yay! ie. Samples of objects with their number, FARM_FINGERPRINT hash, corresponding group and title 2. Shorten your queries with EXCEPT An often overlooked keyword is . This allows you to query all columns except a subset of them. EXCEPT It proves useful when the table schema is very furnished, like in . the_met.objects For example: Another example with the image annotations available in . Among the available annotations, there are the : the_met.vision_api_data faceAnnotations Schema for `face_annotations` in `the_met.vision_api_data` What if we were interested in all columns except and , would this query work? faceAnnotations.surpriseLikelihood faceAnnotations.sorrowLikelihood In practice, this query is not allowed as it references a nested field in . needs to be before referencing. EXCEPT faceAnnotations UNNEST ed The above query works but are now unnested. faceAnnotations The next section shows how to perform the task while preserving the nested structure, thanks to keyword . In general, maintaining nested structures turns out to be more cost-effective in terms of storage and processing power, compared to fully flattened tables. ARRAY 3. Modify arrays inline with ARRAY In BigQuery, fields (aka. fields) refer to columns for which one line can have several corresponding values. For instance, in , one object can correspond to several : ARRAY REPEATED vision_api_data faceAnnotations To one line (=one `object_id`) can correspond several `faceAnnotations` You may know about or , but have you heard of often overlooked ? ARRAY_AGG ARRAY_LENGTH ARRAY According to the , returns an with one element for each row in a subquery. Let us look at several use cases. documentation ARRAY(subquery) ARRAY 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. Notice that is necessary when querying multiple columns within an clause. SELECT AS STRUCT ARRAY 3.2 Filter lines in an ARRAY field This time, instead of filtering out columns, we will filter out lines within an , based on a condition on a nested column. ARRAY Assume we are not interested in faces which are very likely to be under-exposed ( ). ie. underExposedLikelihood='VERY_LIKELY' Above: `faceAnnotations` before `WHERE` filtering Above: `faceAnnotations` after `WHERE` filtering 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. `webDetection` schema Let us enrich this schema with matching from (image urls of the museum objects). This is target schema: object_id the_met.images Target enriched `webDetection` schema The corresponding query and the result Samples of the `webDetection` schema enrichment. The output schema has been simplified for readability. 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 BigQuery allows you to write your own (UDFs) in JavaScript, which can prove useful when StandardSQL does not support what you are trying to achieve. User-Defined Functions For instance, we could be interested in extracting, from , the with the second highest , for each . In StandardSQL, you can select top lines but not the second top line (at least not in a single query). vision_api_data labelAnnotation score object_id the latter task is actually achievable quite simply. Please refer to the comment section to see how it can be done. EDIT: This can be achieved with The result We prepended the table schema with the argument of the UDF for convenience. Note that UDFs, at the time this post was written, are not supported in BigQuery views. 5. Bonus part on 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 . BigQuery ML 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. Logistic Regression task: predict whether or not an art piece has a face representation We will rely on the Cloud Vision API for ground-truth labelling of presence of a face: IF( ARRAY_LENGTH(faceAnnotations)=0 OR faceAnnotations IS NULL , 0 , 1) AS label We will use a linear for this purpose. logistic regression 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 (if not, create it), the syntax looks like the following: bqml Notice the mixture of types in the input features: , , . STRING BOOLEAN INTEGER 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 [{"precision": "0.6363636363636364","recall": "4.160228218233686E-4","accuracy": "0.8950143845832215","f1_score": "8.315020490586209E-4","log_loss": "0.3046493172393632","roc_auc": "0.857339"}] As a side note, the prior distribution is the following: [{"pc_with_faces": "0.1050043372170668","pc_no_faces": "0.8949956627829332"}] 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 and . Other unstructured description text fields could have been exploited but this goes beyond the current scope of BigQuery ML. culture classification If you are keen to serve Machine Learning in the cloud, but find BigQuery ML to be limiting, you may find my useful. previous article 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 Photos credits: kevin laminto on Unsplash
