GitLab Data Team, 5 Jan 2021

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Taylor Murphy and Parul Luthra pairing session on BambooHR extracts

Description

Relevant MRs https://gitlab.com/gitlab-data/analytics/-/merge_requests/4181 and https://gitlab.com/gitlab-data/analytics/-/merge_requests/4180

A

Recording this all right awesome. So do you see the issue and vs code? Yes, okay, cool um all right, so this is the one we want to do first.

A

So in this mapping we have field numbers but not sure what they relate to and you're, specifically wanting to get a list of the fields right.

B

A

Okay, so let's.

C

Go ahead and open this.

A

Murder quest, I will bring up.

A

C

Seven four: two: five.

A

Okay, all right so now we're on that branch. um Okay, so the bamboo extract is in this folder, so we've got two files api and execute, so the api is kind of the the wrapper around all the different endpoints that they have so looking at.

A

So you want the get a list of fields from this, so it this is the example path. So company v1 meta fields- and I don't think we have that path written out, but we can do that pretty easy.

A

um Let's see if we've got anything similar, so we've got the base url here, so api gateway, um subdomain is going to be gitlab and we've got v1. So everything um this is. This is the only thing we need. Is this meta slash fields got it. Okay, we've got two options here, so what I'm looking at here is for each of these functions we're generating a url so we're using this base url and then we're appending something to it. um In this case, like we have one specifically for the employee directory, we have one.

C

A

Tabular data one for the reports, um so we need one just for I think the simplest would be to just make one for um the meta specifically for the metadata, and then we can update it if we need to so okay, um since looking at this there's nothing that you would pass in that's variable.

A

um As far as I can tell oh actually, I took that back so there's so there's meta fields, there's meta tables metal list, so there are a couple of options that we could. We could add in so we'll start simply here we'll. Let's take this function, I'm just going to copy it and get meta data.

A

um So we're going to have this function, take just a string, um we'll call it a name just. Why not- and I think we'll just do that- just do that. One for now.

B

A

um All right so similar format we're going to import that base url and then we know the url is going to be. Meta is a consistent one and then we want.

A

This this is the variable the request, and then everything else is um is really the same, because I really have to check it otherwise. So.

B

A

I'm just gonna update the docs string here, api method to retrieve tabular data for.

C

I don't really know what I'm getting here I'll just copy. This.

C

C

List of available.

A

um The data types- okay, so that's a good enough documentation, so we're gonna pass it.

A

We know just to get started, we're just going to pass it fields, but we could update it later. If we wanted to to pass it something else.

B

A

So that's we consider that api uh updated there and then um this execute actually like goes through and we tell it what we're we're getting so the way we've done this is we basically have we either directly get it like in the company directory.

A

We just say: okay, um hey we're getting the employee directory run the function, get the counts, dump it to a json file and then upload that or we have so it's like calling a single function, this one's like iterating through a bunch of different options and then getting calling that specific one doing all this stuff.

B

A

It so for just simplicity, I'm going to basically copy the exact same format. I think as custom reports here just to make it simple like.

B

A

This we could probably refactor it down the line, but yeah you know for for short things right now, so we're gonna do metadata.

A

We're gonna call this meta fields and the text string is just going to be fields.

A

My son's making it to the noise.

A

Data for fields.

A

So this is one thing, so this is doing a.

A

This does a check on the data, so I'm actually going to comment that out for a second. So we have a function in here that will basically um upload the data and then count to make sure that it's like compared to the.

B

Previous data set, it works and is okay. Okay,.

A

um All right, but so we'll we'll skip that for now, um so data get oh, we need to update this. Get meta metadata, make sure I don't need to import that.

A

No, because I get the whole api okay, so I was just making sure that the function that I'm trying to use here, I don't need to explicitly import. um I don't because we're importing the entire class here, okay and then we're setting here. We set this variable to this class with the subdomain and then we just reference this and call different functions each time, okay, um so, okay, so we're saying we're, get metadata, um we're going to upload the json file, that's written out and in this case it's meta fields.

A

If I uh spell it out what this is doing, so it's looping through this dictionary, which happens to have one end entry. So it's going to say for key value and the keys, meta fields, values, fields in this and then it you know, does stuff based on that, so it's going to upload metadata or meta fields.json and we want to load it into oh, so this function.

A

Here under definition, found that's because it's in.

A

So we have some helpers.

A

Hold up so in this gitlab data utils project we have helper functions that we use a lot and so snowflake stage load copy. Remove is pretty much says what it does it. Basically, when you're uploading to snowflake, you have to put something in a stage which is kind of like an s3 bucket and then a copy from that stage um into a table, and so that's that's what we're doing we basically say take this file upload into this stage, copy it into this table using this uh connection string, so click on it, okay, yeah!

A

So in this case it's pretty standard. I don't have to change the stage because it's for bamboo hr, anyways, um we, uh the only thing we need to worry about- is the table which in this case I think I actually it's not smart enough to actually make the tables itself. So I'm going to go. Make that real, quick.

A

C

C

All right actually.

A

Okay, so I am logged in so everything, and so this uploads to the raw database. Everything in the raw database is owned by the loader role.

B

A

A

A

Yeah, so you can see we have. We have all these tables here, so I need to make a a new table um that we're going to call meta fields and maybe there's a better name for it. I don't know if you have opinions on that, but I'm.

B

Yeah, I'm okay with that. Okay.

A

So I never remember the create table statement.

A

This is one of those okay do better documentation at so I I happen to know that in our snow cloud page here we have this create table statement which I just copy and reuse constantly, so um so creator replace table we're going to do raw dot, bamboo hr, dot, meta fields which maps to what we have here and then this is the exact same because we know at least for this when you query the api you're getting back a json or like a dictionary blob, and we just insert that into a single column.

