Novice Level

Working with Data in Google Cloud SQL

30 minutes

7 Learning Objectives

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.
Leave the region and zone options as their defaults.
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=root
When 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.

Additional Resources

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:

Enable necessary APIs.
Create Cloud Storage bucket.
Get remote files and copy to bucket.
Create Cloud SQL instance.
Create MySQL database.
Import schema and data.
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.

Get Started

Who’s going to be learning?

Myself My Organization

How many seats do you need?

$499 USD per seat per year
Billed Annually
Renews in 12 months

Ready to accelerate learning?

For over 25 licenses, a member of our sales team will walk you through a custom tailored solution for your business.

$2,495.00

Checkout

Welcome Back!

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