{"id":5615,"date":"2019-07-01T15:08:18","date_gmt":"2019-07-01T15:08:18","guid":{"rendered":"https:\/\/max-drake.cc\/?p=5615"},"modified":"2019-07-01T15:08:24","modified_gmt":"2019-07-01T15:08:24","slug":"google-cloud-sql-instance-for-data-studio","status":"publish","type":"post","link":"https:\/\/max-drake.cc\/?p=5615","title":{"rendered":"Google Cloud SQL instance for Data Studio"},"content":{"rendered":"\n<p>In the last article I tried linking to my VPS MySQL &amp; PostGres databases and couldn&#8217;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. <\/p>\n\n\n\n<p>So, this time around I&#8217;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<\/p>\n\n\n\n<p>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. <\/p>\n\n\n\n<p>So you&#8217;d think it would be easy to set up a cloud SQL instance, not so.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"instance-name-header\">Setting up and populating Cloud MySQL DB<\/h3>\n\n\n\n<p>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.<\/p>\n\n\n\n<p> 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&#8217;t be imported. Why it just couldn&#8217;t say &#8220;No to the import&#8221; is beyond me. <\/p>\n\n\n\n<p>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. <\/p>\n\n\n\n<p>I found the following video that shows how you use Cloud shell to run on the command line:<\/p>\n\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe title=\"The Quick Start of Google Cloud SQL for MySQL\" width=\"678\" height=\"381\" data-src=\"https:\/\/www.youtube.com\/embed\/E8e8xw1Y4N4?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<p>After fluffing around for a while, especially with the password login, I finally got the cloud shell running and got into the MySQL&gt; command prompt to actually make the table. <\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" width=\"1024\" height=\"591\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042743-1024x591.jpg\" alt=\"\" class=\"wp-image-5616 lazyload\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042743-1024x591.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042743-300x173.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042743-768x443.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042743-100x58.jpg 100w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042743-173x100.jpg 173w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042743-1109x640.jpg 1109w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042743-640x369.jpg 640w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042743-1330x768.jpg 1330w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042743-1871x1080.jpg 1871w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042743-1920x1108.jpg 1920w\" data-sizes=\"(max-width: 1024px) 100vw, 1024px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1024px; --smush-placeholder-aspect-ratio: 1024\/591;\" \/><\/figure>\n\n\n\n<p class=\"has-background has-luminous-vivid-amber-background-color\">This I did using y original dump file below, where it errored out on the last line  <strong>ENGINE=MyISAM DEFAULT CHARSET=latin1; <\/strong> once I took that out it built the table. Hoorah!<\/p>\n\n\n\n<p>CREATE TABLE <code>HousingData<\/code> (<br>   <code>Id<\/code> int(11) NOT NULL,<br>   <code>Property_Code<\/code> varchar(14) NOT NULL,<br>   <code>Property_Name<\/code> varchar(30) NOT NULL,<br>   <code>Property_Level<\/code> int(11) NOT NULL,<br>   <code>Type<\/code> varchar(16) NOT NULL,<br>   <code>Name<\/code> varchar(28) NOT NULL,<br>   <code>Baselife<\/code> int(11) NOT NULL,<br>   <code>BaselifeLower<\/code> int(11) NOT NULL,<br>   <code>BaselifeUpper<\/code> int(11) NOT NULL,<br>   <code>Quantity<\/code> decimal(6,2) NOT NULL,<br>   <code>Unit<\/code> varchar(2) NOT NULL,<br>   <code>Unit_Rate<\/code> int(11) NOT NULL,<br>   <code>Condition_c1<\/code> int(11) DEFAULT NULL,<br>   <code>Remaining_r1<\/code> int(11) DEFAULT NULL,<br>   <code>Condition_c2<\/code> int(11) DEFAULT NULL,<br>   <code>Remaining_r2<\/code> int(11) DEFAULT NULL,<br>   <code>Condition_c3<\/code> int(11) DEFAULT NULL,<br>   <code>Remaining_r3<\/code> int(11) DEFAULT NULL,<br>   <code>Condition_c4<\/code> int(11) DEFAULT NULL,<br>   <code>Remaining_r4<\/code> int(11) DEFAULT NULL,<br>   <code>Condition_c5<\/code> int(11) DEFAULT NULL,<br>   <code>Remaining_r5<\/code> int(11) DEFAULT NULL<br> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;<\/p>\n\n\n\n<p>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. <\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Connect instance to Data Studio<\/h3>\n\n\n\n<p>So, back to Data Studio and input all the information it asks for to connect to the DB:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" width=\"1024\" height=\"497\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042807-1024x497.jpg\" alt=\"\" class=\"wp-image-5617 lazyload\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042807-1024x497.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042807-300x146.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042807-768x372.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042807-100x49.jpg 100w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042807-206x100.jpg 206w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042807-640x310.jpg 640w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042807.jpg 1268w\" data-sizes=\"(max-width: 1024px) 100vw, 1024px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1024px; --smush-placeholder-aspect-ratio: 1024\/497;\" \/><\/figure>\n\n\n\n<p>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).<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" width=\"870\" height=\"1024\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042909-870x1024.jpg\" alt=\"\" class=\"wp-image-5618 lazyload\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042909-870x1024.jpg 870w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042909-255x300.jpg 255w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042909-768x904.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042909-100x118.jpg 100w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042909-85x100.jpg 85w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042909-544x640.jpg 544w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042909-640x754.jpg 640w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042909-652x768.jpg 652w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042909-1024x1206.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042909-917x1080.jpg 917w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/06\/00042909.jpg 1183w\" data-sizes=\"(max-width: 870px) 100vw, 870px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 870px; --smush-placeholder-aspect-ratio: 870\/1024;\" \/><\/figure>\n\n\n\n<p>So, at the end, it worked fine. Then I got a bill, not sure if I&#8217;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&#8217;t think it was that much data but computers never lie(?). <\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" width=\"1024\" height=\"331\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/07\/00043017-1024x331.jpg\" alt=\"\" class=\"wp-image-5620 lazyload\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/07\/00043017-1024x331.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/07\/00043017-300x97.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/07\/00043017-768x248.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/07\/00043017-100x32.jpg 100w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/07\/00043017-309x100.jpg 309w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/07\/00043017-640x207.jpg 640w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/07\/00043017.jpg 1601w\" data-sizes=\"(max-width: 1024px) 100vw, 1024px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1024px; --smush-placeholder-aspect-ratio: 1024\/331;\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">End comment<\/h3>\n\n\n\n<p>So, using Google services the connections work fine, but using your own VPS connection to a MySQL DB is tough (no success to date). <\/p>\n\n\n\n<p>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&#8217;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. <\/p>\n\n\n\n<p>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 &amp; 1,600$\/year.  <\/p>\n\n\n\n<p>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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the last article I tried linking to my VPS MySQL &amp; PostGres databases and couldn&#8217;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&#8217;m going to try and use [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":5620,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36,265,29],"tags":[],"class_list":["post-5615","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-google-docs","category-web"],"featured_image_src":"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/07\/00043017.jpg","featured_image_src_square":"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/07\/00043017.jpg","author_info":{"display_name":"Max Drake","author_link":"https:\/\/max-drake.cc\/?author=1"},"_links":{"self":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/5615","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5615"}],"version-history":[{"count":0,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/5615\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/media\/5620"}],"wp:attachment":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5615"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5615"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5615"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}