Skip to playerSkip to main content
Alex The Analyst
  • 5 days ago
Take my Full Databricks Course Here: https://www.analystbuilder.com/courses/modern-data-workflows-in-databricks

Try it for Free Here: https://bit.ly/aa-dbxfree

Get the File here: https://github.com/AlexTheAnalyst/DatabricksIDP

IDP in Databricks is used to help speed up the process of transforming unstructured data into structured, usable data. It's not an easy process, but IDP makes it much easier!

IDP Documentation: https://www.databricks.com/blog/pdfs-production-announcing-state-art-document-intelligence-databricks
____________________________________________

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 ch
Transcript
00:00:00What's going on, everybody?
00:00:01Welcome back to another video.
00:00:02Today, we're gonna be learning IDP and Databricks
00:00:05in another two hours.
00:00:12Now we're about to dive into IDP,
00:00:14which stands for Intelligent Document Processing.
00:00:16It's basically AI to extract data from your documents
00:00:20that typically you wouldn't be able to use.
00:00:22This may be data that is sitting in a document type
00:00:24like a PDF, and you want to extract that data
00:00:26and you wanna put it in a database
00:00:28because it's very useful.
00:00:29Well, I'm gonna show you how you can do that.
00:00:32This very long lesson is multiple lessons
00:00:34that I've been doing over the past several weeks
00:00:35put into one video.
00:00:36And I'm gonna show you what IDP is,
00:00:38how to actually extract the data.
00:00:40And then at the end, we're gonna build out
00:00:41a full end-to-end project
00:00:43with a lot of different file types.
00:00:45I absolutely love IDP.
00:00:46I think it is extremely good
00:00:47for extracting data out of your files
00:00:49and I cannot wait to show it to you.
00:00:51So without further ado, let's jump into the first lesson.
00:00:53Now, as a data analyst,
00:00:54I spent a lot of my time on data collection.
00:00:56So that's gonna be getting the data
00:00:58into a usable format for other teams.
00:01:00And a lot of the times, it's just in a CSV
00:01:02or it's in a database.
00:01:03It's really easy to use, but it isn't always the case.
00:01:06In fact, a lot of the data that I used to work with
00:01:09was in unstructured formats.
00:01:11Specifically, this was in healthcare
00:01:12when we were working directly with hospitals.
00:01:14And so we would get doctor's notes,
00:01:15we would get clinician notes,
00:01:16we would get claims documents,
00:01:17and that data is not formatted in columns and rows.
00:01:20It's just free text.
00:01:21And I'm old enough to remember,
00:01:22we would use OCR to extract all of this data
00:01:25and make it usable and put it into columns and rows.
00:01:28That process was very difficult,
00:01:29filled with a ton of rules and things
00:01:31that would break down a lot,
00:01:32but I don't think that's the way
00:01:34that you should be doing it anymore.
00:01:35In fact, I've been using IDP in Databricks,
00:01:38and it is amazing.
00:01:39So let's dive into IDP and see what it is
00:01:41and why it is so useful.
00:01:43The first thing to look at is just what IDP actually is.
00:01:46It stands for intelligent document processing.
00:01:48It just uses AI to extract data and information
00:01:51from your documents.
00:01:52And I'm sure a lot of you guys
00:01:53have actually tried this at some point.
00:01:55You just pop it into ChatGPT, you say,
00:01:57hey, tell me what this document says.
00:01:59And it gives you kind of a right answer.
00:02:01I've done that myself.
00:02:02And for the most part, it's okay, but it's not amazing.
00:02:05You can even see here on the left-hand side,
00:02:07this is a benchmark test for this type of process.
00:02:09This is gonna be accuracy versus pricing.
00:02:11And you can see ChatGPT way on the right-hand side.
00:02:14It's gonna be quite expensive for just okay accuracy.
00:02:17Right around 76, 77%.
00:02:19You'll notice though, Databricks is in the top left,
00:02:22which is kind of exactly where you wanna be.
00:02:24You want it to be the most accurate for the lowest price.
00:02:27And Databricks does that.
00:02:28Now, why does IDP matter at all?
00:02:30And this goes back to the exact thing I was talking about
00:02:32at the very beginning,
00:02:33which is a lot of data is not actually stored in databases
00:02:36and columns and rows.
00:02:38It's just free text, it's unstructured data.
00:02:40There is a ton of really important data
00:02:42in these unstructured data types.
00:02:44And just these free texts and documents
00:02:45that are sitting out there that people want to be able to use,
00:02:48but they just can't in its current state.
00:02:50Something that makes IDP really useful
00:02:52is that it is taking into context
00:02:54the document that you're processing.
00:02:56So it isn't fully rules-based, which OCR pretty much was.
00:03:00When I was back on my data collection team,
00:03:02we had a lot of pipelines that had OCR in it.
00:03:04And I would say at least once a week, they would break.
00:03:07They would just break down because the structure change,
00:03:09or something changed in the document,
00:03:10or the document file type change.
00:03:12It just would happen all the time.
00:03:14This is what makes IDP really powerful
00:03:16because it isn't completely rules-based like OCR was.
00:03:19Because if anything changed within that document,
00:03:22it just broke.
00:03:22It just immediately stopped working and it was horrible.
00:03:25And we had to fix things all the time.
00:03:26But with IDP, it's really flexible to the document
00:03:29that you're actually using.
00:03:30For example, on this right-hand side,
00:03:31this is just a PDF document.
00:03:33This is some image that somebody put out there,
00:03:35but it isn't sitting in columns and rows.
00:03:37There's free text on the left-hand side.
00:03:39There's columns and rows on the right-hand side.
00:03:42Now, this data might be really useful
00:03:45that you want to extract even the data in the free text
00:03:47on the left-hand side.
00:03:48With IDP, you'll be able to write pretty simple code
00:03:51to extract the data in the free text.
00:03:53And it's very easy to extract the data on the right-hand side.
00:03:56And if things change, maybe they flip sides,
00:03:58or more data is added,
00:04:00or there's more free text on the left-hand side.
00:04:02It's still gonna be really flexible with that data type.
00:04:04And in future lessons, we are going to deep dive into this.
00:04:07I'm gonna actually show you how to extract data just like this.
00:04:10So let's take a look at the difference
00:04:11between traditional processing and IDP.
00:04:14Now, traditional processing is the one that I kind of grew up with
00:04:17or the one that I've used the most,
00:04:19because this is just what we had
00:04:20when we were doing this type of work.
00:04:22This is gonna be using OCR,
00:04:23which stands for Optical Character Recognition.
00:04:25This is what I used,
00:04:27and basically everybody else used for unstructured data.
00:04:29And you would set custom rules
00:04:31for each document that you were working with.
00:04:33There were so many different tools and templates that you can use,
00:04:36and those were really helpful.
00:04:37But again, things would break fairly easily.
00:04:40And so it was a lot of maintenance
00:04:41to actually keep up with these pipelines.
00:04:43The most frustrating thing about using this
00:04:46was when a customer would not tell you
00:04:47that they're gonna be changing your format to a PDF,
00:04:50or whatever document they were sending you,
00:04:52they would just change it.
00:04:53And you wouldn't find out until like a week later,
00:04:55or two weeks later, when the data
00:04:57just wasn't coming in anymore.
00:04:58Now, it was still worth maintaining,
00:05:00it was still worth using those OCR systems,
00:05:02because we would get a lot of really good data out of it,
00:05:05but it wasn't 100% accurate.
00:05:07We had several data quality analysts
00:05:09who would be on our team,
00:05:10who would manually check a lot of these things
00:05:11and run tons of queries and tons of code
00:05:13to make sure that the data was really accurate.
00:05:16Again, traditional processing with OCR is not a bad system.
00:05:20It's just a lot of work.
00:05:22Now with IDP, we're gonna be using an AI agent-based system.
00:05:25So when it processes your document,
00:05:27it's gonna understand the layout
00:05:28and the context of your document,
00:05:30without you having to create the rules for it.
00:05:32One of the best things about this
00:05:33is just that it doesn't break very easily.
00:05:35It's really hard because it's so flexible.
00:05:37It's really hard to actually get it to break.
00:05:39So with traditional OCR,
00:05:41if you change it from a PDF into like a doc X
00:05:43or just a Word document, it's going to break, right?
00:05:46It's looking for a specific file type.
00:05:48But with IDP, it's just going to really easily,
00:05:51flexibly change from one document type to another.
00:05:54IDP has a ton of built-in functions,
00:05:57and those are what we're gonna be looking at
00:05:58in the next several lessons.
00:05:59They have a bunch of built-in functions
00:06:01that do a lot of the heavy lifting for you.
00:06:03You basically just need to point at it.
00:06:05You need to say, here's what you should be looking for,
00:06:07and it's gonna do most of the work for you.
00:06:09So there aren't a ton of custom rules
00:06:11that you need to be creating.
00:06:12Now in just a second, we're gonna talk about
00:06:14how we actually use IDP in Databricks,
00:06:16but there's just a visual for you to see
00:06:18kind of what it looks like for that transformation.
00:06:21It's super simplified, but it can take a raw PDF,
00:06:24and in that PDF, it could be free text,
00:06:26it could be columns and rows.
00:06:27We'll look at both in the next lessons,
00:06:29but you're gonna parse out that data.
00:06:31You're going to use the IDP process,
00:06:33which we're gonna look at in just a sec,
00:06:34and it's gonna put it into columns and rows
00:06:36to make that data usable.
00:06:38This is what I would say 95% of people want
00:06:40when they have these PDF documents that they wanna use.
00:06:43They want to just have it in columns and rows
00:06:45that they can query off the data,
00:06:46and they can actually use the data.
00:06:48And so how do you do that within Databricks?
00:06:50I had mentioned those AI functions,
00:06:51and that's what you're gonna use.
00:06:52You're gonna use it for parsing,
00:06:53extracting, and then classifying your data.
00:06:55If you know how to use those functions,
00:06:57as well as just some basic SQL or Python,
00:06:59because you can use either one,
00:07:00you can use IDP within Databricks to do this entire process.
00:07:04I personally like using SQL.
00:07:06That's what we're gonna use in the next several lessons,
00:07:08but it's just as easy to use Python in the notebooks as well.
00:07:11One of my favorite things about using IDP in Databricks,
00:07:13and I've used a lot of tools,
00:07:15I've used a lot of processes.
00:07:16One of my favorite things is that it's all in one place.
00:07:19This isn't some complex pipeline
00:07:21that uses six or seven different tools.
00:07:22It's all within Databricks.
00:07:24And so you have your raw data, your raw PDF files sitting in the catalog.
00:07:28And then once you process it,
00:07:30you're gonna have your structured data sitting right next to it.
00:07:33And so you have your raw data,
00:07:34you have your code, and you have your structured output all in one place.
00:07:38And so it makes it really easy to actually do quality checks on your data.
00:07:42And if you've ever run quality checks on your data before,
00:07:44which you absolutely should be doing,
00:07:46when it's all spread out, it's all in these different systems,
00:07:49it is very, very tedious.
00:07:51And so actually having it all in one place makes it 10 times easier.
00:07:55Now, in the next several lessons,
00:07:56we're gonna be diving into these functions of parsing, extracting,
00:07:59and classifying our data.
00:08:00And then at the very end, we'll have an end-to-end process
00:08:02where you can build out an entire process where you have your raw documents.
00:08:06We're gonna process all of them.
00:08:08It could be 50, could be a hundred documents.
00:08:10We're gonna process it into structured data.
00:08:12I'm gonna show you that it's actually quite easy and extremely accurate.
00:08:15I am a huge data collection nerd.
00:08:17This is like something I really enjoy.
00:08:18So I can't wait to get hands-on, actually start writing out all the code,
00:08:21and showing you how this works.
00:08:23We're gonna be using the Databricks free edition for all of this.
00:08:25So all of this is completely free to use.
00:08:28All you have to do is click on the link in the description,
00:08:30go ahead and create an account and follow along in the next several lessons.
00:08:33What's going on, everybody?
00:08:34Welcome back to another video.
00:08:35Today, we're gonna be parsing documents in Databricks.
00:08:44Now, Databricks has a built-in function called AIParseDocument.
00:08:47That's what we're gonna be using in this lesson.
00:08:48We're gonna take a PDF and we're gonna start processing it
00:08:51so that we can turn it into usable data.
00:08:53Now, let's take a look at what AIParse actually is.
00:08:55So AIParseDocument is kind of the first part of the process.
00:08:59We need to take this free text and we need to put it into a format
00:09:02where we can actually start using it.
00:09:03And so what we're gonna do is we're gonna use the AIParseDocument on our PDF.
00:09:07And I'm gonna show you that in just a second.
00:09:08We're gonna parse it and it's gonna turn it into HTML.
00:09:11HTML kind of looks like this on the right-hand side.
00:09:13This is not columns and rows just yet, but this is part of the process.
00:09:17Because then in the next lesson, we're gonna use AIExtract
00:09:20in order to take all that data out of the HTML and put it into columns and rows.
00:09:24But AIParseDocument is really useful because it doesn't just work with free text.
00:09:28It also works with tabular data as well.
00:09:30We can use images, PDFs, documents, all sorts of different files,
00:09:34and it's gonna work really, really well.
00:09:36Let's take a look at our PDF that we're gonna be using.
00:09:39All right, so this is our medical claim.
00:09:41And if you've never seen a medical claim, it'll look something like this, right?
00:09:44We have some CPT codes, ICD codes, description of what happened,
00:09:49how much it was actually billed, and how much the customer actually paid,
00:09:52as well as some other information.
00:09:54And this is all just kind of free text.
00:09:56Now, this is not narrative data.
00:09:58We will get to some more narrative data in our final project.
00:10:01But for demonstration purposes, I wanted to use something
00:10:03that I think would be easy to follow along.
00:10:05And I think this is very realistic.
00:10:07I've worked with this type of data a lot.
00:10:08You'll be able to download this PDF from a link in the GitHub below.
00:10:11Be sure to go get that so you can follow along with this lesson.
00:10:14Now, let's come over here, and let's get into Databricks,
00:10:16and let's get me out of here because I don't want to be taking up the whole screen
00:10:19while we're trying to look at all the code we're about to write.
00:10:22Now, here's what we're gonna do.
00:10:23Let's come over here to our catalog, and let's actually create a new catalog.
00:10:27So, we're gonna come in here.
00:10:28I'm gonna say, create a catalog, and we're just gonna call this IDP.
00:10:32Now, we're gonna start off with just one file,
00:10:35and that's gonna be the file that we just looked at.
00:10:36But in future lessons, we'll be using many different files,
00:10:39and we're gonna store them all in here.
00:10:41So, let's come in here.
00:10:42We're gonna actually add it to this default.
00:10:45So, we're gonna come right over here, and we'll create a volume
00:10:48so that we can put all of our data in here.
00:10:50I'm gonna name this one YouTube lesson, and we'll go ahead and create this.
00:10:56And now, we have this YouTube lesson in here.
00:10:58Now, here's where we need to upload this document.
00:11:00So, let's go to upload to this volume,
00:11:02and we just need to drag our file in there.
00:11:04Let's go ahead and do that. I'm gonna select our file.
00:11:07I'm gonna come right over here to sample medical claim.
00:11:09That's the one that we were just looking at.
00:11:11And I'm gonna open it up, and we are going to upload this file.
00:11:15So, now, we have our file right here sitting in our YouTube lesson volume,
00:11:20and then that is within our IDP catalog.
00:11:23So, we have it exactly where we want.
00:11:25Now that the file is actually sitting within Databricks,
00:11:27everything can happen within Databricks.
00:11:30We can have our raw data, which is our PDF,
00:11:31and then we can read in that file in our notebook,
00:11:34and we can start transforming it using the AI parse document.
00:11:37So, let's go up here.
00:11:39We're gonna go to a notebook.
00:11:40Now, you can rename this notebook if you'd like.
00:11:42I'm just gonna say IDP series,
00:11:45because we'll do all of this within this notebook.
00:11:48And I'm also gonna make this default be SQL.
00:11:52And yes, just because we're gonna be using SQL,
00:11:55you can use Python if you really love Python.
00:11:58But I like the syntax of SQL, and I also think it really flows well with what we're gonna be
00:12:03doing.
00:12:03So, let's come right in here.
00:12:05Now, here's one thing I wanna note.
00:12:06I am going to be writing basically everything out from scratch,
00:12:10but you can do a lot of it with the generative AI as well,
00:12:13if you know what to say and kind of how to talk through it.
00:12:16But if you don't, I'll walk through and I'll explain all the code as we go.
00:12:20Now, since we're gonna be transferring this data,
00:12:22I highly recommend setting up some type of temp table,
00:12:24so that we can kind of stage this data before we start transforming it.
00:12:28So, I'm gonna say create or replace.
00:12:32I'm gonna say temp view.
00:12:35And we'll call this raw unstructured document.
00:12:40And now, we're gonna say as, and we're gonna come right down here.
00:12:43And now, we just need to select our data.
00:12:44So, we have to select the data from the catalog and the volume that we just created.
00:12:49So, we're gonna say select.
00:12:51And I already know what this looks like.
00:12:53So, I'm kind of cheating a little bit.
00:12:55But we're selecting the path and the content.
00:12:58And I don't need that.
00:13:01But now, the from is the most important part.
00:13:03We could have just selected everything, but I kind of know what I want.
00:13:06But from is the most important part.
00:13:07So, we're gonna say read file.
00:13:09And this is gonna help us read in this unstructured data.
00:13:12We're gonna do it open parentheses.
00:13:13And we need to put this all in quotes.
00:13:15So, I know where this is located.
00:13:17So, we're gonna say forward slash volume.
00:13:21And we'll do idp default and then the YouTube underscore lesson.
00:13:30Let's go ahead and run this.
00:13:32And actually, I think the file is read files, not read file.
00:13:37Let's go ahead and run that.
00:13:38It looks like it read that in properly.
00:13:40Now, we can view this raw unstructured temporary table that we just created.
00:13:44So, let's create this another cell down here.
00:13:47And we can say select everything.
00:13:50And now, we'll do this from.
00:13:51And we can copy this raw unstructured doc.
00:13:55Let's go ahead and run this.
00:13:55Now, if we scroll right here, you can see that we are pulling in this sample medical claim.
00:14:00This is the only file that's in here.
00:14:02If there were different file paths and there were different files in there,
00:14:06it'd be giving us all the different files that we have.
00:14:09Now, this just looks like a bunch of gibberish as of right now.
00:14:13But that's because it's not actually reading in the real data.
00:14:16This is basically just telling us that there is content in it.
00:14:19In fact, if I were to really do this, I would probably do a path here.
00:14:23And then I would say length of content because I'm not actually looking to validate the data.
00:14:28I'm really just making sure that there is data in there.
00:14:32And there we go.
00:14:33So, this is probably more what I'm looking for rather than the actual content that was right up here
00:14:38when reading in this file.
00:14:40Now, let's keep going down.
00:14:41Now, we're getting to the fun stuff.
00:14:43We're reading in this file and there is data in there.
00:14:45So, now we want to use our AI function.
00:14:48This is going to be our AI parse document.
00:14:50Again, I want to set up another temporary view here.
00:14:53So, I'm going to say create or replace temp view.
00:14:59We'll call this one the parsed structured document.
00:15:04And now we need to specify what's in there.
00:15:06So, we'll select our path.
00:15:09And then next, we're going to say AI underscore parse underscore document.
00:15:14Now, we have our autocomplete here.
00:15:16I am going to use this because this is basically what we need except this select statement right here.
00:15:21So, I'm going to get rid of that.
00:15:22And now this is looking really good.
00:15:24Let's go ahead and run this.
00:15:26This okay tells us that that temporary view with the parse structured doc worked.
00:15:31Let's go ahead and run our query on this.
00:15:34We're going to say select.
00:15:35And we're just doing the path and the parsed content.
00:15:38So, we're going to say path and parse content from the parsed structured doc.
00:15:44Let's go ahead and run this.
00:15:46Now, we're starting to actually see what is in this document.
00:15:49Let's come right down here.
00:15:51And let's pull this down just a little bit so we can see a lot more information.
00:15:56So, this is our sample medical claim.pdf.
00:15:59Again, we are using right here the AI parse document.
00:16:02This is actually reading in all of the data.
00:16:05And it's putting it just like this.
00:16:08There's a lot going on in here.
00:16:09But one of the things I want to focus on is right here.
00:16:12We have this table and this table contains our CPT code or ICD description,
00:16:18build amount and paid amount.
00:16:19Those were our columns that were inside of our table.
00:16:22This is the data that I want to focus on right here.
00:16:25It's this table of data.
00:16:26I want to try to extract this one.
00:16:29So, let's come right down here and let's come to our code.
00:16:32And now that we have this parsed out.
00:16:34So, we have this parsed structured doc.
00:16:36Now, we want to specify what data in here we kind of want to focus on.
00:16:40Once we're able to identify this,
00:16:42we're going to use the AI extract in the next lesson
00:16:44to actually start structuring our data.
00:16:46Now, just while we're here as a quick note,
00:16:48I said this was an HTML.
00:16:49This also is kind of a JSON variant.
00:16:52And so, whether you call it JSON output,
00:16:54whether you're calling it an HTML output, it's okay.
00:16:56Either one is fine.
00:16:58They're both kind of right in this instance.
00:17:00And I guess it depends on the actual file type that you're using.
00:17:03But it's kind of a JSON HTML output.
00:17:06Now, again, remember this part right here.
00:17:09We have this table.
00:17:11Let's come right down here.
00:17:12And we're going to create one more temporary table,
00:17:15purely for staging purposes.
00:17:17And what we're going to do is we're basically going to
00:17:19specify what data we're looking at.
00:17:21And we're going to use a function within Databricks called explode.
00:17:24Now, if you don't know what the explode function is, that's okay.
00:17:26I'm going to show you how to use it.
00:17:27It's basically going to turn a map column, which is this.
00:17:30It's an array into multiple rows.
00:17:32Because we have lots of rows of data.
00:17:34And so, I'm going to show you how to use that.
00:17:36So, let's come down here.
00:17:37So, we're going to do the same thing as we did before.
00:17:38We're going to say create or replace temp view.
00:17:43We'll call this one the structured tables.
00:17:47And then we need to specify what data we're going to be getting here.
00:17:50So, we're going to select the path.
00:17:52And what we're going to do is we're going to cast that content as a string,
00:17:56just in case it doesn't work.
00:17:58And we'll do that down below when we explode that function as well.
00:18:00If you don't understand this stuff, it's okay.
00:18:03This is something that as long as you know how to use it and what it does,
00:18:06you can use AI to help you write this.
00:18:08And it's, you know, right here for you, if you want to do it that way anyways.
00:18:11So, we're going to try underscore cast.
00:18:14And we're going to do content.
00:18:16And we'll do that as a string.
00:18:20And we'll just call this as table HTML.
00:18:24Now, we have to specify where we're getting this from.
00:18:27So, this is the parsed structured doc.
00:18:29I'm just going to auto tab that.
00:18:31But now, we're going to do something different.
00:18:32We're going to do something called a, oops, lateral view.
00:18:36Now, this is actually something called Spark SQL.
00:18:39So, this isn't native to just SQL.
00:18:41But a lateral view allows you to use table generating functions like explode,
00:18:46like you can see in our autocomplete right here,
00:18:47to turn that into a column.
00:18:49And then you can join it back to the original row.
00:18:52And so, let's see how this works.
00:18:54We could just hit tab right here.
00:18:56But I'm going to write this out because I still want to use the try cast here.
00:19:02And this is actually exactly what I want to write.
00:19:04But we're going to use the parse content as an array.
00:19:07And make sure it's sitting as a string.
00:19:10So, let's go ahead and hit tab here.
00:19:11Or you can write it out as well.
00:19:13We need to close these parentheses as it's suggesting.
00:19:15And then we'll just give it an alias here.
00:19:19And if we want to, we can also go like this.
00:19:21And make sure it knows where it's coming from.
00:19:23If you want to be super thorough like me, you don't have to.
00:19:27But now what I'm about to do is I'm about to specify what data we actually want to pull.
00:19:31Because we only want to pull it where it's tables.
00:19:33And you can actually see that right in here.
00:19:35I think it hears me or it knows what I'm looking for here.
00:19:38But I'm going to do, again, a try cast.
00:19:41And I'm only doing this just in case some of the data types are having any issues.
00:19:45Which they shouldn't.
00:19:46You don't have to do this.
00:19:47I just like to be thorough or to be more explicit in what I'm doing.
00:19:51So, I'm going to do where the type.
00:19:52And we'll make sure that's as a string is equal to table.
00:19:57And this is actually in the wrong spot.
00:19:59Let's get rid of that.
00:20:00Just like that.
00:20:02This looks fancy.
00:20:03But really all we're doing is we're adding some try casts in here to make sure that
00:20:07there aren't any issues with data types.
00:20:09And we're exploding this column right here.
00:20:12This parse content.
00:20:13So, we can pull the data where it is a table.
00:20:16Let's go ahead and run this.
00:20:18And it should give us an okay.
00:20:19Now, we're getting this error right here.
00:20:21And I was just looking at this for a sec.
00:20:23I actually think when we're casting this as an array, it actually needs to be a variant here.
00:20:29And I used the auto tab earlier.
00:20:31And I shouldn't have.
00:20:32But this array needs to be a variant.
00:20:35And let's try running this.
00:20:38And there we go.
00:20:39So, now we should be able to query this.
00:20:42So, let's come and just select everything.
00:20:45So, select everything.
00:20:46And we'll do that from the...
00:20:49And I miss...
00:20:50Don't write like I write.
00:20:52I miss said that.
00:20:54So, now it says structured tables.
00:20:56But let's go ahead and run this.
00:20:58And we are getting no output.
00:21:00And I'm pretty sure I know why.
00:21:03It's because we need to be more specific on the actual content within this JSON.
00:21:07We need to specify that it's coming from the document as well as the elements.
00:21:12So, let's come right here.
00:21:14We're going to do colon documents, colon elements.
00:21:19And let's run this again.
00:21:22That shouldn't take long.
00:21:23There we go.
00:21:24Now, let's try running this.
00:21:26And there we go.
00:21:28It's always those little things that I kind of forget as I'm doing these walkthroughs.
00:21:31But let's go ahead and take a look at what we're pulling out right here.
00:21:36If we go back to our original document, we have the CBT code.
00:21:40Here they are.
00:21:40ICD codes, descriptions, build amount, paid amount.
00:21:43This is what we're pulling out.
00:21:45Because this is kind of our somewhat structured data that we already just want to have.
00:21:50And as you can see, here we have our CBT codes.
00:21:53Then our ICD codes right here.
00:21:55And then our descriptions.
00:21:56And in the next lesson, we are going to take this.
00:21:59And we're going to extract this data and put it into columns.
00:22:02Now, if you've never used like a traditional OCR system for pulling data out of PDFs and things,
00:22:07this might seem a little difficult.
00:22:09But I assure you, this is actually really easy.
00:22:11Once we actually extract that data out and put it into columns and rows,
00:22:15this is by far one of the easier processes for extracting data from unstructured documents that
00:22:21I've ever used.
00:22:22Now that we have this data in this format, this is just like HTML.
00:22:25We can now use that AI extract to pull that data out and put it into columns and rows.
00:22:31Honestly, all the code that we wrote here was not crazy difficult.
00:22:34And with the assistant that they have in here, if you don't know how to do this stuff,
00:22:38that's okay.
00:22:39Because the assistant actually is quite useful.
00:22:41You could probably do most of these things with autocomplete and kind of tweaking it here
00:22:45and there to get what you want.
00:22:47But the AI parse document was able to do the heavy lifting and identifying the things that we wanted.
00:22:52And then we just started pairing it down to the data that we specifically want to put
00:22:55into columns and rows.
00:22:56Now in our last lesson, we used AI parse document to specify what things we wanted to actually pull
00:23:01out of our PDFs.
00:23:03Now we want to use AI extract to pull the specific values that we want and put it into columns
00:23:07and
00:23:07rows to make that data a lot more usable.
00:23:10Let's not waste any time.
00:23:11Let's jump on my screen and get started.
00:23:13Now really briefly in the last lesson, we used AI parse document to basically get it to this format
00:23:18right here.
00:23:19So this table HTML that we were using, we can even look at the document right here.
00:23:23So we were trying to pull in this data, which is kind of in these columns and rows, but because
00:23:28it's in a PDF, it's mostly unusable.
00:23:31And so we were using AI parse document to specify, this is what we were looking at right here in
00:23:36this table.
00:23:36And now we're going to do is we're going to extract out these specific values within each
00:23:41of these columns and rows.
00:23:43So here's what we're going to do.
00:23:44We're going to run all of this code, just to make sure that all of our temporary tables
00:23:48are working, everything's up to speed.
00:23:50And now we're going to do is we're going to start using AI extract to pull these values.
00:23:55So you have these CPT codes, ICD codes, description, build amount, and paid amount.
00:24:00So let's come right down here and let's see how we're going to do this.
00:24:03First, we can actually just copy this whole thing.
00:24:07So we're going to pull this down.
00:24:09And the only thing I want to add is right here.
00:24:13So I'm going to hit enter.
00:24:14I'm going to say AI underscore extract.
00:24:17There are several things that we want to pull from here, right?
00:24:20We want to pull the CBT code, the ICD description, but all we have to specify is kind of what
00:24:26we're looking for, but it's going to use this AI to go in, look for exactly what we're specifying
00:24:30and pull that data.
00:24:32So let's come down here.
00:24:34We're going to pass through the column that we're going to be using.
00:24:37That's going to be our table HTML.
00:24:38And I can just use a tab there.
00:24:40And then all we're looking for is a CBT code and you don't have to write it exactly like
00:24:46this right here.
00:24:48We can do lowercase, uppercase, whatever.
00:24:50It's going to know what we're looking for.
00:24:52Now I need a comma right here.
00:24:54Just to make sure that this, uh, everything is separated from our AI extract.
00:24:58Now let's try running this.
00:25:00We're going to get an error and then I'll explain it just a sec, but this is just a data
00:25:04type issue.
00:25:05We have a mismatch here.
00:25:06So it says the second parameter requires an array type.
00:25:09Now, all we have to do is convert this really easily.
00:25:12We'll just say array.
00:25:13So it's going to convert.
00:25:16And let me get rid of this real quick.
00:25:18It's going to convert that CBT code or this right here into an array.
00:25:24So let's go ahead and run this.
00:25:26Now we need to go over a little bit.
00:25:29And here we have just our CBT code.
00:25:32Now you're going to notice right up here, we actually have multiple CBT codes.
00:25:36So we have CBT code 99213, 853 and 9300, but we're only getting the first one.
00:25:44Now this is okay.
00:25:46I'm going to show you how we're going to fix that in a little bit to pull in everything.
00:25:50But let's go through and kind of get this logic because we can basically use this to
00:25:56get everything.
00:25:57So I'm going to put a comma here and then I'm going to copy this.
00:26:00Now we have five.
00:26:01So that's two, three, four, and five.
00:26:04Now the next one that we're going to be looking for is that ICD code.
00:26:08So we'll say ICD code.
00:26:10And then we have description, build amount, paid amount.
00:26:14So we'll come right down here and we'll say description.
00:26:19And I need to spell this right.
00:26:22And then we have build amount and paid amount.
00:26:32Now, each of these is going to be in its own column because we're separating it out.
00:26:36And we're saying, okay, use a extract within our table, HTML, which is just this right up here.
00:26:42And we're saying, pull the value where there is a CBT code or an ICD code or a description.
00:26:48So let's go ahead and run this.
00:26:51Now let's scroll over.
00:26:53And you'll see we're getting in our data properly.
00:26:56Now this is perfect.
00:26:58This is exactly what we want.
00:26:59But you will notice we have kind of these key value pairs.
00:27:03And there's actually several ways to just get this value.
00:27:07We can map it to key value pairs within this and then just pull out the value.
00:27:12But within SQL, we could also just do it like this, where we say dot, and then we're specifying
00:27:18the key that we're looking for.
00:27:20And so for our very first one, let's go back over.
00:27:23We have CPT code.
00:27:25So if we specify right after that dot, we're going to say CPT code.
00:27:31If we do it like this, and then I'm also going to label it while we're here, just like this.
00:27:36Let's go ahead and run this.
00:27:38And if we go over, we should now see it's just a raw value instead of this key value
00:27:45pair in this struct array.
00:27:47So let's get rid of this everything really quick and let's go through.
00:27:54Now, this should look very good in our output.
00:27:57We should just have our data.
00:27:59We don't have even everything else that was in the table beforehand.
00:28:02We're just extracting the values within each of the columns.
00:28:06And so it should look really clean.
00:28:08We have CBT code, ICD, description, build amount, and paid amount.
00:28:12Now you will notice these are both strings because we have ABC, ABC.
00:28:16You can convert them.
00:28:18You would just need to remove that and then convert to a numeric data type.
00:28:22And so if you want to do that, you absolutely can.
00:28:24Now, here's the thing about this.
00:28:26We can use this, but this isn't all of our data, right?
00:28:31We don't want to just pull in the first row of data.
00:28:34That is not very useful.
00:28:35And in fact, 99.9% of the time, you're going to have a lot more than just one row.
00:28:39So we need to specify that we have lots of rows of data.
00:28:44So let's come back up here.
00:28:46Let's just pull this down just so we have it right here while we're looking at it.
00:28:50But I'm going to go ahead and run this.
00:28:52And what we need to do is we need to do just the tiniest bit of regular expression.
00:28:58And it's not going to be anything crazy, I assure you.
00:29:01But what we need to do is we have all this data in here.
00:29:04We have these table headers and we have these table rows with the data inside of it.
00:29:10So that's what these th, tr, and td actually mean.
00:29:13And so we just need to specify, hey, let's take all of this between the tr and this tr over
00:29:20here.
00:29:21Let's take all this, put it on one row.
00:29:24Then we'll start at the next tr, which is right here.
00:29:26We're going to take the next row, put it on its own row.
00:29:29And then we'll come back and run this on it.
00:29:32And that will extract all the data from every single row.
00:29:36So it isn't actually crazy complicated.
00:29:39We just have to actually write it out.
00:29:41Now let's keep all of this because what we're going to do is we're going to do something called explode.
00:29:45We looked at that in the last lesson.
00:29:48Then we're going to say reg xp extract all.
00:29:52Now this is actually exactly what we're going to do.
00:29:54I'm going to go ahead and tab over just so we can save ourselves some time.
00:29:57But I'll say as tr because these are going to be as our rows.
00:30:01Now all this is going to do, and let's close up parentheses.
00:30:03All this is going to do is use just a tiny bit of regular expression.
00:30:07It's saying within the table HTML, look for each of these rows and take everything up until the end of
00:30:15it.
00:30:15And so this part that I'm highlighting right here, this basically says stop as soon as possible,
00:30:19because we don't want to just take all of our HTML here.
00:30:22We just want between the tr and the forward slash tr.
00:30:27Let's go ahead and run this and see what it looks like.
00:30:30Now, if we go over here, we have all of our table HTML, but now we have the different CPT
00:30:36codes on each row.
00:30:38So now when we come back and we actually run this, this operation is going to happen on each row.
00:30:45And then we'll have all of our data. The only thing about this though, this is just something, you know,
00:30:50it's good to get out of the way is this data right here isn't actually data.
00:30:56These are just the column headers and we wouldn't want that.
00:30:58So we can filter that out. We could write a CTE or a temp table or really anything we want.
00:31:05I'll use a CTE here. I'm just going to say with rows as, and then we'll put everything within this
00:31:13and I'll do it like this. You can see it. Then we're going to just filter that out. That's it.
00:31:18So we're going to say select and we'll do path,
00:31:24comma, tr and that's from rows. So I'm going to say from rows, but now I want to filter this
00:31:30out.
00:31:31So I'm going to say where the tr and that's this right here, where the tr, we only want to
00:31:36pull in
00:31:37ones that have TD in it. So I can just use like here and I can say like, and then
00:31:43within here,
00:31:44we're looking for TD just like this. So we have our TD with the greater than or the less than
00:31:51on
00:31:51either side. Let's go ahead and run this. This is just classic, uh, HTML right there. And now we only
00:31:58have the data. All we have to do is now take this and apply that to this logic right here.
00:32:07And we can do that really easily. We can just put a comma here. And now this is going to
00:32:10be the next
00:32:11part of our CTE. So we're going to say data rows as, then we open up our parentheses and this
00:32:18is our
00:32:18next part of our CTE. And then all we do is take this and put it at the very bottom.
00:32:24That's it.
00:32:25So we're just layering this, but now we're not pulling from structured tables anymore.
00:32:30Now we're pulling from this data rows. So now we just need to easily fix this and say tr
00:32:37because that's where we're pulling or that's where we're passing through when we're using our
00:32:41AI extract. Now we're passing it through this tr right here and we're just pulling in the data.
00:32:47So we're selecting only the data for each of these. So now let's go ahead and run this. And just
00:32:53like
00:32:53that we have all of our data in columns and rows. This really is one of the easier ways to
00:32:59extract
00:33:00data from a PDF. I've done it a lot of different ways, but it's very accurate. It gets it right.
00:33:04Basically every single time I've never had it messed up personally and code wise compared to other
00:33:09systems. This is very easy. I've worked with some really complicated systems where we write a ton of
00:33:13code and a ton of regex and all these rules and all these different tools that you're piecemealing
00:33:17together, but you can do it within Databricks on this free edition. And it's pretty impressive. So that is how
00:33:22we
00:33:22use AI extract to actually pull the data out and put it into columns and rows. Now there are a
00:33:27ton
00:33:28of very real use cases for this, but let me give an example that I have personally seen it myself
00:33:32and one that we're actually kind of going to be working through in this lesson. Let's say there's a
00:33:36hospital. They're not super organized. They just put all of their data in PDFs and they put it into a
00:33:41folder on a local machine. I know that sounds insane to a lot of us data people, but I have
00:33:46seen
00:33:46hospitals do this. And what they do is they would then manually go through these files and then they would
00:33:50type it into a computer later on or have somebody do it. But this hospital just got really far behind
00:33:56and then they realized they weren't categorizing at all. If it was a claims data, if it was a medical
00:34:01record or what it was, and the files are just piling up and it's taking a lot of time just
00:34:05to go
00:34:05through them. Now they can go manually into each one and then put it into a different folder.
00:34:09And that's going to take forever. I mean, genuinely weeks or months of work just to categorize and
00:34:14separate all these files out. But what if they could just drop all their files into one place,
00:34:19write a tiny bit of code and it categorizes all these files for them at one time? I would imagine
00:34:25that they would really like that, especially because it's on Databricks free edition and they
00:34:29can do this for free and you can too. And that's what we're going to be doing in this lesson.
00:34:32So
00:34:32let's not waste any time. Let's jump right in my screen and take a look. Now in our previous lessons,
00:34:37we were using AI parse document and AI extract, and we're able to extract the exact data. That's not
00:34:42exactly what we're going to be doing in this lesson. Let's come right over here and we're going to go
00:34:47to
00:34:48our IDP in default and our YouTube lesson. Now, right now we only have one file in here and for
00:34:55classifying and categorizing data, that's not a lot. So let's actually upload to this volume and let's
00:35:01browse and bring in more PDFs. Right here, I have a ton of other ones and these are all just
00:35:06a bunch
00:35:07of kind of different styles of PDFs and I can open up one or two of them so you can
00:35:12see what they look
00:35:13like. But we have lots of different PDFs. Let's actually come down here really quick and let's
00:35:18just look at one of these. This is an administrative document, just has some text in here, but we're
00:35:22going to want to classify this. Let's pull another one. Let's look at this claims data. So medical
00:35:28claims summary with the patient name, the claim ID, and some information as well. And let's open up a
00:35:35lab report. So in this lab report, we have some results, where they got it, what the actual test was,
00:35:40and who the patient was. So we have text information. Now these are just sample files,
00:35:44but you can imagine them being a lot more complex with real hospitals. Now let's get rid of this
00:35:51and let's come over and we're going to go back to our workspace. We're going to go down to our
00:35:57IDP
00:35:57series and we're actually going to copy this real quick, just so we have it down here. We don't have
00:36:03to, but I'm going to do it just so we have it down here and we actually see it, but
00:36:07we're just going to
00:36:07read in all of the files that are at this location. So let's go ahead and run this. Now,
00:36:12believe it or not, we're going to come back up and get one more piece of code. And this is
00:36:16just
00:36:16because this was kind of an easier one. We were just using the AI parse document. And if you didn't
00:36:23watch that lesson, I highly recommend it, but we're using AI parse document to look at the content within
00:36:29in each of these. So now let's come right down here and we're just going to take a look at
00:36:35what's in
00:36:35here. So we're going to say, select everything from this parsed structure document. So we read all of
00:36:45our files in, then we just AI parse all the content that was within each of those files. And now
00:36:52we're
00:36:52looking at it. So we're just getting everything set up. So now we have all these different file
00:36:57paths and let's pull this down. And this would be each of the PDFs that we're dropping in there.
00:37:03Imagine this is like a thousand or 2000 different PDFs, which it absolutely could be. Then each one
00:37:08of these are going to have a specific name or a unique name, which is the file path within our
00:37:13catalog over here. Now within this parse content, you can go in and you can look and you can see
00:37:19here's what is in every single one of these documents. So we can go into this lab report. We can
00:37:24see our patient name right here. The test was a comprehensive metabolic panel. We got the test
00:37:29results right here. And what we're going to be doing is we're going to basically use the contents
00:37:34within each of these documents to use the AI classified to read it in and classify it within
00:37:41the buckets that we specify. So let's come right down here because I want to keep that just so I
00:37:45can see
00:37:45it, but we could have used that if we wanted, but I'm going to keep everything so I can see
00:37:49this
00:37:50and I can see this parse content. I'm going to keep everything. Now we're going to come over here.
00:37:54We're going to put a common. We're going to say AI underscore classify. Now there's two things that
00:38:01we have to pass through two parameters. The first is just the content that we want it to read in.
00:38:06So we're going to say parsed underscore content, and then we have to specify what we want here.
00:38:13So what do we want it to categorize it into? Now we have a lot of different things,
00:38:18but let's come down here and let's give it a few different categories. We're going to say medical
00:38:22claim, but then we're also going to add in medical invoice, lab reports, clinical note,
00:38:32or let's just add in other. Now, if we try to run this just like this, we should get an
00:38:39error here
00:38:40because we actually have two things that we aren't doing right. And of course you guessed it,
00:38:45it's going to be a data types issue. It's always a data types issue. The first thing is that we
00:38:49cannot resolve AI classify due to the first parameter requiring a string data type. So all
00:38:55we have to do is cast this as a string. So we'll say cast, and then we'll come right here.
00:38:59We'll say
00:39:00as string. So that one should be fixed. Let's go ahead and run this. You guessed it as well. The
00:39:05second one requires an array type. Again, super easy to fix. All we have to do is put array. So
00:39:13now
00:39:13that should be fixed. Let's go ahead and run this. So let's pull this down just a little bit and
00:39:19let's
00:39:20scroll over. So it looks like, and I actually want to, I'm just going to say as label because that
00:39:26is
00:39:26really long. And yeah, let's run this one more time. That's just a really long, and I'm going to bring
00:39:33it
00:39:33over so we can see exactly what we have. Let's come over here. Let's bring this all the way back
00:39:40over. And so for our sample medical claim, which is our very first file that we were working with
00:39:44for the past several lessons, it's going to label that as a medical claim, the claims as a medical
00:39:49claim, the lab report as a lab report, clinical note as clinical note, administrative doc, as well
00:39:55as a clinical note, and then an invoice as a medical invoice. Now, if we came, let's go look at
00:40:01that
00:40:01administrative doc just to see what's in there. I guess I could see why it would classify that
00:40:07as a clinical note. Now, it doesn't surprise me. It really is just trying to fit it into one of
00:40:13these categories. And so if we do have administrative docs that we want it to be in its own category,
00:40:18we should absolutely specify that. We could even come up here and let's just try this. Let's try
00:40:25administrative document and let's try running this. And of course, it's going to pick up on that.
00:40:30It's going to classify it properly. And so the buckets that we put in here are really important.
00:40:36This is what determines how it reads it in, what bucket or category it's actually trying to classify
00:40:41that file as. The more specific, the more options that we have, the more it's going to be able to
00:40:46determine which one is the correct one. So this is how you can use AI classify quite simply in order
00:40:52to classify and categorize your data. Now, if you wanted to take this a step further, you can go into
00:40:56these job runs and you can create an ETL process where you do all of these, you get it to
00:41:01this
00:41:02stage and then you say, okay, take this file and put it into a different place in our catalog.
00:41:07That's how you're going to actually separate and categorize all these files. And so this is a very
00:41:11real use case. It doesn't just have to be with medical data. It could be anything that you have.
00:41:16You're able to read in the contents of that file and categorize it appropriately. Now, I'm super excited
00:41:21for this project because this is a really great use case for using IDP. We're going to be working
00:41:26with financial data and we're going to have invoices, purchase orders, and receipts. And we're
00:41:30going to be dumping them into a catalog within Databricks. There's going to be different data
00:41:33that we want to extract from each document. And so what we're going to do is we're going to parse
00:41:37it out using AI parse document. After that, we'll want to extract the data that we want and put it
00:41:42into an actual table. I'm going to show you how to do it. It's super easy to write the code,
00:41:46but I don't want to put all the data from all these different files into one table.
00:41:50So we're going to use AI classify to kind of group these and categorize these. So we know what data
00:41:56to put in each table. At the very end, we'll drop in some more files. We'll run through the entire
00:42:00process. And you can see how now we have this entire system set up where you just have to
00:42:04upload the files, run your code, and you have your new data within your tables. This is a fantastic
00:42:09use case, not just for financial data, but basically any type of data that you'd want to do this with.
00:42:13And so I'm really excited to show you how to do this. Let's not waste any time. Let's jump on
00:42:18my
00:42:18screen and get started. All right. So let's come over here to our catalog. And of course,
00:42:23we've been working within this IDP and this YouTube lesson. Now we don't want to continue
00:42:28in the YouTube lesson. We want to create a new one. So we're going to come over here. We're going
00:42:32to go
00:42:32to create, we're going to create a new volume, and we're going to call this one our final project.
00:42:38And this is for all the marbles. You're going to build out this project. You're going to go tell
00:42:42your mom about it. You're going to be really excited. Let's go browse this. Let's select our
00:42:46files. And right in here, we have all these invoices. We have a lot of different purchase
00:42:51orders and we have different receipts. And within our new files, these will be the files that we
00:42:56actually drop in after we've completed everything. We're going to drop in extra files, run through
00:43:01the process again. So we're not adding these new files in, but we're going to add in all of these
00:43:06files right here. And we're going to open them up. We can go ahead and upload these and they should
00:43:11just upload really quickly into our volume right here. Now let's go take a look at one of these
00:43:18files just really quickly, just so we can see what it looks like. And this is opening up in
00:43:24Microsoft Edge. It looks like there's some text overwrite. That must be a Microsoft Edge thing.
00:43:29I don't know, but this is what our invoice looks like. And we're not going to go and look at
00:43:33each one
00:43:33right now, but we will in a little bit, but we can pull out as much or as little information
00:43:39as we
00:43:39want. If we just want to pull out the invoice ID and maybe the total amount for each invoice,
00:43:46that's all we want to do. Sure. Why not? Or if we want to include, you know, their address and
00:43:51all
00:43:51this other information, we can. In the past several lessons, we've shown how to get data out of tables
00:43:57like this. And so if we wanted to pull all of this data in and see each of the kind
00:44:01of items that we
00:44:02had, we can do that for this lesson, because this is a lot of different types of files,
00:44:08we're probably just going to stick with some basic information. And then we're going to show we can
00:44:13extract that data and then put it into a table. So it's a lot more usable than just sitting in
00:44:17our
00:44:18files. So this is our invoice. And all we're going to do is we're going to start with creating a
00:44:24new
00:44:24workspace. And I actually need to come up here. Let's just create a new notebook.
00:44:30And I do want to have it in SQL. I'm going to rename this as our final project, just in
00:44:35case we
00:44:35need to come back to it later. But the first thing that we need to do is just read in
00:44:39our files.
00:44:40That's all we need to do. So we're just going to say select everything. And then we'll say from,
00:44:45and then we'll do read underscore files. And we need to put in our file path here. Now this should
00:44:50be
00:44:50in quotes, forward slash volumes, forward slash IDP, forward slash default, forward slash final
00:44:59project. Let's go ahead and run this. So now we can see all of our files, the modification time of
00:45:06the time that we should put it in there, the length, so we see that there is data in there.
00:45:10And then we
00:45:10have our content. Now we haven't used AI parse document yet. So it's just not reading it in properly.
00:45:15This isn't the real content, but now we can see we have all of our files in here. We have
00:45:21our
00:45:21invoices, our purchase orders, and our receipts. Now, what we're going to want to do is we're going
00:45:27to pull out and we're going to parse this first, and then we're going to classify, and then we're
00:45:32going to extract everything and place it into the tables. And so what we need to do is come right
00:45:37down here and we're going to start out by parsing out the content. I will be adding it to some
00:45:43kind of
00:45:43temporary tables as we go, just so we can keep using them in later queries. But let's start out
00:45:49by just saying select, and we're going to select the path, and that's going to be this. And let me
00:45:54go up. That's going to be this path right here. And let's read this in. So I'm going to say
00:46:01from
00:46:01this volume, and then I just want the content. So I'm going to do AI parse document, then we'll open
00:46:08this up, and we're going to put in the content, and we'll put this as parsed content. Now let's
00:46:15go ahead and run this just to make sure it's working properly. And then we'll put it into
00:46:18that temp table so that we can use it in future queries down below. Now that we parsed out these
00:46:24documents, we can come in here and let's actually go over just a little bit. We have a lot of
00:46:31data in
00:46:32here, right? We have, you can see we have tables, just like we had in the previous lessons, we have
00:46:38tables of data. If we wanted to pull that out, we could, we're not going to in this lesson, but
00:46:42we
00:46:43know how to do that from previous lessons. But we have all of our invoice data, all of the website
00:46:48information, as well as the total information right over here. So we have our total. And this is the kind
00:46:54of data that we'll be pulling out from each one of these. And so you can see we got all
00:46:59that from all of
00:47:00our different files. We parsed out the content. Let's actually create our temp table because I
00:47:04want to kind of store this so that we can use it later. So we're going to say create or
00:47:08replace.
00:47:09And I always just do that kind of as a default, because if I run this later and it's already
00:47:14stored in there, I want it to replace it. Otherwise, I'm going to start getting errors saying this has
00:47:18already been created. And so I just do that by default, knowing that later I'm going to come back
00:47:22and use it again. So I'm going to create a table and we'll call this parsed underscore data.
00:47:28And then we'll say as here. So now when we run this, we can reuse this parsed data table that
00:47:34we
00:47:34are creating within this query right here. Now it says no rows returned. And that's okay, because
00:47:39we're doing a create or replace table here. If we just come right down here and run it, you'll see
00:47:45it worked properly. That's just kind of a default output that you're going to get when you create
00:47:50these tables. Let's just select everything from this table and you'll see it's all there. Now while
00:47:59that's running and okay, there it is. But while that's running, this data right here is in a
00:48:05perfectly fine state, but it can be a little bit difficult to kind of look through this and know
00:48:12what we want to pull out. And I actually think it'll be a little bit easier just for us to
00:48:17be able to see
00:48:17it. We can kind of loop through this and make it a little bit easier to read. I'll write out
00:48:22the
00:48:22code. I may even use AI to help write this. We're basically going to clean this up before we actually
00:48:27classify it. And in fact, cleaning it up a little bit might help the classification anyway. And so
00:48:33we're going to kind of clean this up and then we're going to classify it next. So let's come right
00:48:37up here and I'm going to start running it out. We'll see if AI picks up on it while writing
00:48:41it
00:48:41because it often does, especially within Databricks. But let's come up here to our select statement.
00:48:46So we're going to have our path. We'll put a comma here. And then we're going to use concat
00:48:51underscore WS. We're going to put forward slash N. That's just our new line. That's going to be our
00:48:57separator. Then we'll put a comma here. And then what we want to do is basically take out each of
00:49:01these elements. And so we're going to go in, we're going to take the elements and we're going to
00:49:04transform them just a little bit. It's nothing crazy complex. I'm just going to write it all out and
00:49:09kind of talk as I go so I can get it all written down. So I'm going to say transform.
00:49:14And then within this
00:49:15transform, we're going to say parsed content. So that's this column right here. And then we're
00:49:21going to do a colon. We'll say document. And then we'll do another colon and say elements. And I need
00:49:26to spell that right. And that's just right here, our document and then our elements. And then I'm
00:49:31going to say as array. And then we'll do this right here, which is a variant, just like that.
00:49:37And of course I spelled transform wrong. Transform. There we go. Once we specify our data, we'll do
00:49:44a comma here. And then we're going to do something like this. So we're going to say coalesce. And then
00:49:50we're going to try to cast this as a string. So we're going to say try cast and e-content
00:49:56as string
00:49:57is exactly what I want. And then we'll do a comma and put our quotes right here. We'll enter down.
00:50:03This is basically everything we want. And we'll say as doc underscore text. Let's try to run this.
00:50:10Now the syntax I might just be getting wrong. Let's try to diagnose this error using the AI
00:50:17helper here or the AI assistant, I believe is what it's called. And it's saying we don't need to put
00:50:22it as an array. Let's just accept it and try to run it. And let's see if it works. And
00:50:27again,
00:50:27we're getting an error here. Let's try to fix this. In the error, it's saying I need it as an
00:50:32array
00:50:32variant. I think I just wasn't using the right, maybe the right, what are these called? Less than
00:50:39or equal to. This is the exact syntax as I had before, but maybe with kind of the right thing
00:50:45right there. And that's what it was. I was just using the wrong symbol here, but this is exactly
00:50:49how I had it written before. So thanks AI. It looks like I had some syntax error in there. I
00:50:54wasn't
00:50:54using the right symbol or character right there. But now if we look at this doc text, it looks very
00:51:01different, right? This looks very human readable. And when we go to classify this, we don't have
00:51:06to do it this way. I just prefer it when I've been using it. I have been doing it like
00:51:11this.
00:51:11And so it makes it a little bit more easily readable. And then you can see, okay, here's
00:51:15our table. This is with our data in it. Here's another table with our data in it. And we can
00:51:19go
00:51:19down here to like a purchase order. We can see we have a bunch of table data right there. And
00:51:25then
00:51:25in here, we have some kind of header information. And all this is doing is putting it on a new
00:51:30line
00:51:30or transforming it, basically taking out each element and then placing it as a string on a new
00:51:36line. So now this looks ready to go. I think we're ready to classify this. And in fact, let's set
00:51:41this
00:51:41as a temp table or just as a table that we can use. So we're going to say create or
00:51:48replace table.
00:51:51And let's call this one pretty underscore data. And we'll say as. So we're going to set this one
00:51:58as pretty data just because it looks a lot better. And we're taking out a lot of kind of the
00:52:03unnecessary
00:52:04characters that we don't really need. And so now we're going to take this pretty data and let's
00:52:09just confirm it's there. So we're going to say select everything from pretty data. Let's go ahead and
00:52:15run that. Now what we need to do is classify this information. So we're going to come up here.
00:52:20We're going to take everything, but then we'll add a classification to this. So we're going to say
00:52:25AI classify, and then we need to pass through what we want it to classify it on. So of course,
00:52:31I'm just going to do a tab here for the doc text. It's telling us to do these, but those
00:52:36are terrible.
00:52:37Let's write array here. And within our array, we want it to be either an invoice, a purchase order,
00:52:43or a receipt. So let's write that out. So we'll say invoice, purchase order, a receipt. Am I spelling
00:52:52receipt right? R E I P T. Okay, great. And then if for whatever reason, there was an other, we'll
00:52:59put
00:52:59an other there. I don't think we'll need it, but that's fine for now. And we'll call this as doc
00:53:04underscore classification. Let's go ahead and run this. Let's see how it looks. Now let's scroll
00:53:13down and let's go over and we have all these classifications on the far right. Let's just
00:53:18bring this over. So now we can see we have invoices and that looks correct. We have our
00:53:23purchase orders and those also look correct. And then we have all of our receipts. Now you have to
00:53:29remember, we're doing this classification based off of the information within it. We're not doing
00:53:34it based off of whether it says invoice right here or not, because sometimes this invoice might just
00:53:38come in as a number one zero one one nine dot PDF. And so we want to actually look at
00:53:43the contents of
00:53:43the document. And so we looked through here, we parsed through, we said, okay, this is for sure an invoice.
00:53:49And that was of course with the AI classify. And so that looks good. It doesn't matter what documents
00:53:54we drop in later, even if it's not labeled correctly with the PDF or with the actual
00:53:59title, it's still going to read in the contents and classify it properly. But this table right here
00:54:04has everything that we need. We have our data in it. We have our classification in it. And what we
00:54:10can now do is we can use this classification to filter down which ones we want, extract that data
00:54:16and put it into a table. So we'll start with invoice. We'll say, let's extract the data we want
00:54:21from invoice, place it into the invoice table. Then we'll filter on purchase order, extract the data
00:54:27from the purchase order and put that into a separate table. And then we'll do the same for
00:54:30receipts. So let's place this into a temp table, just like we've been doing. We'll say create
00:54:37or replace, and we'll say table. And we'll do this as classified data, or we'll call it classified
00:54:46data. It sounds like we're a secret agent over here or some spy agency, but I assure you we're just
00:54:51classifying using AI classify. Although I feel like a secret agent. Let's just confirm that this is in
00:54:58here properly. So we'll say select everything. And then we'll start filtering on it. And then using AI
00:55:04extract to extract our data. Let's come down here. Let's go over. And this looks great. So here's what
00:55:11we'll do. We're going to start really simply. We're just going to say where the doc classification is
00:55:16equal to invoice and let's run this. So now we're just filtered on our invoices. So with just this
00:55:25data, now we want to use AI extract to pull out the values we want. And then once we have
00:55:30it done
00:55:30for one of these file types, the invoices to start, we're going to be able to basically copy down and
00:55:35just alter it a little bit. It's going to be very quick. So let's come up here. We're going to
00:55:40select
00:55:40everything. We're going to do a comma here. And now we're going to use AI underscore extract. We do
00:55:46want to pass through both the doc text and an array here. So I am going to keep that, but
00:55:51I'm going to
00:55:52kind of format a little bit better. And within this array, we need to specify what we're pulling out of
00:55:57the document. We can go to our raw document if we want. So we can come up here, just look
00:56:03at one of
00:56:03these invoices. Some of the data that I want to pull out is things like invoice, invoice ID, the due
00:56:08date,
00:56:08maybe the payment method. And let's do the total down here. Let's also pull out who it is. Now,
00:56:15there isn't an actual title for this. It doesn't say that this is the vendor name. I'm going to see
00:56:20if it's able to pick this up. I think it should. So let's come down here. Let's do vendor underscore
00:56:29name. And it's going to do a lot of autocorrect here, but I'm going to do vendor name. Let's go
00:56:35right
00:56:35down here. Let's do the invoice number. So within our quotes, we'll say invoice underscore number.
00:56:44Then we'll do a comma. We'll do the invoice underscore, and I need to spell that right,
00:56:50invoice underscore date. We'll go to our next one. We'll do the due underscore date. Let's come down
00:57:00here. Let's also get the payment method. So we'll do payment underscore method. And then lastly,
00:57:08let's get our total. And that's at the very bottom, right? We have this total right down here. So this
00:57:14is a little bit more difficult. We'll see if it's able to extract this. It absolutely should. I've used
00:57:20this quite a bit. I'm almost certain it's going to work very well. But now we need to close out
00:57:25this
00:57:26parentheses. Let's say as extracted. And let's go ahead and run this. I just noticed I accidentally
00:57:33did space invoice date. I'll correct that, but I won't run it again. But let's go. Let's pull this
00:57:39down a little bit. Let's bring all this over and let's look at this extracted information. Now it's
00:57:47saying null for the invoice date. And that may be because I actually wrote it wrong. Let's try running
00:57:54this again. I think that space may have thrown it off just because I messed it up on my end.
00:58:00All right, let's come over here. And that's what it was. So I had that extra space there. Just threw
00:58:06it off. Let's come through and just make sure it looks like these look correct. We can even pull
00:58:12up this document really quick. So let's pull up that 10119. We'll pull it up on this side just to
00:58:21see
00:58:21if it's extracting it properly. So it did pull in the vendor name without any specification. This
00:58:27doesn't say vendor name. So it's great that it pulls that out. So we have marketing, Riverbend
00:58:31Marketing Studio. There's the invoice dash. Yep, that one looks correct. Invoice date 117, due date 117,
00:58:40payment method is the wire transfer. And then the total doesn't look correct. That's because
00:58:46I didn't put a comma here. And that's pure user error. Let's run this again and see if it gets
00:58:53the total right. Because that's the one I actually think is going to be the most impressive for it to
00:58:58pull out, simply because it's in a table already. And so let's run this. Let's come over here. And
00:59:04let's see if it took the total right. So 456.79. And everything is correct. So just a little fixing
00:59:12of this array right here purely because I messed up a few times. But now that we have this all
00:59:18good
00:59:19to go, now what we can do is we can pull out all this data, put it in its own
00:59:23columns and its own
00:59:25rows. And then we can place that data into a table. And that's actually quite easy. We're not copying.
00:59:30Let me actually place this. So I'm going to say create or replace. You guys know the gig. I'm going
00:59:37to call this invoice data. And we'll say as. And I need to say table here. So we're creating this
00:59:43invoice data table. And that's where we're then going to just kind of pull out the final values
00:59:49into its own columns and rows. We actually looked at how to do this in the last lesson.
00:59:54Let's go ahead. We're going to select everything from here. And all we're doing is we're specifying
01:00:02real quick. Let's come over. We're just going to take this extracted information. So let's come up
01:00:08here to the select. Let's just do the path. And then we'll come into each value. And we're going
01:00:13to select it and put it into its own column, essentially. So we'll do extracted dot. And
01:00:19then it even says what we can do here. So we're going to take out the vendor name first. And
01:00:24I'm
01:00:24going to hit tab to auto do a little bit of this and due date and payment method. I'm going
01:00:28to hit tab
01:00:29as well. And the total. It's just autocorrecting this all for me. And then I'm just going to rename
01:00:34these because I want the actual column headers to be correct. And we'll do vendor. We'll do as
01:00:41invoice. Let's call this invoice number. And then we'll do as invoice date as due underscore date as
01:00:55payment underscore method. And then last one as and should just be total. Now let's go ahead and run
01:01:03this. Let's make sure that this is all working properly. So now we have within each of our
01:01:09documents, we have our vendor name. Let me pull this down just a little. We have our vendor name.
01:01:14We have our invoice number, invoice date, due date, payment method, and the total. And this all looks
01:01:19perfect. We don't have to pull in this path and put this data into it, but we can, if we
01:01:25want to,
01:01:26but all we have to do is come right up here and we're going to do the, almost the same
01:01:30thing.
01:01:30We're going to say, create or replace. Now, this is probably the most important one to do this on
01:01:35because we're actually going to say a table. Now we're going to do IDP dot. And all this is going
01:01:41to
01:01:41do is going to specify where in our catalog to place this data. Now we have to place this into
01:01:47an
01:01:47actual catalog. We can't just place it and call it here. We're going to create this in just a sec.
01:01:52We're going to call this finance dot invoices. We don't have this finance table yet, right? We have
01:02:00to create this. So I'm actually going to come up here and I'm going to create this. So I'm going
01:02:05to
01:02:05say, create schema, if not exists, and we're just going to call it finance. Now we just have to
01:02:12specify where it goes. So I'm going to say IDP dot, and let's run this and that should create it.
01:02:18Then we're going to come down here. And now we're selecting all of this data into this IDP finances,
01:02:26and then we're labeling it invoices. So now we're going to say as, and this is where it's going to
01:02:31go. Let's go ahead and run this. And then let's go and check our catalog to make sure it got
01:02:35in there
01:02:36properly. So it looks like this worked well. Let's come over here and we're going to go to our
01:02:41catalog. We're going to go down to our IDP. And now we have a new schema. We have finance. And
01:02:48then within it, we have our invoices. So now this is sitting just like a regular table would
01:02:54if you uploaded like a CSV file or something else, except we're working with an unstructured data
01:02:59format. This is not easy to pull this data in. And so we now have basically this pipeline where we
01:03:05can drop data in. Let's actually select our compute real quick and start this just so we can
01:03:10see the data, but we can select this data in. We can bring this data in. We can extract the
01:03:16data
01:03:16and place it into tables just like regular data, which is amazing. Now we can wait for this to show
01:03:21our sample data, or we could just go back to our workspace and query this data from this actual
01:03:29table now, which it's reading it in. Let's go to the very bottom. It's actually there. So now we can
01:03:36do code and we can say select everything from IDP.finance.invoices. And let's read this in
01:03:46because now it's just sitting like a regular table. So now this data is kind of permanently stored
01:03:54within Databricks, just like we would if we were uploading a CSV to a table, which is fantastic. This
01:03:59is awesome. And we have some documentation with the path. If we wanted to, we could have even
01:04:04included things like a timestamp here of when the data was here, but you know, that goes beyond what
01:04:09we're trying to look at in this lesson. Now what we're going to do is we're just going to repeat
01:04:13this
01:04:14and do the exact same thing, but for the other type of data. So let's come and bring this all
01:04:20the way
01:04:20down here. It's going to be really similar. Let's just comment this out. And now instead of invoice,
01:04:27let's do our purchase. I think it's like this. Maybe I need to go back now. Actually, while that's
01:04:36running, let me go back up to our classification. It's right up here. Let me get rid of this just
01:04:45for
01:04:46a sec. We want to revert this code back because we'll be running through this all again, but I just
01:04:51want to see what I called this. Oh, I called this a purchase space order. Okay. That's totally fine.
01:04:57I just wanted to make sure. Now let's go back down. Let's get our purchase space order. Now these are
01:05:05not the things that we need from our data. And let's actually go into one of our purchase orders
01:05:11and take a look at this. So we have our buyer, we have our vendor right here, and it looks
01:05:18like we
01:05:18have some other information like currency. We have a date right here, and then we have our total.
01:05:23So let's pull out a few different things. Let's pull out our merchant name. Let's pull out maybe
01:05:29our PO number right here, as well as our PO date. And then we'll do our total amount, just like
01:05:36we did
01:05:36before. I think these are all good kind of key things that we want to pull out. Let's get out
01:05:43of here.
01:05:43Let's come right here. Now we're going to call this a merchant name. We want the transaction date
01:05:51and we'll do it just like that. And I spelled that wrong. Let's actually pull up this one more time
01:06:00because we have the PO number, the PO date and the total. So let's get that PO date
01:06:09purchase order date. And then we'll do the total. And let's get rid of these. There we go. And I
01:06:18think
01:06:18we just need to close out this parentheses. Now let's just look at this first. Let's make sure that
01:06:26it's running properly. And then once we verified that, we'll just continue on with the process like
01:06:31we did in the previous one. So let's come over here. Let's look at our extracted information.
01:06:37So we have a transaction date as null. That's not good. Let's just see if I need to pull it
01:06:42as
01:06:42something else. But then we have our purchase order date. And I think the transaction date is
01:06:48the same thing as the purchase order date. I actually don't want or don't need both of these. I only
01:06:54need
01:06:54one. Let's come over here because we have the purchase order date, which is this information right
01:06:59here. I wanted the PO number. That's what I need. So let's actually replace this with the PO
01:07:08underscore number. And let's run this. The best thing I love about this is just that it knows kind
01:07:15of what I'm saying. I don't have to go in like hard code in anything. It uses AI to go
01:07:21in and extract
01:07:22the information that I need. So this is looking great. And let's verify this by let's get the
01:07:29Hawthorne 25 document. So Hawthorne 25. Let's get rid of this. Let's come over here. So we have
01:07:39Riverbend Marketing Studio. We have the PO 24371. We have 202516. And then we have 5,219.45.
01:07:51That is perfect. So I think we are rocking and rolling with this one. Now we just need to create
01:07:58this. So we're going to create this as purchase order data. And let's run this. And then what we
01:08:07have to do is extract each value just like we did and place it into a table. So let's copy
01:08:12this. I don't
01:08:21extracting each value. And so we're going to do it just like this. We're going to select the path
01:08:28and then we're going to do extracted dot. And then we'll start pulling out each one. And let's see if
01:08:35it does this for us. There we go. This is perfect. That's it just tabbed it and kind of knew
01:08:40what I
01:08:41wanted at this point. Let's just run this just to make sure it gets everything right. And there we go.
01:08:48That's perfect. And let's place this into a new table, which I'm going to create. So we'll come
01:08:55back up. We will take this right here. And actually we don't because that's the schema. We just have to
01:09:03create the table. That's it. So let's come back down here. Look, some of this I'm doing, you know,
01:09:09just out of memory. So I apologize. I'm going to call this purchase underscore order. And we have our
01:09:17purchase order. That's going to be our new table. Let's go ahead and create this. And then we'll go
01:09:23check it out. And then the last one we'll do is receipts. And then we'll do kind of where we
01:09:27put
01:09:27all of our new data in as well. Looks like this should work. Let's go to IDP, go to finance.
01:09:34And now
01:09:34we have our purchase order in here. I'm looking at sample data. We don't need to, but here's our data.
01:09:41So that's our sample data, but it is all of our data. But if we had thousands and tens of
01:09:45thousands of
01:09:45files, then, you know, this would just be a small sample. So this looks perfect. We are just cruising
01:09:51through this. And the very last one that we're going to do is our receipts. Let's scroll down.
01:09:58Let's, well, that was our purchase order. Let's go right here and pull this right here and put it
01:10:05at the bottom. Cause now we just need a filter on receipts. And so we'll say receipts. And I never
01:10:12know
01:10:13if I spell that right. I before E except after C. Hey, that's kind of the one time it actually
01:10:17works.
01:10:18All right, let's go down to a receipt right here. And within this receipt, we got some good information.
01:10:24One, we have the name of, you know, the parking area. We have the receipt number, the date and time,
01:10:30and we have a total. Now, again, all these totals have been within these tables and it's extracted them
01:10:35very well, which I've been really happy about. We could also pull the payment method. We can kind of pull
01:10:40anything we want from these. I'm just going to try to keep it kind of simple. But let's come back
01:10:45here
01:10:46because now we need receipts. So let's come in here. We'll just keep this as the merchant name.
01:10:53Let's see what else we want to pull.
01:10:57Let's do the receipt number instead of the PO number. So I'll do receipt number. Let's go back.
01:11:04Let's just do transaction date. That's what we're going to call it. We'll see what it extracts exactly.
01:11:08And we'll do the total. So we'll do here. We'll do transaction date. And let's go ahead and run
01:11:15this. Now, which one are we looking at here? We have the 41151. I'm going to pull this over
01:11:23and do it just like this real quick. I really shouldn't be doing that. And in fact, let's
01:11:27get rid of this, even though I already created it. Yeah, this doesn't look as good. I'm going to
01:11:33revert back. You know what? I'm doing it. I'm going back. I'm going to do it like this.
01:11:37And it looks like we didn't extract anything. Let's go back up. This is just part of the
01:11:42process. All right. Sometimes we've got to figure out what happened here. Let's go back
01:11:46up. We called it receipt. That's why. Sometimes it's the simplest thing. I called it receipts
01:11:53here, but we filtered on the wrong thing. Let's go ahead and run this. And we should get an
01:11:58output this time. And there we go. Let's go over to this right-hand side. And let's find
01:12:04the 41551. That's this one. Let's just see what data it pulled out and if it looks correct.
01:12:12So let's look at this. We have a skyline parking, R347861, 201513. That's just the transaction
01:12:20date. We didn't do transaction date and time. We probably could have. And then we have our
01:12:24total of 288.16. Perfect. This is exactly what we wanted. Now, we already created this
01:12:32by accident. So let's just come down here and we're going to make sure it's in there. Select
01:12:39everything from this table. We'll extract the specific things that we want. Let's run this
01:12:44again. Because again, I forgot we had receipts before. We do have to run this actually. Because
01:12:50before we had receipts, we put no data in this table. And so it didn't actually work. But
01:12:57now it should work when we do this. That's good. Let's go down and run this again. We
01:13:03should actually have data in here. Thank goodness. All right. Now we just have to do exactly what
01:13:08we did before. We're going to take just the path, do a comma. We'll do extracted dot and
01:13:14let's see. Oh, I need to do extracted. Extracted dot. And I'll do tab, tab, tab. And let's see
01:13:22if it does it. There we go. Thank you, autocomplete. What would I do without you? I love autocomplete.
01:13:28It's great. We have merchant. We have the receipt number, transaction date, and the total. This
01:13:34looks perfect. Let's go ahead and place this. I'm just going to go back and copy. I could just
01:13:40copy this. Because then we'll place it right here. We'll call this idp.finance.purchaseorders.
01:13:54And let's run this. Looks like it ran well. Let's go back to our catalog.
01:13:59Got IDP within our finance. We now have. And I put purchase orders. Oh, goodness. This is receipts.
01:14:07Oh, geez. What a mistake. What a mistake. I'm going to have to delete that table now.
01:14:13Oh, my Lanta. All right. This is not purchase orders. I had done its receipts. And I called
01:14:21it purchase orders on this one too. Oh, I should have done receipts here. Jeez. All right. I'm
01:14:28fixing this because in just a sec, we're going to drop all the files in there. I want everything
01:14:33to actually be correct. You can save this and use this later on. So we're going to fix it
01:14:38because, you know, that's what we do. We make mistakes. We fix them. And then we call that
01:14:42receipts and from receipts. Perfect. Now let's run this because now we just created this receipts
01:14:50table and we're placing this in here. In our catalog, we should now see we can get rid of
01:14:57this purchase orders. And I'm just going to come over here and delete this real quick
01:15:02just because that was an accident. Now we can go into receipts. And if we look at our sample
01:15:08data, our data should be in there. And that is perfect. Now this is not just a one-time use
01:15:14for this code. We can use this as much as we want. Let's go into our default because this
01:15:20is where we're storing our data. Let's go to our final project and let's upload, I'm going
01:15:26to go to browse. Let's upload these new files. Now these new files are very similar, but they
01:15:32are not the same. Let's upload these. And now we have a bunch of new data in here. All we
01:15:40have to do, because we just put like 15 new files or 10 new files in there. All we have
01:15:46to
01:15:46do now is come over here and run all. That's it. So all I'm going to click is run all.
01:15:51It'll
01:15:52run all of our code down to the bottom. And we have, I guess, 13 different cells that we
01:15:57use. It's going to run all of these and then it should work. Now real quick, I want to make
01:16:01sure fix error here. Let me make sure we don't have an error message somewhere. I think that's
01:16:05just some AI labeling thing. All right, let's run all and let's see if it works. This should
01:16:11take a minute or two, and then we'll have all of our data completely classified and put
01:16:16it into our tables. All right, that took about one minute to run. It looks like everything
01:16:21ran successfully. Let's go back to our catalog. Let's come in here and we have all of our tables.
01:16:29And now let's look at our sample data. And we have a lot more data in here now. And it
01:16:36looks like everything worked perfect. Let's go to our purchase order. We've got some more
01:16:44data in here now because we only have three before. So we had two more. And then let's
01:16:48look at our receipts and there's our new data. And there we go. I have done similar things
01:16:54to this in the past in real work using, you know, PDFs and all sorts of different documents.
01:16:59It was 10 times harder than this. This is really just awesome to use. It's a fantastic tool.
01:17:05And if you have to do something like this, this is like the first place that I would go. I
01:17:09really
01:17:09hope you enjoyed this project. I thought it was super fun. I've done stuff like this in the
01:17:13real world in the past. And IDP just makes it really easy within Databricks. I wish I had this
01:17:18many years ago when I was doing this kind of work. Thank you guys so much for watching. I really
01:17:22appreciate it. If you liked this video, be sure to like, and subscribe. I'll see you in the next video.
Comments

