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
Friday, July 1, 2016
Apache Kafka
Wednesday, June 22, 2016
Hive - Working with JSON files
)
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;
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;
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 structHandling JSON using SerDe's
Saturday, May 17, 2014
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.
Sunday, May 4, 2014
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)
Tuesday, November 19, 2013
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
Monday, November 18, 2013
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
Subscribe to:
Posts (Atom)
