{"id":9001,"date":"2026-09-18T10:20:53","date_gmt":"2026-09-18T10:20:53","guid":{"rendered":"https:\/\/prwatech.in\/blog\/?p=9001"},"modified":"2026-09-19T15:47:46","modified_gmt":"2026-09-19T15:47:46","slug":"working-with-cloud-sql","status":"publish","type":"post","link":"https:\/\/prwatech.in\/blog\/google-cloud-platform\/cloud-sql\/working-with-cloud-sql\/","title":{"rendered":"Working with cloud SQL"},"content":{"rendered":"\r\n

Loading and Querying Real Data in Cloud SQL<\/h1>\r\n

This picks up with an existing Cloud SQL for MySQL instance and puts it to actual use, creating a database, loading a real dataset of New York taxi trips into it from two CSV files, then running a series of analytical queries against it directly from the mysql command line client. If you do not have an instance running yet, the Creating Cloud SQL Instance Using Cloud Shell guide covers that first step.<\/p>\r\n

Doing this through the mysql client instead of a graphical tool keeps everything in one place, the same Cloud Shell session that created the instance, and every command here is something you could drop directly into a script later. It is also simply a fast way to get comfortable with real SQL against a real, imperfect dataset, complete with the zero fare rows and zero distance trips you will filter out along the way.<\/p>\r\n

This walks through creating the database and its table, importing both CSV files into it, then running six queries that explore what is actually in the data.<\/p>\r\n

Step by Step Process of Loading and Querying Real Data in Cloud SQL<\/h2>\r\n

Step One: Set Your Variables<\/h3>\r\n

Paste these one at a time, replacing the IP address placeholder with your actual instance’s IP address:<\/p>\r\n

export PROJECT_ID=$(gcloud info –format=”value(config.project)”)
echo $PROJECT_ID

export BUCKET=${PROJECT_ID}-ml
echo $BUCKET

MYSQL=IP_ADDRESS
echo $MYSQL<\/p>\r\n

Each export line saves a value into a variable, and each echo line prints it back so you can confirm it is correct before moving on.<\/p>\r\n

Step Two: Connect to MySQL<\/h3>\r\n

mysql –host=$MYSQL –user=root –password<\/p>\r\n

Enter your password when prompted. It will not show on screen as you type it, this is normal.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Step Three: Create a Database<\/h3>\r\n

create database if not exists bts;
use bts;<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Step Four: Create the Trips Table<\/h3>\r\n

create table trips (
\u00a0\u00a0vendor_id VARCHAR(16),
\u00a0\u00a0pickup_datetime DATETIME,
\u00a0\u00a0dropoff_datetime DATETIME,
\u00a0\u00a0passenger_count INT,
\u00a0\u00a0trip_distance FLOAT,
\u00a0\u00a0rate_code VARCHAR(16),
\u00a0\u00a0store_and_fwd_flag VARCHAR(16),
\u00a0\u00a0payment_type VARCHAR(16),
\u00a0\u00a0fare_amount FLOAT,
\u00a0\u00a0extra FLOAT,
\u00a0\u00a0mta_tax FLOAT,
\u00a0\u00a0tip_amount FLOAT,
\u00a0\u00a0tolls_amount FLOAT,
\u00a0\u00a0imp_surcharge FLOAT,
\u00a0\u00a0total_amount FLOAT,
\u00a0\u00a0pickup_location_id VARCHAR(16),
\u00a0\u00a0dropoff_location_id VARCHAR(16)
);<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Exit the mysql client to go back to the plain Cloud Shell prompt:<\/p>\r\n

exit<\/p>\r\n

Step Five: Copy the Sample Data Files<\/h3>\r\n

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<\/p>\r\n

This copies two CSV files of sample taxi trip data from a public training bucket into your Cloud Shell home directory.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Step Six: Check Both Files<\/h3>\r\n

Open the first file in the nano editor to confirm it copied correctly. Press control x to close it.<\/p>\r\n

nano trips.csv-1<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n
\"\"<\/figure>\r\n\r\n\r\n\r\n

Do the same for the second file:<\/p>\r\n

nano trips.csv-2<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n
\"\"<\/figure>\r\n\r\n\r\n\r\n

Step Seven: Import Both Files Into the Table<\/h3>\r\n

mysqlimport –local –host=$MYSQL –user=root –password –ignore-lines=1 –fields-terminated-by=’,’ bts trips.csv-*<\/p>\r\n

The ignore lines flag skips each file’s header row, and the wildcard at the end tells mysqlimport to pick up both trips.csv-1 and trips.csv-2 in one command rather than importing them separately.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Step Eight: Reconnect and Select the Database<\/h3>\r\n

mysql –host=$MYSQL –user=root –password<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

use bts;<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Step Nine: Explore the Data<\/h3>\r\n

A first look at which pickup locations actually appear in the data:<\/p>\r\n

select distinct(pickup_location_id) from trips limit 5;<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

The shortest and longest trip distances in the dataset:<\/p>\r\n

select
\u00a0\u00a0max(trip_distance),
\u00a0\u00a0min(trip_distance)
from trips;<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

How many trips are recorded with zero distance, which is worth knowing before trusting any distance based analysis on this data:<\/p>\r\n

select count(*) from trips where trip_distance=0;<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

The same check for zero fare trips:<\/p>\r\n

select count(*) from trips where fare_amount=0;<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

A breakdown of how many trips used each payment type:<\/p>\r\n

select
\u00a0\u00a0payment_type,
\u00a0\u00a0count(*) from trips
group by payment_type;<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Confirming the table exists in the database:<\/p>\r\n

show tables;<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

The table’s full column structure:<\/p>\r\n

desc trips;<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Common Mistakes to Avoid<\/h2>\r\n