NoSQL One Liners

During NoSQL Session, After the first half of sessions. Learners shared their comparison of RDBMS vs NoSQL

An RDBMS includes functions that maintain the security, accuracy, integrity and consistency of the data

RDBMS - consistent and availability

All tables are related to each other in RDBMS and  in NoSQL all tables are independent of each other

NoSQL - partition tolerance at the expense of availability

ACID (atomicity, consistency, isolation, durability) is a set of properties of RDBMS  for transaction 

RDBMS fixed schema and No SQL have dynamic schema (Schemaless)

In NoSQL, we can exclude the information which is not required 

When to Choose what?

• High reads consistent data - RDBMS
• High writes low reads - HBase, Cassandra
• Document-based storage (multiple key-value pairs, or key-array pairs, or even nested documents) - Mongodb, Couchdb
• Key-Value stores are similar to maps or dictionaries where data is addressed by a unique key - Redis

Keep Exploring!!!

NoSQL Summary - Options

 A bit of a relook on NoSQL for a class helped me consolidate my learning.

NoSql - Not only SQL. During Engineering when it comes to Database design it is all about

• Codd's Rule
• Normalization Techniques 

What I thought in 2005

• How we handle columns, data types, relationships everything is key. Handling Null, Default values, constraints, etc...

Systems data was structured in 2000

• 20 years back there was no social media, no WhatsApp. Most of the data is structured data, transactions, automating orders, etc..

What all performance improvements/challenges came as data volumes increased?

• Partitioning by products/duration
• Replication to manage read / writes
• Use of Snapshot isolation/options
• Denomalizing few tables
• Migrating to the latest version / Rewriting some of the slow-performing reports
• Pagination of reports instead of fetch all approach
• Archiving completed orders
• Vertical Scaling- Add more RAM, CPU

Since the social media age

• Now we have more unstructured, semi-structured data from mobile phones, social media, reviews, ratings, rankings, messages, images, and videos.

I still remember the 2010 period when Hadoop was much spoken about. Moving computation where data is available. I looked up my post in 2011 on MongoDB. 

The evolution of databases is from

• Stage 1 - Papers, Ledgers
• Stage 2 - Excel, Access
• Stage 3 - Databases
• Stage 4 - Hadoop for large-scale data
• Stage 5 - NoSQL
• Stage 6 - lakehouse = (Hadoop + RDBMS + NOSQL + AI for data extraction from unstructured sources)

Building a RDBMS perspective is Tables, Keys, Relationships

Ref - Link

Everything revolves around Reading Correct Data vs Dirty Data (Transactions in progress may or may not commit). 

Everything in DBMS is

• Create
• Read
• Update
• Delete

How does read/write balance, Essentially a record or row needs to be locked before update. This ensures we work in a consistent state.

CAP theorem is the Crux of Everything

Ref - Link

Now you need to choose DB based on preference (C - A - P)

Questions to ask to decide on the choice of Database?

• Is Query pattern aggregates or select for individual records?
• What is projected database growth?
• Is it structured / semi-structured data?
• What are my top 2 choices, can I do a quick prototype and performance test to validate 
• Schema design what practices are relevant to each database type? What maps closely to the current context?
• Is Consistency a key thing, What about Availability / Partition tolerance, Is this system queried across geography to have availability in different regions
• If it exists how different copies will sync up, Will there be a master-slave approach / Replication / Log copy?
• What is cost allocated considering volume, and high availability needs?

Different NoSQL Systems

• In one of the SaaS products we worked on, the Redis Key value pair was used for session management
• IoT platform for device management in one of my friends Team Cassandra was used to push device data / Generate reports
• One of the big retailers I was familiar used heavily columnar database Vertica to manage all their aggregate data for BI / ML work

We need to consider the use case, data volume, velocity, type of reporting, cost, growth, security everything to decide on choosing a database.

Ref - Link

I say table in RDBMS, Collection in MongoDB, What is the conceptual mapping?

