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