Structure data
- cloud SQL
- cloud Spanner
- BigQuery
Semi structure data
- BigTable
- Datastore
- Memorystore
Cloud SQL
Fully managed Relational database service for Mysql, PostgreSQL etc
Lift & shift above database
Regional Database with 99.95% SLA
Storage up to 30TB
Scale up to 96 core & 416GB memory
No Horizontal Scaling
Data is encrypted with Google managed key or CMEK
Cloud SQL can be accessed from anywhere like - App Engine, Compute Engine..
Used for storing Transactional database
Ecommerce, CRM kind application backend.
No maintenance & auto update
- Back-up Database
- On-demand Backup
- Schedule backup
Database migration service(DMS)
migrate data from different SQL system to Cloud SQL
Point-in Time Recovery
Scale with Read replicas - To transfer workload to other instance
Export data
- gcloud utility or Cloud Console
- In SQL/CSV format
After provisioning of your Cloud SQL instance.
whenever you want to connect to database, you need to whitelist your IP.
whitelist your ip
you can whitlist your ip to go to connections network and provide by cmd or you can white list your ip in cloud shell by below cmd.
gcloud sql connect my-first-sql-1 --user=root --quiet
Cloud Spanner
Distributed & scalable solution for RDBMS in GCP
Fully managed, Mission critical application
Horizontal Scalability
use when Data volume > 2 TB
Costlier than Cloud SQL
Cloud SQL has just Read replicas,
- where as in cloud spanner horizontal read/write across region
Highly scalable, Petabyte scale
Data is strongly typed.
- Must defined schema database
- Datatype for each column of each table must be defined.
- 99.999% availability
Cloud native solution - specific to GCP
- Lift & shift not possible, Not recommended
Spanner = Cloud SQL + Horizontal Scalable
Scale to petabyte
Regional/Multi-region level instance can be created
Data export
- can not export with gcloud
- cloud console or cloud dataflow job
Cloud Spanner Demo
Create Spanner Instance
Create databases edu_db
Create 2 tables
Author
- AuthorID
- AuthorName
Book
- BookId
- BookName
- AuthorID
create database edu_db
created author table
CREATE TABLE author (
aid INT64,
authername STRING(100),
) PRIMARY KEY(aid);;
created book table
CREATE TABLE book (
aid INT64,
bid INT64,
bookname STRING(200),
) PRIMARY KEY(aid, bid),
INTERLEAVE IN PARENT author ON DELETE CASCADE;;
in above interleave is meaning both table should be in 1 instance recommded practice for execution and latency kind of purpose keep both the table data in 1 instance.
On delete cascade means any of record delete from book table, the corresponding record also will be deleted
which database to use when
Cloud SQL
- Lift & shift SQL based system
- CRM, Ecommerce App
- Max Data size is 30TB
Cloud Spanner
- Horizontal scalability
- Low latency
- High scalability in terms of storage + compute
- if Data Storage requirement is beyond TB
NoSQL Introduction
Not a SQLFlexible Schema
Variable number of property
Data model will be like
- Document
- key-value pair
- Graph based
No-Structure datastore
Datastore and firestore are almost same, firestore is next generation version of datastore.
Cloud Datastore
Highly scalable NoSQL database
Serverless
Document kind data storage- MongaDB
App Engine + Datastore
SQL Like Queries - GQL
Support ACID Transaction
Multiple indexes
Data replication across different region
Use case
- Session info
- Product catlog
Export data from gcloud utility only
Compare NoSQL(datastore) and RDBMS
Datastore
At a time only 1 service we can use datastore or firestore in a project.
and whatever we enable 1st, it could be either datastore or firestore.
Create entity
Namespace: default
kind: Student
Properties
Name: sid
Type: Integer
Value: 100
Index this property
Properties
Name: sname
Type: String
Value: John
Index this property
Admin
Cloud Firestore
Firestore is the next generation of Datastore
Highly scalable NoSQL database
Collection & Document Model
Two mode
- native Mode
- datastore mode - leard above
Real-time updates
Mobile and web client libraries
Indexes
Composite index - create index - student - fields sid/sname-asc
Import/Export - Bucket
Cloud Memorystore
Fully managed Inmemory database
sub-millisecond data access
Two engine supported
- Redis
- Memcached
Only Internal IP
Highly available with 99.9% SLA
Import/Export data from Cloud Storage to memory store
add redis instance
get the redis ip
make sure you creating in same subnet that you creating vm machine in same zone and region.
create vm machine
install redis tool
sudo apt install redis-tool
redis-cli -h redis-ip
set foo bar
get foo
Cloud Bigtable
Fully managed
Wide column NOSQL database
Not serverless
Scale horizontally with Multiple Node
Scale to huge volume of data
Data stored at column wise
Column are grouped into column family
Milli second latency
Handles millions of request per second
How to access
- cbt - command line(part of cloud sdk)
- Hbase API
No Multi column index
- Only Row key based indexing
Design Row key is very important
- which is your frequent query in application
- No Hot spotting
- Don't use monotonically increasing key
Seamless integration with
- Warehouse - BigQuery
- Machine Learning Product(ML prod.)
Used for
- Financial data
- Time series Data
- Every single record have 1 row key to access it, you can't create indexing on other column, like name,age,salary etc.
you can only retrieve the data based on row key.
and its wide column database, so may single record have 100s of diff. column.
It can go upto million of column
managing such huge column very hard, there is another concept column family,
exam. grouping name, age in one column family - personal_data_cf
when we need to access salary
professional_data_cf:salary
but it is not necessary each row column have data.
Maybe Personal_data_cf:name have data but Personal_data_cf:age - dont have.
check cbt version
$ cbt version
0.16.4 f0a7cba 2023-11-07 18:11:35
check config file if have project and instance name as below
$ cat .cbtrc
project = atomic-matrix-401102
instance = my-first-bt
create big-table
$ cbt createtable emp
2023/12/11 05:49:50 -creds flag unset, will use gcloud credential
list big-table
$ cbt ls
2023/12/11 05:51:04 -creds flag unset, will use gcloud credential
emp
create column family
$ cbt createfamily emp professional_data_cf
2023/12/11 05:55:30 -creds flag unset, will use gcloud credential
list column family
$ cbt ls emp
2023/12/11 05:55:45 -creds flag unset, will use gcloud credential
Family Name GC Policy
----------- ---------
personal_data_cf <never>
professional_data_cf <never>
create column name under column family name with key info J1
$ cbt set emp J1 personal_data_cf:name=john
$ cbt set emp J1 personal_data_cf:age=28
Read the big-table
$ cbt read emp
2023/12/11 05:58:08 -creds flag unset, will use gcloud credential
----------------------------------------
J1
personal_data_cf:age @ 2023/12/11-05:57:51.631000
"28"
personal_data_cf:name @ 2023/12/11-05:57:30.845000
"john"
$ cbt set emp J1 professional_data_cf:salary=32000
cbt read emp
2023/12/11 06:00:10 -creds flag unset, will use gcloud credential
----------------------------------------
J1
personal_data_cf:age @ 2023/12/11-05:57:51.631000
"28"
personal_data_cf:name @ 2023/12/11-05:57:30.845000
"john"
professional_data_cf:salary @ 2023/12/11-05:59:53.463000
"32000"
$ cbt set emp A1 professional_data_cf:education=master
$ cbt read emp
2023/12/11 06:02:36 -creds flag unset, will use gcloud credential
----------------------------------------
A1
professional_data_cf:education @ 2023/12/11-06:02:30.956000
"master"
----------------------------------------
J1
personal_data_cf:age @ 2023/12/11-05:57:51.631000
"28"
personal_data_cf:name @ 2023/12/11-05:57:30.845000
"john"
professional_data_cf:salary @ 2023/12/11-05:59:53.463000
"32000"
update J1 key profession_data salary, so there will be multiple version exist
$ cbt set emp J1 professional_data_cf:salary=32000
$ cbt read emp
2023/12/11 06:04:24 -creds flag unset, will use gcloud credential
----------------------------------------
A1
professional_data_cf:education @ 2023/12/11-06:02:30.956000
"master"
----------------------------------------
J1
personal_data_cf:age @ 2023/12/11-05:57:51.631000
"28"
personal_data_cf:name @ 2023/12/11-05:57:30.845000
"john"
professional_data_cf:salary @ 2023/12/11-06:04:20.852000
"32000"
professional_data_cf:salary @ 2023/12/11-05:59:53.463000
"32000"
count record in table
manjeetyadavrajokri@cloudshell:~ (atomic-matrix-401102)$ cbt count emp
2
In bigtable UI, we have option
to Create tables - earlier that option was not there, it was with cbt cmd only, but now this option available
Take backups
Monitoring
No comments:
Post a Comment