RCFile vs ORC vs Parquet

Row major data:
 Advantages:
  1. Suppose we perform a operation select all columns from a table, then read each row one by one 
  and display the details.
 Disadvantages:
  1. If we select specific columns for a table, then we need to read all columns data and then skip unwanted 
       columns and fetch the only specific 
     column and it will continue same for all other rows. Because of read and skip unwanted data to reach 
     specific column , it will impact the 
     performance.
  2. Also because of different data types in each row, compression also not much efficient.

Column major data:
 Advantages:
  1. Store the data in columnar way. All column 1 for all records are stored together.
  2. Select specific column is vert fast, because of all data related to specific column is stored together.
  3. Storing same type of data together, Compression will be good.
 Disadvantages:
  1. If we want to selecting all columns data , then it is not much efficient

RCfile: Combination of both row major and column major formats. 
  Partition(called row group) the data into row based first, then for each partitions convert 
        into columnar file format. 
  Stored in Binary format.
  Default partition size is 4MB
 Disadvantages:
  1. No Metadata about columns
  2. Many partitions(row group) will be created for larger data sets because of default 
   partition size is 4MB , so sequential will suffer 
  3. Because of no metadata about columns, compression is less efficient

ORC: Similar to RC , ORC also divide row and columnar formats called stripes. Along with data it 
will also create indexes/statistics at stripes level as well as in file footer level.
Statistics like MIN and MAX for each column in data. 

Parquet: It is also a columnar file format, similar to RC and ORC, but parquet stores nested data 
structures in a flat columnar format. 
  Also support very good compression methods.

Kafka - Single broker setup

Apache Kafka

Apache Kafka
  -> Distributed Streaming platform
  -> Publish and subscribe to streams of records, similar to a message queue or enterprise message system
  -> Store streams of records in a fault-tolerant durable way
      - Keeping multiple copies of data - Replication
  -> Replication ( Leader - follower model)
      - One broker is selected as a Leader
      - Producer will connect to Leader and send data
      - Consumer request Leader for required data
      - Depends on Replication factor, Followers will copy data from Leader
  -> Process stream of records as they occur

Key Components in Kafka
  -> Producer : An application that sends a data/message
  -> Message  : Small piece of data( Array of bites)
  -> Consumer : An application that receive data/message
  -> Brokers  : Kafka server
  -> Cluster  : Group of instances and each will act as one broker
  -> Topic    : Unique name to a data stream
  -> Partitions : Distributing topic data into multiple partitions
  -> Offset   : Sequence number given to a message
  -> Consumer Group : Group of Consumers acting as single

Apache Kafka setup

1. Download tar file from apache.org
   curl -k https://www-us.apache.org/dist/kafka/2.3.1/kafka-2.3.1-src.tgz > kafka-2.3.1-src.tgz
   tar -xzf kafka-2.3.1-src.tgz

2. Update server.properties files

   Broker.id=1
   listeners = PLAINTEXT://:9092
   log.dirs=/home/bigdata/opt/kafka/kafka-2.3.1/logs

3. Zookeeper setup
   - Coordination service for a distributed system
 
   wget https://archive.apache.org/dist/zookeeper/zookeeper-3.5.6/apache-zookeeper-3.5.6-bin.tar.gz
   tar -xvf apache-zookeeper-3.5.6-bin.tar.gz

4. Update Zoo.cgf file

   tickTime=200
   dataDir=/home/bigdata/opt/zookeeper/apache-zookeeper-3.5.6/data
   clientPort=2181
   maxClientCnxns=60
   initLimit=10
   syscLimit=5

5. Start Zookeeper services
   
   bin/Zookeeper-server-start.sh config/zookeeper.properties

6. Starting Kafka Broker server
   bin/kafka-server-start.sh config/server.properties

7. Creating a Topic 
   bin/kafka-topics.sh --create --zookeeper localhost:2181 --replication-factor 2 --partition 1 --topic Demo

8. Starting Producer console to publish messages into Topic
   bin/kafka-console-producer.sh --broker-list localhost:9092 --topic Demo

9. Starting Consumer console to consume messages from Topic
   bin/kafka-console-consumer.sh --zookeeper localhost:2181 --topic Demo --from-beginning


