Creating a MySQL Database on Google's Cloud Compute for Lahman's Baseball Data

Google recently released fully managed MySQL and postgreSQL instances that are very easy to deploy. While not GIS per se, SQL is a fundamental GISer skill in general, and so as a baseball fan, I decided to see how easy it would be to create a MySQL database for Sean Lahman's database. It took me about 15 minutes to figure out and do the work. Following is how it's done.

Step 0, go to this page and follow the directions. Frankly, I can't do it better than Google's quick start. After completing this tutorial, you will have created a small database.

Now, after doing that, you can download the Lahman's database.

1) First, exit MySQL:

mysql> exit

2) Use wget to download the most current SQL database from this page:

$ wget http://seanlahman.com/files/database/lahman2016-sql.zip

3) And then unzip the file:

$ unzip lahman2016-sql.zip

4) Open MySQL

$ gcloud beta sql connect <databasename> --user=root

5) See what databases exist on your MySQL server already.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

6) Create a new database for the Lahman data.

mysql> CREATE DATABASE lahman2016

7) See that your new database is there.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lahman2016         |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

8) Connect to the new database.

mysql> use lahman2016;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

9) Import the Lahman saved SQL database you downloaded. When you use this command, it will import the database and a long list of output will show as it adds the data.

mysql> source lahman2016.sql

10) Examine the database.

mysql> SHOW TABLES;
+----------------------+
| Tables_in_lahman2016 |
+----------------------+
| AllstarFull          |
| Appearances          |
| AwardsManagers       |
| AwardsPlayers        |
| AwardsShareManagers  |
| AwardsSharePlayers   |
| Batting              |
| BattingPost          |
| CollegePlaying       |
| Fielding             |
| FieldingOF           |
| FieldingOFsplit      |
| FieldingPost         |
| HallOfFame           |
| HomeGames            |
| Managers             |
| ManagersHalf         |
| Master               |
| Parks                |
| Pitching             |
| PitchingPost         |
| Salaries             |
| Schools              |
| SeriesPost           |
| Teams                |
| TeamsFranchises      |
| TeamsHalf            |
+----------------------+
27 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM Batting;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| playerID | varchar(255) | YES  |     | NULL    |       |
| yearID   | int(11)      | YES  |     | NULL    |       |
| stint    | int(11)      | YES  |     | NULL    |       |
| teamID   | varchar(255) | YES  |     | NULL    |       |
| lgID     | varchar(255) | YES  |     | NULL    |       |
| G        | int(11)      | YES  |     | NULL    |       |
| AB       | int(11)      | YES  |     | NULL    |       |
| R        | int(11)      | YES  |     | NULL    |       |
| H        | int(11)      | YES  |     | NULL    |       |
| 2B       | int(11)      | YES  |     | NULL    |       |
| 3B       | int(11)      | YES  |     | NULL    |       |
| HR       | int(11)      | YES  |     | NULL    |       |
| RBI      | int(11)      | YES  |     | NULL    |       |
| SB       | int(11)      | YES  |     | NULL    |       |
| CS       | int(11)      | YES  |     | NULL    |       |
| BB       | int(11)      | YES  |     | NULL    |       |
| SO       | int(11)      | YES  |     | NULL    |       |
| IBB      | varchar(255) | YES  |     | NULL    |       |
| HBP      | varchar(255) | YES  |     | NULL    |       |
| SH       | varchar(255) | YES  |     | NULL    |       |
| SF       | varchar(255) | YES  |     | NULL    |       |
| GIDP     | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
22 rows in set (0.01 sec)

11) Dominate your fantasy baseball league.