Python - Converting Rows values to Columns names

	import pandas as pd
    
	InputFile = 'location of txt file' 
	read_file = pd.read_csv(InputFile, sep=r'|', names = ['col1','col2'], skipinitialspace=True).fillna('')
	read_file['sno'] = read_file.groupby(['col1']).cumcount()+1
	df = read_file.pivot(index='sno', columns='col1', values='col2').reset_index()
	df.to_csv('Output Path',index=False)

Spark Core components and Execution Model

The core components of the spark application are:
* Driver
    * When we submit a Spark application in cluster mode using spark-submit, Driver will interact with the Cluster 
    Resource Manager to start the Application Master.
    * It is also convert user code into logical plan (DAG) and then convert to physical plan.
* Application Master
    * Driver request Application Master for executors for executing the user code, application Master will negotiates 
    the resources with the Resource Manager to host these executors.
* Spark Context
    * Driver will create Spark Context for each application and Spark Context is the main entry point for executing 
    any Spark functionality.
* Executors
    * Executors are processes on the worker nodes whose job is to execute the assigned tasks.

The Spark execution model can be defined in three phases: 
* Logical Plan
    * Converting user code into different steps which will be executed when an action is performed.
    * Logical plan will create DAG for how spark will execute all transformation
* Physical Plan
    * Converting Logical plan into Physical plan using Catalyst and Tungsten optimisation techniques.
    * Few methods while choosing/translating best physical plan using Catalyst optimiser.
        * Remove repeated operations(ex. adding of two numbers to perform for each row) 
        * Predicate pushdown : pushes the filters as close as possible to data sources.
        * Column pruning : only select needed columns. 
    * Tungsten: executing query plan on the actual cluster, which generate optimised code based on  query plan that 
    generated by Catalyst Optimiser
* Executions:
    * Physical plan is covered into number of stages and then convert into tasks.
    * Driver request the Cluster Manager and negotiates the resources. Cluster Manager will allocate containers and 
    launches executors on all the allocated containers and assigns tasks to run on behalf of the Driver.
 

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)

  

Commands in HDFS :

           Creating Directory in HDFS
           ->hadoop fs –mkdir /user/systemname/foldername
                       OR
           ->hadoop fs –mkdir foldername

           Delete a Folder in HDFS
           ->hadoop fs –rmr /user/systemname/foldername
                       OR
           ->hadoop fs –rmr foldername
           
           Delete a File in HDFS
           ->hadoop fs –rm /user/systemname/foldername/filename
                       OR
           ->hadoop fs –rm foldername/filename
           
           Copying a file from one location to another location in HDFS
           ->hadoop fs –cp source destination
           
           Moving a File from one location to another location in HDFS
           ->hadoop fs –mv source destination
           
           Putting a file in HDFS
           ->hadoop fs –put /source /destination
                       OR
           ->hadoop fs –copyFromLocal /source /destination
           
           Getting a file in HDFS
           ->hadoop fs –get /source /destination
                       OR
           ->hadoop fs –copyToLocal /source /destination
           
           Getting list of file in HDFS
           ->hadoop fs –ls
           
           Reading content of a file in HDFS
           ->hadoop fs –cat filelocation (head, tail)
           
           Checking HDFS condition
           ->hadoop fsck
           
           Getting Health of Hadoop
           ->sudo –u hdfs hadoop /
           ->sudo –u hdfs hadoop /sourcepath –files –blocks –racks
           
           UnZip a file in Hadoop
           -tar xzvf  filename
           

Hadoop Installation steps on Centos

// Installation Steps in Centos//
// suppose MyClusterOne is masterMyClusterTwo and MyClusterThree are slaves and hdpuser is user in all //

Step 1: Install JAVA & Ecllipse using System->Administratio->add or remove progrm->package  
        collection select needed packages and download.
                      (OR)
    Download latest java package from "http://www.oracle.com/technetwork/java/javase/downloads/....."

    1)cd /opt/jdk1.7.0_40
    2)tar -xzf /home/hdpuser/Downloads/jdk-7u40-linux-x64.tar.gz
    3)alternatives --install /usr/bin/java java /opt/jdk1.7.0_40/bin/java 2
    4)alternatives --config java