SQL vs NoSQL Design Thinking 

• How do I design collection in document DB, nested 1 to 1, 1 to many relationships
• What information I store in key-value pair, What key value will be unique and will not result in duplicates
• What column family I will create, How many aggregate queries will look like

Schema / Relationships / Keys will vary based on the Database type.

Which Database for What Application Purpose?

• High reads consistent data - RDBMS
• High writes low reads - HBase, Cassandra
• Document-based storage (multiple key-value pairs, or key-array pairs, or even nested documents) - Mongodb, Couchdb
• Key-Value stores are similar to maps or dictionaries where data is addressed by a unique key - Redis

Above all cost also plays a key role. Knowing what to choose based on size, data growth, and access patterns is key to deciding the type of Database for implementation.

RDBMS, KeyValue, Columnar, Graph, Document Collection all these forms of databases will co-exist :)

Data Stack

Ref - Link

Modern Analytics Stack

Ref - Link

Source - Link

New Cardinality Notations and Styles for Modeling NoSQL Document-store Databases 

NoSQL data modeling

ERD Plus

Keep Learning!!!

CAP - #Consistency, #Availability, #PartitionTolerance

 #Consistency,

• When True, I remember exactly everything, I will recall the same details every time
• When False, I remember approximately before the latest info, I may have a poor memory

#Availability,

• When True, I will attend your call everything, You will get a reply (When you impress your partner)
• When False, I may not respond when I sleep (Post marriage no reply on every call)

#PartitionTolerance,

• When True, You have two networks/numbers to reach me, When one number is not available another number you can reach me, You have an alternate network
• When False, When one number is not available, Network issue, You cannot reach me

Keep Exploring!!!

Good Read - Databases Vs Blockchain

Paper - Trends in Development of Databases and Blockchain

Key Notes

• ACID (Atomicity, Consistency, Isolation, and Durability) 
• CAP - (Consistency, Availability, Partition tolerance)
• DCS (Decentralization, Consistency, Scalability) theorem

Difference between blockchain and database

• Blockchain differs from traditional databases in numerous ways like its decentralization, cryptographic security using chained hashes, no administration control, immutability, freedom to transfer
• These distributed databases have their consensus mechanism for the joint agreement on a data block by the network parties
• blockchain databases = distributed databases, support features like complex data types, rich query structure,
• ACID compliant [3], low latency, fast scalability, and cloud hosting

CAP 

• Consistency - Any read in the distributed system gives the latest write on the nodes.
• Availability - A Client always receives a response at any point of time irrespective of whether the read is the latest write.
• Partition Tolerance - In case of partition between nodes in the distributed system, the system should still be functioning

BCS

• Decentralization - There is no trusted entity controlling the network, hence no single point of failure. 
• Consistency - The blockchain nodes will read the same data at the same time. 
• Scalability - The performance of blockchain should increase with the increase in the number of peers and the number of allocated computational resources. 

Good Read!!!

Distributed Systems - Session #3 - Aurora

Sometimes I felt not connected to the session. Needs a lot of focus and patience to stay connected and focused :)


Key Summary points

• Amazon early offering EC2
• Rented out VMs to customers
• VMM (Virtual Machine Monitors) that run/manage EC2 instances
• EC2 good for stateless web servers
• S3 - Scheme for storing large chunks of data (Periodic Snapshots)
• Disks for EC2 instances - Fault Tolerance (EBS)
• EBS (Elastic Block Store) - Looks for EC2 instances as it is a harddrive
• Databases on EBS sends a large volume of data over the network
• Amount of writes on Network Storage System
• CPU / Disk space consumption
• EC2 / EBS are in same availability zone
• Transaction & Crash Recovery
• Transaction (Sequence of operations / commands / atomic / ex- bank transfer money between accounts)
• Reads page from disk
• Make Changes in local cache
• Then write changes to disk
• Log entries describe the transaction
• Three log records - Modify Operation, Old Value, New Value
• Aurora is based on MySQL
• RDS (Database replicated in multiple availability zones)
• All the transactions mirrored to other databases (EBS Servers)
• Multiple copies managed and updated to keep everything in sync
• Read / Write Quorum will overlap 
• Voting does not work to read from which server
• These systems have version numbers
• Readers takes the ones with highest version number
• Split database into replicas
• Data Sharding
• Data across protection groups

