GSP423

Overview

The Google APIs Explorer is a tool that helps you explore various Google APIs interactively. With the APIs Explorer, you can:

• Browse quickly through available APIs and versions.
• See methods available for each API and what parameters they support along with inline documentation.
• Execute requests for any method and see responses in real time.
• Make authenticated and authorized API calls.
• Search across all services, methods, and your recent requests to quickly find what you are looking for.

Cloud SQL is a fully-managed database service that makes it easy to set up, maintain, manage, and administer your relational PostgreSQL and MySQL databases in the cloud. Cloud SQL offers high performance, scalability, and convenience. Hosted on Google Cloud, Cloud SQL provides a database infrastructure for applications running anywhere.

In this lab, you will build a Cloud SQL instance and attach a MySQL database to it. After creating a sample CSV file and uploading it to Cloud Storage, you will create a table and pull the file’s contents into your MySQL database. Finally, you will remove the database and its associated data. You will accomplish these tasks with the Cloud SQL Admin API, where you will call various methods through the APIs Explorer tool.

Objectives

In this lab, you will use the APIs Explorer tool to:

• Build a Cloud SQL instance.
• Configure a MySQL SQL database and attach it to a Cloud SQL instance.
• Pull CSV data from a Cloud Storage bucket and upload it to a MySQL database table.
• Delete a MySQL database.

Prerequisites

This is a fundamental level lab. You should be familiar with the basic functioning and architecture of APIs. Experience with Google Cloud Shell and command line interface tools is recommended.

Familiarity with the APIs Explorer tool is also recommended. At a minimum, take the following labs before attempting this one:

• Introduction to APIs in Google
• APIs Explorer: Qwik Start

If you are unfamiliar with Cloud SQL, the labs Cloud SQL for MySQL: Qwik Start and Introduction to SQL for BigQuery and Cloud SQL have valuable information that will orient you with the content of this lab. Once you're ready, scroll down and follow the steps below to get your lab environment set up.

Setup and requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.

This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).

Note: Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.

• Time to complete the lab---remember, once you start, you cannot pause a lab.

Note: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your account.

How to start your lab and sign in to the Google Cloud console

1. Click the Start Lab button. If you need to pay for the lab, a pop-up opens for you to select your payment method. On the left is the Lab Details panel with the following:

   • The Open Google Cloud console button
   • Time remaining
   • The temporary credentials that you must use for this lab
   • Other information, if needed, to step through this lab

2. Click Open Google Cloud console (or right-click and select Open Link in Incognito Window if you are running the Chrome browser).

   The lab spins up resources, and then opens another tab that shows the Sign in page.

   Tip: Arrange the tabs in separate windows, side-by-side.

   Note: If you see the Choose an account dialog, click Use Another Account.

3. If necessary, copy the Username below and paste it into the Sign in dialog.

   {{{user_0.username | "Username"}}}

   You can also find the Username in the Lab Details panel.

4. Click Next.

5. Copy the Password below and paste it into the Welcome dialog.

   {{{user_0.password | "Password"}}}

   You can also find the Password in the Lab Details panel.

6. Click Next.

   Important: You must use the credentials the lab provides you. Do not use your Google Cloud account credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.

7. Click through the subsequent pages:

   • Accept the terms and conditions.
   • Do not add recovery options or two-factor authentication (because this is a temporary account).
   • Do not sign up for free trials.

After a few moments, the Google Cloud console opens in this tab.

Note: To view a menu with a list of Google Cloud products and services, click the Navigation menu at the top-left.

Task 1. Build a Cloud SQL instance with instances.insert

You will now build a Cloud SQL instance with one of the methods found in the APIs Explorer.

1. To access the Cloud SQL APIs Explorer tool, open up the navigation menu and select APIs & Services > Library.

2. In the search bar type Cloud SQL and select the Cloud SQL Admin API from the results list.

3. Make sure that API is enabled, if not click Enable.

