Home
Contribute Contact Us Browse all meetings
Home Contribute Contact Us Browse all meetings
Delta Lake / Delta Lake Tech Talks / 14 May 2020
Delta Lake / Delta Lake Tech Talks / 14 May 2020
Previous Meeting Next Meeting
⏯
Add meeting Rate page Subscribe
youtube image
►
From YouTube: Best Practices on How to Process and Analyze Audit Logs with Delta Lake and Structured Streaming

Description

Cloud computing has fundamentally changed how companies operate - users are no longer subject to the restrictions of on-prem hardware deployments such as physical limits of resources and onerous environment upgrade processes. With the convenience and flexibility comes challenges on how to properly monitor how your users utilize these conveniently available resources. Failure to do so could result in problematic and costly anti-patterns.

In this tech conversation, Denny Lee will interview Craig Ng and Miklos Christine to discuss the best practices on how to process and analyze Databricks audit logs using Delta Lake and Structured Streaming. We will discuss and demonstrate how administrators can utilize audit logs to track resource usage and identify these potentially costly anti-patterns.

Agenda: 10AM PDT - 11AM PDT (GMT-8)

10:00AM - 10:50AM - Tech Talk
10:50AM - 11:00AM - Q&A

To join the live chat, check out the meetup page: https://www.meetup.com/data-ai-online/events/270455958/ Databricks is proud to announce that Gartner has named us a Leader in both the 2021 Magic Quadrant for Cloud Database Management Systems and the 2021 Magic Quadrant for Data Science and Machine Learning Platforms. Download the reports here. https://databricks.com/databricks-named-leader-by-gartner

A

So thanks everyone for joining us. This is um our third actually online event that we ran this week, which is really exciting. It's always great to have um awesome, content and speakers to bring to you. uh So this is another online meetup, best practices on how to process and analyze audit logs with delta lake and structured streaming.

A

So just a quick call out, we have two ways to join. uh Folks are joining us on youtube, live, so I'd suggest. Turning on your notifications, that's a great way to uh stay up to date and informed on uh upcoming online tech talks, because we stream everything live through youtube as well, and that's also where the video recordings live in the playlist. The online meetups playlist there's the link there, and I can also drop that in the chat after I'm done presenting, and then we also have our data plus ai online meetup group.

A

So if there's anyone joining from youtube who's interested in joining us there, um zoom is gonna. If you join through zoom. That's what how you're gonna be able to ask questions and kind of engage a little bit more with the speakers and um ask questions, uh so I'd encourage all of you to join in both areas both spaces. We also have some great other content in our youtube channel, so check check that out and then just a quick call out for our spark and ai summit is coming up.

A

It's june 22nd to the 26th, and it's completely virtual, and so we have a lot of awesome training opportunities for you and uh as kind of like a thank you to our community. We have a 25 off discount code for you so use that code in that red box to get a discount if you're interested in the training options and I'll also drop the link to that as well and just before I pass it over to jenny to do some speaker introductions.

A

I just want to do a call out so chat's going to be great for if there's any audio issues or anything um that I can help troubleshoot I'll, be moderating that, while the presentation's going on and then, if you have any questions for the speakers, uh please drop those in the q a feature. So that's going to be the best way that denny and the speakers are going to be able to keep track of questions coming in and answer what they can if they have some time.

A

So with that, I'm going to pass it over to denny.

B

Thanks very much karen all right rock on well hey! Welcome to this awesome delta lake tech. Talk we're going to be doing it, interview style with demos as well of course. So that way we don't make it a little bit too boring. We will put some slides in just to give some concepts, but we definitely want to make this a conversation so because we do want to make this a conversation.

B

What we'd love you to do is to go ahead and make sure you chime in your questions in the q, a okay, so there's a q a at the bottom we'd love to see those questions. So if you can go do that, that would be awesome so saying that, um uh let's switch to our other speakers, hey craig, once you introduce yourself.

C

Thanks cindy, uh hey everyone, my name is craig, I'm a customer success engineer at databricks, and so you know I work with customers who um you know our customers, so that you know they're folks who have already purchased data bricks and I work with them on you know. Everything related to you know, implementations um kind of architectural discussions and just generally, you know, help them be successful with databricks. It's great to be here.

B

Nice to have you on board correct, well, hey micklesh, want to introduce yourself as well. Please.

D

Hi, I'm mikwash christine I'm a solutions architect here at databricks I previously worked at cloudera. I've been in the big data space a while. As of my role here, I work with existing customers and new customers, identify their business use cases and see how our software can help solve those problems.

D

So I'm happy to be here to talk about an internal workflow that we do today. That also helps our customers.

B

Perfect, okay! Well then, actually that leads us to the first question so we're talking about helping our customers, which is always a great thing to become obsessed. So can let's start with that? How can you be customer assessed when we're talking about audit vlogs? Why does auto logs are? Are they even important for the customers now? Hopefully I don't sound too dumb. When I ask that question, but for a lot of folks, they want to understand that context. So why don't you guys take it away and explain that concept? Please.