Step 2: Configure Environment variables for JAVA
    # export JAVA_HOME=/opt/jdk1.7.0_40
    # export JRE_HOME=/opt/jdk1.7.0_40/jre
    # export PATH=$PATH:/opt/jdk1.7.0_40/bin:/opt/jdk1.7.0_40/jre/bin

Step 3: Steps to Give SUDO permission to User:
    1) Go to Terminal and Type "su -" , it will connect to root and enter root password.
    2) Type "visudo" , it will show "sudoer" file and enter "i" to edit the file. 
    3) Add user details after line
        ## Allow root to run any commands anywhere 
        Ex : hdpuser    ALL=(ALL)    ALL
    4) Add Password permission details after line
        ## Same thing without a password
        Ex : hdpuser    ALL=(ALL)    NOPASSWD: ALL
    5) Press Esc, and Enter ":x" to save and exits.

Step 4: Create User ( We can also use existing user)
    1) Sudo Useradd hdpuser
    2) Sudo passwd hdpuser , Enter New password.

Step 5: Edit Host file
    1) open /etc/hosts 
    2) Enter Master and slave node IP addresses in Node ( Master and Slves)
        xxx.xx.xx.xxx    MyClusterOne
        xxx.xx.xx.xxx    MyClusterTwo ....

Step 6: Configuring Key Based Login
    1)Type "su - hdpuser"
    2)Type "ssh-keygen -t rsa"
    3)Type "sudo service sshd restart" to restart the service
    4)Type "ssh-copy-id -i ~/.ssh/id_rsa.pub hdpuser@MyClusterOne"
    5)Type "ssh-copy-id -i ~/.ssh/id_rsa.pub hdpuser@MyClusterTwo"
    6)Type "ssh-copy-id -i ~/.ssh/id_rsa.pub hdpuser@MyClusterThree" 
           ( Add details of all slavenodes one by one)    
           ( if any connecting error,then need to start sshd service in all slaves)
    7)Type "chmod 0600 ~/.ssh/authorized_keys"
    8)Exit
    9)ssh-add

Step 7: Download and Extract Hadoop Source
    1) cd /opt/hadoop-1.2.1/
    3) sudo wget http://apache.mesi.com.ar/hadoop/common/hadoop-1.2.1/hadoop-1.2.1.tar.gz
    4) sudo tar -xzvf hadoop-1.2.1.tar.gz 
    5) sudo chown -R hdpuser /opt/hadoop-1.2.1
    6) cd /opt/hadoop-1.2.1/conf

Step 8: Edit configuration files
    1) gedit conf/core-site.xml  ( Master Node Details)
    
       <configuration>
        <property>
            <name>fs.default.name</name>
            <value>hdfs://MyClusterOne:9000/</value>
        </property>
        <property>
            <name>dfs.permissions</name>
            <value>false</value>
        </property>
        </configuration>

    2) gedit conf/hdfs-site.xml     ( Master Node Details)
        <configuration>
        <property>
            <name>dfs.data.dir</name>
            <value>/opt/hadoop-1.2.1/dfs/name/data</value>
            <final>true</final>
        </property>
    
        <property>
            <name>dfs.name.dir</name>
            <value>/opt/hadoop-1.2.1/dfs/name</value>
            <final>true</final>
        </property>
        <property>
            <name>dfs.replication</name>
            <value>2</value>
        </property>
        </configuration>
    3) gedit conf/mapred-site.xml
        <configuration>
        <property>
            <name>mapred.job.tracker</name>
            <value>MyClusterOne:9001</value>
        </property>
        </configuration>
    
    4) gedit conf/hadoop-env.sh
        export JAVA_HOME=/opt/jdk1.7.0_40
        export HADOOP_OPTS=-Djava.net.preferIPv4Stack=true
        export HADOOP_CONF_DIR=/opt/hadoop-1.2.1/conf

Step 9: Copy Hadoop Source to Slave Servers 
    1)su - hdpuser
    2)cd /opt/hadoop-1.2.1
    3)scp -r hdpuser MyClusterTwo:/opt/hadoop-1.2.1
    4)scp -r hdpuser MyClusterThree:/opt/hadoop-1.2.1 ....