A

So all of these tables have a variant json.

B

A

And then the upload of that time stamp and this sets it to the default value. So if you don't, when you're copying into the table, if you don't give it a value, it sets the default to just the current timestamp, which is basically what we want. So okay um creator replace table baby hr, meta fields, cool, so that's done um so now. What I want to do is um test this, and I think I can I'm gonna check to make sure I've got.

A

The api key locally- they don't necessarily have to have it locally.

A

uh Let's do it, I won't do it locally, we'll do it we'll do it in airflow. So don't worry too much about this. This is just kind of our process for testing airflow. um Oh I've never seen them.

A

So you know we run airflow in kubernetes, which is like a container orchestration tool um in local testing. We can spin up airflow locally and it will run in kubernetes in like a testing, namespace.

A

Okay, there's there's a lot like there's a lot of steps, and it's it's not great. Honestly. This is not our best. It works, but it's not as easy as I would like it to be. Okay, so docker's running so now I should be able to do make a knit. I'm.

C

Actually, going to check.

C

A

Sorry, I'm doing stuff off screen, so I can see some settings that I have okay, so that should actually work. Fine, okay, so making it airflow.

B

So then, um if I were to try running that command, would it work for me or um would it not work based on my roles.

A

Probably wouldn't work based on your roles. You can try it. um I think it's going to try and connect to our gcp instance and it's creating a lot of errors, um because it's doing a bunch of stuff it's setting up like a local database, spinning up a web server.

A

um It's doing quite a lot behind the behind the scenes. Okay, so that's done, and then I need to run make airflow and this actually brings up the web.

B

C

And the scheduler and the database and all that stuff locally.

A

Okay cool! So now, if I go to localhost 8080, I'm connecting to the local web server running.

C

Oh perfect, this is actually already on, so I'm going to turn this off turn this off, because I don't want that to run. Oh, please stop.

A

I have some stuff lingering around here that I don't actually want to run okay, so this is the the dag, um the airflow dag that runs it. So we have one specifically for bamboo hr. It does a bunch of stuff, but it basically just runs. This execute script is all it's like: that's what it's doing at the end of the day, so for testing. I don't actually want to test um that all this other stuff is is running, so I'm going.

A

To uh I'm gonna comment out all of this stuff and just test the one thing that we care about. um Okay, so I need to actually real quick. I think I need to commit this.

A

I think um okay, so this so add, metadata function to bamboo hr api, commit that and then I'm going to.

A

Actually, let me commit add metadata.

B

um Is this an airflow or.

A

This so this is a tool called sublime, merge, okay, so the visual way to do some of the command line stuff to understand. What's changed what you're committing you can check out the visualization history? I have okay yeah, so it costs money and actually I haven't paid for it. Yet after about like 10, it's an unlimited trial. Just have okay.

C

A

B

Can you also do the same thing in visual studio.

A

uh Yeah, you can yeah, so there's so like if I change out of space here, so visual studio has its own little get thing. Yeah.

A

Yeah, so you can see yeah so like this. This is in.

B

A

Just I don't know, I'm just I'm used to my this work. Yeah. That's.

B

A

um So I'll discard that change. Okay, so I've committed that because I think it pulls from the remote. I don't know, um let's see what it does so, okay, so the code's up there.

A

This is nice too, because it's creating the um the databases on the backend. I don't know if I'm gonna need the raw clone one of the downsides to testing this is that I think it actually tests in production quote unquote, like I think when I run.

B

A

It's gonna actually just write to the true.

B

A

Okay, which, in this case since it's a new extract, is kind of fine, but in I think ideal, world we'd have a clone. It would write to a separate thing we wouldn't have to. We wouldn't have this overlaps.

B

A

Okay cool, so it's running now um so we're waiting for it to spin up a kubernetes pod. So I can look at gcp.

A

A

um So let's look at the workload, so it should be trying to spin. Oh my gosh there's all this. Oh, I see.

C

C

Man, it's got a lot of bad stuff here.

A

Let me get rid of all these. I think this is because I tried to spin something up. Oh yeah cause that dag was running.

C

C

C

C

Something's, oh oh, dead, coming.

A

Oh, my gosh, I have a this little icon here. It's a tool called one tab and like when you have a bunch of tabs, you click it and it like puts it all onto a single.

C

A

A list- and it I just did.

C

C

Okay, so it's running it's just waiting for something to.

C

C

Oh, did it pass.

A

Okay cool, so this these log files are a nightmare, but um so if we look at um the dag again, we can see we can try and figure out what it's doing. So this is the main bit of the command, and you can tell we copied this from other things because it says sftc, but this should be hr.

B

A

So it runs it's running this command, so the command the list of commands are like when you spin up run, run all of these commands. So the last one and the one unique to the dag is this python. You know says we're saying.

B

A

To run this script, um this clone and setup extraction command is a standard um thing where we're like. Okay, the orchestration stuff is in here move to this folder, and then we have a bunch of other stuff where it clones the project into the thing. Okay,.

B

We can look at the whole.

A

List of what it's actually doing, so, if we look at the render template in here, um do you have access to um airflow.

B

I just submitted an um access requests.

A

C

Cool um we cause, you should be able to do this real.

A

Quick, um so within any dag, really you can go into this rendered template which shows like hey. This is based on all your code. This is what we're running and this arguments command is what um is actually being run, which is it's a lot of text to say um so.

A

If I look here so it's like it's doing a bunch of stuff, it's doing a git clone it's echoing some stuff to the command line, um moving to a directory and then running python. So that's all it's doing, okay, um so we're so we got this locally. It ran it passed.

A

