MARTIN: Hi, Mete.
METE: Hi, Martin, what's going on?
MARTIN: I loaded some e-commerce order data into BigQuery.
Now I want to transform that data
and create a new table that's optimized for my business
intelligence dashboard.
Somebody told me you might have pointers for how to do that.
METE: Yes, you should check out DBT.
It's a third party tool to transform data by simply
writing select statements.
It doesn't extract or load data but it
is extremely good at transforming
data that's already loaded into your warehouse.
MARTIN: Cool!
Will do.
[THEME MUSIC]
Hi, Mete.
Thanks for your tip about DBT.
I found a really nice tutorial on how to transform data
within BigQuery.
It does pretty much what I need.
It joins a customer and an order table
into a new dashboard friendly table.
I got it working on my Google Cloud account, can I show you?
METE: Sure, let's see.
MARTIN: All right, I'll switch over here to this.
I'll go into the jaffle shop example that I loaded.
I'm running DBT Run.
We'll see if it runs.
So there, it says completed successfully.
And if I go over to my project and I go into BigQuery
and I go into my customers' data set here.
I have a table and a customer table
that was created by this job.
I click Preview and sure enough, there's
my denormalized customer data.
METE: Good job, Martin.
But I have one question.
How are you going to run this in the long run?
I can imagine typing DBT Run every day will get old
pretty quickly.
Also, you probably want BigQuery to always
have the latest data even when you're out on vacation.
MARTIN: OK.
Well, I guess I could trigger the command on a schedule
from a cron job on my laptop.
METE: Wouldn't your laptop be closed when you're on vacation?
MARTIN: Maybe I could get a virtual machine
to run this in the Cloud.
METE: You could, but why keep a virtual machine running 24/7
when you only need for a few seconds per day?
And you probably don't want to add server maintenance to your
to do list.
MARTIN: Ugh, I hate managing servers.
I'm not good at it.
METE: Let's go serverless instead.
MARTIN: But how would I run a custom command line
tool from a serverless product like Cloud Functions?
The Cloud Functions runtime doesn't have DBT installed.
METE: That's true, but we have other options.
In Cloud Run you can create your own runtime with a container.
There's a base container image with DBT,
we can start with that.
MARTIN: That sounds great.
How do I set that up?
METE: Open your Google Cloud project and I'll show you.
MARTIN: All right, so I'm here in my project.
METE: Cloud Run lets you run containers
without having to maintain a server
and you only pay when your code runs.
MARTIN: Cool!
But I don't know much about containers or .go As a matter
of fact, I don't even have .go installed here on my laptop.
METE: No worries, we'll build the container
using Cloud Build.
Your code in Cloud Run is triggered by HTTP requests.
In this case, we will run the DBT command
in a shell script in response to an HTTP request.
Let's find a simple HTTP server that can run shell scripts.
Go to Cloud Run tutorial and find a sample
for running a shell script in Cloud Run
and copy that code to your machine.
MARTIN: OK, so I'm in the quick start
for build and deploy on Cloud Run.
Scrolling down the page here, I want to run a shell script.
Here's a shell script.
OK, it seems like there is a Go program here
that can run shell scripts.
So I copy that, I paste that into my machine.
OK, I've saved it as invoke.go on my machine.
METE: Cool.
Then save your comment in a script file.
MARTIN: All right.
I did that already.
I have a script.sh here on my machine.
METE: Now let's containerize our code.
I sent you a .go file for the container image.
We are relying on a base image that includes the DBT tool.
We are also copying the go HTTP server along with the script
file to the container.
The entry point to the container is the HTTP server.
Let's build the container with Cloud Build,
I sent you the command for doing that.
MARTIN: All right.
You sent me, let's see, build container.
So I'm running that now.
All right, it finished building the container.
METE: Let's deploy the container to Cloud Run.
That's the second command I have sent you.
MARTIN: All right.
I got it.
So deploy container, very good.
I'm running that now.
All right.
The script for deploying container finished running.
So it says something about serving traffic 100% at URL
and there's a URL with a strange hash code in it.
What's this URL?
METE: That's the service URL.
When you hit that URL, the code you just uploaded runs.
Try it!
MARTIN: Oh, cool.
All right.
So I'm going to my browser here, pasting it in, and running it.
Let's see what happens.
Oh, cool.
So I see the printout that I previously got from my DBT job
in my console, I now see that in my web browser.
And let's see, if I go over to BigQuery and refresh here.
I open my customers data set, click the customer table,
and let's see.
There is a table there, I click Preview.
And sure enough, there's my denormalized customer table,
including order dates and everything.
METE: Nice.
MARTIN: Great.
All right.
So let me delete this table again to make sure
that we can recreate it.
Customers, delete.
So I moved my DBT job from my laptop to the Cloud.
I can run the DBT job by hitting URL,
but I still have to manual hit that URL from my browser.
The job won't run by itself when I'm on vacation.
METE: That's right, but we can fix that.
Cloud Scheduler is a service that can trigger an end
point on a given schedule.
Let's go to Cloud Scheduler page and create a new job.
MARTIN: All right, I'm clicking Cloud Scheduler here.
Create new job, and, OK, so I have a bunch of fields here.
I guess for a name I'll say, right, DBT job, perhaps.
Frequency, this is something with stars and numbers.
What's this about Mete?
METE: So that's the frequency that the Cloud Scheduler
will call your service.
It's in cron format.
So I guess in this case, we want to run it once a day every day
in the afternoon.
So we will enter something like 016, star, star.
MARTIN: Very good, I did that.
And let's see, time zone.
I want it to be in my time zone.
I'm in the Pacific, US Pacific Standard Time.
Oh and I needed to fix the name here.
All right, so Mete, the next one is target.
So I can choose between HTTP, pub sub, and App Engine HTTP.
What should I pick here?
METE: So in this case, we want to hit a Cloud Run
service, which is an HTTP end point, so we'll choose HTTP.
MARTIN: Oh, OK.
HTTP it is.
And for URL, I guess I should paste in that URL
that I hit manually before?
METE: Yes, that's the Cloud Run service URL.
MARTIN: Excellent.
And if I remember correctly I hit it with a git, with a post.
I'm clicking Create here.
And look, there's my new job.
METE: Yes, it's created but it's not running yet.
We can actually test it out by just clicking the run now
button to trigger it manually.
MARTIN: Oh, cool.
OK, I'm clicking Run now.
Uh-huh, very good.
Something happened.
All right, so I'm going into my project here in BigQuery.
I see the customers data set there
and there's the customers table that I deleted before.
It has been recreated.
Let's just have a look at the preview of that table.
And yep, there is my denormalized order data.
Table is there, everything is working.
But Mete, I'm accessing this from an incognito window.
I just hit the URL here and it just came through.
Can anyone access that URL and run my job?
METE: That's right, it's a public service right now
but we can make it private so only authenticated users can
access it.
Then we can configure class schedules
to use a service account to access this private service.
I have written a tutorial that explains
how to do that, the link is in the description below.
MARTIN: OK, so let's summarize what we did.
We started with Cloud Scheduler that
hit a URL on a schedule that triggered a Cloud Run job.
That Cloud Run job ran a script file, a shell script file,
which in turn is running a DBT command that updates,
transforms the data in my BigQuery data warehouse.
METE: Exactly!
And the key point is that we run DBT commands without having
to maintain servers and we run it on a schedule
so that you don't need to be around to trigger it every day.
MARTIN: I like that I don't have to remember
to run this every day manually.
METE: Now you can go back to the beach
without having to think about service, Martin.
[CHUCKLES]
[THEME MUSIC]