Loading Data into Google Cloud SQL

2 minute read

Published:

Google Cloud SQL is a fully-managed database service that makes it easy to set-up, maintain, manage and administer your relational MySQL, PostgreSQL, and SQL Server databases on Google Cloud Platform.

In this post, we’re going to look at how to create a cloud sql instance as well as load some data into the instance.


Preparing the Environment

Create environment variables for the project ID and the cloud storage bucket that will contain your data.

export PROJECT_ID=$(gcloud info --format='value(config.project)')
export BUCKET=${PROJECT_ID}-ml

Creating a Cloud SQL instance

  • Enter the following commands to create a Cloud SQL instance called my_sql_instance.

gcloud sql instances create my_sql_instance --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 my_sql_instance --password <YOUR_SQL_INSTANCE_PASSWORD>

  • Create an environment variable for the IP address of the Cloud Shell.

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

  • Allow the Cloud Shell instance to connect to the SQL instance (whitelisting).

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

  • Press Y to accept the changes when prompted.

  • Get the IP address of your Cloud SQL instance by running.

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

  • Create a dummy table by logging into the mysql CLI.

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

  • When prompted for a password enter <YOUR_SQL_INSTANCE_PASSWORD> that have been set previously.

  • Paste the following queries into the CLI to create a simple schema for the dummy table.

CREATE DATABASE IF NOT EXISTS my_database;

USE my_database;

CREATE TABLE IF NOT EXISTS my_table (
  col_a VARCHAR(16),		
  col_b INT,
  col_c FLOAT
);
  • Exit the MySQL CLI.

exit


Loading data into Cloud SQL instance

For simplicity, let’s assume that we’ll use the following CSV data for our previously created dummy table (my_table).

# file: my_table.csv

col_a,col_b,col_c
hello,100,0.5
world,100,9.5
cloud,900,0.5
sql,900,9.5

In case the above CSV file is located at a cloud storage, simply copy the file to the local (VM instance where the Cloud Shell is being connected to). Run the following in the CLI to do so.

gsutil cp gs://<BUCKET_NAME>/<PATH_TO_CSV> my_table.csv

Now, let’s import the CSV data into our Cloud SQL instance.

mysqlimport --local \
            --host=$MYSQLIP \
            --user=root \
            --password \
            --ignore-lines=1 \
            --fields-terminated-by=',' \
            my_database \
            my_table.csv
  • When prompted for a password enter <YOUR_SQL_INSTANCE_PASSWORD>.

  • Connect to the MySQL CLI to perform some queries.

mysql --host=$MYSQLIP --user=root --password
  • When prompted for a password enter <YOUR_SQL_INSTANCE_PASSWORD>.

  • Exit the MySQL CLI when you’re done.

exit