Producer - Callback and acknowledge methods

1. Send and Forget
   Send a message to broker and wont check whether the message is delivered successfully or not

2. Synchronous send
   Send a message and wait for response
     Success : we get record metadata object
     Fail : Exception

3. Asynchronous send
   Send a message and provide callback function to receive acknowledge. We can record message details for later analysis.

Apache Kafka

Installing Apache Kafka

Steps to Install Apache Kafka and Testing

1. Download "kafka_2.11-0.10.0.0.tgz" from Apache.

2. Extract files from tgz file
   tar -xvzf kafka_2.11-0.10.0.0.tgz

3. Starting zookeeper server
   bin/zookeeper-server-start.sh config/zookeeper.properties

4. Starting Kafka Broker server
   bin/kafka-server-start.sh config/server.properties

5. Creating a Topic 
   bin/kafka-topics.sh --create --zookeeper localhost:2181 --replication-factor 1 --partition 1 --topic topictest

6. Starting Producer console to publish messages into Topic
   bin/kafka-console-producer.sh --broker-list localhost:9092 --topic topictest

7. Starting Consumer console to consume messages from Topic
   bin/kafka-console-consumer.sh --zookeeper localhost:2181 --topic topictest --from-beginning

Hive - Working with JSON files

Hive has two built-in functions, get_json_object and json_tuple for dealing with JSON. There are also a couple of JSON SerDe's (Serializer/Deserializers)- OPENX, Cloudera and Hcatalog for Hive.

Handling JSON using Build in Functions


{
    "Product": "Laptop",
    "ID": "123456",
    "Address": {
        "BlockNo": 12,
        "Place": "Bangalore"
    }
}


CREATE TABLE json_test ( json string );

LOAD DATA LOCAL INPATH  '/test.json' INTO TABLE json_test;

 Using get_json_Object

select get_json_object(json_test, '$.Product') as Product, 
       get_json_object(json_test, '$.ID') as ID,
       get_json_object(json_test, '$.Address.BlockNo') as BlockNo,
       get_json_object(json_test, '$.Place.Bangalore') as Place
from json_test;

 Using json_tuple

select v.Product, v.ID, v.Address, v.BlockNo 
from json_test test
     LATERAL VIEW json_tuple('Product', 'ID', 'Address', 'Address.BlockNo') v
     as Product, ID, Address, BlockNo;

Handling JSON using SerDe's


Converting JSON schema to Hive table Create Schema
Step 1: 
        Open a file in notepad++
        Replace all " with empty
        Replace all :{ with :struct<
        Replace all :[ with :array<
        Replace all } with >
        Replace all { with struct<
        Replace all ] with >
        Replace all Null with STRING
        Replace all hive keyword ( function, group) with 'function' or 'group'
        Replace all field values to STRING

Step 2: Example JSON file

 {
    "Product": "Laptop",
    "ID": "123456",
    "Address": {
        "BlockNo": 12,
        "Place": "Bangalore"
    }
}

Step 3:Creating Hive Serde table

CREATE TABLE json_serde (
  Product string,
  ID string,
  Address struct
)
ROW FORMAT SERDE 'com.cloudera.hive.serde.JSONSerDe';

Step 4 : LOAD DATA LOCAL INPATH  '/test.json' INTO TABLE json_serde;

Step 5: SELECT Product,ID,Address.BlockNo as BlockNo, Address.Place as location from json_serde;
 

SQL Server : Isolation Levels

ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.

Atomicity is an all-or-none proposition.

Consistency guarantees that a transaction never leaves your database in a half-finished state.

Isolation keeps transactions separated from each other until they’re finished.

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

Types of Isolation Levels 
 The ISO standard defines the following isolation levels, all of which are supported by the SQL Server Database Engine:
 1. Read uncommitted : (the lowest level where transactions are isolated only enough to ensure that 
                 physically corrupt data is not read)
 2. Read committed :  (Database Engine default level)
 3. Repeatable read
 4. Serializable : the highest level, where transactions are completely isolated from one another)
 5. Snapshot : The snapshot isolation level uses row versioning to provide transaction-level 
              read consistency. 
                 Read operations acquire no page or row locks; only SCH-S table locks are acquired. 
              When reading rows modified by another transaction, they retrieve the version of the 
              row that existed when the transaction started. You can only use Snapshot isolation 
              against a database when the ALLOW_SNAPSHOT_ISOLATION database option is set ON.
              By default, this option is set OFF for user databases.

