Working with Data in Google Cloud SQL

Hands-On Lab

 

Photo of Joseph Lowery

Joseph Lowery

Google Cloud Training Architect II in Content

Length

00:30:00

Difficulty

Beginner

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.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Working with Data in Google Cloud SQL

In this hands-on lab, we’ll 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, we’ll run through several sample queries.

How to Log in to Google Lab Accounts

On the lab page, right-click Open GCP Console and select the option to open it in a new private browser window (this option will read differently depending on your browser — e.g., in Chrome, it says "Open Link in Incognito Window"). Then, sign in to Google Cloud Platform using the credentials provided on the lab page.

On the Welcome to your new account screen, review the text, and click Accept. In the "Welcome L.A.!" pop-up once you're signed in, check to agree to the terms of service, choose your country of residence, and click Agree and Continue.

Working with Data in Google Cloud SQL

Now, on to the lab!

Enable APIs.

  1. Navigate to APIs & Libraries > Libraries.
  2. Search for "Cloud SQL".
  3. Select the Cloud SQL card.
  4. Click Enable.
  5. Head back to APIs & Libraries > Libraries.
  6. Search for "Cloud Storage".
  7. Select the Cloud Storage card.
  8. We should see it's already enabled. (If it isn't, click Enable.)

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. Click the icon in the upper right navigation menu to Activate 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. Navigate to SQL.
  2. Click Create instance.
  3. Select Choose MySQL.
  4. Set Instance ID to met-museum.
  5. Set Root password to root.
  6. Leave the region and zone options as their defaults.
  7. Click Create.

Create database, table, and import schema.

  1. From the SQL dashboard, click the la-met entry.
  2. Select the Databases tab.
  3. Click Create database.
  4. Name the database met_museum.
  5. Leave the other settings as their defaults, and click Create.
  6. At the top, click 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. Click 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 query:

    SELECT Title, Medium FROM MetObjects LIMIT 20;
  5. Then, enter this query:

    SELECT Title, Medium, Link_Resource FROM MetObjects WHERE Object_Begin_Date >= '2000' LIMIT 20;Date >= '1800' LIMIT 20;
  6. Click any link returned to view a Met object.

Conclusion

Congratulations on completing this lab!