Happy Learning!!!

Distributed Systems - Session #2

I paused it a lot as I didn't really get involved much but finally managed to complete it.

Key Lessons

• Go lang examples for threading, locking, RPC, Typesafe and memory safe, Garbage Collected
• Threads - Tools to manage concurrency in programs
• Stacks are within address space of the program
• I/O Concurrency - Overlapping of progress of different activities wait ing / executing
• Parallelism - Parallelize CPU / IO cycles / routines
• Process is a single program / single address space. Inside process there are multiple threads
• Process -> memory area -> routines sit inside the process
• Process implemented by the operating system
• Thread challenges - Sharing data
• Mutex / Locks for shared data
• Data Access - Managing Locks / Deadlocks / Starvation / Blocking
• Channels (Go Lang) - Send data between threads
• WaitGroup, Sync.Cond
• Webcrawlers design for parallel processing using threads
• Handling concurrency / multiple parallel threads / optimum network capacity utilization
• Remember doing SSIS ETL parallel tasks for Data pull

A multi-threaded Web crawler implemented in Python
Crawler
Multi-Threaded Crawler in Python

Happy Learning!!!

Data Perspectives

Different perspectives to decide on choosing the right database?

• Strict data types - Schema on write
• Schemaless data - Schema on read
• Read-only immutable data
• Eventually consistent data
• Dirty read vs Committed data
• Multi-version concurrency control
• Replicate data based on logs
• Replay committed logs
• Data sharding
• High reads consistent data - RDBMS
• High writes low reads - HBase, Cassandra
• Document-based storage - Mongodb, Couchdb
• CAP, ACID Properties

Things I Wished More Developers Knew About Databases

Want to Debug Latency?

I wrote an initial draft on the things I wished more developers knew about DBs. It touches a variety of topics: write skews, external consistency, clock skews, database-generated IDs, nested transaction issues, caches & more. Is there anything you wished more devs knew about DBs?

— Jaana Dogan (@rakyll) April 13, 2020

Almost similar and deep-dive techniques from the tweet conversation

• Read heavy vs write heavy. Insert vs updates. Vaccuuming
• Replication or not, transaction logging, why indexes matter, performance tuning, i/o scheduler, unicode, gender isn't binary
• Locks, cache effects, isolation levels
• IO bound vs network bound especially in the situation of replication, scaling strayegy, concurrency vs distributed.
• Materialized views, and the dangers of invalidating them unexpectedly.
• Connection pool, scaling techniques to handle distributed application / system, improve performance, optimization of query etc.
• I'd be interested in how this applies to a distributed system. Concurrency (specifically MVCC), connections, DB threading, backpressure handling
• Disk storage implementation and optimization

Keep Thinking!!! 

Difference between SQL and NOSQL Systems

Reposting from my two-year-old Quora answer

The Key differences between them lies in the understanding CAP theorem

• Consistency
• Availability
• Partition Tolerance

In layman terms. SQL systems ex-RDBMS will adhere ACID properties (Atomicity, Consistency, Isolation, Durability).

• The datatypes, schema are predefined, You cannot store non-matching datatypes
• To avoid dirty data, systems enforce isolation levels that govern only committed data is read (Consistency)
• Only latest records are available, records at that point in time are not available
• Banking Systems, ordering systems where data needs to consistent will be mostly SQL based systems where consistency is important

No-SQL systems (Not Only SQL)

