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
Creating database named bts.
$ create database if not exists bts;
Use the database bts
$ use bts;
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)
);
$ 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
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
$ nano trips.csv-2
Importing the database content into mysql.
$ mysqlimport --local --host=$MYSQL --user=root --password --ignore-lines=1 --fields-terminated-by=',' bts trips.csv-*
Open mysql. Type the password. Password won’t be visible.
$ mysql --host=$MYSQL --user=root --password
To use the bts database.
$ use bts;
SQL queries
$ select distinct(pickup_location_id) from trips limit 5;
$ select
max(trip_distance),
min(trip_distance)
from trips;
$ select count(*) from trips where trip_distance=0;
$ select count(*) from trips where fare_amount=0;
$ select
payment_type,
count(*) from trips
group by payment_type;
$ show tables;
$ desc trips;
Working with Google Cloud SQL: An Introduction to Cloud SQL
