SQL (Structured Query Language) databases are commonly used to store information that is highly regular and strongly typed. In addition, such databases support transactional properties such as atomicity, consistency, isolation, and durability. Most cloud providers support managed SQL databases that obviate the need for the developer to manually install servers themselves. In this codelab, we will practice the basics of the SQL language and show how SQL databases can be quickly brought up in the cloud.
Visit the SQL quiz at https://www.w3schools.com/sql/sql_quiz.asp. Take the quiz and include a screenshot with your OdinID on it of the "Check your answers" page at the end of the quiz. You do not have to be perfect, but check the ones you got incorrect.
Google Cloud Platform's Cloud SQL provides a drop-in replacement for a variety of SQL databases that one can run. In this section, we will create a managed MySQL database within Cloud SQL, set up its tables, and then query it using standard MySQL tools. To begin with, within Cloud Shell, download the database files and change into the lab directory
git clone https://github.com/GoogleCloudPlatform/training-data-analyst cd training-data-analyst/CPB100/lab3a
Examine the data definition language (DDL) commands in
cloudsql/table_creation.sql that specifies the schema and answer the following questions:
Examine the data that fills in these tables at
cloudsql/accommodation.csv and answer the following questions.
We will now stage these files on a storage bucket so that the Cloud SQL service can use them when creating and initializing a server for us. To begin with, create a regional storage bucket in
us-west1 with a unique name:
gsutil mb -c regional -l us-west1 gs://<BUCKET-NAME>
Then, copy the files from the
cloudsql directory over to it.
gsutil cp cloudsql/* gs://<BUCKET-NAME>/sql/
In subsequent steps, we will be accessing our SQL database from Cloud Shell. To enable access, we must first find the external IP address of the Cloud Shell session. There are a variety of sites that will report back the IP address of a web request. To do so for your Cloud Shell session, run the following command and record your session's IP address.
curl http://ipecho.net/plain ; echo
Finally, visit the Cloud Storage service from the web console to verify that your files have been copied over.
In the web console, scroll down and select the SQL service.
Create an instance specifying MySQL.
With the default settings, name the instance
rentals, place it in
us-west1-b, and set the root password for the database. (Remember the password). Make a note of the external IP address of the MySQL instance that has been brought up.
Within Cloud SQL, click on the instance, then on "Edit", then on "Connectivity" under Configuration Options.
Then, click on "Add Network".
Enter the IP address of your Cloud Shell session as a CIDR route (e.g. w.x.y.z/32). Click on "Done", then "Close".
Within Cloud SQL, click on the instance, then on "Import" on the top menu bar. Click on the "Browse" button and browse to the storage bucket you created initially for this lab. Locate the lab's SQL files and select
We will then import the two CSV files containing the table data from your storage bucket. First, import
accommodation.csv into the database that was created in the previous step (
recommendation_spark) and into the table
Repeat the import for
rating.csv using the table
Within your Cloud Shell session, connect to the Cloud SQL instance using its IP address and the root password you set previously.
mysql --host=<MySQLIP> --user=root --password
Within the MySQL client shell, set the database for the session.
Show the tables that have been created.
Verify the data has been imported properly.
select * from Rating;
Run queries for accommodations at two price levels of your choice and two types of your choice. Show screenshots of the query output for your lab notebook.
select * from Accommodation where...
Visit Cloud SQL in the web console and delete the instance created. Then, in Cloud Storage, delete the storage bucket created. Cloud SQL uses an
n1-standard-1 machine by default. If you leave it up, billing sadness like below will result.
As with Cloud SQL, Amazon Web Service's Relational Database Service provides a drop-in replacement for a variety of SQL databases that one can run. To begin with, go to AWS Educate and bring up your "AWS Starter Account". From this, click on "AWS Console" to bring up the AWS web console. Note that the browser must not have pop-up windows blocked. Ensure that the region in the upper right corner is
us-east-1 (N. Virginia) as shown below.
Before we set up our RDS instance, we will first configure a security group to ensure access is only allowed from a particular network address. There are several options for connecting to your instance using a MySQL command-line client. You can:
curl http://ipecho.net/plain ; echoto obtain the IP address of the instance
linux.cs.pdx.edu, then perform
curl http://ipecho.net/plain ; echoto obtain its IP address.
With the IP address, visit the web console and go to "VPC", then click on "Security Groups" and "Create security group"
Configure the group to add a rule to allow in-bound traffic for MYSQL (Port 3306) for the IP address using its CIDR notation.
Create the security group.
Go to the web console and bring up RDS. Click on "Create database".
Choose Standard Create, MySQL and the RDS Free tier template.
Go to the web console and bring up RDS. Click on "Create database".
Name the database instance and configure a master username/password as shown below.
Keep the default settings for "DB instance size", "Storage", and "Availability & durability". Continue to the next step. Do not create the instance yet.
Typically, one would put the RDS instance in a private network (VPC) and disallow access from external locations. For the purpose of this lab, however, we will enable public access. Under "Connectivity", use the default VPC, but expand "Additional connectivity configuration". Use the default subnet group setting and click on "Publicly accessible".
Then in the "Existing VPC security groups", select the dropdown and add the security group you created previously to the instance.
Finally, create the database. Visit the web console of RDS and wait until the "Status" of the database is "Available". Note the endpoint address on the bottom-left that will be used to connect to as shown below:
On the machine whose IP address you added to the security group, connect up to the database using the MySQL client and the Endpoint hostname given for the instance.
mysql -h <instance_endpoint> -P 3306 -u admin -p
Show a screenshot of the successful connection similar to below:
Go back to RDS in the AWS console. Select the database created and delete it. Do not save a snapshot.