We look at the logs. The most interesting bit is down here so saying cloning into analytics, checking out the head, getting the branch that we're on and then this is all like snowflake stuff, so it's getting the metadata, and so that ties back to what we wrote here, getting metadata for fields and then this is all snowflake stuff, responding back like it's putting the json file, it's copying into table and then it's removing the file from the stage, because we just want to clear that out when we're done so now,.

C

A

A

Here um now, if I look at meta.

A

A

We can see, we've got data, oh, is it not just showing okay yeah stupid.

C

Snowflake is being stuff like that.

A

um So yeah, so we've got data there and we've got um uploaded. It.

B

A

So cool it works um now. What we want to do real, quick is um uncomment out the uh testing that we did to make sure um everything works. So I'm not super familiar with this this function. So what is it doing? Well, there's no comment here, but so it's taking the data looking at the snowflake table tables to skip field name json text, which is the default.

A

So if there's tables to skip.

A

Oh interesting, okay, so I think what he's doing is.

A

If it's looping through oh, if there's like wildly variant data, I guess, um if you wanted.

B

So it's eliminating it from the test.

C

Yeah okay, hold on I'm gonna. Do something to make this easier to understand.

A

All right, so so here's the test extraction. So what he's doing so use here's the length of the data we've got the snowflake table, which is the key we've got the engine and then tables to skip.

C

Do I always need.

A

To have that is that.

C

Always set, or is that a global.

A

Oh, I guess maybe it's for testing. It's really interesting, I'm not sure where that is, but okay, let's just let's just test it, um okay, so we need to tell it what data we want. um I.

C

Guess well interesting.

A

So up here he's getting length of employees- oh yeah, okay, so I see what he's doing so. It's coming in employees like, I think, it's nested, and so he gets all that information there. I think this is just it looks at the entire length of the data, so I think we can follow this. This format here.

A

Okay, um so if we just do record key counseling length data and what do you do.

A

We'll just do.

C

Is this even used.

A

A

Oh, I see okay, so oh interesting, so he's using something, that's called sorry. I keep saying he because I think um michael did a lot of this.

B

A

Okay, um so if we look in production, so it's using something called an x-com which is a way to pass data kind of between different jobs. So if I look at the production, bamboo hr extract and I come into a specific job. And then I look at xcom.

A

It's creating this dictionary of values and basically it's like a data quality check, and so what we're building in this script is a dictionary, and so we have record counts here and in each instance, he's setting the dictionary so he's setting the key to whatever we've set and then he's setting the value to whatever the length of the data is, and this is used in.

A

I think when we're getting um we're sending like logs to prometheus, I think in here. If I look for bamboo hr.

A

C

No, not that one.

A

Yeah here we go um so this data is being emitted. So, like prometheus, looks at this page and scrapes this data, and then we have like historical information for for log data.

B

A

Yeah and so like you can see, the record counts here for each of these and we can look at those over time and then we have. I think we have some alerting if it drops dramatically or something so that basically will tell us if something goes wrong with the extract. Okay, cool we're getting caught up here. So um so all we need here is to pass in or let me actually do it on the other side. So we've got our test extraction here. So all we really need is data.

A

We're telling it to look at the key table, so I don't need to hard code that everything else is the same, and I can get rid of this because we have a default which is json text, which is what we need. So I'm going to drop this thing and then that should work. So let me enter.

B

In the comma, after line.

A

So that's that's! Okay, so in python.

B

Dictionaries, you can you.

A

Can have that and it it's, it works, okay, okay, um all right! So let me commit this real, quick, so add that quality check and then.

A

All right, so I'm going to run this.

C

A

uh All right, so I am still local, so I'm just gonna trigger this again and I should would expect to see it's gonna upload, another another file, um but I wanna see in the logs. That's actually doing this. This test.

A

C

C

Make sure nothing else is ready.

C

C

A

Yep, it's good, it's getting getting query now, it's uploading to snowflake um and then it should do.

A

I guess it doesn't admit anything when it does the.

A

Okay, well the job passed.

A

ah But if I look.

C

Under the admin page.

C

A

Oh yeah, so if I come so this is locally you can see here.

A

uh This is the dag run duration. Did it actually get.

C

Interesting, I didn't get saved.

A

Oh, you know what I didn't push it.

A

That'll, do it okay, so let's go back and let's rerun.

A

It and this is and so like. This is one of the reasons that I'm not a huge fan of. I mean testing and prod, but like we're just entering in the same data a bunch of times but yeah. It's fine like it's not a huge amount of data, it's very cheap so and since it's the real data like I'm, not taking a test and since like it's after this gets merged, I'm just gonna leave it in there like it's, not hurting. Okay,.

A

um So you have to push it up to the remote, because one of the commands in here is: is cloning like getting your branch from gitlab.com so got it? Okay,.

B

A

It's not like reading from my local directory.

A

Okay, so getting metadata, um it's running the query.

C

I think it's all good.

C

This is it's really hard to read, but maybe if I go back to.

C

It's killing me here what what was it down here.

C

A

A

Interesting, that's not being written, maybe that's something weird about local doing it locally. um Okay, I'm not super worried about it. Let's just put it that way.

B

A

Okay, cool um so we're gonna say that works uh so now. Basically, the last thing left to do just.

B

A

um Uncomment, this get the code back to a happy state um comment and there's. There was two ways to do this, so I had a commit here that I could have like specifically reverted or it's just easier, sometimes to just manually, revert it or just like manually undo the changes.

A

um So that's good, so push it up refresh this page and just kind of sanity check um that you've done everything right, so bamboo hr, we've got our new function and we've got the new extract, and so what I would do next is you know, edit the description. Get it ready like I'll, take some screenshots to show how I tested it. A sample sql query to show that it's in the database and then I'll pass it off to michael for review. So, okay, that one's.

B

Pretty much ready.

A

B