D

Yeah, I could touch upon that. So audit logs provide a lot of valuable information of how users are engaging with your platform and by providing this level of detail about different pieces of the software. It helps the admins or the business owners understand who's using the platform, who's utilizing it efficiently, as well as who's having challenges, because we're not only audit logging. The successful thing we're also audit logging if people are having issues, and that also gives a good idea of what's being done on the platform and how we can improve.

D

And when you have many users- and you can imagine, enterprise organizations have hundreds of users. You can't always go ask and pull everyone to figure out all these things, but with data you can answer a lot of questions and then ask more specific questions to answer and help out your user base. So audit logs are really valuable for improving the overall experience with any data and analytics workflow.

B

Perfect and so since we're talking about the databrick service in terms of looking at the auto locks behind it, are there some cool examples that uh that um that you could provide in terms of us looking at the audit logs allowed us to understand service, for example, one that perhaps comes to mind is, for example, uh the introduction to spark r or any other ones that happen to be handy for you.

D

Yeah one question that we get from our customers is what what are their notebooks, that they're using and we have a lot of templates and they want to know who's using which clusters and how many people are cloning, these notebooks and leveraging them and using them, and that's a great way that we can identify and help present that to the end users, and we can create dashboards and repeated workflows. To do that. Another thing is cluster configuration. We have made it a lot simpler.

D

Let's just see what spark releases are the most popular that people are using. Are people trying bleeding edge versions, or are they sticking with really the long time supported versions and are fine with that? So it does allow us to answer some key questions of what people are using within the platform and then how we can improve on that as well.

B

You got it, that's that's.

D

Really.

B

Oh go ahead. Craig! Please sorry! It turned out.

C

Yeah, and just just out of that, you know, I think it's um you know, I think there are other things that you know are considerations for admins. You know in the sense that, um for example like if you have resources that have been provisioned, but um you know like maybe there's no auto termination set on them right, that's the future that we have databricks that you know if a cluster goes idle. uh You know the it'll it'll spin down automatically, and so it could be that you have.

C

You know users who aren't aware of that, and so they they uncheck auto termination- and you know they just have the this. You know the resources um continue on, for you know essentially an indefinite period of time, and so you can use audit logs to help identify those resources um not only to you know, to terminate them and make sure that you know you're, not. You know wasting uh computer resources on on nothing, but also to help educate users and let them know hey.

C

You know you should leave this on, or you know things like that. So it's not just about identifying. um You know any of these anti-patterns, but it's also about helping you educate your users uh on kind of the right way to use the platform.

B

Got it oh, so super interesting, so, okay, so now we're thinking about this prospect of like lots of logs that actually do in fact provide a lot of cool business value, but then I guess the question is like well. How are you storing all this? What do you do what's the process by which to take these logs that are just churning out of some system and then eventually getting some value out of it? I'm pretty sure, and I'm pretty sure, that's the purpose of today's session by the way.

B

But the context is you know: what's that flow? What's that process workflow that you're gonna describe and by the way for those who are attending online right now, we will switch to demos uh soon. So don't worry.

D

Craig I'll, let you go first.

C

uh Sure yeah, so I think at least you know if we speak about, you know, databricks, auto logs, specifically, um they are delivered uh as json files uh to our customers. S3 buckets, um you know, and I think that that file format is fine, for uh you know for for transporting the data you know, but in terms of making sense of it, you know, I think, there's certain ways. We structure the data that uh you know makes sense in terms of streamlining delivery, but you know when you're actually analyzing it.

C

You know it's not necessarily like the the easiest to use, and so um you know it's any alluded to.

C

We will be going into a demo where we actually walk you through. um You know how to utilize structure streaming in delta lake to take all these raw data. um You know put it into. uh You know what we call our medallion architecture, where we have bronze silver and gold tables. Where you know at a high level. What's happening is um we're doing you know, applying transformations, we're cleaning, um we're enriching the data, and so you know once we get to those gold tables, uh it's it's much easier for admins to query the data.

C

You know we kind of split them out into uh you, know different tables in a sensible way, and so you know that that's all about um you know being more performed, but also just making it generally easier to use.

B

And just to add to craig's point since we're talking about gold tables, uh we actually have previous tech talks from part of this delta lake online tech. Talk series that actually describe in detail a little bit about that delta architecture in terms of the bronze silver gold. So we're sort of jumping ahead into these con, applying these concepts right away, as as you can tell from today's session, so uh relate to that. Oh actually, before I continue on uh nicholas, did you want to add to exactly what crick was calling out.

D

No, I think he covered it, and this demo will cover more of it. So, okay.

B

