Working with Data in Databricks (Free Edition)

Alex The Analyst

Take my Full Databricks Course Here: https://www.analystbuilder.com/courses/modern-data-workflows-in-databricks  There a lot of ways that you will interact with data in Databricks! In this lesson we dive into connecting to data, the SQL Editor, and Notebooks!  Get a Databricks Free Account Here: http://signup.databricks.com/?provider=DB_FREE_TIER&amp;utm_source=youtube&amp;utm_medium=video&amp;utm_campaign=AlextheAnalyst  Get the data here: https://github.com/AlexTheAnalyst/DatabricksSeries  Hackathon Link: https://www.databricks.com/resources/free-edition-hackathon ____________________________________________   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  Udemy Courses: Python for Data Science - https://bit.ly/3Z4A5K6 Statistics f

Transcript

00:00What's going on everybody? Welcome back to another video. Today we're going to learn how we can work

00:03with data in Databricks. There are a lot of ways to work with data in Databricks and we're going

00:14to cover a lot of them in this lesson. We're going to upload data like a CSV and a JSON

00:18file.

00:18Then we're going to connect to an external data source and we're going to bring that data in.

00:22Then we're going to see how we can actually use our data working in the SQL editor as well as

00:25using a notebook. The best way to follow along is to create a Databricks account. I will leave a

00:29link in the description. It's Databricks free edition. So it is completely free. All you have

00:34to do is create an account and sign in and you'll have access to everything. You don't have to put

00:37in a credit card at all. It is completely free, which is amazing. With all that being said, let's

00:42jump on my screen and get started. We are starting out fresh. We haven't uploaded any data or ingested

00:47any data into Databricks yet. So you are right where you need to be. Now there's actually a lot

00:51of different ways that you can bring data into Databricks. Right now we're in our workspace, but

00:57it's let's go over to our catalog and let's go into our workspace. Let's go into default. Right now

01:03we have no data in our default workspace. Now what we're going to do is let's click into this default

01:09and we're going to come right over here and we can go to create. Now we have a few different

01:14options.

01:14These two are the only ones that are really relevant to what we're doing right now. So let's create a

01:18new volume here and I'm just going to call this a YouTube series. And then I'm going to go ahead

01:25and create this. Now you'll see right over here under default, we now have this folder called

01:31YouTube series. If I click in this YouTube series, we can upload to this volume. So we can do that

01:39right here. We don't have to actually go outside of it or go to any of these other options. We

01:44just click

01:44on upload to this volume. So let's go ahead and click browse. And I have all these different files.

01:50Now I created these. They're very simple files. We're not getting crazy in this lesson, but I have

01:55a customer's CSV and a customer's JSON. So one's a CSV file. One's a JSON file. I also have order

02:02CSV,

02:03orders JSON, product CSV, and products JSON. We're not going to use all these, although I will have all

02:08these files in a GitHub. You can just find them in a link below. So you can download these exact

02:13files.

02:13If you want to work alongside me, but let's bring in the customer's CSV. So I'm going to click on

02:18this

02:18customer CSV and you can see this is the destination path that we are using. Let's go ahead and upload

02:25this file. And very quickly, like in one second, it took, we now have our customer's CSV.csv. Now I

02:33just named it that. So, you know, if the .csv wasn't there, we'll still be able to see it.

02:38Let's click into the CSV really quickly. We have our customer ID, first name, last name, country,

02:43and signup date. And then you can see our data is all separated by a comma. Now this data is

02:50just

02:50being stored as a file. This isn't actually being stored as a table. So you can see it just sits

02:54in

02:55here as a .csv. So if we go over to our files, it's going to sit there as an actual

03:00CSV file.

03:01Now we can still query off of this data just as it is as a file. We don't actually have

03:07to have this

03:07in a table format. Let's come right over here and let's copy this path. And then we're going to go

03:14down to our SQL editor and let's come over here to a SQL query. Now, if we want to access

03:20this data,

03:21we can say select everything. And then we'll say from, and I'm going to put this path in here.

03:28I should be able to use back ticks just like this. And let's try running this. And actually I need

03:35to

03:35put CSV dot right here. Now let's try running this. And as you can see, we were able to read

03:43in our data

03:44without it actually being in a table. A CSV file is one of the easiest files to work with. It's

03:49just

03:50values that are separated by commas. And so when we do this, when we say CSV dot, and then we

