In the last article I tried linking to my VPS MySQL & PostGres databases and couldn’t. On a test DB site I could connect but to maintain the free DB I needed to log in each week, so I decided not to proceed with that.
So, this time around I’m going to try and use Google Cloud as the MySQL database. For the first year you have $300 to play with their services , see https://cloud.google.com/free/, for other pricing see: https://cloud.google.com/sql/pricing
So I only have the free service for a year, but its worth testing out and the instance costs are not that great for a commercial operation. The free tier is fine for testing.
So you’d think it would be easy to set up a cloud SQL instance, not so.
Setting up and populating Cloud MySQL DB
I have an account so I logged on and created a SQL instance, located in Australia. That took a while to build. OK, its a one off.
Then I had a SQL dump from my VPS MySQL DB. You cannot load it directly to the SQL instance. You have to create a storage instance and load it from there. So I created that and uploaded the file. Then you import the data to the SQL instance from the storage instance. But I got an error on that. I was asked for SuperUser authorisation, and after hunting around for that found that what that meant was that the data was in the wrong structure and wouln’t be imported. Why it just couldn’t say “No to the import” is beyond me.
So I looked for a CSV file of the data and uploaded that to the Storage and then tried to import that. No, far too easy. It would not make the table, you had to create that yourself in the database.
I found the following video that shows how you use Cloud shell to run on the command line:
After fluffing around for a while, especially with the password login, I finally got the cloud shell running and got into the MySQL> command prompt to actually make the table.
This I did using y original dump file below, where it errored out on the last line ENGINE=MyISAM DEFAULT CHARSET=latin1; once I took that out it built the table. Hoorah!
CREATE TABLE HousingData ( Id int(11) NOT NULL, Property_Code varchar(14) NOT NULL, Property_Name varchar(30) NOT NULL, Property_Level int(11) NOT NULL, Type varchar(16) NOT NULL, Name varchar(28) NOT NULL, Baselife int(11) NOT NULL, BaselifeLower int(11) NOT NULL, BaselifeUpper int(11) NOT NULL, Quantity decimal(6,2) NOT NULL, Unit varchar(2) NOT NULL, Unit_Rate int(11) NOT NULL, Condition_c1 int(11) DEFAULT NULL, Remaining_r1 int(11) DEFAULT NULL, Condition_c2 int(11) DEFAULT NULL, Remaining_r2 int(11) DEFAULT NULL, Condition_c3 int(11) DEFAULT NULL, Remaining_r3 int(11) DEFAULT NULL, Condition_c4 int(11) DEFAULT NULL, Remaining_r4 int(11) DEFAULT NULL, Condition_c5 int(11) DEFAULT NULL, Remaining_r5 int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I could then load in the CSV file data (from the Storage instance) via the import button from the SQL instance. So I now have my data Table in a MySQL DB in Google Cloud.
Connect instance to Data Studio
So, back to Data Studio and input all the information it asks for to connect to the DB:
and it pulls it in with no fuss, it also loads pretty fast too (I suppose all the waiting around setting up the instance is equivalent to slow load of a Google Sheet).
So, at the end, it worked fine. Then I got a bill, not sure if I’m on free service or not so i blew the SQL database away as well as the storage instance. It seemed a bit high for 8 hours of the database, although I did make 3 tables so it says 1.4Gb for Server. I didn’t think it was that much data but computers never lie(?).
So, using Google services the connections work fine, but using your own VPS connection to a MySQL DB is tough (no success to date).
Cloud services, once you get the logic and process do a good job, but a bit expensive. So a good exercise to test, so I know it works, but I wouldn’t use it personally. I think the AWS services are far cheaper than this. I had a VPS E2 instance running and the billing was about the same and I had lots more things happening there. So thank you Google for allowing me to play with it but no thank you, too expensive.
I sometimes go to Dev Ops meetups and they talk about AWS services and Azure but no-one mentions Google Cloud. Apparently not too popular in NZ. I can see why with an 8 hour use of a MySQL database costing $1.47. That adds up to over 30$ /week and 125$/month & 1,600$/year.
This seems such a contrast. Google have such fabulous free stuff Gmail, Docs, Calendar, Drive, Data Studio and then overdo costs in other areas, such as Cloud. I think they were a bit heavy handed when they changed their Maps API platform that drove a lot of people to other map platforms too.