And then for the pipelines, it's just running the snowflake pipeline.

A

There is nothing really to run um so a lot of the pipelines are set for the analysts, like with a few exceptions, there's not a ton of pipelines that run in here, one that.

B

You did remind.

A

Me there is one thing that we need to do um so we need to lint our code, so the data engineers have good linting the data analysts. We don't have good linting, um so.

A

We, the easiest way that I have found to do this like this- is not great either. So one of one of the jobs that's gonna run in here is it's gonna, um it's running. So black is the tool that we use for python to lint it.

A

We have a bunch of other tests that run um so it's like it's trying to find all the tests like the python tests and run those we don't have any tests for the bamboo hr extract and that's just one of the unique things, but we do need to lend everything so locally. What I do is, I do a lot of development in permafrost and we use black also for permafrost, and I have a virtual, like a python virtual environment set up.

A

So what I do to lint is I'll just start the permafrost environment and then just run black period, which tells it to lint the entire linton format, the entire project. It's going to come through it reformatted a bunch of stuff which is interesting.

A

I'm not going to worry about the stuff that I didn't touch so discard that one, let's grab that one just go to that one. So you can see so this one yeah. So what it's doing here is it's bringing so this the function that we copied, because we got rid of one of the um function parameters it just puts it online. It gets rid of an extra line here, drops some extra wipes white space on that.

A

So I'll get these two in there bring that up. Now, it's still gonna black is still gonna fail because, like it tried to lint those other things and it didn't work for whatever reason, but I'll take.

B

A

That in a separate, mr, um but we linted those two files so we're good to go so there's really there's really nothing you have to run. um Ideally, this would would be green too. So.

B

A

B

Is really it's just like testing, locally and um or testing in prod to make sure everything runs as expected.

A

Essentially, yeah: um okay, well, testing test testing locally in fraud. So it's like it's a different name space in kubernetes, but it's actually writing to the production database, which yeah for.

B

A

Hr is okay, that's, but it's not a good best practice. I would say.

B

A

All right so that one's done so, let's move on to this next one. um Let me.

A

Get situated here.

C

Make sure there's nothing left to.

C

A

All right, so I'm gonna check out the master branch again. So, let's open up another, I should put.

C

A

um All right, let's uh we'll, create a merch request from.

A

A

So we're on that new branch, let's clear up all the spaces here.

C

Let's see what we're trying to do all right, we're.

A

Good, okay, all right, so this will probably force us to make this private. So we have a project here that is not publicly available. So if I go to a private window, so I'm not logged in it's going to redirect me to sign in so we have to be authenticated.

A

We have um in fact, let me actually do this, so we have a user like a bot user called get lab analytics, which may actually already have access to this. So it's the analytics api, gitlab log and it's like a true getlab login. So I'm going to log in as this this little guy, I'm in a private window to test this out. So I guess he has a two-factor authentication code. Let me move this down here, so let me add: analytics.

A

Aha, yes, so we've got two factor: auth set up in one password, so, okay, this so login, okay! So my so not my! The api user does not have access to this project. So that's it to do. Is I have to set up an access request for that, but um let's say that it did. What are we going to do so? We need to basically get all of well, we specifically want one, but we need to get all the ones all right. So let's look at the.

A

So, let's start off with the dag, so we have our gitlab data, yaml extract and what's it looking at so we've got two files that it's running it's running, get that data yaml and gitlab feature flags example in the extracts folder.

C

A

Here so the gitlab features flag. One is one that I just wrote for camille um yeah to get the uh to get lab the product repo and then copy some data out of that.

A

um But I put it like it's in the same job because it's a very similar job but the one we.

B

A

Is this gitlab data yaml upload.pi so uh similar to what we did with um bamboo hr we've got dictionaries of you know: here's the key: here's the value, the key maps of the table, value maps, the the file name um and what we're what we're doing is going to the raw data.

A

So it's yeah raw master data and then getting performance indicators. Then I'll just do.

A

C

B

I don't know if it's called sale. Oh no! Oh it's sales.

A

um Oh, I need yaml.

A

There we go um so that's yeah, so that's the raw data. So that's what we want for for this. um So let's go.

B

So I think we have the location factor file. What we want to bring in now is the geosomes.

A

uh Okay is what do you mean, so this is. This is all behind like we're not pulling.

A

What is this actually pulling, then.

B

A

So it doesn't exist, so I I think, because.

B

I gotten oh because it got moved.

A

Right, okay, so if we look at so, I think this what's been happening is this has just been running, but nothing is probably being uploaded.

A

Yeah see so it's nothing has been uploaded, um so we need we yeah, so we'll have to move them all. But um what am I trying to do here? So I just want to get the.

A

This raw url so.

A

Yeah this is the full, so this is the new url. So the question is: we have to authenticate.

A

How do we do that? Can I even do it with curl.

A

All right, so I'm gonna have to do some googling here, because.

A

um This is where the true data engineering happens, and by that I mean googling until I find an answer that okay kind.

B

A

Makes sense: okay,.

C

um I don't authenticate.

A

So yeah so a private token seems to be the way to go, and actually um I can't I don't want to share this on the screen, so I'm gonna, so I'm doing something uh locally. um So one of the things so I do have my own private token.

A

So one of the things that I do via the command line just to share this, so we have um like I'll do snowflake spend.

A

So this is our dbt package for our snowflake spend and um I cut releases that are tied to a specific commit. So you know so, like you in your dbt packages.aml can say, get me version 1.2.0 and okay. Now you can do this by via the ui, but it used to be. You can only do it via the command line, and so you would literally um I have let me copy.

C

This, my private token.

A

um So this right here is like how I would cut a release, so I'm I'm using curl, which is a command line utility for connecting to a url.

A