03:55provide

03:55the path, we are reading in this data as if it is in a table. Now, if you come from

04:01just a SQL

04:01background, this may seem very unintuitive to you and that's okay. Let's go back to our catalog

04:08and we're going to come over here to workspace. We're going to go to default. And instead of going

04:13into our volume right here, we're going to come back to our default. And so now we're going to create

04:21a table. When we go to create a table, we're still using our serverless starter warehouse here,

04:26but now we have the ability to connect to a data source or to upload a specific file format.

04:32Let's go ahead and click browse. We just uploaded the customer's CSV, but let's go down to the orders

04:38JSON because JSON is a totally different format. Let's go ahead and open this up. It's going to read

04:44in that JSON and make it tabular, which is fantastic because JSON by definition is not a structured

04:50format. And so being able to read in that data really easily and then put it into columns and

04:55rows is very helpful. So now we're going to do a create a new table. We can name this table

05:02anything

05:02we want. I'm just going to get rid of this JSON because once it reads it in, it's like a

05:07table

05:07anyways. So we're going to keep it as orders here. Then we're going to create this table.

05:11Now you'll notice over here under our default, we have tables and we have volumes. So they are

05:18separated out because they're two totally different things. One is going to be storing different files

05:23in kind of a folder format. And then one is the tables underneath our default workspace.

05:29So let's come down here to orders and that's actually right over here. So now under our orders,

05:35we can see customer ID, order date, order ID, product ID, quantity, and total amount. We can get a

05:40little bit of metadata on this file. And if we come up here, we can come here and just create

05:45a query.

05:46So let's click on create query. It's going to open up a new query in here. And now we are

05:51just

05:51selecting everything from our orders table. It's already hitting off of our default schema in our

05:57workspace. So we don't have to start doing, you know, workspace dot default dot orders. We don't have

06:05to have all that. You can, but I'm just going to hit a control Z here. Let's go ahead and

06:11run this.

06:11And now we can see all of our data in this really pretty view. So, so far we've ingested a

06:17CSV file

06:17and we just did that as a CSV file. We were still able to read that data in, which is

06:23really fantastic,

06:23but we're also able to just create tables and then read that data in like any other SQL database.

06:29But now let's come over here to data ingestion. There's a lot of different ways you can access data

06:35that is not just sitting in a file. Right off the bat, we have our Databricks connectors, things like

06:39Salesforce, Workday, ServiceNow, Google analytics, Azure SQL server. These are a lot of the things that

06:45I've used in my actual work. Almost all companies are going to have at least one of these. And so

06:49connecting to that data source, bringing it in is really common. We can also bring in data from a file,

06:53like we did before or create a table from Amazon S3. And then we have our five train connectors

06:58right down at the bottom. I know personally, I've worked at different companies. I've consulted with

07:03companies that use Google drive is like their store of information. That's where they keep everything.

07:07So let's go over here to Google drive and I'm going to actually connect this. So I'm going to say,

07:12I want to put it in my workspace. Let's go ahead and click next really quickly. I have a file

07:18over here,

07:19orders, CSV dot CSV. It's sitting in a Google drive. So that's what we're going to go and try

07:24to connect to. Let's go ahead and click next. This is my email that I'm using for this Google drive.

07:30We're going to connect this to five train. Since this is a new account for five train,

07:34I need to create a password. So I'm going to do that really quick. We're going to come right down

07:37here and we're going to go to share. And all we have to do is make sure that this is

07:42not restricted.

07:43So we're going to say anyone with this link, which means five train as well. And then we're going to

07:48copy this link and put it in our folder URL. Let's go ahead and save and test this. And it

07:54looks like

07:54our connection test passed. Let's go ahead and click continue. And now what we're going to do

07:58is sync our data. We'll start the initial sync and it should be very quick because I do not have

08:04much

08:05data in this folder. So it looks like our connection was successful. Let's come over here to our schema.

08:11Our one file that we have in our Google folder is synced up. So we should be good to go.

08:15Now let's come

08:16back to our Databricks. Let's come right over here and we're going to go into our catalog. We're going

08:23to go into our workspace and now we have default and we also have Google drive. Let's click on our

08:28Google drive and we have this orders CSV. And you'll notice that we now have it in here as a

08:36table.

08:36Let's go ahead and create a query for this so we can look at our data. So now we have