Cool cool cool we're perfect. Well, then, we've we're going to switch the demo in about five in a few more minutes, but I did want to talk a little bit about the concepts before we right into it, which is uh a little bit about. Why, specifically, why conceptually? Why structure streaming and why delta lake.

C

um Yeah well happy to do that. uh Let's see so, we did uh prepare a few slides. I think, that'll kind of um you know we will we'll reiterate uh why why you should care about auto logs and then we'll we'll step into. um You know why why you, why we use delta lake and why we structured streaming so uh give me just one second.

B

Sure no problem apologies for jumping the jump of the ship on that one, then no, no, no.

C

No worries no worries all right.

C

Are you able to see my screen? uh Yes, we can cool.

C

Cool um cool, so it's just uh for a high level agenda. um You know, as I mentioned, you know, we're going to talk about. You know why auto logs are important, we'll kind of reiterate what uh what we've already covered for folks, who might have joined a little bit late. um Well, we'll talk about. You know why we chose dalton lake and then why structure streaming and then from there we can. We can jump into the demo.

D

Great, so why are audit logs important? What we've covered already is that it gives the admins or the business owners a lot of information about how they use a big data platform now with just using open source tools, it's really hard to track this. If you're stitching together a lot of different components, it's not centralized and you have to stitch together a lot of things just to try to get some valuable insight.

D

So with databricks we help track and audit log all of these actions, so that admins can understand. Are we using resources efficiently? Are there too many clusters or that are too small? Should we use bigger clusters? Is there malicious activity? Are people trying to break into specific notebooks but they're getting an error all the time or even just user engagement? What are most popular notebooks that people are using, are they using are the most or python?

D

Now combining this, we also have cloud provider logs. We can get a full picture of what the data platform looks like with s3 logs and ec2 logs. We can understand that full underlying infrastructure, so we go to the next slide.

D

Now why do we choose the delta lake design delta lake allows us to efficiently translate that json data into a better format for spark to process we're going to be slicing and dicing the data using sql and possibly data frame apis, and we want to do that if as efficiently as possible, so with delta, it allows us to use binary formats underlying is apache parquet, which is a columnar format where there's better compression and data skipping and a lot of other cool things we can do to make.

D

The queries fast now delta allows us to gracefully handle schema evolution where we have an evolving data set. As we add new services, we need to add data to the audit logs.

D

One of those parameters that we'll be talking about is the request parameters, which is a struct field in that json structure that is going to be evolving over time now. Delta also does something really cool, which is it provides schema validation on write instead of read traditionally, data platforms do lazy, evaluation and do validation on read. So it's not until someone actually reads the data. Will you understand or figure out, if there's an error now with delta?

D

We want to change that paradigm and say, let's verify, while we're writing so that there's no problems down the line, you don't want to set one or a ticket where you're blocking people from doing their analysis or their day jobs, because someone changed a data type from an inch to a decimal or something that's minor and very common in these in these scenarios. But you want to do that validation on right, so we remove that risk of schema inconsistency.

D

Now we also take advantage of the performance optimizations like optimize, where we can improve the read performance now. Optimize is a thing that we can do to pack together. Many small files and efficiently lay out the data in these binary formats like parquet, so optimize will allow us to do that and gather statistics, so we can do column, skipping and other things that are going to improve the read performance while you're doing your slicing and dicing and analytics and I'll. Let craig talk about the next portion.

C

Awesome yeah, so uh for the for this final slide, you know motivating how we've designed this entire architecture. You know we'll talk about why we chose structured streaming. You know first bullet point: state management, state management, state management.

C

The reason why we chose like one of the you know main reasons we chose structured streaming is rather than having to reason about uh you know where, when we last processed based on you know, record id or or time stamp, uh or something like that, you know we allow structured streaming to uh to handle all that state management. So we can use uh the built-in check pointing and write ahead logs to ensure that we're only processing the newly added audit logs right.

C

So you know rather than saying okay we're we're going to check the latest date. Okay, it was you know what is it like may, uh like may 12th right, so we'll process just may 13th using structure streaming, uh we're now able to just say: okay, we're! Basically, you know we're using offsets based on files and not necessarily based on timestamps. So again it just kind of simplifies that it takes out.

C

um You know that kind of additional piece of logic that you might have had to write in and then you know, I think, most folks when they think about structured streaming right. They think, oh, this has to be only for for streaming type data. The way we can architect this job and the way we you can. I guess you can architect any job, but the way we've architected this particular job is um we utilize, structured, streaming's trigger once functionality with a daily job, and so we treat them kind of like pseudo batch jobs right.

C

So you know there are a couple different paradigms with you know, when you're using streaming technologies right, one one is to you know to have continuous processing with an always-on cluster, and you know again, that's if you have that kind of sla. That requirement where you need data as quickly as possible. That is definitely a valid architecture, but it is, it can be quite costly now you know if you have, if your slas are a little less stringent, uh you know where data are arriving um at you know less frequent intervals.