um Okay, that's in a bunch of information passing your private token and then like tell it what you're you're trying to do um so, I think we're gonna, follow the same format and pass in this private token um to get this data, and so I'm, what I'm going to do. Real quick is just test off screen to see if this this works.

C

So curl and I'm just going to straight up.

C

I wonder I don't want to post, I want to.

A

Let's just see what happens if I get.

C

Just curl the raw file.

C

Being redirected.

C

Let's be smarter about this: okay.

C

Look at that girl.

A

So I know that I need to basically pass in some something: um okay.

C

So I don't want an api request. I want.

C

C

C

I wonder if I need to.

A

This is what I do for half the time is.

C

Just google around until I figure out- oh here's, okay girl.

A

Okay, here we go: okay, yeah header, private token,.

C

More access token, it should be.

C

It's hitting the api.

C

C

C

C

A

So now I need to figure out it's like, so what I don't know is, can I get this data just by hitting the api.

C

Api b4- maybe maybe let me just add, see what happens.

C

It's not gonna work, four or four, not pound.

C

I guess what now? What are the end.

C

A

So this is to get data back about stuff repository files, okay, okay, we're onto something.

A

Okay, so read repository.

C

Okay, so it gets information about the file. I need the actual.

A

Data, though, file blame, get raw file from repository. Okay, all right we're onto something, so we need the project number.

A

So the way to do that is come here, come to compensation, calculator and the project id is here. So let me test this locally.

C

What's the project slash.

C

C

A

And then I need repository files.

A

File, oh repository files, file path, got it okay,.

C

Oh, my goodness, okay.

A

All right, so let me build this. I want to build this up, something that I can actually show. You did not reveal my token okay, so so we're down here. So this is what I'm testing locally. I just replaced the the token here, so I've got the project, so I need to get rid of all of this, because this is the project name and then I think I get rid of raw. I don't need the branch name, so I think it's just data location factors, dot, dml, slash raw, maybe.

C

Let's see what that.

C

C

C

A

Okay, so I don't know how familiar you are with oh interesting, so this percent to f percent to e. This is a url encoding and that's what it says here so url encoded, full path to new file. So, okay, url.

A

Excuse me, um so if you just google url decode encode, so you put, I copy it in, and it's going to tell you it's lib class.rb, so, okay, I need to. I need to encode what did we have here? We had location data, location factors, dot, yaml, so I'll paste that whole thing in here and code. It got it here, paste that in there come to the command.

A

Do I need so this looks oh the ref. I need the ref. Oh my gosh come on just assume what I want.

A

No matches found okay, but we're making progress.

B

um You have to have to oh no.

C

Location is it location factors, so it's.

C

B

That's what it.

C

A

Oh interesting that it doesn't oh, oh, um so it encoded the slash but not the period. But if you look at the docs it encoded the period as well. So I'm just going to copy this percent to e okay and replace that.

C

Oh, that soul doesn't make it happy no matches out.

A

Interesting so this is this is what I'm doing here. So, let's compare so I've got curl header private token, my access token uh https gitlab.com. I know I know this is right because when I shared previously like how I cut a release, that is.

B

A

Is going to get left, that's.

B

A

B

A

Api v4 projects.

A

Oh, I need I need repository files. I think, is what I'm missing. Yes.

A

So after the id repository slash files file path, there we go. Let's try that, oh and then I need master or not. I'm sorry.

A

Hey: okay, that worked okay, cool, okay! So now we know so I've tested with my user. So I can. I can authenticate so we'll leave it as a separate thing to test with um the gitlab analytics either so.

B

Could I do this locally too, like uh yes, like based on the directions, just like create a token and then try to grab a file? Okay, cool yep,.

A

Yeah, especially since you already have access, so I think in your gitlab settings yeah, you just make your own personal token. um Okay, I think just follow the directions in the personalized.

B

Gitlab docs yeah.

A

Yeah, okay, so I think you need read repository, probably a re-registering okay. I would make a read-only token for sure.

B

A

Cool okay, so a couple things, so I'm going to comment this in here just so, we don't.

A

So that's what we're trying to um work through here.

B

A

Awesome, so what we can do, I think the best way to do this, I think, is just where I'm gonna update the um this function, to just take a private token and set it to none.

A

And because so so what's happening, so it's loop. Basically, we have a bunch of these dictionaries it's living through and just calling the curl and upload function. So if I just update this function,.

B

A

The private token that shouldn't be um that should be no problem. So, okay, I think the easy way to do this is, if private token is not. None then set header equal to um we're. Gonna do.

A

This actually I'll just do whole string better. I need.

A

So we are kind of doing this weird, because.

A

Like there's a whole, you can use requests, it's like the python standard way to make url requests, but what I'm actually doing.

B

A

Is shelling out to a sub process, a bash sub process to just run, to like pretend I'm running on command line so literally the exact same command. So it's literally just running um okay.

B

C

A

Okay, close it okay. So what I'm? Yes, what I'm doing here is I'm literally setting this header variable to the entire string um dash dash header private token, set the private token, um and if it is none, then I just want header to be an empty string, and then I think all I can okay. All I have to do is just put in here header, and that should be okay, because I think.

A

I think so we will need to okay, so.

B

You have to update your dictionary, no.

A

um Well, oh yeah good point. Well so we'll do that down um yeah when you need a dictionary, so we need a people ops. Well, actually I did all of these move right.

B

um No, not the performance indicator file.

A

Yeah! Sorry! No! No, not that one! um I'm talking about the oh.

B

But everything else yeah.

A

Well, so roll this is very confusing. So a lot of this changed.

A

But, oh it's okay! So we need to update all these basically right.

A

Is this data like none of this exists anymore.

B

B

Categories is still in the same project that it was, and it's not private um everything else, I think, is.

A