Step 10 : Configure Hadoop on Master Server Only
    1) su - hdpuser
    2) cd /opt/hadoop-1.2.1
    3) gedit
         conf/masters ( Add Master Node Name)
        MyClusterOne
    4) gedit conf/slaves ( Add Slave Node Names)
        MyClusterTwo
        MyClusterThree

Step 11 : To Communicate with Slave, Firewall need to be OFF
    1)/etc/init.d/sudo service iptables save
    2 )/etc/init.d/sudo service iptables stop
    3) /etc/init.d/sudo chkconfig iptables off
Step 12: To use All system space or NameNode backup 
    1) edit Core-site.xml and add any Folder in /Home/
    2) we should give permission to that created 
     sudo chmod 755 /home/folderrname
Step 13: Add hadoop Path details in hadoop.sh
    1) gedit /etc/profile.d/hadoop.sh

Step 12: Format Name Node on Hadoop Master only
    1) su - hdpuser
    2) cd /opt/hadoop-1.2.1
    3) bin/hadoop namenode -format

Step 13 : Start Hadoop Services
    1) bin/start-all.sh

Comma Separated values

-- Sample Table 

create table #Emp(Name varchar(10),Skills varchar(max))

insert into #Emp values('Ramesh','Hadoop,SQL,DBA')
insert into #Emp values('Arjun','SQL,MSBI')
insert into #Emp values('Mohan','Java')

select * from #Emp

Name  Skills
---------------------------
Ramesh  Hadoop,SQL,DBA
Arjun  SQL,MSBI
Mohan  Java

-- Code to Display below output

Name  Skill
---------------------
Arjun  MSBI
Arjun  SQL
Mohan  Java
Ramesh  DBA
Ramesh  Hadoop
Ramesh  SQL


SELECT DISTINCT Name,LTRIM(RTRIM(i.value('.', 'VARCHAR(MAX)'))) AS SKILL into #sample
FROM
(
SELECT Name,Skills
,CAST('' + REPLACE(Skills, ',', '') + '' AS XML) AS Des
FROM #Emp
) List
CROSS APPLY Des.nodes('//i') x(i)


select * from #sample 

-- for the above output, Code to Display below output

Name  Skills
---------------------------
Ramesh  Hadoop,SQL,DBA
Arjun  SQL,MSBI
Mohan  Java