C

uh You can, you know, explore using this trigger launch functionality with you know, daily, weekly, monthly jobs, etc, uh and that functionality will kind of uh will still benefit you either way, and so um you know we certainly do have a you know additional resources on this. uh We have a blog that uh explains the benefits of using trigger once to reduce costs. uh But again you know this is all about. You know understanding the needs of your business, and you know what what the true slas are around making that data available.

B

Cool well, then, I think that definitely describes the concepts really well. I appreciate the fact that we definitely did the part about delta lake and structure streaming, especially trigger watts, and just to reiterate what craig mcclush are talking about. Even for pseudo bash jobs, it's quite appropriate so just need to understand we're going to be sending these slides out with the session. That actually has these links that you see here. So that way you can dive deeper into that, because we really want to switch to demo mode right.

B

So let's go do that, so why don't we switch to that? And then you, I'm sure, you're going to have lots of questions so for the person who just asked? Where are we going to share the slides with the links we will right after this session, it'll show up in the youtube channel uh so that way it'll be there, and so let's go ahead and dive right into the demo. Please awesome.

C

Thanks danny um cool, so uh you know for this demo, I think we're gonna split it into two parts right, so one is gonna, be focused on the actual etl portion, and you know when we talked about you know this medallion architecture, bronze, silver, gold tables.

C

You know all that processing is going to be covered in this notebook and so we'll kind of explain, uh concepts along the way, and then we have a secondary notebook, which shows. You know some example queries of how you might actually access this data.

C

um I think we I don't know if we've mentioned this yet, but uh you know we, we do have a blog post, that's coming out sometime in the next few weeks uh that you know we'll also cover this in much more detail, and so once that blog gets released, you'll have access these notebooks then as well cool all right. So you know the way we design this notebook at a very high level is to either allow our customers to.

C

You know who who already have are already using auto logs to you, know, specify a source and sync bucket, but you know that's not strictly necessary uh to run this demo. uh We do also have um this data source option using widgets within databricks notebooks, to specify um you know this. uh This uh synthetic data set that we've hosted on uh on github cool. So first thing to do: is you know for the purposes demo?

C

Just if this auto logs database already exists, we'll drop it if you're doing this in your own environment and you actually have an audit logs, you know database. You probably want to remove this, but just you know it's something that is kind of a convenience convenience section um again. You know we'll start off with some imports.

C

You know we're making sure that we have all the libraries and functions that we need uh available and again this is some code about you know how to process, or you know, parse, that fake data or um or to get the actual s3 bucket source uh specified in these widgets up here.

C

So you know, as we covered uh before the way that databricks delivers audit logs today is we we send them as json to an s3 bucket um that you know our customers specify and so uh what how we start off is you know in order to instantiate this uh streaming data frame, uh we need to first grab the schema, and so you know we we use the spark json data source, we read uh the bucket directly and we we grab the schema from there from there.

C

uh We instantiate uh our stream reader right, so we're loading directly from that source bucket and then we're uh we're using the schema that we had we'd already grabbed next step would be to instantiate that for the actual stream writer right. So this first step is where we we take the data that are the raw json files, and then we we just write it essentially straight straight into a delta lake table, and so um what we do is you know we we use the the stream writer api, we specify the format as delta.

C

You know we partition it by date, um we set these checkpoint locations. You know again, you know this. This is where we talked about the state management for structured streaming, um and you know this is where you need to specify the checkpoints so that structure streaming knows.

C

You know exactly uh how much data it's already processed so that the next time you run the job, it will only process those data which you know have not been processed since the last checkpoint.

C

So here we specify a couple of other options like you know where exactly we want to write out to and then again we use that trigger once option.

C

Here we have a little bit of code that just ensures that you, uh the streaming query, finishes before uh we move on to the next step, and so so for here we just you know. While while the streams are active, we ensure that uh it stays awake and then you know the next step would be uh to create a database if it doesn't exist right.

C

So what we did up here with the uh with the streamwriter is we we just wrote out the uh the raw files uh in in this delta lake format, but we actually need to uh create an entry uh in our you know: databricks internal metastore, uh to ensure that um you know it's just more easily accessible for end users, and so what we do is we we instantiate the database first and then we create this bronze table again. The bronze table is completely raw data. It's essentially the json. That's just been copied in.

C

And the next step would be to optimize the bronze table, and so, as mike lash covered in his slide, um you know we do have uh these performance uh focus capabilities for uh for delta lake on databricks, and so in this case you know what we recommend is you know if you have batch updates these tables um that you you optimize them every every time you write.

C

And that will increase the read performance like like mklosh mentioned, we can pack files together to ensure that read performances is as performant as possible.

B

So, to help clarify what corrects talk about, instead of actually having all these small little files that are prototypical of streaming now, what you end up, having is some compacted files that are larger in size, not too large that it takes forever for it to read, but at the same time, it'll actually be large enough, where the overhead of reading a lot of small files is basically avoided. So that way, you.

