Scheduling BigQuery jobs: this time using Cloud Storage & Cloud Functions

Intro

Post update: My good friend Lak over at Google has come up with a fifth option! He suggests using Cloud Dataprep to achieve the same. You can read his blog post about that over here. I had thought about using Dataprep, but because it actually spins up a Dataflow job under-the-hood, I decided to omit it from my list. That’s because it will take a lot longer to run (the cluster needs to spin up and it issues export and import commands to BigQuery), rather than issuing a query job directly to the BigQuery API. Also, there are extra costs involved with this approach (the query itself, the Dataflow job, and a Dataprep surcharge – ouch!). But, as Lak pointed out, this would be a good solution if you want to transform your data, instead of issuing a pure SQL request. However, I’d argue that can be done directly in SQL too ūüėČ

Not so long ago, I wrote a blog post about how you can use Google Apps Script to schedule BigQuery jobs. You can find that post right here. Go have a read of it now. I promise you’ll enjoy it. The post got quite a bit of attention, and I was actually surprised that people actually take the time out to read my drivel.

It’s clear that BigQuery’s popularity is growing fast. I’m seeing more content popping up in my feeds than ever before (mostly from me because that’s all I really blog about). However, as awesome as BigQuery is, one glaring gap in its arsenal of weapons is the lack of a built-in job scheduler, or an easy way to do it outside of BigQuery.

That said however, I’m pretty sure that the boffins over in Googley-woogley-world are currently working on remedying that – by either adding schedulers to Cloud Functions, or by baking something directly into the BigQuery API itself. Or maybe both? Who knows!

BigQuery & new users – the top “WTF!?” moments

“What the Fudge?”

I use Google BigQuery a lot. On a daily basis I run dozens of queries, use it to build massively scalable data pipelines for our clients, and regularly help new users navigating it for the first time. Suffice it to say I’m somewhat accustomed to its little quirks. Unfortunately, the same can’t be said for the new users who are commonly left scratching their heads, and shouting “What the fudge!?” at their monitors.

Here’s the top three WTFs that I regularly hear from new BigQuery users:

Beam me up Google – porting your Dataflow applications to 2.x

Will this post interest me?

If you use (or intend¬†to use) Google Cloud Dataflow, you’ve heard about Apache Beam, or if you’re simply¬†bored in work today and looking to waste some time, then yes, please do read on. This short post will cover why¬†our team finally took the plunge to start porting some of Dataflow applications (using the 1.x Java SDKs) to the new Apache Beam model (2.x Java SDK).¬†Spoiler – it has something to do with this. It will also highlight the biggest changes¬†we needed to make when making the switch (pretty much just fix some compile errors).

Creating a serverless ETL nirvana using Google BigQuery

Quite a while back, Google released two new features in BigQuery. One was federated sources. A federated source allows you to query external sources, like files in Google Cloud Storage (GCS), directly using SQL. They also gave us user defined functions (UDF) in that release too. Essentially, a UDF allows you to ram JavaScript right into your SQL to help you perform the map phase of your query. Sweet!

In this blog post, I’ll go¬†step-by-step through how I combined BigQuery’s federated sources and¬†UDFs to create a scalable, totally serverless, and cost-effective ETL pipeline¬†in BigQuery.

Google Cloud Dataproc and the 17 minute train challenge

multiple-seats

My work commute

My commute¬†to and from work on the train is on average 17 minutes. It’s the usual uneventful affair, where the majority of people pass the time by surfing¬†their mobile devices, catching a few Zs, or by¬†reading a book. I’m one of those people¬†who¬†like to check in with¬†family & friends on my phone, and see what they have been up to back home in Europe, while I’ve¬†been snug as a bug in my bed.

Stay with me here folks.

But aside from getting up to speed with the latest events from back home, I also like to catch up on the latest tech news, and in particular what’s been happening in the rapidly evolving cloud area. And this¬†week, one news item in my AppyGeek feed immediately jumped off the screen at me. Google have launched¬†yet¬†another game-changing product into their cloud platform¬†big data suite.

It’s called Cloud¬†Dataproc.

Test Driving Google Cloud Dataflow

Bq_tOGxCMAELB4k

Back in June 2014, at the annual Google IO in San Francisco, Google unveiled their newest, and much hyped cloud product, Cloud Dataflow. The demo they did that day, using a live twitter feed to analyze supporter sentiment during the 2014 world cup, got my mouth watering at the prospect of working with it. It looked downright freaking¬†awesome, and I just couldn’t wait¬†to get my hands on it to take it for a spin.