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!
21 January, 2018
/ 7 Comments