Recommended

End-To-End IDP Project in Databricks | PDFs to Useable Data in 30 Minutes
Alex The Analyst
4 days ago
Working with Data in Databricks (Free Edition)
Alex The Analyst
2 days ago
Intro to Databricks (Free Edition) | UI Walkthrough
Alex The Analyst
1 day ago
Job Orchestration in Databricks | Data Engineering in Databricks
Alex The Analyst
1 week ago
Job Market in 2025: Over Saturated, Low-Paying, Helpless
Alex The Analyst
10 hours ago
Alex The Analyst April Livestream!
Alex The Analyst
2 weeks ago
Installing and Creating Database in PostgreSQL
Alex The Analyst
2 weeks ago
Resume and Portfolio Review Livestream!
Alex The Analyst
2 weeks ago
Filtering in PostgreSQL | Where Clause
Alex The Analyst
2 weeks ago
Like Statement in PostgreSQL | Using LIKE to find Patterns
Alex The Analyst
3 weeks ago
Microsoft Copilot Cowork Just Launched | Here's What You Need to Know
Alex The Analyst
1 week ago
Claude in Excel - Better than Copilot?
Alex The Analyst
1 week ago
Select and From in PostgreSQL
Alex The Analyst
2 weeks ago
Alex The Analyst March Livestream!
Alex The Analyst
6 days ago
Semantic Layers in the Age of AI are 100% Needed
Alex The Analyst
1 week ago
Switching to Mac after 10 years as a Microsoft User
Alex The Analyst
2 weeks ago
Should You Take Google's AI Certificate? My Honest Take
Alex The Analyst
2 weeks ago
Learn ETL Pipelines in Databricks in Under 1 Hour - Data Engineering in Databricks
Alex The Analyst
3 weeks ago
AI Demo Day: Rocktop’s Intelligent Data Integration Suite (IDIS)
HousingWire
2 years ago
I Wrote a Book on How to Build Your Data Career!
Alex The Analyst
3 weeks ago
Group By and Aggregations in PostgreSQL
Alex The Analyst
3 weeks ago
Former Aide Claims She Was Asked to Make a ‘Hit List’ For Trump
Veuer
3 years ago
Musk’s X Is ‘the Platform With the Largest Ratio of Misinformation or Disinformation’ Amongst All Social Media Platforms
Veuer
3 years ago
59 companies that are changing the world: From Tesla to Chobani
Fortune
3 years ago
3 Things to Know About Coco Gauff's Parents
People
3 years ago