Read phenomena
 The ANSI/ISO standard SQL 92 refers to three different read phenomena.
 Dirty read (uncommitted dependency) occurs when a transaction is allowed to read data from a row 
              that has been modified by another running transaction and not yet committed.
 Non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice 
              and the values within the row differ between reads.
           Non-repeatable reads phenomenon may occur in a lock-based concurrency control method when 
              read locks are not acquired when performing a SELECT, or when the acquired locks on 
              affected rows are released as soon as the SELECT operation is performed. Under the 
              multi-version concurrency control method, non-repeatable reads may occur when the requirement 
              that a transaction affected by a commit conflict must roll back is relaxed.
 Phantom read occurs when, in the course of a transaction, two identical queries are executed, 
              and the collection of rows returned by the second query is different from the first.
   This can occur when range locks are not acquired on performing a SELECT ... WHERE operation. 
          The phantom reads anomaly is a special case of Non-repeatable reads when Transaction 1 repeats 
          a ranged SELECT ... WHERE query and, between both operations, Transaction 2 creates (i.e. INSERT) 
          new rows (in the target table) which fulfill that WHERE clause.
Isolation Levels  vs. Read Phenomena 


  
Locks
  
Isolation Levels
 
  
Dirty reads
  
Non-repeatable reads
  
Phantoms


  
Nolock
  
Read Uncommitted
  
May Occur
  
May Occur
  
May Occur
 


  
Shared Locks
  
Read Committed
  
   --    
  
May Occur
  
May Occur
 


  
Exclusive Range
  
Repeatable Read
  
   --    
  
   --    
  
May Occur
 


  
Exclusive lock on entire table
  
Serializable
  
   --    
  
   --    
  
   --    
 

SQL Server : System Databases

 Every SQL Server relies on FIVE primary system databases, each of which must be present for the server to 
operate effectively. 
 
  1. Master :  The Master database stores basic configuration information for the server. This includes 
information about the file locations of the user databases, as well as logon accounts, server configuration 
settings, and a number of other items such as linked servers and startup stored procedures. 

  2. Model  :  The Model database is a template database that is copied into a new database whenever 
it is created on the instance. Database options set in model will be applied to new databases created on 
the instance, and any objects created in model will be copied over as well.

  3. MSDB   :  The MSDB database is used to support a number of technologies within SQL Server, 
including the SQL Server Agent, SQL Server Management Studio, Database Mail, and Service Broker.  
A great deal of history and metadata information is available in msdb, including the backup and 
restore history for the databases on the server as well as the history for SQL agent jobs. 

  4. TempDB :  The TempDB system databases is a shared temporary storage resource used by a 
number of features of SQL Server, and made available to all users.  Tempdb is used for temporary objects, 
worktables, online index operations, cursors, table variables, and the snapshot isolation version store,
 among other things.  It is recreated every time that the server is restarted, which means that no objects 
in tempdb are permanently stored.
 
  SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:
 
  1. Local Temp Table
  2. Global Temp Table
 
  Local Temp Table
  
        Local temp tables are only available to the current connection for the user; and they are 
        automatically deleted when the user disconnects from instances. Local temporary table name is 
        stared with hash ("#") sign.
 
  Global Temp Table

        Global Temporary tables name starts with a double hash ("##"). Once this table has been created by 
        a connection, like a permanent table it is then available to any user by any connection. 
        It can only be deleted once all connections have been closed.
 
 Table created under tempdb considered as permanent temp table.

 5. Resource :  The Resource database is responsible for physically storing all of the 
SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of 
SQL Server system objects with the ability to overwrite only this database.

 Apart from this 5, other databases also available. 
 ReportServer : Primary database for Reporting Services to store the meta data and object definitions.
 ReportServerTempDB :  Report Server Temp DB.