Oh you're right, okay, so we'll leave that one alone and I'm just gonna rename.

C

A

So we know that that's the handbook, and so now this is going to be. uh What do we call this couple? It's called the compensation comp calculator.

A

We have we're gonna have several of these right yeah, so we want.

B

um The main ones that we're looking for are location factors and geo zones. um I haven't used these other ones, but I can take a look at them.

A

Yeah, I did oh so wait. Whole team didn't change, stages, didn't change.

B

A

Job fails, change.

B

A

Factors changes.

B

A

Okay, good, um so so we'll do location factors and we want job families, presumably.

B

A

I'm just going to keep the same stuff here to make sure that works and then we'll just add geo.

A

Zones so that means we only have to make one new table um for the geo zones.

C

Which well, we just go ahead and do that real, quick.

A

So the nice thing about this is the same format. You have zones right because it's all these are just json text and uploaded that so confirming gitlab and gamma geo zones matches geo zones. Oh I'm creative! Okay, so I got that out of the way um all right.

C

C

I want to rename.

C

I want to rename.

C

A

Fudge, so I want to rename this to handbook url just to be clear.

A

That goes there that can still be based url that can be based url. This could be a handbook url, so the I want the function to be generic, but I want the variables here to be clear. So I want this is the handbook url, the pi url appends, to the handbook url the function is generic referencing a base url, and then we pass into the function handbook url, so so we've added private token equals none, and so the format here is for.

A

Our key value in.

A

We want um I'm going to make this even more specific, we're going to call this com calc url we're just going to pass in the comcat url. So.

C

I think that's right.

A

Oh, and actually this is the wrong comcad calc url.

A

Oh, since we have to do the api, so we're going to pass in yeah, there's actually a bunch of stuff. We have to change.

C

I don't want to do.

C

This uh we'll make it easy we'll just.

A

Okay, we can refactor this if this doesn't work, um so I'm gonna. So, okay, sorry, so I'm! What I'm thinking here is this actually is a little different because it assumes you can just append it, but we have to do that um special.

B

A

B

A

So this won't work, so I'm just going to take this and wrap it all. Inside of.

A

If statement and then I can refactor it a little bit if I okay, I don't like it.

C

A

Here so we're saying downloading file name to file.json. If the private token is not none we're going to have this header we're going to basically do the same stuff a little bit. So we're going to say, curl header.

A

Base url, and that is not what we're sending you're doing. We reference our url here, so the base url is actually.

A

That that's specific to the project, the point. Actually, we could do.

B

A

Right data for.

A

A

C

A

What does that do so? It thinks so in python. The percent is like a um string interpolation. So it's like it thinks it's referencing the variable when you do that.

B

A

I think I can do it as an f string that should work. Are you familiar with f strings.

B

A

An f string is a way to pass in. um You can google this later, but basically it's a way to pass in variables to a string, and so it used to be. You have to do like you have to do the string name, then dot format and then pass in the variables and it references like those percent values. This just makes it easier, so you can do like stuff. You can do with like jinja and just reference it like that. um Okay. But I think if I said this is an f string it.

A

It makes this happy so percent 2f. So that's my base url and then we have uh the file name and we have.

B

A

There we go so yeah that actually works pretty well, and so actually this could be. We could actually easily refactor this into a well. Let's get this working and then then we'll maybe look at refactoring real, quick, okay.

A

Cool, so I'm actually pretty happy with.

A

This um okay, so let's test this: let's test this locally, I think this will actually work, um so I'm going to test it. How am I going to test this.

C

B

Did you just run the final names.

A

uh No, I gotta I gotta. I have to pass it in the private token, so I got to think oh.

C

I don't want to set the private.

A

Token, um so I think we're gonna call, so we are from the environment copying the configuration dictionary so presumably within here. I would pass in a secret so we'll call this bamboo.

A

uh No, let's call it git lab analytics private token.

A

um So if this is, this is telling it when it spins up the pod, that's gonna run the job in to pass it this private token, um and how do I test this.

A

Because there's a bunch of things, I need to actually do so.

B

You have to have that.

A

Yeah, I have to add so you have to add this into here, so this is.

A

Like user private token, so you have to have um this whole thing, which tells it how to map reference. I don't like this confusing, but that's kind of what we need. um There's that I need to.

A

Quickly test this before reference it there, let me pause the recording real quick just so I can upload something.

B

Could you also um I was gonna ask? Could you also add this the token to your um adam profile and then do it that way like if it, if you have one.

A

um Not quite because remember so, I'm going to test this in airflow again and so airflow.

B

Doesn't read from your.

A

Local environment there there is a world, you could run this job locally, there's just a few more things you have to set up like you'd, have to have the snowflake user and everything that's just kind of a pain that was you to set up um okay. So for the time being, I'm going to comment that out and then just get all this merged up. So.

C

I'll actually stage, both of these and token.

A

A

Just stage all this update to pull from private project push that up and then we're gonna run. All of our.

A

Good air flow spun back up. I could have left it running this whole time, but it didn't.

B

C

It's not defined locally. Maybe I have to define it locally.

A

Let's see if this just works, because it is in the testing, I think it'll work.

A

Oh, it won't even load because it's not defined.

C

That's interesting.

A

A

So I need to import it from that cube sequence file and then I just remove it. I just realized. Oh it's locked down for you.

A

A

That's fun always fun. Okay. um I just realized we're not actually passing in the um private token here, so we need to reference the config dict and then tell it hey. I want you to use.

A

Oh, I shouldn't jump. um This.

C

A

How tight are you on timing.

B

A

Can go a little bit: okay, cool.

B

A

All right, I did.

B

Ask a question by the way in.

A

B

A

Yeah, I can't see it.

B