4. Now that you have verified the API's enablement, open the Cloud SQL Admin API reference page.

5. Under the Reference tab, navigate to All APIs and reference > REST Reference > v1beta4 > instances > insert, to select sql.instances.insert method or, to create an SQL instance resource, use the Method: instances.insert reference page.

You'll now fill out a form to use the sql.instances.insert method.

The Request body contains the resource properties that you want to use for creating your MySql instance:

1. Set the project: = your Qwiklabs Project ID.

2. Then click the blue plus sign icon inside of the curly braces in the Request body field. You'll be asked to select a property - choose "name".

3. Next to it give name as my-instance

• "name": "my-instance"

This will generate the next level.

4. Click inside the brackets and click the blue plus sign icon, select - "settings".

5. Inside "Settings" click inside the curly brace, click the blue plus icon and select "tier"; next to it type "db-n1-standard-1"

• settings:
  • tier: db-n1-standard-1

Your method should resemble the following:

6. Make sure that Google OAuth 2.0 checkbox is selected under Credentials section.

Note: To view Credentials FAQs, click on the question mark icon next to Credentials title.

7. Make sure that there are no trailing spaces in any of the fields.

8. Now scroll down and click the Execute button.

9. When prompted to select an account, click on your Qwiklabs Google account.

10. On the next screen, click Allow to give APIs Explorer access.

You can see the Request that was sent to your project as code, built from the input you provided in the form.

Your response should resemble the following:

{ "kind": "sql#operation", "targetLink": "https://content.googleapis.com/sql/v1beta4/projects/qwiklabs-gcp-a8af8f7d195267dd/instances/my-instance", "status": "PENDING", "user": "gcpstaging97434_student@qwiklabs.net", "insertTime": "2019-11-04T11:22:49.393Z", "operationType": "CREATE", "name": "563bc142-1adf-40f7-a9de-eefe3894ece2", "targetId": "my-instance", "selfLink": "https://content.googleapis.com/sql/v1beta4/projects/qwiklabs-gcp-a8af8f7d195267dd/operations/563bc142-1adf-40f7-a9de-eefe3894ece2", "targetProject": "qwiklabs-gcp-a8af8f7d195267dd" }

You have successfully created a MySQL instance for a Google Cloud project. Now view it in the Cloud Console.

View your Cloud SQL instance

1. Return to the Cloud Console for this step.

2. From the Navigation menu, select SQL, which is located under the Databases header. This will bring you to the instances page, where you will see your MySQL instance being created.

It will take a few minutes for your instance to be created. You will know that your instance has been created when my-instance resembles the following:

You may have to refresh your page from time to time to see changes.

3. Return to the APIs Explorer page once your instance has been created and you're ready for the next step.

Test completed task

Click Check my progress to verify your performed task. If you have completed the task successfully you will be granted an assessment score.

Build a Cloud SQL instance with API Explorer

Task 2. Create a database with databases.insert

Now that you have a Cloud SQL instance created, add a database to it with the databases.insert method.

1. Under the Reference tab, navigate to All APIs and reference > REST Reference > v1beta4 > databases > insert to select sql.databases.insert method or, to create database, use the Method: databases.insert reference page.

You'll now fill out a form to use the sql.databases.insert method.

2. The Request body contains the resource properties that you want to use for creating the database:

• project: = your Lab Project ID

• instance: = my-instance.

3. Then in the Request body:

Request body = Click inside the brackets to select the following properties:

• instance: my-instance
• name: mysql-db
• project: your Qwiklabs Project ID.

Your method should resemble the following:

Make sure that Google OAuth 2.0 checkbox is selected under Credentials section.

Note: To view Credentials FAQs, click on the question mark icon next to the Credentials title.

4. Make sure that there are no trailing spaces in any of the fields.

5. Now scroll down and click the Execute button.

6. When prompted to select an account, click on your Qwiklabs Google account.