A

Have.

B

Faster reach just like that, so that's what the call of the optimize is.

C

Awesome thanks danny um cool, and uh you know we mclash also referenced. uh This request, params field uh that you know is, let's say evolving right, because you know, as we add uh additional services, as we add uh more events that we log, uh we do uh keep them all in this one kind of canonical struct right, and so, if we kind of look ahead to how we want to architect this, you know when databricks ships, the audit logs. We include events from every service that we track.

C

We kind of combine them all together, and so eventually, what we want to do with each of the gold tables is we want to have a different table for each service name, so examples would be.

C

You know, we'll have a separate gold table for jobs, a separate table for clusters, for example, and so you know what we want to do is we want to actually take this request? Params field that you know is meant to to cover. You know every event type for uh for every service name and we want to actually strip it down and you know create a schema for the request params for each of those specific gold tables. So you know you'll the request. Params probably have 150 to 200 fields.

C

You know when when they come raw, but you know for for a given service name like clusters, there might only be 20 fields that are relevant right and so we'll kind of go through uh this process that we um we've developed to ensure that you know for that clusters table. We only have those 20 fields. So it's it's just. You know a little bit easier to uh to analyze the events cool, and so you know what we do here is we we divide. We define this function that you know for every field.

C

It will uh strip out all the nulls for that particular field. So you know, let's say you have this one event um within you know the cluster service name it it's going to be. If we left the request params, as is, they would be, you know, a very sparse struct, and so instead, what this?

C

What this function does that we eventually define as a udf so that we can use it uh in spark, is it'll it'll, remove all the fields that are null so it'll leave you with you know, just those you know five or six fields that actually have values.

B

Cool, so hey craig just want to chime in request. uh Could you clarify uh why the the care for sparsity, uh in terms of like why you prefer not having spurs data in this case for your structs.

C

Yeah yeah, so um I think it's just um as you're as you're doing your analysis and as you are uh trying to make, uh you know, make use of the audit logs. uh If you have a sparse struct, it just becomes much more difficult to parse out what uh what's actually relevant and what's not relevant, and so, when you have a struct, that's not sparse. You can just see okay, I'm gonna look in and I can see that they're they're, five or ten fields.

C

So I know I you know it's much easier to sort through what's relevant for this particular type of event that you're trying to track- or this particular question you're trying to answer rather than you know, having to sort through 150 fields only to find that they're, the relevant value they're only relevant values for four or five of them.

B

Makes sense makes sense thanks thanks for the clarification.

C

Yeah no worries uh cool, and so you know again, you know if we, if we go back to our this medallion architecture, that we talk about where we have bronze silver and gold tables with the bronze tables. You know completely raw now as we're moving into the silver table. This is where you know we're starting to apply transformations like stripping out the nulls, and so what we do is we we utilize this function in delta lake called table to table streams.

C

So essentially, what we do is we take this bronze, this bronze table that we have, and we can actually you know, instantiate a stream reader from that and we can actually write to our new silver table. So what we do is we instantiate our stream reader?

C

We give it the path to the the underlying files for for the uh for the bronze table, and then you know we're going to apply these the following transformations right: we're going to strip all the nulls, as we mentioned. With that udf, um we're going to take this email uh construct and we're going to parse or we're going to parse email from the user identity.

C

Struct, we're actually going to you, know parse, the time stamp into something that's a little more human readable and then we're going to drop the the raw request, params and user identities, and so you know this is totally fine for a silver table, because you have all the the raw data in your bronze table right and so what that allows you to do is you know? Let's say you find some kind of issue in your data pipeline in silver gold.

C

If you have all the raw data in bronze or very close to raw, you can actually just replay the entire pipeline to make sure that you know any mistakes that you found can be corrected, uh and so here here's a here's, an example of what I was talking about with the request programs. You know it's, um I guess in this case it's not it's not too too bad, but you know in the case of um some others, you know it's yeah. I can uh the.

B

Number of.

C

Fields can be pretty overwhelming.

B

Don't forget, this is also fake generated data as opposed to real live data. So that's right. Thank.

D

You.

C

We forgot.

B

We didn't want to overdo it right. That's.

C

True that, no that's that's totally fair.

C

Cool and so uh here here's the actual um query that we want to uh instantiate for the for the stream writer. um So this is where we actually follow all the steps that we detailed above with uh you know, stripping out the nulls parsing email parsing time and then dropping the raw fields.

C

So again, uh you know we are we're checkpointing, we have a separate checkpoint because it's a separate stream. So in this way, like you know, rod or bronze is you know that has one set of checkpoints, bronze silver that has another set of checkpoints and then uh silver to gold. You'll see has another set of checkpoints.

C

So we also specify uh the path that we're writing out to and then again we're uh where we're doing we're using trigger.

