Ai_extract in Databricks

Key Takeaways

What's going on everybody? Welcome back to another video. Today we're going to be extracting data from our documents in data bricks. [music] Now, in our last lesson, we used AI parse document to specify what things we wanted to actually pull out of our PDFs. Now, we want to use AI extract to pull the specific values that we want and put it into columns and rows to make that data a lot more usable. Let's not waste any time. Let's jump on my screen and get started. Now really briefly in the last lesson we used AI parse document to basically get it to this format right here. So this table HTML that we were using we could even look at the document right here. So we were trying to pull in this data which is kind of in these columns and rows but because it's in a PDF it's mostly unusable. And so we were using AI parse document to specify this is what we were looking at right here in this table. And now what we're going to do is we're going to extract out the specific values within each of these columns and rows. So here's what we're going to do. We're going to run all of this code just to make sure that all of our temporary tables are working, everything's up to speed. And now we're going to do is we're going to start using AI extract to pull these values. So we have these CPT codes, ICD codes, description, build amount, and paid amount. So let's come right down here and let's see how we're going to do this. First, we can actually just copy this whole thing. So, we're going to pull this down. And the only thing I want to add is right here. So, I'm going to hit enter. I'm going to say AI extract. There are several things that we want to pull from here, right? We want to pull the CBT code, the ICD description, but all we have to specify is kind of what we're looking for, but it's going to use its AI to go in, look for exactly what we're specifying, and pull that data. So, let's come down here. We're going to pass through the column that we're going to be using. That's going to be our table HTML. And I can just use a tab there. And then all we're looking for is a CBT code. And you don't have to write it exactly like this right here. We can do lowercase, uppercase, whatever. It's going to know what we're looking for. Now, I need a comma right here just to make sure that this uh everything is separated from our AI extract. Now, let's try running this. we're going to get an error and then I'll explain in just a sec. But this is just a data type issue. We have a mismatch here. So it says the second parameter requires an array type. Now all we have to do is convert this really easily. We'll just say array. So it's going to convert and let me get rid of this real quick. It's going to convert that CPT code or this right here into an array. So let's go ahead and run this. Now we need to go over a little bit. And here we have just our CBT code. Now, you're going to notice right up here, we actually have multiple CBT codes. So, we have CBT code 99213, 853, and 9300. But, we're only getting the first one. Now, this is okay. I'm going to show you how we're going to fix that in a little bit to pull in everything. But, let's go through and kind of get this logic because we can basically use this to get everything. So I'm going to put a comma here and then I'm going to copy this. Now we have five. So that's two, three, four, and five. Now the next one that we're going to be looking for is that ICD code. So we'll say ICD code. And then we have description, build amount, paid amount. So we'll come right down here and we'll say description. And I need to spell this right. And then we have build amount and paid amount. Now each of these is going to be in its own column because we're separating it out and we're saying okay use a extract within our table HTML which is just this right up here. And we're saying pull the value where there is a CBT code or an ICD code or a description. So let's go ahead and run this. Now let's scroll over and you'll see we're getting in our data properly. Now this is perfect. This is exactly what we want. But you will notice we have kind of these key value pairs. Now there's actually several ways to just get this value. We could map it to key value pairs within this and then just pull out the value. But within SQL, we could also just do it like this where we say dot and then we're specifying the key that we're looking for. And so for our very first one, let's go back over. We have CPT code. So if we specify right after that dot, we're going to say CPT code. If we do it like this, and then I'm also going to label it while we're here, just like this. Let's go ahead and run this. And if we go over, we should now see it's just a raw value instead of this key value pair in this strruct array. So, let's get rid of this everything really quick and let's go through. Now, this should look very good in our output. We should just have our data. We don't have even everything else that was in the table beforehand. We're just extracting the values within each of the columns. And so, it should look really clean. We have CBT code, ICD, description, build amount, and paid amount. Now, you will notice these are both strings because we have ABC. you can convert them. You would just need to remove that and convert to a numeric data type. And so if you want to do that, you absolutely can. Now, here's the thing about this. We can use this, but this isn't all of our data, right? We don't want to just pull in the first row of data. That is not very useful. And in fact, 99.9% of the time, you're going to have a lot more than just one row. So, we need to specify that we have lots of rows of data. So, let's come back up here. Let's just pull this down just so we have it right here while we're looking at it. But I'm going to go ahead and run this. And what we need to do is we need to do just the tiniest bit of regular expression. And it's not going to be anything crazy, I assure you. But what we need to do is we have all this data in here. We have these uh table headers and we have these table rows with the data inside of it. So that's what these TH, TR, and TD actually mean. And so we just need to specify, hey, let's take all of this between the TR and this TR over here. Let's take all of this, put it on one row. Then we'll start at the next TR, which is right here. We're going to take the next row, put it it on its own row, and then we'll come back and run this on it, and that'll extract all the data from every single row. So it isn't actually crazy complicated. We just have to actually write it out. Now, let's keep all of this because what we're going to do is we're going to do something called explode. We looked at that in the last lesson. Then we're going to say reggg xp extract all. Now, this is actually exactly what we're going to do. I'm going to go ahead and tab over just so we can save ourselves some time. But I'll say as tr because these are going to be as our rows. Now, all this is going to do and let's close that parenthesis. All this is going to do is use just a tiny bit of regular expression. It's saying within the table HTML, look for each of these rows and take everything up until the end of it. And so this part that I'm highlighting right here, this basically says stop as soon as possible because we don't want to just take all of our HTML here. We just want between the TR and the forward slashtr. Let's go ahead and run this and see what it looks like. Now, if we go over here, we have all of our table HTML, but now we have the different CPT codes on each row. So, now when we come back and we actually run this, this operation is going to happen on each row. Then we'll have all of our data. The only thing about this though, this is just something, you know, it's good to get out of the way, is this data right here isn't actually data. These are just the column headers, and we wouldn't want that. So, we can filter that out. We could write a CTE or a temp table or really anything we want. I'll use a CTE here. I'm just going to say with rows as and then we'll put everything within this. And I'll do it like this so you can see it. Then we're going to just filter that out. That's it. So, we're going to say select and we'll do path, tr. And that's from rows. So, I'm going to say from rows, but now I want to filter this out. So, I'm going to say where the tr and that's this right here. Where the tr. We only want to pull in ones that have TD in it. So, I can just use like here. And I can say like, and then within here, we're looking for TD just like this. So, we have our TD with the greater than or the less than on either side. Let's go ahead and run this. This is just classic uh HTML right there. And now we only have the data. All we have to do is now take this and apply that to this logic right here. And we can do that really easily. We can just put a comma here. And now this is going to be the next part of our CTE. So we're going to say data rows as. Then we open up our parenthesis. And this is our next part of our CTE. And then all we do is take this and put it at the very bottom. That's it. So we're just layering this. But now we're not pulling from structured tables anymore. Now we're pulling from this data rows. So now we just need to easily fix this and say TR because that's where we're pulling or that's what we're passing through when we're using our AI extract. Now we're passing it through this TR right here and we're just pulling in the data. So, we're selecting only the data for each of these. So, now let's go ahead and run this. And just like that, we have all of our data in columns and rows. This really is one of the easier ways to extract data from a PDF. I've done it a lot of different ways, but it's very accurate. It gets it right basically every single time. I've never had it messed up personally. And code-wise, compared to other systems, this is very easy. I've worked at some really complicated systems. to write a ton of code and a ton of regex and all these rules and all these different tools that you're piece mealing together, but you can do it within data bricks on this free edition. It's pretty impressive. So, that is how we use AI extract to actually pull the data out and put it into columns and rows. Now, in the next lesson, we're going to be using AI classify to look at our documents and then label and classify them. But we're not just working with one document, we're going to be working with many documents.