- 2 days ago
In this series we are going to dive into the Data Engineering side of Databricks!
This video will orchestrating jobs to automate our data pipelines.
Get the Data Here: https://github.com/AlexTheAnalyst/DatabricksSeries/tree/main/Data%20Engineering
Try out Databricks Free: http://signup.databricks.com/?provider=DB_FREE_TIER&utm_source=youtube&utm_medium=video&utm_campaign=AlextheAnalystDE
____________________________________________
RESOURCES:
💻Analyst Builder - https://www.analystbuilder.com/
📖Take my Full MySQL Course Here: https://bit.ly/3tqOipr
📖Take my Full Python Course Here: https://bit.ly/48O581R
📖Practice Technical Interview Questions: https://bit.ly/46pDqqL
Coursera Courses:
Google Data Analyst Certification: https://coursera.pxf.io/5bBd62
Data Analysis with Python - https://coursera.pxf.io/BXY3Wy
IBM Data Analysis Specialization - https://coursera.pxf.io/AoYOdR
Tableau Data Visualization - https://coursera.pxf.io/MXYqaN
*Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!*
____________________________________________
BECOME A MEMBER -
Want to support the channel? Consider becoming a member!
This video will orchestrating jobs to automate our data pipelines.
Get the Data Here: https://github.com/AlexTheAnalyst/DatabricksSeries/tree/main/Data%20Engineering
Try out Databricks Free: http://signup.databricks.com/?provider=DB_FREE_TIER&utm_source=youtube&utm_medium=video&utm_campaign=AlextheAnalystDE
____________________________________________
RESOURCES:
💻Analyst Builder - https://www.analystbuilder.com/
📖Take my Full MySQL Course Here: https://bit.ly/3tqOipr
📖Take my Full Python Course Here: https://bit.ly/48O581R
📖Practice Technical Interview Questions: https://bit.ly/46pDqqL
Coursera Courses:
Google Data Analyst Certification: https://coursera.pxf.io/5bBd62
Data Analysis with Python - https://coursera.pxf.io/BXY3Wy
IBM Data Analysis Specialization - https://coursera.pxf.io/AoYOdR
Tableau Data Visualization - https://coursera.pxf.io/MXYqaN
*Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!*
____________________________________________
BECOME A MEMBER -
Want to support the channel? Consider becoming a member!
Category
📚
LearningTranscript
00:00What's going on, everybody?
00:01Welcome back to another video.
00:02Today, we're gonna be orchestrating
00:04and automating our ETL pipelines in Databricks.
00:12Now, in the last two lessons,
00:14we've been building out our ETL pipeline.
00:16We've been writing all of our code
00:17and getting everything set up.
00:18But once we actually have everything set up,
00:20then we need to automate this process
00:22so that we don't have to manually go in
00:24and run the code ourselves.
00:26Luckily, Databricks has this already built out for us.
00:28It is called a job.
00:29And so we're gonna jump into Databricks.
00:31We're gonna create our own custom job
00:33and we're gonna see all the small things
00:34that you need to do in order to create this automation.
00:37Now, in our last lesson,
00:38we built out this bronze to silver to gold ETL pipeline.
00:41And we're basically creating two separate tables,
00:44this S3 underscore clean silver,
00:45and then this insights gold.
00:47And that is our silver and our gold tables
00:50after they're transformed and we find our business insights.
00:53Now, just for demonstration purposes,
00:55I also just kept our regular code in here as well.
00:58We have this bronze to silver,
01:00then we have another notebook for silver to gold.
01:02Now, these are just regular notebooks in Databricks,
01:05but I do wanna show you how you can use this
01:08within a job as well.
01:10But we have this bronze to silver transformation,
01:12and you can see it in a pipeline.
01:13And then if we just go to our bronze to silver,
01:15this is just a regular notebook.
01:17Now, in order to create our job,
01:19let's come right down here.
01:20We're gonna go to runs.
01:21We're gonna come over to job,
01:22and this is orchestrate notebooks,
01:24pipelines, queries, and more.
01:26So let's come in here.
01:28Now, this is a new UI for us.
01:30And what you can do here is you can orchestrate
01:32the different steps that you want within your job.
01:35If we click right down here,
01:37we can see all the things that we can do.
01:39We can create ingestion pipelines,
01:41or we can use existing ones.
01:42We can come down here and we can run notebooks,
01:45Python files, SQL queries, SQL files.
01:47And we have some more advanced things right down here,
01:51like if-else conditions,
01:52or you can create triggers from another job.
01:56And then we also have this ingestion and transformation.
01:59And these are really useful
02:00because if you have an ingestion pipeline,
02:02an ETL pipeline, or a database table sync,
02:05then you can just use those that you've already created.
02:07Now, we've created an ETL pipeline.
02:10Let's go ahead and click on this ETL pipeline.
02:12We're gonna come down here,
02:13and we're gonna click on this bronze
02:15to silver gold ETL pipeline.
02:17Now, I'm just gonna call this bronze
02:21to silver to gold, keep it simple.
02:24And all we would need to do is create this task.
02:27Now, of course, that would be a little too simple, right?
02:30But this is as simple as it can get
02:32for any type of pipeline orchestration
02:34that you're trying to do.
02:36Oftentimes, when I'm creating entire pipelines,
02:38and there's a lot of different steps to it,
02:40I package everything into an ETL pipeline,
02:43and then I just place it in here.
02:45And then what I'll do is I'll come over here
02:47to schedules and triggers.
02:49Now, we'll look at that in just a second, really quick.
02:51We can also trigger a full refresh on this pipeline,
02:54so we can click on this.
02:56We can also add notifications
02:57if you want to send this notification
02:59when it kicks off or when it finishes.
03:00We can also look at retries.
03:04Now, this is really important
03:05because sometimes you are gonna have things that fail
03:07just for a various number of reasons.
03:09Maybe you're trying to run this,
03:10but the data hasn't all imported yet,
03:12and so you're trying to run this transformation,
03:14but there's some connection issue,
03:15and that caused it to fail.
03:17You'd want to retry maybe an hour later
03:20or on a different day.
03:21You would want to attempt to try this.
03:24And so you can come in here,
03:25and you can say, okay, I wanna try this a ton of times.
03:28Let's try it 30 total times,
03:30and every single time, we're gonna wait
03:33maybe 30 or 40 minutes between each try,
03:36and then it'll keep trying until it is successful.
03:39Again, with this, you can notify yourself
03:41and make sure that you know what's happening,
03:43especially if this is a really important pipeline
03:45within your company.
03:46It is important to have these things set up
03:48so you don't have to manually go in there
03:50and see it failed last night
03:52and just never got a notification.
03:54It never tried again.
03:55So this would absolutely be something that you'd want to do.
03:58And then you have metric thresholds.
04:00You can set these,
04:01especially for something like a run duration.
04:03If you know this should take five minutes at most,
04:06you can set a timeout threshold
04:07or a warning threshold at maybe 30 minutes
04:09so that it isn't just gonna keep running
04:11because sometimes it gets stuck in these loops
04:13and it keeps trying and it's gonna run forever
04:15and it's gonna cost a lot of money
04:16and you don't want that to happen.
04:18So these are all really important things to think about
04:20when you are actually creating these jobs.
04:23Now let's come back here to schedules and triggers.
04:26For something like this,
04:28when you've done almost all the work in an ETL pipeline,
04:30you are gonna wanna schedule or trigger this most of the time.
04:34Now for something like this pipeline,
04:36what we've done is we've extracted data
04:38out of an S3 bucket.
04:39What we would wanna do is probably set a trigger for this.
04:42Now what we need to do is we need to create this task first
04:45so that it is saved in there.
04:46And then let's say this is our entire job.
04:49It's a very simple one.
04:50But now we can come in here and we can add a trigger.
04:53There are several different types of triggers.
04:55One, we have a schedule,
04:56which is as simple as it sounds.
04:59We are just going to schedule this.
05:01Right now it'll be active.
05:02You can pause it.
05:03We're just gonna schedule this
05:04and we'll say every one week.
05:07And so every one week we're gonna save this
05:09and this is gonna run every week.
05:11So that's super simple.
05:13Now let's delete this and let's add another trigger.
05:16We can also schedule it.
05:17We can go a little bit more advanced
05:19and we can schedule it at a very specific day and time.
05:22Now this is what I usually do
05:24because there are certain cadences and timing to things
05:27that I really like.
05:28For example, at a previous job that I used to work at,
05:31we wanted the data to be as fresh as possible
05:33because we actually had it refresh often,
05:35like every 10 minutes.
05:36And so what we were doing was we were trying to run it
05:39as soon as we could in the morning
05:41to where it would still run,
05:42but it would give us the freshest set of data
05:44by about 8.30 in the morning.
05:45So we would kick off this job at like 7.45
05:48so that the freshest data would be available by 8.30.
05:51This is more advanced.
05:52You don't have to do this,
05:54but this is a really useful thing to do.
05:57The next thing that you can do
05:58or the next type of trigger is a file arrival.
06:02So if we click on file arrival,
06:03we're going to say,
06:04when a file arrives at this location,
06:07kick off this job and run everything within it.
06:10Now for our process,
06:11this would be like our S3 bucket.
06:13If we can go and look at our S3 bucket,
06:15if a new file gets dropped in here
06:17or this gets updated,
06:18then we may trigger this job and it will run.
06:22And of course we have advanced settings as well,
06:25where we can wait a minimum time between triggers,
06:27because what if you're uploading a lot of documents at the same time,
06:30you don't want it to trigger 20 times
06:31because you just dropped 20 different files in there one at a time.
06:34You'd want to wait for all these files to get in there.
06:37So that is absolutely an option.
06:39And if we go back,
06:41we also have a table update.
06:43So this would trigger when new data is updated on a table.
06:47Now for our use case,
06:48this may work because we have S3 data.
06:51We're bringing it into our bronze table.
06:53So I can come in here and I can say,
06:55when this table,
06:56and I would just specify that table name that we've been using.
06:59When this bronze table gets updated from that S3 bucket,
07:03then kick off this job,
07:05which of course this ETL pipeline takes that bronze data.
07:08We transform all the data.
07:10We create our gold tables and then we have all that data sitting there.
07:13So this might be a really good use case.
07:15We have some advanced options down here,
07:17minimum time between triggers and wait after last change,
07:20just like we did before,
07:21because sometimes data gets updated continuously.
07:24And so it might trigger it many times.
07:26These are things that you should test and try out within your pipelines,
07:29just to make sure you get them right.
07:31Now let's cancel out of this.
07:33And let's actually get rid of this entirely.
07:36Let's actually come here and we're going to go back to our runs.
07:40Or sorry, back to our jobs.
07:43And I want to show you one more thing within here that might be really useful.
07:46Now we just kind of came down here and we pulled in this ETL pipeline,
07:50but let's actually pull in and run a notebook.
07:53So we're going to specify our notebook.
07:56We're just going to do this as our bronze to silver.
07:58And this is a notebook.
07:59It's within our workspace, not a Git provider.
08:02And let's select our notebook.
08:03So we're going to come in here.
08:04We're going to do bronze to silver.
08:06Let's confirm this.
08:07And you'll notice we have a lot of different options in here.
08:11Some similar, right?
08:12We have retries.
08:13We have notifications and we have metric thresholds, but we also have parameters.
08:17These are parameters that you can pass down to the task.
08:19Because this is just a notebook, it doesn't have all that built in stuff
08:23that we were talking about in the last lesson within the ETL pipeline.
08:26So you do need to configure this a little bit more within a job.
08:30So we can add these parameters where we create these kind of key value pairs that we pass
08:34into a notebook, but let's come in here.
08:38Let's create this task.
08:39And now we're going to add in another task.
08:42So let's come here.
08:42We're going to add in another notebook, and this is going to be our silver to gold.
08:50Now, these two tasks, and let's actually name this.
08:53These two tasks that we've created, these two notebooks, do the exact same thing as our pipeline.
08:59But I wanted to show you this because it does give us some more information
09:03when we're actually building out these jobs.
09:05So we specified our path, we have our computer serverless,
09:08but now we have something called a dependency or a dependency chain.
09:11This right here, this line, is a dependency.
09:15With what we have right now, this silver to gold is completely dependent on this bronze to silver,
09:21which means if we get this data in and this bronze to silver does not run correctly,
09:26then this silver to gold is never going to run.
09:29And in this use case, that's perfectly fine because this relies heavily on this bronze to silver.
09:35But there are going to be use cases where that is not the case,
09:38where we would not want that to be, you know, a dependency.
09:41We wouldn't have to rely on it.
09:43Or we also have an option right down here to run if dependencies.
09:48And we have a lot of different options.
09:50So right now, all succeeded means this has to run properly in order for this to run.
09:56But there are going to be cases when you create these chains or these dependency chains,
10:00where you're like, it doesn't matter if this one runs.
10:04We just want it to run after this one runs, whether it fails or not.
10:07And so for that one, you can come in here and say, at least one succeeded,
10:11none failed, all are done, at least one failed or all failed.
10:16It doesn't matter.
10:17You can specify whichever option you need.
10:19For us, we would want to keep this all succeeded because if this one runs,
10:24we don't actually create the silver tables that are needed in order to run this one.
10:28So that is pretty important.
10:30We can come down here and we can create this task.
10:33And now we have this job that we've created and we can run it now.
10:37Or of course, we could add in our trigger.
10:41Now, typically with something like this, it could go either way.
10:44You could have it on file arrival, table update, or a schedule.
10:47It really is just very dependent on your workflow and how you want this to trigger.
10:51For most of these, you're going to have some type of trigger.
10:54Let's just set it on a schedule and let's go to advanced.
10:57And we're going to set this for every week.
10:59And let's do this on a Monday.
11:02And let's do it at 745 because that's when I used to do our, some ones at a previous job.
11:07So I'm going to do at 745 every morning.
11:10Let's go ahead and schedule this.
11:11And now we've updated this job.
11:13And now we can also rename this.
11:16I'm going to call this our silver to gold job.
11:21So now if we go back to our jobs and pipelines, we have our silver to gold job right here.
11:27This was the pipeline that we built out in the last lesson.
11:30And this is going to be orchestrated and scheduled to run this pipeline.
11:35Well, actually, we used the notebooks instead of the pipeline for that last example, but we're
11:39going to be running that code to actually create and update those tables.
11:42So that is how we create a job in Databricks.
11:45This is extremely, extremely useful.
11:47Again, like we did just a little bit ago for our silver to gold job.
11:52And let's go into the tasks.
11:53If it's a really small transformation and maybe it's just for me,
11:57I'll just do it like this where I just have the notebooks.
12:00But if it's a larger transformation, especially if there's a lot of dependencies,
12:04if there's a lot of complexity, I will use an ETL pipeline.
12:08So get in here, mess around with this, try this out, because this is super fun to play
12:12around with and kind of get all those dependency chains going and getting the ETL pipelines where
12:16they're triggering off of each other or when a file is updated.
12:19This is really cool stuff to mess around with and is awesome to use within Databricks.
12:23I really hope that this was helpful.
12:25If you haven't, be sure to create a free Databricks account.
12:28I will have a link down in the description and you can try all of this completely for free.
12:32If you liked this video, be sure to like, and subscribe, and I'll see you in the next lesson.
Comments