C

Once so again, you know we, we write a bit of code to ensure that uh you know, while the stream is still active, that we don't advance the cell and then again like we mentioned before, um we're writing all the raw data for the silver table, but we haven't yet registered that table to the the databricks hive metastore, and so we registered again to ensure that, uh for you, you know it's for ease of access for, for the end users.

C

And so this is just a little extra guarantee to make sure that you know that the number of records in in bronze and silver are the same. This is not something you actually have to reason about because of structured streaming, but it's just something. That's in there to you know for added comfort, cool and then last step is to to optimize, as we mentioned before is something that you want to do.

C

Every time you you run these batch jobs that update these tables right. So you want to make sure that, as often as possible, you're able to compact the files to to maximize your read performance.

C

Cool and then uh you know, there's going to be a a bunch of code that that happens uh in the in this in this next few cells, but essentially, what we're trying to do when we create the gold tables is when we created that silver table. What we did was we actually stripped out all the nulls for for every given event right so for every you know, you know xyz event that happens in xyz cluster uh uh service. um You know, we've only kept those keys that are relevant for that event.

C

Now that's actually not going to be a completely comprehensive, because you know if we look at kind of the the total set of possible. uh You know fields in request, params, for uh for a given service name, it's actually there's actually probably going to be. You know 15 or 20, even if every kind of event within that service name only utilizes, three or four right. So excuse me.

C

um So what uh what we're doing here is we're actually just we're grabbing um all the keys for every service name and then we're we're using that to define the schema at the individual gold table level right and so in this case the the request frame fields that are going to show up at the end of the you know in those gold tables they themselves are you know it's not going to be sparse, but you know maybe, for every event, it may only be a quarter to um you know a half filled with with actual data, so you know that that's something that you know maybe is uh maybe like a little a little more cumbersome, but you know compared to having you know, 150 to 200 fields with you know, 80 of them being uh null.

C

Is you know? This is something that we find it's much easier to deal with.

B

So so, basically, the context is that, because of the nature of the data you have it was, it actually makes more sense instead having a single one single table with basically 150 fields, and even if you've removed, like you know the nulls and all that stuff, it would still be pretty sparse in some ways versus multiplexing it out into multiple tables. Is that the context here yeah exactly yeah rock on that's, I always love multiplexing. If, for another reason just to say it.

C

Awesome cool and so what we? What we do here is we define this function that allows us to, you know, create a schema, essentially a schema per service name and then write that out to an individual table um and again you know we we are using uh structured streaming, um and so in this case uh you know this is the transition from silver to gold right and so here we're we're doing our final and any final enriching of the data.

C

um We do define uh our our separate checkpoints for for these gold tables um and we also define a location where we actually want to store the files again using trigger once and then here. What we do is so here we're getting a distinct value of service names. uh Essentially, so you know, we know how many gold tables to to create right, and so what we do uh with this is actually we loop through each service name and then we we run that function to essentially create that table again.

C

You know, as we've done with with uh with our bronze and and silver uh transformations. We have this uh helper helper code to ensure that we uh we don't advance the notebook uh while the while the streams are still running and then here what we're doing is. We are registering each of those gold tables to the meta store like we did, for, for bronze and and for silver and then again for each table that we've created.

C

uh We, we run optimize to ensure that uh you know we're we're avoiding the small foul problem as much as we're able to, and then you know, as we mentioned before this assertion- that's not really necessary, but we put in there for extra comfort.

C

We we take the total account of the silver table and then we uh we take the account of all the gold tables and we assert that the the row counts match cool and so now uh you know we have these.

C

In this case, we have a bronze table and a server table and for our fake data we only took data, you know from one day uh from the cluster service name, and so the only goal table that we we output was clusters. But you know, were you to run this on the full databricks audit logs uh you'd, have clusters, jobs and and every other service name.

B

Cool, that's pretty awesome, so you know so it's simplified for the purpose of now and just in terms of clusters. But to over to overemphasize that point. You know in a real audit log. You would just see bronze and silver. You would see clusters jaw multiple other tables multiplexed out from the single table. So that way it allows you to go ahead and focus, allows the analysts to basically focus on just the services that they care about.

D

Exactly perfect cool, I know we have a few questions before we get into the next part of it. I think it would be good just to answer those questions.

B

Yeah by all means go for it. Please. I've.

D

Been reading them craig, so while you present so I can answer them, the first question comes and I'll read them out loud is the conventional or traditional spark applications that people are really familiar with in other distros can have a different set of spark configs, which are provided with each application.

D

Data breaks a little different in that we expose a cluster level sparking bid, and the question is: is there a way we can provide a different config for different applications that are on a shared cluster? Not just for um just for we. Okay. Can we set different application configurations that run on the same cluster within data roots?

D

Now that's a great question. The the model of databricks is we're helping to tune and configure the clusters for our end users, but that also allows you to still provide configurations for different applications.

