Novice Level

Working with Data in Google Cloud SQL

Pricing
30 minutes
  • 7 Learning Objectives

About this Hands-on Lab

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.
  1. From the main Google Cloud console navigation, choose APIs & Services > Library.
  2. Search for Cloud SQL and enable the service, if necessary.
  3. Return to the APIs Library page and search for Cloud Storage and enable it as well, if necessary.
Create Cloud Storage bucket.
  1. From the Google Cloud console main navigation, choose Cloud Storage.
  2. Click Create bucket.
  3. Name the bucket uniquely and click Continue
  4. In the Location Type section, select Region and click Continue.
  5. Click Create.
Clone a GitHub repository and copy to bucket.
  1. Activate the Cloud Shell.
  2. From the Cloud Shell, issue the following command to clone the repository for this course:
    git clone https://github.com/linuxacademy/content-gcpro-developer
  3. 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.
  1. From the main console navigation, choose Cloud SQL.
  2. Click Create Instance.
  3. Select Choose MySQL.
  4. Set the instance ID to la-met.
  5. Set the password to root.
  6. 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.
  7. Click Create.
Create database, table, and import schema.
  1. From the Cloud SQL dashboard, click the la-met entry.
  2. Select the Databases tab.
  3. Choose Create database.
  4. Name the database met_museum.
  5. Leave the other settings as their defaults and click Create.
  6. Choose Import.
  7. Locate the bucket containing the uploaded files by clicking Browse.
  8. Choose MetObjects_Table.sql and click Select.
  9. Make sure the Format of import is set to SQL.
  10. From the Database list, choose met_museum.
  11. Click Import.
Import data.
  1. Choose Import.
  2. Locate the bucket containing the uploaded files by clicking Browse.
  3. Choose MetObjects_subset.csv and click Select.
  4. Make sure the Format of import is set to CSV.
  5. From the Database list, choose met_museum.
  6. In the Table field, enter MetObjects.
  7. Click Import.
Query database.

  1. In the Cloud Shell, connect to the database instance with the following command:
    gcloud sql connect la-met –user=root

  2. When prompted, enter the password: root.

  3. Declare the database to use:
    use met_museum;

  4. 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;

  5. Click any link returned to view Met object.

Contact SalesSee Pricing

Additional Resources

Note: To avoid the lab shutting down due to changes in GCP's default settings: When creating a MySQL Instance:

  • Select a single zone deployment (we don't need high availability for this lab)
  • In Customize your instance, choose Shared Core
  • Coose 10 GB for the storage capacity

The team has come to you with a big project: Set up a Cloud SQL instance to handle a MySQL database of over 600,000 items. After setting up the instance and the database, you’ll need to bring the relevant files into a Cloud Storage bucket so you can import the schema and populate the database. Once that has been accomplished, they want you to run a couple of sample queries to confirm that all is working properly.

To accomplish this task, you’ll need to complete the following steps:

  1. Enable necessary APIs.
  2. Create Cloud Storage bucket.
  3. Get remote files and copy to bucket.
  4. Create Cloud SQL instance.
  5. Create MySQL database.
  6. Import schema and data.
  7. Run test queries.

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Don't have an account?

Get Started
Who’s going to be learning?
MyselfMy Organization