Cloud SQL

Cloud SQL is a default choice for those OLTP or Online Transaction Processing workloads on Google Cloud.

Create a Cloud SQL instance

gcloud sql instances create taxi \ --tier=db-n1-standard-1 --activation-policy=ALWAYS

Set a root password for the Cloud SQL instance:

gcloud sql users set-password root --host % --instance taxi \ --password Passw0rd

Create an environment variable with the IP address of the Cloud Shell:

export ADDRESS=$(wget -qO - http://ipecho.net/plain)/32

Whitelist the Cloud Shell instance for management access to your SQL instance.

gcloud sql instances patch taxi --authorized-networks $ADDRESS

Get the IP address of your Cloud SQL instance by running:

MYSQLIP=$(gcloud sql instances describe \ taxi --format="value(ipAddresses.ipAddress)")

Check the variable MYSQLIP:

echo $MYSQLIP

Create the taxi trips table by logging into the mysql command line interface.

mysql --host=$MYSQLIP --user=root \ --password --verbose

Create the taxi trips table by logging into the mysql command line interface.

mysql --host=$MYSQLIP --user=root \ --password --verbose

Add data to Cloud SQL instance

Now you'll copy the New York City taxi trips CSV files stored on Cloud Storage locally. To keep resource usage low, you'll only be working with a subset of the data

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

Import the CSV file data into Cloud SQL using mysql:

mysqlimport --local --host=$MYSQLIP --user=root --password \ --ignore-lines=1 --fields-terminated-by=',' bts trips.csv-*

Connect to the mysql interactive console:

mysql --host=$MYSQLIP --user=root --password