All right.
Welcome to the next section.
So what we're going to do here is basically take the schema design we came up with in the last section
for roughly Instagram ish application with photos and users and comments and tags and followers and
all of that we're going to take that schema and implemented.
And I'm going to give you a bunch of data to put in your database like a couple of thousand of things
to enter.
I wrote a giant file and what we'll do is put that in there so we all have the same starting place and
then we're going to pretend that we're the company and we're trying to figure out certain things like
Do we have too many bots on our application or their user accounts that are inactive or their user accounts
that have never posted a photo.
Maybe we could send them an e-mail campaign you know encouraging them to post a photo or hash tags tend
to be most successful.
That kind of stuff.
So all of this section every video will be asking a question which is basically a nice way of saying
open an exercise.
But we're now posing it rather than just giving you a table which is what we've been doing where you
know it's a table and I just ask you to recreate this.
I'm now going to pose the question as if we work at a fictional company and we're trying to find something
out.
For example it might be something like we're running a new ad campaign and we need to figure out which
five hashtags get the most likes.
Something like that.
So that's how this will work every video on the section will be a question and rather than having all
of the solutions at the end making a giant video that takes like half an hour to go through.
I'm just going to break them up.
So every video will have the question at the beginning and then a following solution.
But the first thing we need to do is insert all of our data so included in this section is this file
here starter data as well.
And there's two components the first one is setting up our schema.
And there's nothing new here we talked about all of this in the last section but if you were following
along and doing it yourself you may have named something slightly differently.
Like instead of photo tags you may have called that tagging.
Or instead of you know I don't know instead of follow me and follower and follows Maybe you call that
following or relationship or something.
So in order to give you this data here to insert I needed to make sure that we all have the same schema
even if just one thing is named differently.
It's a problem.
So the first part of this is just a bunch of create tables.
All the tables are discussed as well as dropping the existing Instagram clone database creating a new
database and then using it if you want to if you want to keep your current Instagram clone database.
If you've been working with that just give us a different name whatever you want but you need to do
it to all three of these.
OK.
So we set up the schema but then the fun part which is all of these Insearch statements and it doesn't
look that impressive when you look at it in cloud 9 because it doesn't wrap them onto new lines.
And if I start scrolling you'll see I'm scrolling really fast but look down here at the progress bar
I've barely made a dent.
And this is not a great way to visualize the data.
So instead here I am in Sublime Text and here's the file that I generated.
It's the same exact file.
It's just displayed all here rather than having to scroll across.
I have to scroll down.
So you know we won't do any sort of grand tour of the data but I will say this I spent a lot of time
trying to figure out how I wanted this to work.
Obviously I didn't type this myself.
I would have taken days probably Instead I wrote some code which still took probably an entire day and
a half to get it this how I wanted because I didn't want just randomized data.
If I just wanted to insert a bunch of random users and posts and comments that have no meaning to it
I could have done that pretty quickly but instead I tried to design this so that there is certain personas
to our to our users so some users post a lot so they have a lot of comments a lot of posts.
Some users are lurkers meaning that they don't really do anything except look at people maybe like certain
things.
At best they don't comment and they don't post.
Then we have things like bots which on Instagram are a real problem where bots are accounts that exist
basically to go out and like a bunch of things.
Comments on things usually the same exact comment but they don't post things and then we also have like
celebrity accounts who have very few people that they actually follow.
But they have tons of followers and they're not very active with commentating or liking with very active
with posting.
So then we've got things like hashtags and I didn't want to just do a bunch of random hash tags.
I tried to actually come up with combinations of hash tags.
It would be common.
So you know things like smile and style and fashion might be together often but then we might also have
smile and party or concert.
Those might go together or we might have you know landscape and sunrise and sunset and beach could be
a combination for a beach photo at sunset.
But then we could also have beach night with party or beach with beauty or something like that.
So that's just kind of scratching the surface.
But I tried to design a lot of that in there so that we had somewhat real data.
With that said things like comments the text of a comment is just random Lorem Ipsum text.
So there's nothing there you know that's going to be meaningful about the comment itself.
What's more meaningful is the relationship between the comment and who posted it.
And if they have a lot of comments or they don't have many comments and that sort of thing.
OK so that's enough of that.
So even with all the effort that I've put in here there's still only you know 100 or so or exactly 100
users about 250 something photos.
But then when we start talking about likes there's thousands and comments there's tons.
And following follows whatever recalled it there's tons and tags there's only maybe 20 hashtags.
But then when you apply them to all of those photos 200 photos we get thousands to thousands of instances.
So all you have to do is get that into a file and then execute that file scroll back over and take too
long.
Oh boy almost there.
Here we go.
So execute this file and I'll just do that now.
With source Instagram slash starter data and that should just be the path that corresponds to wherever
that file is and whatever you've named it.
OK so it takes a little bit compared to what we've seen so far.
So if you take a look we've got you know a bunch of tables being created seven I think.
And then a bunch of things being inserted 100 257 seven thousand six hundred twenty three 7400 eighty
eight eight thousand seven hundred eighty two 21 and then 501.
So this is still a very small dataset compared to.
I mean tiny miniscule compared to Instagram.
This is maybe like a minute of Internet activity.
Probably less than that a couple of seconds.
As far as the number of photos and stuff but it's still an order of magnitude greater than what we've
been working with.
One other thing that I'll show you is that we haven't really seen giant amounts of data like this.
And if you try and do it to do a select star from likes which I think we have a lot of you actually
if you if you start to scroll through it you'll see that we're not going to be able to see all of them.
There is a number that we that the terminal or that the Seelye will print out for us and then it just
gives up or it decides not to display all of them.
That doesn't mean they're not there.
So if he did account for example
count star from like we get eight thousand.
And if you you know join them with another table it will join all of them it just won't display everything.
So for some reason you needed to display every single like it would be better not to do it using the
CSI but to do it from you know another file or some code somewhere.
Anyways so that's it we've got the data set up if you'd like.
Play around that explored a bit and then we'll start with our fun challenges in the next video.