MySQL / Google Cloud Platform Setup
In this run-through, I explore the setup of Google Cloud Platform (using MySQL), the Python code required to inject data into your MySQL DB and the visualization of this data in Google Data Studio
Creating a Cloud Database:
- You need a Google account for this...
- Go to Google Cloud Platform and click "Try it Free" - you get $300 to play with for 60 days (as of Feb 2017) but you still need to assign a payment method to it
- To the right of "Google Cloud Platform" you should see a small drop down, click it and choose "Create Project" and name it (NOTE: this is NOT the server name, it's the project name, so if you're going to add a few things under the same umbrella, such as "Performance", "Tests" etc, then name it accordingly here - I called mine "DatabaseTesting")
- It'll think for a bit....(~30 seconds)
- When it loads, choose "Create a Cloud SQL Instance"
- Click "Choose Second Generation" (this may be removed in the future and become the default)
- Give it a name. I called mine "thetechnicalartist" - I left everything else at the default
- It'll take a while for the instance to be created.....
- Click on the name of the instance to open the settings
- Click on "Databases" and then on "New database"
- I named mine "newstracker"
- Click on "Access Control" and create a new user that isn't you (i made mine without a password as this data isn't important)
- Download and install "MySQL Workbench" (choose custom when you install so you ONLY install the Workbench)
- Click the + next to "MySQL Connections"
- Connection Name: A name just for you in the control panel
- Hostname: the IP address of your instance
- Port: 3306
- Username: the user you added a moment ago in step 12
- Click "Test Connection"
- If it fails:
- This is probably an access control thing
- Click on your instance, then "Access Control", then "Add Network"
- Go to http://ipv4.whatismyv6.com and find you IP address. Enter this in as the new network, and click "Save" and wait a moment
- Now test the connection in MySQL Workbench again
- In MySQL Workbench, click on the icon for your new connection at the top to open the editor
- On the left panel, expand the database name under "Schema", right click on "Tables" and choose "Create Table"
- Give it a name, and then add the columns we described earlier:
- Click "Apply" and execute the construction query
- The database is now setup and ready to accept data
Using Python to Connect to the Database
Now we have our database up and running in the cloud, let's try adding some test data to it via Python. We're making the assumption here that you're all set up with Python and an IDE
Cleaning Up The Code:
So far we've covered everything, but we need to wrap things up... so let's see what we can do
- Add several more data sources (news site URLs)
- Add a "nice name" for each URL for the purposes of presentation, as well as country names
- Entry for each keyword
- Wrap the injection with the data existence check
- Comment everything
The script is set up to run on a scheduled task at 9AM every day.
So let's check back in a couple of weeks when we have a data set, and we can look at the visualization....
For this, we'll be using the fairly new (still in Beta) but free Google Data Studio.
- Go to Google Data Studio and click on "Data Sources" on the left
- Click the blue "+" in the bottom right
- Choose "Cloud SQL" from the list
- It may ask to authorize, do this with your Google login
- Enter your connection details and click "Authorize"
- For me, at this stage, I had "internal error", which suggested it couldn't connect. So I had to perform the following steps:
- Head over to your Google Cloud Platform and go to your database
- Click on "Access Control" and add the IP list from GDS Help to your white-listed networks
- Click Save and wait for it to finish
- After it connects, you should see your tables listed on the right
- Click on "Connect" at the top right
- Click "Create Report", then choose "Add t Report" on the popup (may need to authenticate again)
- Now you have your blank report. Click on "Untitled Report" to give it a name