Super Mario Maker Database

Hey Guys Tachyo here! And I’ve got good news, we are going to release my SMM database, the one ceave used
for his awesome videos. i mean Well.. it probably isn’t that much
news to you, assuming you just watched one of his amazing videos. However it is good
news to me. Cause’ I finally get to make my very own YouTube video. Speaking of which, and in case you’re wondering,
this video is supposed to be something like a Manual for the database. First, there were quite a few comments on
Ceave’s videos asking about what I’ve done and how all of this works. So we will first
take a look at that. Afterwards, …, I’m gonna show you guys how
to work with the database; if you, like most people, have never worked
with an relational database before, don’t worry. This video got you covered! So how did we scrap the data from the bookmarking
page? First, let’s take a look at what happens when we request a page from the internet. Let’s assume that we type something into google, like “ice cream”, and hit enter. What happens
now is that our browser sends a request to the google servers. This will generally look
something like this. Let’s break that down, the first part, the
host part is just which server we wanna talk to and and we will ignore it for this video. What interests us, is the actual “GET” part
of this requests. In this case we are requesting a page called
“search”. This tells google that we want to do a search
and send us the results. As you probably figured google needs
to know what we want to search for. This is where the next part comes in handy,
that’s what we call a parameter list. In this case there is only one parameter “q”, presumably
for query and it contains, no surprise here, our search term “icecream”. Now google
has all the information it needs. The server can now generate the page, send
it back to us, and our web browser will render That’s really it, it’s actually a surprisingly
simple process and it happen s all the we search for icecream Okay so what has this to do with the Super
Mario Maker bookmarking page? Well, here we
don’t have a single search field but are able to select search parameters. And yes, you
already guessed it, when we hit “send”, this parameters
get encoded in a request and send to the Nintendo server, giving it all the information it needs
to respond. It’s exactly the same pattern as before. Except
this time there is more than one argument and of course the page is called slightly
different. But that’s it! Everything else
works like before. Let’s take a look at our parameters. As you may know we can request up to 100 pages containing 10 levels each but this is per search combination If we change just one
parameter, we get 1000 new levels It’s basically like a combination lock,
and we try all possible combinations and send them as to the server. Now, obviously we’re not going to enter all
these combinations into our web browser, instead we write a little program for that. But that
doesn’t change anything, the program behaves exactly the same way our web browser does,
sending all those requests and getting all the responses. All that’s left to do is to get the information
we are interested out of the html responses and to put them
into a database. And that’s it! that’s how this databaseccame to exist,
and it actually is how every web scraping works. And now, with all that out of the way, it’s
finally time to do what we’ve all been waiting for. It’s time to get our hands on that sweet
sweet data! You can find the Super Mario Maker Database over at github. Let’s see what’s in the Box. First there is
the Python source code. But it comes with a huge disclaimer. This
code was written for in-house use. So there is no interface, no config and no
documentation whatsoever Also there may be typos. We’re professionnels here! I’ve included it in case some of you guys
are interested. However, I strongly discourage you from actually
running it. Besides the fact that It will
run for days it’s also completely pointless. You would just be trying to recreate the exact
same database that’s already here. and I said try because as the levels are already here
All you get is a lots of database exceptions. So that’s about as pointless as it gets. Second, and for most of you properly more
interesting, the Database itself. This SQLite file is what we are going to work
with. You gonna need, obviously, a program to work
with the database. I recommend “DB explorer for SQLite”. It’s cross platform, open source and the one I use So let’s hit “Open database” and select
our SQLite file. First thing we see is the structure of the
database. There are two tables “Levels” and “authors”, but more on that in a second. for
now, let’s head straight over to “Execute SQL” We are going to use something called SQL to
work with the Data. SQL stands for Structured Query Language,
and i know that already sounds complicated but
believe me, it isn’t. SQL was specifically
designed to be very close to plain english. We can think of it as a way to ask questions
and get answers from the database. Let’s take a look at our first statement,
the select statement. It’s pretty straightforward. “Select some columns from some
tabel where a certain condition is true.” Let’s try that, we want to select the id
of a level, its name, how often it was liked and how often it was played. the table we
want to select them from is called levels and as condition… let’s say we want all
levels that have been played over 20000 Now, if we run this, we get a resulting table
containing id, name, liked and played from all levels that have more than 20000 playes This is already a very powerful way to get
specific data from even the largest datasets, but there, of course, is way more that we
can do. Let’s try to recreate Ceave’s level ranking
from his statistically best level video. We already filtering for the right levels,
all that’s left to do is to calculate the “rank”
Luckily SQL allows us to do arithmetics on values of levels. Let’s add a new value to
our results. We want it to be likes divided
by played and we gonna name it rank. Note, that i’m multiplying both values with
1.0 this is so we force the use of proper floating point arithmetics… I don’t wanna
go into any details here just remember that if you get strange results, especially unexpected
zeros all over the place multiplying with 1.0 probably will fix that. We also want to order our result. We can do
that with the ORDER BY keyword, we want to order by rank and we want to order them descendent. That’s it, let’s run it and take look
at the results. Looks familiar, doesn’t it. We’ve come pretty far, we now know how to filter results, how to do calculations on
them, and how to sort them… but there is one more thing i need to show you. the one thing that separates the database
from an excel spreadsheet. the one thing that puts the relational into
relational databases… relations. A while back, i mentioned that there are
two tables in this database. Levels, and authors. If we take a look, we’ll
see that every author has an Id and it just so happens that every level has a value called
author_id. You can think of this value as
a link between these tables. We can use this relation to link the two tables
together, using what is called an inner join! Inner joins are used like this. We can specify
which tables to join and on what value to join them on. Once we’ve done that we can use all the
values from both tables. we can now search
for levels from a specific creator or filter levels per country. But you might have guessed it, this is only
the beginning… Now that we have unleash the power of inner
joins, we can collect and join more and more data. Data from the world bank, the united
nation and the International Atomic Energy Agency. We can use all this power to answer
the most pressing Super Mario Maker questions of all time. Like, which country has the most stars per
GDP per capita. it’s Japan
Or, Which country earns the most stars per dollar spent, in the public primary education
system. Yeah.. again.. that’s Japan. And most importantly. Is there a country that has less Super Mario Maker levels with over
10 stars then it has nuclear warheads Yes, there is one! but it’s
not Japan! Okay, so that’s the Super Mario Maker database,
and how to work with it! I really hope you
enjoyed this video, if you did don’t forget to hit the like button. and If you are interested
in more content like this please make sure to click the subscribe button as well so i
can tinker together more stuff like this. Speaking of which, you probably noticed this
is the first video I uploaded, and I have no idea which direction I want to go with
the channel, so if you have some time on your hands, go down to the comment section and
tell me what you are interested in. With that said, i hope you all have a beautiful
day. tachyo out!

Leave a Reply

Your email address will not be published. Required fields are marked *