He's asking about if your ip address is something because he got an email that analytics api, has a new sign in.

A

Oh, if you're able to reply telling me how that was me.

B

B

Why is she responding to me? I guess slack is down for me too. It's not it's just loading.

A

A

All right so I started this dag and it automatically triggers one. So let's go to the logs, um so it's running it will probably fail on one of them because I didn't no, I didn't make the table. So actually we should be good.

A

Was your question.

B

No, I was looking at the uh for the um file name or the dad.

A

Oh yeah, it's it's just called get that data email extract.

B

A

And then oh actually, I should have okay. It runs this one first good I'll, just stop it after a minute.

C

B

Yeah it's showing on, I said it ran.

B

A

What do you mean.

B

Oh, like all, the boxes are green.

A

Oh yeah in production.

B

Yeah are in airflow.

A

Yeah but so I'm I'm connected I'm running airflow, oh yeah,.

B

A

So got it very different view and connected to a different database.

B

That makes sense: okay,.

C

um What is it trying to do.

A

Create container config error so there's an error couldn't find key gitlab analytics private token is secret really.

C

That's what we call that right.

A

Okay, so that should be happy now. um Okay, so nothing.

A

C

What are you doing.

C

A

A

Oh, maybe it retried itself in between while we're doing this. Oh.

B

A

A

Syntax error, unterminated quoted string, so that's probably related to that percent thing that we were just looking at.

C

Terminated quoted.

C

B

Is it from the command or there's another percentage.

A

Nope it's from here so see I opened um hold on. Let me stop this because it's going to try and do.

A

Does it just cancel the whole jump?

A

Oh nice, it aired out cool, so it's from so the header it's fully wrapped in quotes, and so the private token.

A

So, there's that let me comment out this, so that doesn't run that did not.

C

C

C

All right, let's try this again. We.

A

B

B

So and then um like am I able to get to the local host as well or like everyone on the data team.

A

So localhost just means it's connected to your computer. So when you're running like a web.

B

A

um On your computer you're telling you could connect a local host you're telling the web browser to look on this on your machine to connect to whatever port. So I'm running the airflow web server on 88 and so like when you spin up dbt docs that runs on like you connect to localhost at whatever port number, it happens to be, and it's serving those pages locally instead of connecting.

A

So, if you think about like when the the basics of the internet, is you send a request to another computer at a specific port and send back html files or render you're just saying just look at my computer for these files? At this point,.

B

A

What's going on here.

A

Oh weird, it's running multiple times.

A

Unexpected end of file interesting.

C

Okay, let me cancel all these.

C

I don't think that's retrying.

C

um Okay, unexpected.

A

uh Let's look at that error again.

A

Dash line 22, syntax, unexpected end of file.

A

Interesting, what does that mean?

A

um So let me just try to do.

A

A

A

I'm testing locally real, quick. Okay. um What we're.

C

Running to see if it's.

C

C

I don't need that.

C

A

A

So one of the one of the things I'm doing now is I spun up the data image container, so this runs in. um If you look at this dag it runs in the data image container. So I just spun that up locally so that I have access to all this stuff easier.

A

So this says it worked that json wonder.

A

So luckily I have this in single quotes. I wonder if we need that here.

C

Maybe let's try.

C

C

Stage stage actually on.

A

A

C

Did I really just.

A

I commended it wrong so locally. I got it to work by having these single quotes around the url. Maybe that's what it means. Okay,.

A

Isn't due to engineering fun.

B

I feel like a lot of it is just like figuring out what your errors are.

A

uh Yeah for sure it's 100.

A

All right, this is the worst part of testing, is like it takes the iteration cycle. Time isn't great a lot of.

B

B

Could I do like, um if I just wanted to bring in a data yaml file like the performance indicator files?

B

um I think I'm just gonna test it out to see if I can like go about the process, not the same way that we have it like in python or something.

A

Just for what do you mean.

B

Just for like just just for fun to see, if I like understood how to create it out, yeah.

A

Go for it um interesting, so we're still getting unexpected end of file. I wonder.

C

A

It's not all the.

A

Same must be something with how we're doing how we're building the stream.

B

Can we put that string part into like a variable itself and then just put that in there.

A

Yeah, maybe just to build it up.

C

What I need to.

B

C

A

I want to print the command line, basically.

A

A

A

I'll do it without the private.

A

A

A

I'm just uh printing this to the command line, just to see, what's actually being run, to see if I can find out what the problem is, because I don't have.

A

I don't have insight to what's actually happening, but what I will do this time so I'm going to trigger the dag and then I'm going to try and catch the.

A

Logs here, so it's going to spin up a pod and then I'm going to watch the logs there, because that you usually see a bit more information.

C

So it's creating the container, that's the.

C

C

C

A

C

Pod launching failed.

C

Did I mess something up with.

A

A

A

So, to tell it to run multiple commands in a row, you append double ampersands at the end and because.

C

A

This I had that hanging that was looking for something and then not actually.

C

Doing anything.

A

B

A

A

When you're debugging, so I'm just going to keep that clean in there and then, if this this works, then we'll get it all cleaned up. And then the only thing I'll be blocking is that access requests, which I can.

B

A

A

Slack is is not doing.

B

B

Not a good day for.

B

C

So that's spinning out.

A

Unschedulable, what does that.

A

A

Okay, now it's suddenly schedulable got it.

A

So it's making the container.

C

It's just like.

C

A

It's like a watch pot never boils.

A

B

Does it usually take pretty long to create a container.

A

B

A

Every once in a while, it's slow.

A

Maybe it's related to the issues. Slack is having um yeah so like the logs that I'm looking at here very similar to what we're seeing you just usually get a little bit more information. Okay, um but now it looks like it's traveling along so yeah nice.

A

