Ingesting Data into Databricks | Data Engineering in Databricks

Key Takeaways

What's going on everybody? Welcome back to another video. Today, we're going to be ingesting data into Databricks. Now, in this series, we're going to be focusing on the data engineering side of Databricks. We're going to be ingesting data, we're going to be transforming it, loading it. We're going to be working in a ton of different things, and we'll have a full project at the end. In this video specifically, we're going to be focused on just getting data into Databricks. Sometimes that is as simple as just uploading a CSV file, and sometimes you have to connect to an external data source. And we're going to see how we can do both of those in this lesson. Before we jump into this data engineering series and start doing all the things, I want to slow down for just a second to take a look at what ELT is in Databricks. This is the process that we're going to be walking through for this entire series. We're extracting data, we're getting our data into Databricks. That involves loading the data into different schemas and having that data available, and then we transform that data. Now, ELT might sound odd because most people are used to ETL, where you extract data, you transform it, and then you load it into the database. With a lot of modern data workflows, it doesn't actually make much sense to transform your data before because compute is quite cheap these days. And so, you can just load your data into Databricks and then transform it after. Now, there is something called the medallion architecture. We're going to take a look more at that in the next lesson when we take a look at bronze, silver, and gold architecture. Now, this is a really great way to kind of stage your data, and it's been like this for a long time, even before Databricks. But, we'll be going into why and how we actually do that within Databricks. Our data, when we actually get it into Databricks, is being stored in a Delta table. It's kind of like a Delta file type, which is basically just a parquet file that has this log system where you can kind of revert back and see previous changes to the actual document. And so, we store our data in these Delta tables, and then we can do all of our transformations on it within a notebook or within SQL queries. Now that we've got that out of the way, let's actually jump into Databricks and see how we can do this. All right, here we are on Databricks, and we're going to be doing two things. One, we're just going to upload a CSV file. It's probably the simplest way to get data into Databricks, but then we're also going to connect to an S3 bucket. And so, I'm going to show you how you can do that really easily. And we're going to get all of our data into Databricks. Now, we are just working with sample data for this lesson. But, at the last one, when we start doing our full ETL process and automating this entire thing, then we'll be using real data, and so it'll be a lot more, a little bit more complex. There's a few ways to ingest data. One, you can just click on this bring in data, and it's going to take you right down here. But, we can also just go to our data ingestion. And so, when we click on this, we're going to upload files to a volume, or we're going to create and modify a table. Now, these are two separate things, and these are important things to understand. Let's actually come over here to catalog for a second. And what we're going to do is we're going to come over here, and we're going to create a new catalog. And we're just going to call this one our data engineering Uh that's all we're going to call it. I was going to keep going, but we'll call it the data engineering one. And let's go ahead and view this catalog. Now, when we create a schema, we're going to say this is uh video one. Let's go ahead and create this. We have within our data engineering, we have our default, and then we have this information schema, but we also have this video one. Now, we don't have any data in the schema. But, what we can do is we can create different ways to store our data. We can store it in a volume, or we can store it in a table. Now, in a previous series, I kind of dove into these and how you can store your data, as well as how to access the data once you put it in. We're going to be putting all of our data into tables, so I'm just going to set up one table. Now that we're here though, we can do the same thing that we would do if we came over to our data ingestion, which is basically just drop a file in here like you would on any platform. Let's just go over to the data ingestion just so we get the full experience. We're going to create or modify a table. We're going to select our CSV file, so it's just our users_dirty. I'm going to go ahead and upload this. So, now we have this preview of our data, and we're going to specify what we want to do with it. We could create a table, we could overwrite an existing table, and we want to put this in our data engineering video one. So, if it doesn't automatically populate, you can always just specify where you want to place it. And we're going to call this _csv because we're going to be bringing in the same file from an S3 bucket. So, I just want to specify where we got this data. Let's come down here, and we're going to create our table. So, now we have our data sitting in our video one schema. So, this is our users_dirty_csv. This is the simplest way to get data into Databricks. But, I also have this exact same data sitting right over here in an S3 bucket. And I want to use it. I want to connect to this data. I want to pull it in automatically, and that's going to really help us later on down the line when we start automating this whole process because we're going to create a connection to this data source so we can automatically pull this data in. And that's a big part of just data engineering in general, which is creating systems that can automatically ingest, transform, and load your data. So, what we're going to do is we're going to come right over here. Now, I just want to show you this. I'm going to have a link down below so that you can see this as well. But, this is basically just how you're going to create the connection. I'm going to show it to you in a second. It is very, very simple. So, let's come right over here, and what we're going to do is we're going to come down to data ingestion. Now, we want to go to the Databricks connectors, and we want to go to our Amazon S3 bucket right here. And we need to create an external location. So, we're basically connecting our Databricks account to our Amazon S3 bucket. And then, we can bring that data in very easily. What we're going to be using is this AWS quick start. Let's go ahead and select next. We need to put in our bucket name. So, I'm going to come over here. Let's click in here. We can actually get it right here. There's other places to get it, but I'm just going to copy from here. >> [snorts] >> Uh we're going to go back to our catalog explorer, and there's our bucket name. So, now, what we're going to do is we're going to generate this new token, and we're going to copy this. Now, we're going to come over here to launch in quick start. And all we have to do is it's going to connect to our accounts, that makes it pretty easy if you're already logged in. Then, we're going to come down here, and we're going to say I acknowledge, and we're going to say create stack. It's going to come right here, and it's going to say create in progress. It's just going to validate it for a second. I had already done this uh before when I was making this video earlier just to confirm everything was working smoothly. And what it's going to do is it's just going to say create complete, and then you're going to be good to go. All right, so that took about 2 minutes, and it says it is complete. So, all we're going to do is come back here, and we're going to refresh this page. So, I'm going to go ahead and refresh. And now that that connection is active, we now have access to our users_dirty. Let's come over here. We're going to click on this, and we're going to go to preview table. So, now we get this preview of the exact same data set. There's nothing changed. I'm not trying to trick you. All we have to do is we're going to come up here to data engineering, and we're going to go to video one. And then, we need to name this. So, I'm going to call this one dirty_data_s3. So, I'm just naming it this purely so that we know which one came from which. Let's come down here to create table. And now we can see over here we have our dirty_data_s3 and our users_dirty_csv. I named it completely wrong. But, these are the exact same data sets, and now we have them in from two separate locations. Now, this is really important, especially as we start automating a lot of this. If you have data that's sitting in an S3 bucket, and you have other systems that then upload it into that bucket, we're going to be able to ingest that data automatically, whether it's updated or if it's a new file, and we'll set all sorts of triggers and schedules and all sorts of really cool things in later lessons. So, that's how we ingest data within Databricks. In our next lesson, we're going to be transforming data within an actual data pipeline. So, we're going to have the entire ingestion process as well as the transformation process all in one place. Thank you guys so much for watching. I really appreciate it. If you like this video, be sure to like and subscribe, and I will see you in the next lesson.