D

The real meat of the question here is, if I have different applications running, can they have different parameters? Now spark supports different types of parameters. So the answer to that question is yes, you can, but it depends on the type of configurations you want to apply for that application.

D

If you wanted to there's session configurations and spark that can be applied per application on a shared cluster, but if you are submitting jobs similar to how we're going to be submitting this job, ideally you want to run it on its own cluster, help with fault isolation, help with cost management and tracking of utilization as well.

D

So for these applications that are more production level. We recommend that you launch a job. It runs your application and then shuts down the resources and so that it's efficient, you have no idle time in your job that you're paying for and that you can apply all those different kind of configurations.

D

So the next question is, besides schema validation. There was a blog last year that talked about delta and something called data expectations which will be a new api that will allow you to define rules and actions for your actual data types and incoming data and be able to do this on the deltablade tables. Now that's still coming soon and will be announced late at a later time. So that's still something that's being developed.

D

The next question comes from specifically on where data bricks can run on the azure databricks platform, and the question is: can we send these logs to adls gen 2.? My understanding is, there's actually multiple ways that azure can provide those profit loss. You could tie it into the log analytics service that they offer. They also allow you to place these into a storage account of your choosing. So, yes, we can uh write all these audit logs into your own storage within the azure databricks platform.

D

Now the next question is: can databricks also ship, the cluster logs driver standard out et cetera? Yes, that's that's configurable at a cluster level, so within each cluster there is a logging directory and we can ship logs to whatever storage you would like. So you can have logs shipped to different types of storage if there's different sensitivity, but also into a common log processing bucket.

D

Now. The last question is: can we go into a little more detail as to why we use restream and write stream with a trigger once daily, instead of a read and write once daily, so craig and I actually were collaborating and we went down the route of using, read and write batch jobs with spark now, one topic that we had to discuss was: how do we handle later writing data? How do we handle the validation of what we've already processed?

D

We wrote some code to do this, where we would do a diff between what we've processed and what's what's new, we looked at the different dates that we processed, and that was a good amount of code and then, while we discussed this, we realized that spark streaming actually does this, that's what spark streaming is for now. We don't have a real-time feed of data. It's not coming through a pub sub system where we're constantly getting data.

D

So we have the benefit that we can run this as a pseudo batch job, but leverage the streaming application to handle the late arriving data to do the diff of the different files that we've processed and to keep that checkpoint. So we don't have to maintain any of that state. That's all maintained in that checkpoint folder that craig has described in this etl pipeline.

D

So with with read stream and write stream, we're able to reduce the amount of code because we let structured streaming do the heavy lifting for us. The read stream is at a bucket level, we'll look through and see what new data has arrived. The right stream will make sure we're only committing and writing out. Well, it's been updated. So that's really going to help us simplify the code.

D

One more question that just came in if read stream reads data from the spark data frame. Then, why downstream to say, write to delta tables any different than standard and batch? Is there different than the actual data frame?

D

Is there any difference between the actual data frame we get in terms of partitions and logs?

D

So there's no difference in terms of partitions and logs that are coming in from the data, so the restream is the the bulk of it because we're reading as a stream we're generating the checkpoint, uh but the the the complementary action from a reach stream is you're going to write stream.

D

So you have to use those you read stream, you write stream and then you define how the streaming operation happens and trigger once is a method where we say we're going to read it once we're going to write it once and that's going to complete that action for today.

B

Perfect uh correct uh make list. Excuse me uh thanks very much for answering those questions live uh in for the sake of time, since we only got about 10 minutes left. While we shift right over to the the autolog example queries. Please sounds.

C

Good cool, so, as mentioned before, uh we were with this fake data, we're focusing specifically on you, know the events for one day uh within uh the cluster service, um and so you know in in, as we talked about, you know like why, when we were motivating, you know why you should use odd logs why you should care. um You know. One of the uh yeah, like one of the main applications of auto logs is for monitoring and just understanding how your users are utilizing.

C

uh You know all of your compute resources, um and so you know this notebook is going to be an example. You know walking you through how you can use uh these data that we have. You know transformed from these. uh You know raw json files that are a little a little cumbersome to use, and it's something that's that's much easier to analyze.

C

So you know again we'll use this audit logs database, and then here uh you know now, if you want to know how many clusters, your end users, created uh on a certain date, uh it's as easy as writing. A query like select date and count star from this clusters table right. So you know, whereas before you might have had to sift through the json- and you know try to kind of understand like where you need to uh to set your where clause.

C

um Now uh you can just uh focus on uh uh you know just the clusters and then you can. You know you can just focus on this. One action name create which you know is much more intuitive to understand, and so here we see that, for you know, december 28 2019 uh we've created about 700 clusters, um so you know again, there's not much context here, because uh you know we have.

C

