GCP Cloud SQL

Connecting to GCP SQL

Storage/SQL Decision Chart

SQL Functions

• ORDER BY Aggregates rows that share common criteria (e.g. a column value) and will return all of the unique entries found for such criteria. Or sorts the returned data from a query in ascending or descending order based on a specified criteria or column value.
• COUNT A SQL function will count and return the number of rows that share common criteria.
• AS Creates an alias of a table or column.
• UNION This keyword combines the output of two or more SELECT queries into a result-set. Use UNION to combine subsets of the “example1” and “example2” tables.

Examples:

SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;

SELECT start_station_name AS top_stations, num FROM london1 WHERE num>100000
UNION
SELECT end_station_name, num FROM london2 WHERE num>100000
ORDER BY top_stations DESC;

The first SELECT query selects the two columns from the "london1" table and creates an alias for "start_station_name", which gets set to "top_stations". It uses the WHERE keyword to only pull rideshare station names where over 100,000 bikes start their journey.

The second SELECT query selects the two columns from the “london2” table and uses the WHERE keyword to only pull rideshare station names where over 100,000 bikes end their journey.

The UNION keyword in between combines the output of these queries by assimilating the "london2" data with "london1". Since "london1" is being unioned with "london2", the column values that take precedence are "top_stations" and "num".

ORDER BY will order the final, unioned table by the "top_stations" column value alphabetically and in descending order. (This example was copied from a GCP Lab exercise.)

Proxy Connection

When your application does not reside in the same VPC connected network and region as your Cloud SQL instance, use a proxy to secure its external connection.

Downloading the Cloud SQL Proxy and making it executable:

wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy && chmod +x cloud_sql_proxy

Activate the proxy connection to the Cloud SQL using the SQL connection name (unique):

./cloud_sql_proxy -instances=$SQL_CONNECTION=tcp:3306 &