• The schema is not tightly governed, its flexible you can store different datatypes in same columns
• These may be geographically distributed where data may be synced and eventually be consistent end of day not realtime
• They also support point in time data, data values at a point in time can also be looked up
• Where there is no requirement for consistency we can achieve other 2 Availability and partition tolerance
• Since some of the ACID properties are compromised you will have high availability of this systems

It is more to do with business need to decided SQL or SQL based storage.

Happy Learning!!!

Data Modelling for Workloads

• Denormalized way to suit query patterns
• Data Properties - Concurrent writes 
• Metadata - No continuous updates (Just store data, no update involved)
• Indexes - Single Field, Multikey indexes, Text Indexes, Column Store Indexes
• Sharding - Shardkey could be location_id (Distributed Storage)
• Data Partitioning
• Timeseries Info aggregation in storage level (NoSQL)
• JSON Modelling - Collections in MongoDB, Establishing Hierarchy and Relationships

Happy Learning!!!

Day #262 - Data Modeling for Analytics Translators

Summary Notes

• Flexible, Extensible, Governed Effectively
• DW - Staging - RAW - Processing - Consumption
• Data processed multiple times
• Aggregated at the end
• Operational Data Store

Schema on Write

• Write data
• Read data
• Same Schema. Fixed Structure

Schema on Read

• Apply schema when you read
• Write once read many times
• WORM
• Bringing data separated by different business, data, databases

Example

• Data arrived in JSON format
• Add time_stamp to relate data source
• Add Source_system

Canonical Model 

• Repeating data for right reasons
• Enrich with meta_data, canonical elements
• Link Canonical elements, suppliers together, Provide unique_id

Data Governance

• Data problems comes in mass scenarios
• Reports Data Discrepancies
• IT framework to manage Data Governance
• Master Data Management (MDM is a technology which provides a 360 degree view of a user data coming from different sources)
• Data Quality
• Data Archival
• Data Security

MDM

• Source -> ETL (Clean, Standardize, Transform, MDM) -> Reports, DW, EDW
• Rules Based
• Metadata verification
• Data Collection -> ETL -> Data Quality -> MDM -> DW

Data Quality API / Module

• Add / Remove Business Rules
• Field Level Validations against messages
• Return Error codes or log for failures
• Auditing and Reporting failed messages automatically

Happy Learning!!!

Data Warehousing with Amazon Redshift

• Redshift started from Postgres, 4 Years 150+ features added
• Rebuilt and improved version, columnar storage, scale horizontally, OLAP functionality added
• Wrapped in AWS system

The 5 things to look at creating Redshift 

• Step 1 - Find Fact, Dimension Tables and the Record Count
• Step 2 - Analyze and find the query where clause filter columns
• Step 3 - Define the Sort Keys
• Step 4 - Analyze the record counts, data to find out distribution strategy
• Step 5 - Design Distribution Strategy

Summary of Lessons from Link 

Good Tutorial Table Design Tuning - Link

The Where clause columns are the sort keys - (date, partnumber, year etc). Same as TSQL index design Link -

Summary of Lessons and Key Steps

Step #1 - Demo Example Tables and Record Count

LINEORDER - 600,037,902

PART  - 1,400,000

CUSTOMER  - 3,000,000

SUPPLIER  - 1,000,000

DWDATE  - 2,556

Step #2 - Analyze and find the query where clause filter columns, Link

The Join Columns are

LINEORDER PrimaryKey - lo_orderkey, FK - lo_custkey, lo_partkey, lo_suppkey, lo_orderdate, lo_commdate

PART pk - p_partkey, FK- lo_partkey

CUSTOMER pk - c_custkey, FK - lo_custkey

SUPPLIER pk - s_suppkey, FK- lo_suppkey

DWDATE pk - d_datekey, FK - lo_orderdate, lo_commdate

Step #3 - Define the Sort Keys

All the primary and foreign keys have the sort order keys defined. Based on Query Where Clauses define them

Table name Sort Key

