This lab walks you through GCP Cloud Function, Storage Bucket, and Cloud SQL
Make sure, you have completed the previous labs about GCP Cloud Function, Storage Bucket, and Cloud SQL because here we will not go into detail about theory but we will connect these three services. If you have not completed the previous lab, we recommend you complete the previous lab first.
In this lab, we will create a bucket and upload a sample CSV file into the bucket ( We will provide the file)
We will then create a Cloud SQL instance and database where the table will be created and records will be inserted using Cloud Function.
We will then create a Cloud Function which will connect with Cloud SQL and Storage Bucket.
Duration: 60 minutes
In this lab, we will create a Google Cloud Storage Bucket where we will upload a Sample CSV containing user details, using Cloud Function we will first read the GCS Object first and then using the same Function we will connect with Cloud SQL and create a table and insert the data into Database.
Login into GCP Console.
Creating a Cloud Storage Bucket.
Creating a Cloud SQL Instance.
Create a SQL Database.
Creating a Cloud Function, deploy, and test.
Using Cloud Shell, connect to DB Instance to check whether records are inserted successfully or not.
Launch the lab environment by clicking on . This will create a GCP environment with the resources and roles required for this lab.
Once the lab environment is ready, will be active. Click on , this will open Google Sign-In page. (Make sure you are using an Incognito mode).
On your previous tab, Click on and copy your credentials to Sign-in to the console.
Once logged in, you will be redirected to the GCP Console.
Click on the hamburger icon on the top left corner
In the left sidebar, click on Cloud Storage under the Storage section.
Click on Create Bucket
Enter the name of the bucket as whizlabs-bucket. Make sure you enter the same name or you must include this name in any other name you enter, like whizlabs-bucket-martha or test-whizlabs-bucket. It is required to validate the lab. Keep a note of the bucket name, later you'll need this.
Choose the location type as Multi-region and location as the US and click on Continue.
Make sure you choose the Standard storage class. Our goal is for a short term.
Uncheck bucket public access prevention enforcement
Choose access control as a Uniform to get the same access for all the objects in the bucket.
Keep the encryption as the default Google-managed key.
Click on Create to create the bucket
Once bucket creation is done, click on Upload files and Upload the sample CSV file provided in the supporting documents or you can download it by clicking here.
You can see the file is uploaded successfully. Let move on to the creation of Cloud SQL Instance.
Click on the hamburger icon on the top left corner
Click on SQL.
Click on Create Instance to create your database instance.
Choose your database engine as MySQL.
Enter instance id like import-csv-<your-role>. Make a note of the instance id, you'll need this later in the lab.
Set any password for your root user and keep a note of it, It is required at a later stage. Make a note of the password, you'll need this later in the lab.
Choose database version as MySQL 5.7
Select region as us-central1 and Any zone. Please note, User Interface might differ for you. You should choose an us-central1 region and zone as us-central1-a.
Click on show configuration options
Make sure the machine type is lightweight and has 1 Core with 3.75 GB of memory. If the machine type is different, Change the machine type by clicking the down arrow on the right side.
Click on the dropdown to change the machine type as Lightweight and select 1 vCPU, 3.75 GB. If UI is different for you, please choose machine type as db-n1-standard-1. Kindly do not choose any other option. Doing so might block your account
Change the Storage type by clicking the down arrow on the right side.
You can choose any storage type. In our case, we are choosing SSD and storage capacity as 10 GB
Expand the Backups Tab.
There will be two Advanced Options, Expand the first Advanced Options
Choose the location as region and us-central1.
Keep the other options as is.
Click on Create instance to finally create your SQL DB Instance.
Once you create DB Instance, click on databases
Click on Create Database.
Enter the database name as csv_db. Make a note of the database name, you'll need this later in the lab.
Keep the character set as utf8 and Collation as Default collation.
Click on create
Creating a Cloud Function:
Click on Cloud Function under Compute section.
Click on Create Function.
Enter the function name as import_csv. Do not choose any other name, required to validate the lab.
Choose the region as us-central1
Choose trigger type as HTTP
Choose authentication as Allow unauthenticated invocations. This is for learning purposes, in real-time you should always use authentication.
Click on Save to continue
Click on Next
Choose runtime as Python 3.7
Change the entry point as import_csv
Paste the below code in main.py. Do the necessary changes like connection name, password, database name, bucket name and review the code.
Paste the below code in the requirements.txt file.
Click on deploy to deploy your function
Click on import_csv i.e. your function name, once your function is successfully deployed.
Navigate to the Testing tab.
Click on Test the function.
If you see the output as Ok, means you are able to create the table and insert the records.
Now click on the top right corner to Activate Cloud Shell.
Enter the below query to connect your database with your instance :
Click on Authorize to authorize the request
It will prompt for the password which you entered while creating the DB Instance.
Enter the below command to select the database :
Enter the below query to fetch the records from the table :
You should see the output below.
Completion and Conclusion:
In this lab, you have connected Cloud Function with Cloud Storage and Cloud SQL
You have inserted records using Cloud Function into Cloud SQL.
You have successfully completed the lab.
Once you have completed the steps click on from your whizlabs dashboard.