SQL databases are a foundation of computing in general, and cloud computing is no exception. Cloud SQL is a particularly robust implementation of a relational database service with rapid scalability, high availability, and secure replication. In this hands-on lab, you’ll see how to set up a Cloud SQL instance, add a MySQL database, and import the schema and data of a large dataset. Once it’s set up, you’ll run through several sample queries.
Learning Objectives
Successfully complete this lab by achieving the following learning objectives:
- Enable APIs.
- From the main Google Cloud console navigation, choose APIs & Services > Library.
- Search for Cloud SQL and enable the service, if necessary.
- Return to the APIs Library page and search for Cloud Storage and enable it as well, if necessary.
- Create Cloud Storage bucket.
- From the Google Cloud console main navigation, choose Cloud Storage.
- Click Create bucket.
- Name the bucket uniquely and click Continue
- In the Location Type section, select Region and click Continue.
- Click Create.
- Clone a GitHub repository and copy to bucket.
- Activate the Cloud Shell.
- From the Cloud Shell, issue the following command to clone the repository for this course:
git clone https://github.com/linuxacademy/content-gcpro-developer - Copy the necessary files to the Cloud Storage bucket:
cd content-gcpro-developer/sql-lab
gsutil cp * gs://[BUCKET_NAME]
- Create a Cloud SQL instance.
- From the main console navigation, choose Cloud SQL.
- Click Create Instance.
- Select Choose MySQL.
- Set the instance ID to la-met.
- Set the password to root.
- Under Choose region and zonal availability select a single zone deployment (as we don’t need high availability for this lab). and with a shared core machine type and 10GB storage.
- Click Create.
- Create database, table, and import schema.
- From the Cloud SQL dashboard, click the la-met entry.
- Select the Databases tab.
- Choose Create database.
- Name the database met_museum.
- Leave the other settings as their defaults and click Create.
- Choose Import.
- Locate the bucket containing the uploaded files by clicking Browse.
- Choose MetObjects_Table.sql and click Select.
- Make sure the Format of import is set to SQL.
- From the Database list, choose met_museum.
- Click Import.
- Import data.
- Choose Import.
- Locate the bucket containing the uploaded files by clicking Browse.
- Choose MetObjects_subset.csv and click Select.
- Make sure the Format of import is set to CSV.
- From the Database list, choose met_museum.
- In the Table field, enter MetObjects.
- Click Import.
- Query database.
In the Cloud Shell, connect to the database instance with the following command:
gcloud sql connect la-met –user=rootWhen prompted, enter the password: root.
Declare the database to use:
use met_museum;Enter the following queries:
SELECT Title, Medium FROM MetObjects LIMIT 20;
SELECT Title, Medium, Link_Resource FROM MetObjects WHERE Object_Begin_Date >= ‘2000’ LIMIT 20;Click any link returned to view Met object.