LINEORDER lo_orderdate

PART p_partkey

CUSTOMER c_custkey

SUPPLIER s_suppkey

DWDATE d_datekey

Step #4 - Analyze the record counts, data to find out distribution strategy

• The Largest Dimension Table is PART, Each table can have only one distribution key
• LINEORDER is the fact table, and PART is the largest dimension. PART joins LINEORDER on its primary key, p_partkey
• Designate lo_partkey as the distribution key for LINEORDER and p_partkey as the distribution key for PART so that the matching values for the joining keys will be collocated 

Step #5 - Design Distribution Strategy

Based on data define the strategy for data to be collocated and records returned faster.

Table name Distribution Style

LINEORDER lo_partkey

PART p_partkey

CUSTOMER ALL

SUPPLIER ALL

DWDATE ALL

The final update schema is listed in link. Observe the addition of sortkey, distkey mentioned in the DDL

Stored_Procs

Happy Learning new things!!!

Day #142 - CMU Database Systems - 09 Index Concurrency Control

Concurrency Control

• Method to allow concurrent operation of the shared object 
• Ensures Logical and Physical Correctness

Physical Correctness
Latch Modes
Latch Crabbing
Leaf Scans
Delayed Parent Updates

Locks vs Latches
Locks

• Protects the index's logical contents
• Held for transaction duration
• Need to be able to rollback changes

Latches

• Protect the critical sections of index's internal data
• Held for operation duration
• Do not need to be able to rollback changes (Atomic operation)

Locks

• Separates user transactions
• Protects Database contents
• During Entire transaction
• Lock Modes are shared, exclusive, update and intent
• Deadlock by waits-for, timeout, aborts
• Kept in Lock Manager

Latches

• Separate Threads
• Protects in memory data structures
• During Critical sections
• Red / Write Modes
• Kept in the protected data structure
• Multiple threads allowed to read but only one thread to write

B+ Tree Concurrency Control

• Protect threads trying to modify node concurrently
• Latch Coupling / Latch Crabbing - Allow both threads to access / modify at the same time
• Latch is in FIFO order
• Instead of locking Root and creating contention point, Alternate option Optimistic algorithm
• Assumption leaf node will be safe
• Read / write latches compatibility decides how they manage without deadlocks
• Thread that kill itself if prolonged wait / deadlock / starving
• Retry is better than waiting without timeout / Spin for milli second then restart
• Making B+ Tree threadsafe

Highlevel techniques (Acquire latches in same order, Killing and restarting)

• Page Layout
• Data Structure
• STL Iterator
• Latch Grabbing

Happy Learning!!!

Database Sharding and Scalability Basics

Some Key considerations for NOSQL Vs RDBMS

• Performance - Latency tolerance, How slow my queries can run for huge data sets
• Durability - Data loss tolerance when database crashes losing in-memory or Lost transactions tolerance
• Consistency - Weird results tolerance (Dirty data tolerance)
• Availability - Downtime tolerance

Options for Scalability

• Replication - Create copies of database, Application can talk to either database
• Sharding - Sharding choosing a partition key, Key-value store partition based on key
• Caching - Precomputed and stored, Manage cache expiration time and refresh logic

For streaming data we had already discussed Events Hub, Apache kafka. Now we have something called KSQL (Kafka streaming SQL to run on continuous data)

Great Session Talk

 

RDBMS VS NOSQL Considerations, Quick Summary

• Performance - Latency tolerance
• Durability - Data loss tolerance
• Consistency - Weird results tolerance (Dirty data tolerance)
• Availability - Downtime tolerance

Happy Learning!!!

HBASE & Berkeley DB Internals [Good Reads]

• Berkeley DB - Architecture and Implementation Details
• ACID properties of HBase
• Apache HBase Write Path
• Apache HBase Internals: Locking and Multiversion Concurrency Control
• Columnar Storage Query Execution Basics

Happy Reading!!