um We don't have the data from any other day, but you know for the sake of this exercise, let's just assume that uh the number of clusters here is a triple of any other day right, so something that's a significant outlier and so and then the other assumption would be. um You know that the number of users did not change meaningfully from you know from one day to the other. um So you know, as we think about this and how how users uh utilize the platform uh chances are. The only way that there could be.

C

You know such a massive spike without any change in users would be um that you know they were created, programmatically using something like jobs and again, since you know december 20th, 2019 was a saturday, you wouldn't really expect people to be, you know using databricks or maybe maybe they would. You know using databricks on a saturday, um and so you know we can use uh auto logs to kind of investigate that.

B

Well, let's clarify it, don't forget, this is fake data.

C

Cool- and so you know, since we do have additional information about the cluster creator tag, um you know, so what we can do is we can. Actually, you know from the request params that we've now cleaned up. You know which are now it's now much easier to inspect. We can see that you know for this particular create event.

C

You know we had 709 uh uh events, you know from the job launcher right, so, okay, so that that goes to confirm our kind of intuition about uh you know where, where this might be coming from, and so we know that you know before we had 714 clusters and 709 of them were created by the job launcher again like that, that's something that should definitely jump out, and you know the way that the databricks naming convention works for for job clusters, and you know, as miklosh mentioned with um his.

C

uh You know when he answered the question about setting spark configs when databricks provisions. When you submit a an automated job to databricks, we provision a brand new cluster every time the job runs so that uh it only lives uh just for the duration of the job right and that's about you know ensuring that you're using your resources optimally, and so this name. This is the naming convention that we follow.

C

It's job dash, job id dash, run, dash, run id, and you can think about this, like the job is just um the entity or, like you know, like the uh kind of as it exists in databricks, you know it could be a notebook, it could be a jar.

C

You know one of those two things and then let's say it's set to run daily and the first day was monday, so monday would be run, one tuesday would be run. Two and wednesday would be run three etcetera right. So the job id is not change, but the run id will increment and that that's the cluster name. And so, if we're trying to understand um you know, if there's a certain job, that's uh you know creating that has run many many times and it's creating a ton of these clusters.

C

What we can do is we can actually uh parse the cluster name into the job id and run id separately, and so, in this case, we're using you know, spark sql function to split on hyphen and then grab that grab that second element right, and so in this case, uh so we run this query on our clusters table and now we see that the job id that's in question is 31303, so we know that the vast majority of these job runs are coming from this one particular job id, and so, if we go down a little further, um so if we see that job you know job id 31303 is is the culprit.

C

We can actually uh parse the user id from the autoblogs as well to get the actual person who is- and so here we see it's uh user id, 101 364, and so in this case uh you could utilize the databricks skim api to um to to actually extract the email address of that user. And you know again, um you know when we talk about odd blogs like it's not just about you know identifying. uh You know misuse of resources or anything like that. It's also about giving you tools to educate your end users right.

C

So in this case we have user 101364. We can send that person a quick message or an email and just say: hey, like you, have this job running it's it's. Let's talk about this. Let's, let's figure out what you know, what exactly um how exactly you configure this job uh such that it you know exhibited this behavior.

C

And then uh the next, uh the next thing that we uh we had talked about uh or not next thing, but you know, one of the things we had talked about was um just looking at uh interactive clusters without auto termination right, and so uh this is one of those uh things that we talked about. You know being a potential anti-pattern right so when, uh when users create these interactive clusters- and you know if they, if they uncheck auto termination, essentially the cluster will never terminate right.

C

So you will have you know these underlying cloud resources uh up uh indefinitely right, and so you know what you want to do. Is you want to um you want to identify these clusters so that you can you know a uh you know ensure whether they should actually be up, whether there's uh compute actually occurring or if they're idle, uh and then you know the uh the flip side of that would again be to uh to discuss with the users who created those clusters um to let them know hey, like you know this. Is this?

C

Isn't something you should do you know please create clusters with auto retermination in the future.

B

Super cool stuff, so it's basically what you've done is showcased with this notebook, a real quick way for you to go ahead and analyze the data right down to the used lever, if necessary, but down to understanding the cluster level and be able to make sense of the data almost immediately right off the gold tables, so super compelling yeah um any other. A little call out notes. Oh sorry, before I called notes, there are multiple questions about: can they get access to the notebook? So can they ask us access to the the data?

B

So, as a reiteration, we will be posting some of these resources immediately after on the youtube channel, some of them we're going to be delaying till a little bit um around the 20th or 21st. When the blog post comes out, there's an accompanying blog post that comes with this particular tech.

B

Talk uh and it'll actually have even more details, so you can dive deep into it, so the blog post will be there and we'll post that, of course, in the youtube channel and inside the blog post, we'll have the notebooks and the the data that we used inside for uh for this. So that way you can go ahead and try this out yourself, first on fake data, as opposed to on your real data right away, so that way at least get yourself familiar with the system. First.