Working with Google Cloud SQL: An Introduction to Cloud SQL
Prerequisites
Open Cloud Shell.
Paste the below code.
To export the project id into $PROJECT_ID
$ export PROJECT_ID=$(gcloud info –format=’value(config.project)’)
To print the project id
$ echo $PROJECT_ID
To export the bucket id into $BUCKET
$ export BUCKET=${PROJECT_ID}-ml
To print the bucket id
$ echo $BUCKET
To export the IP address of SQL instance. To get the IP address of SQl Instance Click on this link.
$ MYSQL=<IP_ADDRESS>
To print IP address
$ echo $MYSQL
To open MYSQL connection.
$ mysql –host=$MYSQL –user=root -–password
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-359.png)
Creating database named bts.
$ create database if not exists bts;
Use the database bts
$ use bts;
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-360.png)
Creating table trips
create table trips (
vendor_id VARCHAR(16),
pickup_datetime DATETIME,
dropoff_datetime DATETIME,
passenger_count INT,
trip_distance FLOAT,
rate_code VARCHAR(16),
store_and_fwd_flag VARCHAR(16),
payment_type VARCHAR(16),
fare_amount FLOAT,
extra FLOAT,
mta_tax FLOAT,
tip_amount FLOAT,
tolls_amount FLOAT,
imp_surcharge FLOAT,
total_amount FLOAT,
pickup_location_id VARCHAR(16),
dropoff_location_id VARCHAR(16)
);
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-361.png)
$ exit #to exit from mysql.
Paste the below code into shell outside mysql. It’ll copy 2 files from cloud training public bucket.
gsutil cp gs://cloud-training/OCBL013/nyc_tlc_yellow_trips_2018_subset_1.csv trips.csv-1
gsutil cp gs://cloud-training/OCBL013/nyc_tlc_yellow_trips_2018_subset_2.csv trips.csv-2
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-362.png)
Open the both files which is created and check whether the content is copied or not.
Using nano editor we’ll open the file. To close the file press Ctrl + X
$ nano trips.csv-1
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-363.png)
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-364.png)
$ nano trips.csv-2
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-365.png)
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-366.png)
Importing the database content into mysql.
$ mysqlimport –local –host=$MYSQL –user=root –password –ignore-lines=1 –fields-terminated-by=’,’ bts trips.csv-*
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-367.png)
Open mysql. Type the password. Password won’t be visible.
$ mysql –host=$MYSQL –user=root –password
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-368.png)
To use the bts database.
$ use bts;
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-369.png)
SQL queries
$ select distinct(pickup_location_id) from trips limit 5;
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-370.png)
$ select
max(trip_distance),
min(trip_distance)
from trips;
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-371.png)
$ select count(*) from trips where trip_distance=0;
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-372.png)
$ select count(*) from trips where fare_amount=0;
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-373.png)
$ select
payment_type,
count(*) from trips
group by payment_type;
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-374.png)
$ show tables;
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-375.png)
$ desc trips;
![](https://prwatech.in/blog/wp-content/uploads/2021/05/image-376.png)
Working with Google Cloud SQL: An Introduction to Cloud SQL