SELECT Distinct Name
,STUFF((SELECT ','+SKILL FROM #sample f where f.Name=s.Name FOR XML PATH('')),1,1,'') AS Skills
from #sample s

Differences....

Differences Between Funtion and Procedure

• Procedure can return zero or n values whereas function can return one value which is mandatory.
• Procedures can have input/output parameters for it whereas functions can have only input parameters.
• Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
• functions can be called from Procedure whereas Procedures cannot be called from function.
• Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a function.
• We can go for transaction management in Procedure whereas we can't go in function.
• Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
• UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored Procedures cannot be.
• UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
• Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

Differences Between Char , Varchar and Nvarchar

• Char datatype is used to store fixed length of characters. For example , if we declared char(10) it will allocates memory for 10 characters. Once we insert only 6 characters of word then only 6 characters of memory will be used and other 4 characters of memory will be wasted.
• Varchar means variable characters and it is used to store non-unicode characters. It will allocate the memory based on number characters inserted. For example, if we declared varchar(10) it will allocates memory of 0 characters at the time of declaration and Once we insert only 6 characters of word it will allocate memory for only 6 characters.
• Nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.

Differences Between Primary Key and Unique Key

• Primary Key creates clustered Index by default , where as Unique Key Create NoN-Clustered Index
• Primary Key doesn't allow NULLs , where as Unique Key allows only one NULL value

Differences Between Delete ,Drop and Truncate

DELETE

• Delete is a DML Command
• We can use where clause to filter data, It deletes specified data if where condition exists
• DELETE statement is executed using a row lock, each row in the table is locked for deletion
• DELETE retain the identity
• Delete activates a trigger because the operation are logged individually
• Slower than truncate because, it keeps logs
• Rollback is possible

DROP

• DROP is a DDL Command
• Removes all rows and also the table definition, including indexes, triggers, grants, storage parameters
• No filter criteria allowed, removes all rows and No triggers fired
• DROP ANY privilege on a specific table cannot be granted to another user or role.
• Drop operation cannot be rolled back in any way as it is an auto committed , while statementDelete operation can be rolled back and it is not auto committed.

TRUNCATE

• TRUNCATE is a DDL command
• It Removes all the data
• TRUNCATE TABLE always locks the table and page but not each row
• If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column
• TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions
• Faster in performance wise, because it doesn't keep any logs
• Rollback is possible with TRANSACTION

SQL Server Joins......

Steps to Access a Database which is in Inaccess mode

Steps to Remove Database Inaccess Mode

select databaseproperty('irisdb_admin','isShutdown')

alter database irisdb_admin set offline

alter database irisdb_admin set online

Find table being locked in SQL Server 2008/2005

Find table being locked in SQL Server 2008/2005

SQL Scripts to find Locked table in SQL Server 2005/2008

Script for the list of tables which are being locked :

select object_name(resource_associated_entity_id) as 'TableName',*
     from sys.dm_tran_locks where resource_type = 'OBJECT'
     and resource_database_id = DB_ID()

Script for list of tables with schema mode change lock :

select object_name(resource_associated_entity_id) as 'TableName',*
     from sys.dm_tran_locks where resource_type = 'OBJECT'
     and resource_database_id = DB_ID()
     and request_mode = 'Sch-M'

Snapshot vs Log Shipping vs Mirroring vs Replication vs Failover Clustering

--->Snapshot : A Database Snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and can always reside on the same server instance as the database. Each database snapshot is consistent, in terms of transactions, with the source database as of the moment of the snapshot’s creation.

--->Log Shipping : Log Shipping is an old technique available since SQL SERVER 2000. Here the transactional log (ldf) is transferred periodically to the standby server. If the active server goes down, the stand by server can be brought up by restoring all shipped logs.

--->Mirroring : Mirroring which was introduced with 2005 edition, works on top of Log Shipping. Main difference is the uptime for the standby server is quite less in mirroring. Standby server automatically becomes active in this case (through help of a broker server which is called as Witness in SQL SERVER parlance), without having to restore logs. Additional advantages of Mirroring include support at .NET Framework level plus some new features like Auto Page Recovery introduced with SQL SERVER 2008.

--->Replication : Replication is the process of copying data between two databases on the same server or different servers on the same database. This is one of the methods to maintain the redundant database site for disaster recovery purpose. Replication methodology maintains copies of main database from the primary server on the secondary sever, both of the databases will be in synchronization state and this technology is used to provide high availability.

--->Snapshot Replication: Snapshot replication is initiated at firs by all types of replication to initialize subscribers. In this method of replication, entire data will be captured and sent to the subscriber. What happens in this replication is, all the changes that occur in the publisher will be captured and then later sent to the subscriber when the snapshot agent runs again. Snapshot agent and distribution agents are used in the snapshot replication.
--->Merge Replication: Merge replication is the process of distributing data from publisher to the subscriber. In the merge replication, updates are allowed even when subscriber or publisher are connected or disconnected. If they are disconnected, the updates will be applied when both get connected. Merge agent and snapshot agent is used in merge replication. Merge replication is the most difficult replication method to implement and manage.
--->Transactional Replication: In transactional replication, all changes made in the publisher are captured and stored in the distribution database, and then these changes are transferred to the subscriber database. The best situation to use transactional replication is when data changes frequently and data is transferred to the subscriber immediately as in almost real time scenario.
--->Peer-Peer Replication: Peer-Peer replication is built on the foundation of the transactional replication. This replication method is introduced in SQL Server 2005. This replication is available only in SQL Server Enterprise Edition. Peer-to-Peer replication provides high availability solution by providing multiple copies of databases across multiple instances.

---> Failover Clustering : Failover Clustering is a high availability option only (unlike others above which can be used for disaster recovery as well) used with clustering technology provided by hardware + OS. Here the data / databases don’t belong to either of servers, and in fact reside on shared external storage like SAN. Advantages of a SAN storage is large efficient hot pluggable disk storage. You might see DR options like Mirroring used quite frequently with failover clustering. Here’s a good article on adding geo redundancy to a failover cluster setup.

SQL SERVER – Best Practices for Query Optimization

--> Table should have primary key
--> Table should have minimum of one clustered index
--> Table should have appropriate amount of non-clustered index
--> Non-clustered index should be created on columns of table based on query which is running
--> Following priority order should be followed when any index is created
a) WHERE clause
b) JOIN clause
c) ORDER BY clause
d) SELECT clause
--> Do not to use Views or replace views with original source table
--> Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
--> Remove any adhoc queries and use Stored Procedure instead
--> Check if there is atleast 30% HHD is empty – it improves the performance a bit
--> If possible move the logic of UDF to SP as well
--> Remove * from SELECT and use columns which are only necessary in code
--> Remove any unnecessary joins from table
--> If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)