SQL Server : Database Mirroring

      
 Step 1: Take the Full Backup of Principal database using below backup command.
                
                    BACKUP DATABASE Temp TO DISK = 'C:\Program Files\Microsoft SQL 
                    Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Temp.bak';      
      
 Step 2: Take the Transaction Log Backup of Principal database using below backup command
                 
                  BACKUP LOG Temp TO DISK = 'C:\Program Files\Microsoft SQL 
                        Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Temp.trn';      
      
 Step 3: Copy both backup to Mirror server and  Restore the Full backup database with 
                   RESTORE WITH NORECOVERY MODE in Mirror server using below command

                  RESTORE DATABASE Temp FROM DISK = 'C:\Program Files\Microsoft SQL 
                        Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Temp.bak' with NORECOVERY       
  
      
  Step 4: Restore Transaction Log back up in Mirror server using below command.

                  RESTORE LOG Temp FROM DISK = 'C:\Program Files\Microsoft SQL 
                        Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Temp.trn' with NORECOVERY      
      
 Step 5: Right Click on Principal database (Temp) and choose Properties and then choose Mirror 
                   option in properties list      
      
 Step 6 : Click the "Configure Security" button and then click NEXT if the Configure Database 
                      Mirroring Security Wizard screen appears. 
                 After click on NEXT button,it will ask include Witness server or not, choose YES or NO
                      based on your requirements.      
 
      
 Step 7: Click on NEXT button, It will ask Principal Server instance details along with port number.      
      
 Step 8: Click on NEXT button, It will ask Mirror Server instance details along with port number.      
      
 Step 9: Click on NEXT button, It will ask Service account details.      
      
 Step 10: Click on NEXT button, It will configure Endpoints and Click on OK , Database Properties
                     window will appear , Click on START MIRRORING button.      
        

Some of useful Queries in Morring:

  
 To find Backup History of Database : 
      SELECT S.DATABASE_NAME,M.PHYSICAL_DEVICE_NAME,S.BACKUP_START_DATE
        ,CASE S.[TYPE] WHEN 'D' THEN 'FULL'
		     WHEN 'I' THEN 'DIFFERENTIAL'
                     WHEN 'L' THEN 'TRANSACTION LOG'END AS BACKUPTYPE
        ,S.SERVER_NAME,S.RECOVERY_MODEL
      FROM MSDB.DBO.BACKUPSET S INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY M 
        ON S.MEDIA_SET_ID = M.MEDIA_SET_ID
      WHERE S.DATABASE_NAME = DB_NAME()
      ORDER BY BACKUP_START_DATE DESC
  

  
 To Check the mirroring safety level : 
       SELECT	D.NAME, D.DATABASE_ID, M.MIRRORING_ROLE_DESC,
		M.MIRRORING_STATE_DESC, M.MIRRORING_SAFETY_LEVEL_DESC, 
		M.MIRRORING_PARTNER_NAME, M.MIRRORING_PARTNER_INSTANCE,
		M.MIRRORING_WITNESS_NAME, M.MIRRORING_WITNESS_STATE_DESC
       FROM	SYS.DATABASE_MIRRORING M JOIN SYS.DATABASES D
		ON M.DATABASE_ID = D.DATABASE_ID
       WHERE	MIRRORING_STATE_DESC IS NOT NULL
  
  
To find out about the Mirroring endpoints information
       SELECT E.NAME, E.PROTOCOL_DESC, E.TYPE_DESC, E.ROLE_DESC, E.STATE_DESC, 
		       T.PORT, E.IS_ENCRYPTION_ENABLED, E.ENCRYPTION_ALGORITHM_DESC, 
		       E.CONNECTION_AUTH_DESC 
       FROM   SYS.DATABASE_MIRRORING_ENDPOINTS E JOIN SYS.TCP_ENDPOINTS T
       ON     E.ENDPOINT_ID = T.ENDPOINT_ID 
  
  
 Setup Partner Manually,
       ALTER DATABASE Test SET PARTNER = 'TCP://(Mirror Server Name or IP) :5022' (on Principal side)
       ALTER DATABASE Test SET PARTNER = 'TCP://(Principal Server Name or IP) :5022' (on Mirror side)