um It looks like it's working so because I'm connecting with my private token we're getting location factors, we're getting um it's removing location factors and it's doing job families then should be doing geo zones. Yeah geo zones yeah, so it it seems to have run. So if I.

A

Presumably yeah geo zone says one row and then job families will be updated.

A

um So two questions: how critical is it to get the historical data.

B

um I don't think.

A

B

There would be yeah, I don't think that there would be changes to the location factors between like in that last week of december. I can ask brittany just to confirm, but otherwise we could just use that one for date, for our like the most recent date for um december location, factor, data, cool.

A

um I'll flag it as like, if we need to backfill, we certainly can.

B

Yeah I'll reach out to her just to confirm- and I can test it against- they usually capture the location factor data when they're doing compensation, so they have that just so, I can compare it out.

A

Cool, okay, um all right! So let's get this uh tightened up real quick! um Since we know we've tested that it works locally, um so I'm gonna revert everything back to the way it was.

A

So get that there um get rid of this clean command.

A

I don't need all of that noise. I think what I want to do so so it's working, so we can actually just refactor this real, quick, I think to.

A

Build up the um let's do it this way, we'll pull this back out out of this. If else loop and.

A

Just build up the.

A

Make the command?

A

Oh! No! I need that there.

A

A

What it's going to do, yeah! So now we just reacted a little bit so that we're only running the sub there's only one section where it's running the sub process and it builds the command up separately and then.

C

A

The references here- um okay- and I think that's much cleaner, so just test it again. Real quick.

A

Let's um I'm just gonna run this again and then that's really that's the meat of it, and so I think the follow up from this would be. I need to uncomment that out, but I need to make the access request to get the user to have access to that project. um I'll make the private token. I need to upload that, but that's that's basically it. I think.

B

A

B

Was helpful to walk through just to like understand how we're capturing data from ammo file because, like I see it, but I'm like, do I fully understand it? So this at least gets me to understanding it more.

A

So the other thing so just to walk through this real quick. What we're doing is we, we curl the file to get the data and like if you, if you run this part you you could do it on this one. Since you don't have to authenticate you like curl, that raw file, it's just going to spit out the command line, and so what we're doing here is piping it to this tool called the gamble to json, which is actually built into built into the data image container.

A

So, if you're in so, if you're in the the data I'm going to make.

A

So, if you're in the analytics project, if you run make data image, it's going to yeah spin up the same image that airflow uses and just drop you into the command line there. So you have access to um these all these commands and things like that. So if you actually just come in here, okay, um let's say I build up uh sorry we'll just type it, because that's easier!

A

So let's say I'm doing this. One curl data- and I want categories- is that short team.yaml, um so I'm just trying to copy what I got here hit that so cool it queries it right into the command line, and then you.

B

A

um You can play with yaml to json, um so run that same one, just type it to yaml to json.

A

And it actually converts it to a json file and and spits it out to the command line, and then what this is doing is just says, actually don't fit in the command line. Just write it to an actual file called right.

B

To an actual file, okay.

A

So that's right.

B

A

That's what this latter half is doing and then the front half is just telling where to look and how to get the information.

A

um That's right, yeah and then we just take that file and upload it, and that upload process is fairly standard.

B

A

So this is still running. What's, oh, I did it uh yeah, okay, so the refactor worked it's running. I'm gonna cancel the running um because I commented I put back in this one. uh It takes a little bit longer to run so I'm just going to okay kill the job. um Maybe it already finished.

A

It already finished, nevermind again, no problem, because it's these these jobs are.

C

Let's see what do we have in the merge request.

A

Yeah, so this looks pretty good. um I can comment that retry, but yeah. This is so I'll. You know I'll get this cleaned up, um indicate that it's blocked by that access request, which I can make and then I'll get it reviewed, and we can get this in probably okay. Well, it depends on how long it takes to get that approval to.

B

Get an access request: okay, yeah.

A

B

It sounds good, thank you for walking me through both of those.

A

Yeah, of course, let me stop the recording um any before I do any um any other questions, any I'm sure there's a ton of stuff.

B

um No, I think this is good, um I'm wondering if there's like a table out there or something I could bring.

A

B

um But if you run across anything that you might think would be like a good one for to test out, um that's not gonna require a lot of time. uh I'd be willing to try it out. Actually, I think that paul has one for like greenhouse access requests, which I don't think um that one's.

A

Gonna be harder.

B

Is that harder? Okay, I.

A

Think that's harder! Well, it's only gonna be harder, because I think we have to write the api interface. Oh.

B

A

B

Okay, no, no! I.

A

Was thinking this one you could certainly you could get this updated to pull.

A

You know one of the other files, if that's something that you're interested in oh okay and try and get that working locally and kind of like debug all the stuff. um The only thing is, we have to give you access to gcp, but you could still test test. No, because you don't have the you, don't have the api key. I think.

A

Yeah, maybe just play around with with pulling the stuff locally um and go from there. Okay, okay, cool yeah.

B

I'll give it a stab and then yeah, I think, I'm going to try just pulling in one of the like gitlab data. Yaml files see what it looks like and then see like where it gets stuck just for like practice to begin with.

A

Yeah another a good um tool to learn is this tool called jq, um it's a little bit confusing, but what you can do is so I would recommend. First, you know testing like okay. Can I use curl to get this data and then save it into a file, but then I would play with um using this tool called jq to actually like manipulate the the data in there. So you can so. Let's say I have this this file um json.

A

So I save all this data into a json file right there. So the command cat will just print whatever you have into the command line, and then you can play with.

B

A

Can pipe it to jq and start doing things? I don't think we have jq installed on this, but we can't yeah jk's not found, so you have to do it locally, but this is a fun way to play with json data um and really explore it. So that's another okay!.

B

All right, I'm going to do that.