Style of using @variable and IN operator

Style of Using @variable and IN Operator

Using Of @variable and IN Operator in single statment

create table test (companyid int, title varchar(100), description varchar(100), keywords varchar(100))

insert into test values(1,'company a','company a','aa')

insert into test values(1,'company b','company b','bb')

insert into test values(1,'company c','company c','cc')

insert into test values(1,'company d','company d','dd')



declare @keywords varchar(100)

set @keywords = 'aa,bb,cc,dd';



WITH CTE AS (

SELECT

CAST('' + REPLACE(@keywords, ',', '') + '' AS XML) AS Keywords

)



select * from test where keywords in (

SELECT RTRIM(LTRIM(ExtractedCompanyCode.X.value('.', 'VARCHAR(256)'))) AS Keywords

FROM CTE

CROSS APPLY Keywords.nodes('//I') ExtractedCompanyCode(X)

)

Common Table Expressions (CTE) :-

Common Tabel Expressions (CTE)

Common Table Expressions (CTEs) were one of the most exciting features to be introduced with SQL

Server 2005

A CTE is a "temporary result set" that exists only within the scope of a single SQL statement. It

allows access to functionality within that single SQL statement that was previously only available

through use of functions, temp tables, cursors, and so on...

CTE basics:-

The concept behind a CTE is simplicity itself. Consider the following statement:

with MyCTE(x)

as

(select x='hello')

select x from MyCTE

This defines a CTE called MyCTE. In brackets after the as keyword is the query that defines the CTE.

The subsequent query references our CTE, in this case simply returning the string "hello".

Like a derived table, a CTE lasts only for the duration of a query but, in contrast to a derived

table, a CTE can be referenced multiple times in the same query. So, we now we have a way of

calculating percentages and performing arithmetic using aggregates without repeating queries or using

a temp table:

with MyCTE(x)

as

(select top 10 x = id from sysobjects)

select x, maxx = (select max(x) from MyCTE), pct =100.0 * x / (select sum(x) from MyCTE)

from MyCTE

This returns (on my system):

	x           maxx         pct 

	4           2137058649   2.515723270440

	5           2137058649   3.144654088050

	7           2137058649   4.402515723270

	8           2137058649   5.031446540880

	13          2137058649   8.176100628930

	15          2137058649   9.433962264150

	25          2137058649   15.723270440251

	26          2137058649   16.352201257861

	27          2137058649   16.981132075471

	29          2137058649   18.238993710691

CTE and recursion:-

More interesting, in my opinion, is the use of recursion with CTEs. The table defined in the CTE

can be referenced in the CTE itself to give a recursive expression, using union all:

with MyCTE(x)

as
(

select x = convert(varchar(8000),'hello')

union all

select x + 'a' from MyCTE where len(x) < 100

)

select x from MyCTE

order by x

The query:

select x = convert(varchar(8000),'hello')

is called the anchor member. This is executed in the first pass and will populate the CTE with the

result, in this case hello. This initial CTE is repeatedly executed until the complete result set is

returned. The next entry:

select x + 'a' from MyCTE where len(x) < 100

is a recursive member as it references the CTE, MyCTE. The recursive member is executed with the

anchor member output to give helloa. The next pass takes helloa as input and returns helloaa, and so

on so that we arrive at a CTE populated with rows as follows:

hello

helloa

helloaa

helloaaa

helloaaaa

….........