You can see the Request that was sent to your project as code, built from the input you provided in the form.

Your response should resemble the following:

200 - Show headers - { "kind": "sql#operation", "targetLink": "https://content.googleapis.com/sql/v1beta4/projects/qwiklabs-gcp-a8af8f7d195267dd/instances/my-instance/databases/mysql-db", "status": "DONE", "user": "gcpstaging97434_student@qwiklabs.net", "insertTime": "2019-11-04T12:24:33.776Z", "startTime": "2019-11-04T12:24:33.786Z", "endTime": "2019-11-04T12:24:34.419Z", "operationType": "CREATE_DATABASE", "name": "dc0cb7f7-340c-4c9a-b9d8-3afc4e8680a0", "targetId": "my-instance", "selfLink": "https://content.googleapis.com/sql/v1beta4/projects/qwiklabs-gcp-a8af8f7d195267dd/operations/dc0cb7f7-340c-4c9a-b9d8-3afc4e8680a0", "targetProject": "qwiklabs-gcp-a8af8f7d195267dd" }

You have successfully created a database inside your Cloud SQL instance. You are now ready to add some data to your database. Before doing so, view your database in the Cloud Console.

View your new database

1. Return to the Cloud Console for this step.

2. From the Navigation menu select SQL, which is located under the Databases header. This will bring you to the instances page.

3. Click on my-instance.

4. Then select the databases tab.

You will see a list of databases, with your mysql-db added:

The ordering of databases in the above screenshot may be different from what you see in your Console. Make sure you see mysql-db in the list.

Test completed task

Click Check my progress to verify your performed task. If you have completed the task successfully you you will be granted an assessment score score.

Create a database in your Cloud SQL instance

Task 3. Create a table in your MySQL database and upload a CSV file to a Cloud Storage bucket

Now you will create a table in your MySQL database and a "comma separated values" (CSV) file, which will be uploaded to Cloud Storage.

1. From the Cloud Console, open up a new Cloud Shell session.

2. Run the following command to connect to your MySQL instance:

gcloud sql connect my-instance --user=root

It may take a minute or two to connect to your instance.

3. Enter through the password prompt since you didn't set one. You should now be logged into your instance:

Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 494 Server version: 5.7.14-google-log (Google) Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]>

4. Run the following command to switch over to your mysql-db database:

USE mysql-db;

You will now create a table in mysql-db so you can import data into a specific location.

5. Run the following command to create a table called info:

CREATE TABLE info (name VARCHAR(255), age INT, occupation VARCHAR(255));

You should receive a similar output:

Query OK, 0 rows affected (0.06 sec)

You have successfully created an empty table in your MySQL database.

Do not exit out of this Cloud Shell session.

6. Open a new tab in Cloud Shell by clicking the + tab in the top ribbon.

You will now create a CSV file and upload it to a Cloud Storage bucket.

7. Run the following command to create a file named employee_info.csv:

touch employee_info.csv

8. Now run the following command to edit employee_info.csv:

nano employee_info.csv

9. Copy and paste the following into the file:

"Sean", 23, "Content Creator" "Emily", 34, "Cloud Engineer" "Rocky", 40, "Event coordinator" "Kate", 28, "Data Analyst" "Juan", 51, "Program Manager" "Jennifer", 32, "Web Developer"

10. Hold CTRL+X → Y → ENTER to save the file. You now have a simple CSV file that's ready to be uploaded to Cloud Storage.

11. Run the following command to create a new Cloud Storage bucket, replacing <YOUR_BUCKET_NAME> with a unique bucket name that follows the Cloud Storage naming guidelines:

gsutil mb gs://<YOUR_BUCKET_NAME>

Test completed task

Click Check my progress to verify your performed task. If you have completed the task successfully you you will be granted an assessment score score.

Create a Cloud Storage bucket

12. Run the the following command to upload the CSV file to your Cloud Storage bucket, replacing <YOUR_BUCKET_NAME> with the name of your bucket:

gsutil cp employee_info.csv gs://<YOUR_BUCKET_NAME>/

Test completed task

Click Check my progress to verify your performed task. If you have completed the task successfully you you will be granted an assessment score score.

Upload the CSV file to your Cloud Storage bucket

To upload this file to your MySQL database, update specific permissions with your Cloud SQL service account.

1. From the navigation menu, select SQL and then click on my-instance.

2. From the overview page, scroll down and find the "Service account" card and copy the service account name.

3. Now open the navigation menu and select Storage > Cloud Storage > Buckets.

4. Click on the three-dotted menu on the far right side of the bucket and click Edit access.

5. For the principal field, Click + Add principal.

6. Now paste the Cloud SQL service account name you copied earlier in the New principals.

7. Click the roles drop down and select Cloud Storage > Storage Admin.

Your permissions sidebar should resemble the following:

8. Click SAVE. Your Cloud Storage bucket can now accept API requests from your Cloud SQL instance.

You will now copy the CSV file into your Cloud SQL database.

Test Completed Task

Click Check my progress to verify your performed task. If you have completed the task successfully you you will be granted an assessment score score.

Please add Storage Admin role to Cloud SQL service account

Task 4. Add a CSV file to your database using instances.import

1. Under Reference tab, navigate to All APIs and reference > REST Reference > v1beta4 > instances > import to select sql.instances.import method or, to import data into database, use the Method: instances.import reference page.

2. You'll now fill out a form to use the sql.instances.import method. The Request body contains the resource properties that you want to use for importing the data:

• project: = your Qwiklabs Project ID

• instance: = my-instance

3. Request body = Click inside the brackets to select the following properties:

• importContext:
  • database: mysql-db
  • uri: gs://<YOUR_BUCKET_NAME>/employee_info.csv, replacing <YOUR_BUCKET_NAME> with the name of your bucket
  • fileType: CSV
  • csvImportOptions:
    • table: info

Your method should now resemble the following:

4. Make sure that Google OAuth 2.0 checkbox is selected under Credentials section.

Note: To view Credentials FAQs, click on the question mark icon next to the Credentials title.

5. Make sure that there are no trailing spaces in any of the fields.

6. Now scroll down and click the Execute button.

7. When prompted to select an account, click on your Qwiklabs Google account.

You can see the Request that was sent to your project as code, built from the input you provided in the form.

Your response should resemble the following:

200 - Show headers - { "kind": "sql#operation", "targetLink": "https://content.googleapis.com/sql/v1beta4/projects/qwiklabs-gcp-a8af8f7d195267dd/instances/my-instance", "status": "PENDING", "user": "gcpstaging97434_student@qwiklabs.net", "insertTime": "2019-11-04T13:50:07.537Z", "operationType": "IMPORT", "importContext": { "uri": "gs://qwiklabs-gcp-a8af8f7d195267dd/employee_info.csv", "database": "mysql-db", "kind": "sql#importContext", "fileType": "CSV", "csvImportOptions": { "table": "info" } }, "name": "af3aaeb1-4f68-498c-bec5-d030201baf33", "targetId": "my-instance", "selfLink": "https://content.googleapis.com/sql/v1beta4/projects/qwiklabs-gcp-a8af8f7d195267dd/operations/af3aaeb1-4f68-498c-bec5-d030201baf33", "targetProject": "qwiklabs-gcp-a8af8f7d195267dd" }

You have successfully added a CSV file to your MySQL database table using the APIs Explorer. You will now inspect this table.

Task 5. Inspect your updated database

1. Return to the Cloud Console and return to your MySQL Cloud Shell tab that you left open. You will now see if the info table picked up the CSV file data.

2. Run the following command to view what's in the table:

SELECT * FROM info;

You should receive a similar output:

+----------+------+----------------------+ | name | age | occupation | +----------+------+----------------------+ | Sean | 23 | "Content Creator" | | Emily | 34 | "Cloud Engineer" | | Rocky | 40 | "Event coordinator" | | Kate | 28 | "Data Analyst" | | Juan | 51 | "Program Manager" | | Jennifer | 32 | "Web Developer" | +----------+------+----------------------+ 6 rows in set (0.04 sec)

With one API call in the Explorer tool, you were able to copy a CSV file located in Cloud Storage and merge it with a table in your MySQL database. This illustrates the power and efficiency of API method calls opposed to the multiple steps it would take in the Cloud Console.

Task 6. Delete your database

You will now delete the MySQL database that hosts the employee information.

1. Under the Reference tab, navigate to All APIs and reference > REST Reference > v1beta4 > databases > delete to select sql.databases.delete method or, to delete the database, use the Method: databases.delete reference page.

2. You'll now fill out a form to use the sql.databases.delete method:

• project: = your Qwiklabs Project ID

• instance: = my-instance

• database: = mysql-db

Your method should resemble the following:

3. Make sure that the Google OAuth 2.0 checkbox is selected under the Credentials section.

Note: To view Credentials FAQs, click on the question mark icon next to the Credentials title.

4. Make sure that there are no trailing spaces in any of the fields.

5. Now scroll down and click the Execute button.

6. When prompted to select an account, click on your Qwiklabs Google account.

You can see the Request that was sent to your project as code, built from the input you provided in the form.

Your response should resemble the following:

200 - Show headers - { "kind": "sql#operation", "targetLink": "https://content.googleapis.com/sql/v1beta4/projects/qwiklabs-gcp-a8af8f7d195267dd/instances/my-instance", "status": "DONE", "user": "gcpstaging97434_student@qwiklabs.net", "insertTime": "2019-11-04T14:17:09.072Z", "startTime": "2019-11-04T14:17:09.072Z", "endTime": "2019-11-04T14:17:10.319Z", "operationType": "DELETE_DATABASE", "name": "e8abc1b8-45b4-432c-82c0-436d38ca5879", "targetId": "my-instance", "selfLink": "https://content.googleapis.com/sql/v1beta4/projects/qwiklabs-gcp-a8af8f7d195267dd/operations/e8abc1b8-45b4-432c-82c0-436d38ca5879", "targetProject": "qwiklabs-gcp-a8af8f7d195267dd" }

You have successfully deleted a database inside your Cloud SQL instance. Return to the Cloud Console for the following step.

View your updated Cloud SQL instance

1. From the Cloud Console, from the Navigation menu select SQL, which is located under the Databases header. This will bring you to the instances page.

2. Click on my-instance, then click on the databases tab. You will see a list of databases with mysql-db removed.

This concludes the hands-on practice in this lab. You can end your lab here or with the remaining time explore other Cloud SQL Admin API methods in the APIs Explorer.

Task 7. Test your understanding

Below are multiple choice questions to reinforce your understanding of this lab's concepts. Answer them to the best of your abilities.

Congratulations!

In this lab, you got hands-on practice with Cloud SQL Admin API methods using the APIs Explorer. After building a Cloud SQL instance, you configured and attached a MySQL database. You then created a simple CSV file and uploaded it to Cloud Storage, which was pushed to a MySQL database. Finally, you learned how to delete a database in a Cloud SQL instance. You are now ready to take more Exploring APIs labs.

Finish your quest

This self-paced lab is part of the Exploring APIs quest. A quest is a series of related labs that form a learning path. Completing this quest earns you a badge to recognize your achievement. You can make your badge or badges public and link to them in your online resume or social media account. Refer to the Google Cloud Skills Boost catalog for all available quests.

Next steps / Learn more

Be sure to check out the following labs for more practice with the APIs Explorer:

• APIs Explorer: App Engine
• Google Cloud Speech API: Qwik Start

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated October 13, 2023

Lab Last Tested October 13, 2023

Copyright 2024 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.