Using native BigQuery functionality to generate a dynamic, unique row identifier in SQL — Motivation There are a number of different reasons you might want to build a hash of each row of data in a BigQuery dataset, but they are mostly related to the fact that there is no enforced primary key in BigQuery. This means that your data might contain duplicate rows, whether…

A powerful and fundamental building block which enables custom extension of the core BigQuery platform functionality — Motivation Extensibility is one of the key BigQuery characteristics — along with the continuous development of superpower-like new functionalities — which makes it such a powerful platform for any type of data work, big or small. However the feature set of any platform can be overwhelming, and it can be difficult…

Minimise context switching and make your workflow faster — There are a number of awesome new tools which aim to bring more interactivity, visualisation, collaboration and sharing to SQL workflows: HEX, Count and Hyperquery are three great examples. But sometimes you just want to focus on working in a basic console, with a few extra features to help accelerate…

Powerful foundational techniques to help unlock the power of BigQuery scripting and automation — Working with strings in BigQuery became a lot more powerful with the advent of scripting, and specifically the EXECUTE IMMEDIATE statement. This enables you to construct SQL queries as strings and then execute the constructed SQL queries in ad-hoc or scheduled scripts, or inside a callable PROCEDURE or FUNCTION.

Demystifying this powerful but potentially difficult to grasp aspect of BigQuery functionality, one simple step at a time — Scripting in BigQuery is incredibly powerful but syntactically complicated, especially to anybody who is used to a concise, descriptive, for the most part sequential and human-readable language such as Python. The official documentation on Scripting is a great reference but I have always found it difficult to find a guide…

A simple method for working with BigQuery results in Python (for pandaphobes) I love Pandas. They’re so cute and cuddly and make great pets. On the other hand, I have to admit that I’m not the biggest fan of the Pandas Python Library… while I do use it (and it…

Because simple is better than complex (but complex is better than complicated) — The first Cloud Function I ever deployed was to achieve exactly this task. We had a number of CSV files being dropped into a Google Cloud Storage bucket every night, which needed to be available for transformation and analysis in BigQuery. Spending some time on Google brought me to this…

Analyse the contents of over 25 million books in a matter of seconds When I was at college, finding specific information involved a pretty low-tech and inefficient process. You had to scan through book titles on a standalone computer (from my recollection you could at least search by keywords) to…

Because I don’t carry my Oxford English Dictionary around any more. There are loads of things you probably don’t know about Google Chrome, but this trick is very simple and useful. Step 1: Type `define:` followed by the word you want to look up into the search bar. Step 2…