Google Cloud Platform: Google Cloud Storage(GCS Bucket) and how to use Bucket for manual Data Import and export

Google cloud platform supports storage for both Structured Data and Un-Structured Data. Storage technologies can be summarized as follows -

For unstructured data we have two main storage technologies - Persistence Disk and Google Cloud Storage buckets.

What is fundamental difference between Persistence and GCS bucket?

Persistent Disks is used for Block storage, Buckets are used for Object storage
Persistent Disks has upper limit of Max 64TB in size, Buckets are Infinitely scalable
Pricing mechanism for Persistent Disks is Pay what you allocate, Buckets follows Pay what you use. i.e: If the you create a 100GB disk but you use just 5GB you
pay for the entire 100GB
Persistent Disks tightly coupled with GCE VM, GCS Buckets are independent of GCE VMs
Persistent Disks has Zonal (or regional) access, buckets has Global access

How to create GCS "Bucket" in GCP?

From Resources section, click on "Storage" and start with "Create Bucket"
Since Bucket is globally available, we need to choose a name which is unique. Good practice is to add prefix/postfix your organization/department name to make bucket unique.
GCP use default selection for geographic placement of your data(multi-region), Storage class(Standard) and Access control(Uniform). Based on need and use we can change it.

Significance of Storage class and cost optimization - how often data is accessible is primary factor which drives "Storage class" selection.
- Archive storage class (for beyond cold Data) which is even colder than Coldline and it comes with lowest storage cost & highest data access cost.
- It has 365 day minimum storage, no availability SLA, higher data access costs than other storage classes.
On submit of create request we will have our Bucket ready in cloud storage panel.

Now we have GCS Bucket created, it can be used for import/export activity.

Cloud SQL provides managed interface to import and export of data and internally it use Google Cloud Storage to store the backup. Exporting data involves

Send command for Cloud SQL to run the mysqldump command against your database and
Put the output of that command into a bucket on Cloud Storage
Exports command are run with the --single-transaction flag (meaning that at least InnoDB tables won’t be locked while the export runs).

To get started, go to the instance details page for your Cloud SQL instance, and click the Export button at the top of the page.

Click on Export from MYSQL Instance page and Follows opened popup window & select File format, Database Schema whose data we wanted to export.
Click Browse to select Destination Bucket. Finally click "EXPORT" button to Initiate data export from Managed relational DB to GCS Bucket.

Note: If we do not have bucket created, we can create GCS Bucket by clicking [+] button.
Verify Data exported in GCS Bucket.

Export optimization

If we have a replica DB(read-only replication), we can do export operation from. By doing this we avoid using primary instance’s CPU time while exporting data to Cloud Storage. Repeat above step with readOnly replica instance and verify in GCS Bucket.

Import data from GCS Bucket to Cloud SQL Instance:

Import SQL Insert statement file from local machine to GCS Bucket.
From Cloud SQL primary DB instance, click import button and choose source as above uploaded file.

Verify Imported Data from MYSQL Client:

Before Import- 3 rows returned (ORDERS Table)

  
n0r0082@m-c02z31rnlvdt ~ % mysql -u root --password=PASSWORD -h 35.225.161.77 \
  --ssl-ca=../GCP/certs/server-ca.pem \
  --ssl-cert=../GCP/certs/client-cert.pem \
  --ssl-key=../GCP/certs/client-key.pem
Your MySQL connection id is 57547
Server version: 5.7.36-google-log (Google)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| devInlineTxn       |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.07 sec)

mysql> use devInlineTxn
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from ORDERS;
+----------+-------------+-------------+------------+
| order_id | order_total | customer_id | order_date |
+----------+-------------+-------------+------------+
|        1 | $120.98     | WM-198721   | 2022-02-26 |
|        2 | $11.98      | WM-1100     | 2022-02-26 |
|        3 | $41.98      | WM-4360     | 2022-02-26 |
+----------+-------------+-------------+------------+
3 rows in set (0.05 sec)

After import - 6 rows(including 3 new rows) returned

  
mysql> select * from ORDERS;
+----------+-------------+-------------+------------+
| order_id | order_total | customer_id | order_date |
+----------+-------------+-------------+------------+
|        1 | $120.98     | WM-198721   | 2022-02-26 |
|        2 | $11.98      | WM-1100     | 2022-02-26 |
|        3 | $41.98      | WM-4360     | 2022-02-26 |
|        4 | $20.98      | WM-1921     | 2022-02-27 |
|        5 | $12.98      | WM-1110     | 2022-02-27 |
|        6 | $41.98      | WM-4060     | 2022-02-27 |
+----------+-------------+-------------+------------+
6 rows in set (0.05 sec)

File Imported in GCS Bucket from local : orders-backlog.sql

USE `devInlineTxn`;
LOCK TABLES `ORDERS` WRITE;
INSERT INTO `ORDERS` VALUES (4,'$20.98','WM-1921','2022-02-27'),(5,'$12.98','WM-1110','2022-02-27'),(6,'$41.98','WM-4060','2022-02-27');
UNLOCK TABLES;

----====--------

Google Cloud Platform: Google Cloud Storage(GCS Bucket) and how to use Bucket for manual Data Import and export

Export optimization

Import data from GCS Bucket to Cloud SQL Instance:

1 Comments

Contact Form