Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

NoSQL

NoSQL Data models:

  1. key-value 
    1. Aggregate model. 
    2. key or id is used to get the data. 
    3. Lookup is based on the key.  
  2. document
    1. Aggregate model. 
    2. key or id used to get the data. 
    3. Lookup is based on the fields in the aggregate in form of query. Parts of the aggregate are retrieved rather than whole. DB will create indexes on the aggregate. 
      1. People can put ids for lookup.
      2. Mostly the lookup will be on something else and not id. 
      3. Partial lookups are possible. 
  3. column family
    1. Data is organized into columns. 
    2. Useful in case of reads. 
    3. Imposes structure on the aggregate. 
  4. graph

Data distribution Model:
  1. Sharding: Distributes different data across different servers. 
  2. Replication: Copies data across multiple servers. Configurations include: 
    1. Master-Slave: 
      1. Master handles writes and sync with Slave
      2. Slave handles reads
      3. Reduces the chance of update conflicts. 
    2. Peer-to-peer:
      1. Any handle/system will be able to handle the writes. They will sync with each other. 
      2. Avoids single point of failure but will need to take care of conflicts. 
DB scaling Tips & Tricks: 
  1. Don't use shared databases for different applications. Instead use single database for single applications. Then get the data from different applications into Hadoop file system(shared Database). 
  2. Vertical Scaling: It means adding disk space, storage, memory. 
  3. Horizontal Scaling: It means adding more machines/clusters. 
  4. Relational databases can't work on clusters because they communicate to the single disk space. So by desing they will follow vertical scaling. 
    1. For different sets of data, rdbms runs on different servers(effectively sharding the data).
    2. Application layer takes care of which data is stored where. 
    3. RDBMS are usually single server and negotiation of contracts get tedious. 
    4. They are aggregate ignorant. 
  5. Polygot persistence: It means using different data stores in different circumstances.
  6. NoSQL is used as application DB and not integration DB.
  7. Aggregate orientation ensures that minimum number of nodes are queried to get the data. 
    1. It helps to aggregate data together to ensure that similar data lies on single node so querying is easy and optimized. 
    2. Its central to cluster. 
    3. DB will ensure data is stored together on nodes as aggregates.
    4. Manipulation of multiple aggregate need to happen at the application level. 
You might also like:


SQL Basics

Switch Case:
Following is the syntax of switch case in MySql.

Example:
Employee {
       id int,
       name string,
}


  • Employee table has 2 columns called as id and name. 
  • For ex, if you want string 'First' to be displayed for employee with id = 1 & 'Second' to be displayed for employee id = 2 then the query can be written as follows:

SELECT string,
CASE WHEN id = 1 THEN 'First' WHEN id = 2 THEN 'Second' END
From Employee

So every time id=1 is a part of result, it will display 'First'.

Indexing
Indexing could result in increasing the performance of the database. If indexes are not set then it impacts the performance. Here are some best practices of selecting the indexes:

  1. Pick a good clustering key: Not varchar but an int. 
  2. Int is narrow, stable, ever-increasing, and unique.
  3. An index can be added to any column that is being used as foreign key on other table.
  4. Avoid overlapping indexes. 
  5. Avoid indexing on too many columns. 
  6. Have a baseline number for testing. Before adding any columns your queries are taking 10 seconds to provide a response. Now after adding the indexes find out the time it takes to run the same queries. If its less then 10 seconds then you have added indexes on right columns. So observe, measure, and compare the baseline.

How to convert a string to a date: 
Its very common in business world to convert a string to a date for reporting purposes. To achieve those results here is an operation:
STR_TO_DATE('2003/07/09', 'yyyy/mm/dd')
Result: date value of July 9, 2003

Popular Sql Operations:

  1. COALESCE: Returns first non-null value from a list of strings. 
    1. Example 1: Select COALESCE(NULL, 'Interview questions', NULL, 'Product interview questions'); 
      1. Result: Interview questions
    2. Example 2: Select COALESCE(NULL, NULL, NULL, NULL, 'Interview', NULL, 'Product interview questions'); 
      1. Result: Interview
  2. CURRENT_TIMESTAMP: Returns current date and timestamp. "YYYY-MM-DD HH-MM-SS" format if used in a string context
    1. Example: Select CURRENT_TIMESTAMP
      1. Result: 2018-08-13 01:09:13

NoSQL

NoSQL Data models: key-value  Aggregate model.  key or id is used to get the data.  Lookup is based on the key.   document Aggre...