08:41workspace dot

08:42Google drive is connecting to a different schema. So we have select everything from orders CSV.

08:47Let's run this. And now we have our data. It also adds this in, which is five trans synced,

08:54which is a really, really useful column because if you're syncing this data consistently, you really

08:59want to know when that data gets put into this table. I promise you, that's really helpful that

09:03they put that in there. And then we have all of our data that we have. And so that's how

09:07we can connect

09:08to outside data. In this case, we use five train, but sometimes you'll just do a direct connection

09:12depending on your data source. Now this SQL editor works like any other SQL editor. You're going to

09:17be able to make joins and aggregations and all sorts of different things, but there is a different way

09:22to interact with your data. Let's come right over here. Let's go to new. We can also go and use

09:28a

09:28notebook. We can add code. We can add text. You can also use an AI assistant to help you with

09:35these

09:35things. And so we have markdown file right here. And I can say, uh, this is my first text

09:43right here. And then I have my code down here so I can start writing and typing my code.

09:49Now I can specify right here, whether I want it to be SQL, Scala, R or Python. So for this

09:55notebook,

09:55you can use any of these in this cell right here. I have Python, but let's add another one

10:01and I can use SQL in the next one. And then in the next one, I could use R. And

10:07so you don't have

10:08to just use one. You can use multiple. Now it depends on what you're doing, whether you want

10:13to use a SQL editor or you want to come in here and use a notebook. When I'm just kind

10:17of querying

10:18data, I'm just looking at it. I'm not doing a lot of transformations. I don't need a big programming

10:22language. I'm just querying the data. I'm going to be using a SQL, you know, editor right here,

10:27most of the time. And I can always save these queries and I can pass them along. But if I'm

10:31really digging in and I need to be able to break things out and leave notes, and I'm going to

10:35share

10:35this with my team, a notebook is kind of the way to go. So let's look at SQL really quick.

10:39We already

10:40have a query for this, albeit a very simple query, but let's come over here and let's run this SQL

10:47query right here. So let's go ahead and run this. And so now if we scroll down, we're going to

10:52be able

10:52to see our data just like we did in the SQL editor. But now let's go write the same thing,

10:58but in Python. In order to do that, let's come up here. And this is already in Python. So we'll

11:03just

11:03say spark dot table, and we need to read in the appropriate table. And all that is, is orders.

11:12And let's go ahead and run this. And it is reading it in as a data frame. Let's call this

11:17data frame.

11:19And let's come right down here and we'll say display data frame. And let's run this.

11:26And now we're going to get our data in a table, just like before. Of course, with this, it's going

11:32to save this data, but you can always come in here and you can rename and export it and you

11:37can put

11:37it into Git. You can share this with your friends because all your friends really care about your

11:42notebooks that you are writing, or you can create a new notebook and start from scratch. But this is a

11:47totally different way to interact with your data in Databricks. This is where most of your work is

11:52going to be done. It's right here, querying data, whether it's a SQL query or over here in a notebook,

11:58writing a bunch of code, whether it's in Scala or Python or R or SQL itself. Now, I hope that

12:03was

12:03really helpful because in the next lesson, we're going to be analyzing data with SQL and then also

12:07building out visualizations in Databricks. I want to give a huge shout out to the Databricks team

12:12for sponsoring the series. They have been amazing to work with. They have one of the best platforms

12:15in the world for working with data. I've been using it for many, many years. And so I'm so excited

12:19that

12:20I get to work on the series and work on Databricks free edition to bring this to you so you

12:24guys can

12:24learn and use Databricks for free. I also want to mention that Databricks has a hackathon running right

12:29now, November 5th through the 14th. It's on their Databricks free edition. If you can write down here,

12:34you can see all the requirements, but you are basically going to take your own data set and you're going

12:39to

12:39create a demo on how you use the tool and you're going to all sorts of cool prizes. I will

12:44leave

12:44a link in the description. So you go ahead and check that out, but it is an awesome initiative.

12:48I love hackathons. I've been a part of many of them. And as you can see, there are prizes right

12:53down here. So go and check it out. See if there's something you want to be part of because hackathons

12:57are a great way to learn. With that being said, I hope you'd like this video. If you learn anything,

13:01be sure to like, and subscribe, and I will see you in the next video.

Category

Transcript

Comments

Recommended