I’ve written a book “Instant Apache Sqoop”

I recently finished writing a book Instant Apache Sqoop. Apache Sqoop is a tool which is designed for moving data between Hadoop Ecosystem and structured data stores such as relational databases.

Instant Apache Sqoop covers following topics:

·         Working with the import process (Intermediate)

·         Incremental import (Simple)

·         Populating the HBase table (Simple)

·         Importing data into HBase (Intermediate)

·         Populating the Hive table (Simple)

·         Importing data into Hive (Simple)

·         The exporting process (Intermediate)

·         Exporting data from Hive (Simple)

·         Using Sqoop connectors (Advanced)

• Working with the import process (Intermediate)
• Incremental import (Simple)
• Populating the HBase table (Simple)
• Importing data into HBase (Intermediate)
• Populating the Hive table (Simple)
• Importing data into Hive (Simple)
• The exporting process (Intermediate)
• Exporting data from Hive (Simple)
• Using Sqoop connectors (Advanced)
• Working with the import process (Intermediate)
• Incremental import (Simple)
• Populating the HBase table (Simple)
• Importing data into HBase (Intermediate)
• Populating the Hive table (Simple)
• Importing data into Hive (Simple)
• The exporting process (Intermediate)
• Exporting data from Hive (Simple)
• Using Sqoop connectors (Advanced)

• Working with the import process

• Incremental import

• Populating the HBase table

• Importing data into HBase

• Populating the Hive table

• Importing data into Hive

• Working with the export process

• Exporting data from Hive

• Using Sqoop connectors

Instant Apache Sqoop is a practical, hands-on guide that provides you with a number of clear, step-by-step exercises that will help you to take advantage of the real power of Apache Sqoop and give you a good grounding in the knowledge required to transfer data between RDBMS and the Hadoop ecosystem.

Sqoop export and import commands

Sqoop Import Examples:

Sqoop Import :- Import data from a relational database management system (RDBMS) such as MySQL or Oracle into the Hadoop Distributed File System (HDFS) and its subprojects (Hive, HBase).

Import the data (MySQL table) to HBase:

Case 1: If table have primary key and import all the column of MySQL table into HBase table.

$ bin/sqoop import --connect jdbc:mysql://localhost/db1 --username root --password root --table tableName --hbase-table hbase_tableName  --column-family hbase_table_col1 --hbase-create-table

Case 2: If table have primary key and import only few columns of MySQL table into HBase table.  

$ bin/sqoop import --connect jdbc:mysql://localhost/db1 --username root --password root --table tableName --hbase-table hbase_tableName --columns column1,column2 --column-family hbase_table_col1 --hbase-create-table

Note : Column names specified in --columns attribute must contain the primary key column.

Case 3: If table doesn't have primary key then choose one column as a hbase-row-key. Import all the column of MySQL table into HBase table.

$ bin/sqoop import --connect jdbc:mysql://localhost/db1 --username root --password root --table tableName --hbase-table hbase_tableName --column-family hbase_table_col1 --hbase-row-key column1 --hbase-create-table

Case 4: If table doesn't have primary key then choose one column as a hbase-row-key. Import only few columns of MySQL table into HBase table.

$ bin/sqoop import --connect jdbc:mysql://localhost/db1 --username root --password root --table tableName --hbase-table hbase_tableName --columns column1,column2 --column-family hbase_table_col --hbase-row-key column1 --hbase-create-table 

Note: Column name specified in hbase-row-key atribute must be in columns list. Otherwise command will execute successfully but no records are inserted into hbase.

Note : The value of primary key column or column specified in --hbase-row-key attribute become the HBase row value. If MySQL table doesn't have primary key or column specified in --hbase-row-key attribute doesn't have unique value then there is a lost of few records.

Example : Let us consider a MySQL table test_table which have two columns name,address. The table test_table doesn't have primary key or unique key column.

Records of test_table:
________________
name    address
----------------
abc    123
sqw    345
abc    125
sdf    1234
aql    23dw

Run the following command to import test_table data into HBase:

$ bin/sqoop import --connect jdbc:mysql://localhost/db1 --username root --password root --table test_table --hbase-table hbase_test_table --column-family test_table_col1 --hbase-row-key name --hbase-create-table

Only 4 records are visible into HBase table instead of 5. In above example two rows have same value 'abc' of name column and value of this column is used as a HBase row key value. If record having value 'abc' of name column come then thoes record will inserted into HBase table. Next time, another record having the same value 'abc' of name column come then thoes column will overwrite the value previous column.

Above problem also occured if table have composite primary key because the one column from composite key is used as a HBase row key.

Import the data (MySQL table) to Hive

Case 1: Import MySQL table into Hive if table have primary key.

bin/sqoop-import  --connect jdbc:mysql://localhost:3306/db1 -username root -password password --table tableName  --hive-table tableName --create-hive-table --hive-import --hive-home path/to/hive_home

Case 2: Import MySQL table into Hive if table doesn't have primary key.

$ bin/sqoop-import  --connect jdbc:mysql://localhost:3306/db1 -username root -password password --table tableName  --hive-table tableName --create-hive-table --hive-import --hive-home path/to/hive_home --split-by column_name

or

$ bin/sqoop-import  --connect jdbc:mysql://localhost:3306/db1 -username root -password password --table tableName  --hive-table tableName --create-hive-table --hive-import --hive-home path/to/hive_home -m 1

Import the data (MySQL table) to HDFS

Case 1: Import MySQL table into HDFS if table have primary key.

$ bin/sqoop import -connect jdbc:mysql://localhost:3306/db1 -username root -password password --table tableName --target-dir /user/ankit/tableName

Case 2: Import MySQL table into HDFS if table doesn't have primary key.

$ bin/sqoop import -connect jdbc:mysql://localhost:3306/db1 -username root -password password --table tableName --target-dir /user/ankit/tableName  -m 1



Sqoop Export Examples:

Sqoop Export: export the HDFS and its subproject (Hive, HBase) data back into an RDBMS. 

Export Hive table back to an RDBMS:

By default, Hive will stored data using ^A as a field delimiter and \n as a row delimiter.

$ bin/sqoop export --connect jdbc:mysql://localhost/test_db --table tableName  --export-dir /user/hive/warehouse/tableName --username root --password password -m 1 --input-fields-terminated-by '\001'

where '\001' is octal representation of ^A.

Error that occured in Hadoop and its sub-projects

1. OOZIE job failed:

Error message : ERROR is considered as FAILED for SLA
   

Cause 1 : Not able to find hadoop namenode (master), jobtracker machine.

Suppose you are running oozie, hadoop-master and job tracker on one machine  and datanode, tasktracker are running on another machine.

Your job.properties file contains following lines:

        nameNode=hdfs://localhost:9000
        jobTracker=localhost:9001

   

In above case, FS action will work fine because no map-reduce opertion is perform in FS action case. But, if you run map-reduce action then tasktracker will look hadoop-master on localhost machine becuase we have used localhost:9000 in job.properties file.

   
Solution : Used  IP of hadoop-namenode and jobtracker machine in job.properties file instead of localhost.   
   
Cause 2 : Oozie not able to find Mysql server.
Suppose I am using mysql as a metastore for hive.
Hive hive-default.xml file have following lines :
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
       
Solution : Use IP of mysql machine instead of localhost. 


2. Zookeeper server not running:
Error message: Could not find my address: zk-serevr1 in list of ZooKeeper quorum servers
   
Causes :
HBase tries to start a ZK server on some machine but that machine isn't able to find itself in the hbase.zookeeper.quorum configuration. This is a name lookup problem. 

Solution:   
Use the hostname presented in the error message instead of the value you used (zk-server1). If you have a DNS server, you can set hbase.zookeeper.dns.interface and hbase.zookeeper.dns.nameserver in hbase-site.xml to make sure it resolves to the correct FQDN.

3. Hadoop-datanode job failed or datanode not running: java.io.IOException: File ../mapred/system/jobtracker.info could only be replicated to 0 nodes, instead of 1
   
Cause 1: Make sure atleast one datanode is running.

Cause 2: namespaceID of master and slaves machines are not same.
If you see the error java.io.IOException: Incompatible namespaceIDs in the logs of a datanode , chances are you are affected by bug HADOOP-1212 (well, I’ve been affected by it at least).
           
Solution :               
If namespaceID of master and slaves machines are not same. Than replace the namespaceID of slaves machine with master namespaceID.
- dfs/name/current/VERSION file contains the namespaceID of master machine
- dfs/data/current/VERSION file contains the namespaceID of master machine
        
Cause 3: Datanode instance running out of space.
Solution : Free some space.

Cause 4 : You may also get this message due to permissions. May be JobTracker can not create jobtracker.info on startup.

4.    Sqoop export command failed:
Error message:
attempt_201101151840_1006_m_000001_0, Status : FAILED
java.util.NoSuchElementException
at java.util.AbstractList$Itr.next(AbstractList.java:350)
at impressions_by_zip.__loadFromFields(impressions_by_zip.java:159)
at impressions_by_zip.parse(impressions_by_zip.java:108)

   
Cause : Given field separator is not valid
Solution : Specify correct field delimeter in sqoop export command.

5. HBase regionserver not running :

Error message: 2012-01-02 13:48:49,973 FATAL org.apache.hadoop.hbase.regionserver.HRegionServer: Master rejected startup because clock is out of sync
org.apache.hadoop.hbase.ClockOutOfSyncException: org.apache.hadoop.hbase.ClockOutOfSyncException: Server hadoop-datanode2,60020,1325492317440 has been rejected; Reported time is too far out of sync with master.  Time difference of 206141ms > max allowed of 30000ms

Solution: Clock of regionservers are not sync with master machine. Synchronized the clock of hbase master and regionserver machines.

Installation of HBase in the cluster - A complete step by step tutorial

HBase cluster setup :

HBase is an open-source, distributed, versioned, column-oriented store modeled after Google 'Bigtable’.

This tutorial will describe how to setup and run Hbase cluster, with not too much explanation about hbase. There are a number of articles where the Hbase are described in details.

We will build hbase cluster using three Ubuntu machine in this tutorial.

A distributed HBase depends on a running ZooKeeper cluster. All participating nodes and clients need to be able to get to the running ZooKeeper cluster. HBase by default manages a ZooKeeper cluster for you, or you can manage it on your own and point HBase to it. In our case, we are using default ZooKeeper cluster, which is manage by Hbase

Following are the capacities in which nodes may act in our cluster:

1. Hbase Master:- The HbaseMaster is responsible for assigning regions to HbaseRegionserver, monitors the health of each HbaseRegionserver.

2. Zookeeper: - For any distributed application, ZooKeeper is a centralized service for maintaining configuration information, naming, providing distributed synchronization, and providing group services.

3. Hbase Regionserver:- The HbaseRegionserver is responsible for handling client read and write requests. It communicates with the Hbasemaster to get a list of regions to serve and to tell the master that it is alive.

In our case, one machine in the cluster is designated as Hbase master and Zookeeper. The rest of machine in the cluster act as a Regionserver.

Before we start:

Before we start configure HBase, you need to have a running Hadoop cluster, which will be the storage for hbase(Hbase store data in Hadoop Distributed File System). Please refere to Installing Hadoop in the cluster - A complete step by step tutorial post before continuing.

INSTALLING AND CONFIGURING HBASE MASTER

1. Download hbase-0.20.6.tar.gz from http://www.apache.org/dyn/closer.cgi/hbase/ and extract to some path in your computer. Now I am calling hbase installation root as $HBASE_INSTALL_DIR.

2. Edit the file /etc/hosts on the master machine and add the following lines.

                192.168.41.53 hbase-master       hadoop-namenode 
                #Hbase Master and Hadoop Namenode is configure on same machine

                192.168.41.67 hbase-regionserver1        

                192.168.41.67 hbase-regionserver2

Note: Run the command “ping hbase-master”. This command is run to check whether the hbase-master machine ip is being resolved to actual ip not localhost ip.

3. We have needed to configure password less login from hbase-master to all regionserver machines.

                2.1. Execute the following commands on hbase-master machine.

                $ssh-keygen -t rsa

                $scp .ssh/id_rsa.pub ilab@hbase-regionserver1:~ilab/.ssh/authorized_keys

                $scp .ssh/id_rsa.pub ilab@hbase-regionserver2:~ilab/.ssh/authorized_keys

4. Open the file $HBASE_INSTALL_DIR/conf/hbase-env.sh and set the $JAVA_HOME.

export JAVA_HOME=/user/lib/jvm/java-6-sun

Note:  If you are using open jdk , then give the path of open jdk.

5. Open the file $HBASE_INSTALL_DIR/conf/hbase-site.xml and add the following properties.

    <?xml version="1.0"?>

    <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

    <configuration>

                <property>

                                <name>hbase.master</name>

                                <value>hbase-master:60000</value>

                                <description>The host and port that the HBase master runs at.

                                                     A value of 'local' runs the master and a regionserver
                                                     in a single process.

                                </description>

                </property>

                <property>

                                <name>hbase.rootdir</name>

                                <value>hdfs://hadoop-namenode:9000/hbase</value>

                                <description>The directory shared by region servers.</description>

                </property>

       

<property>

                                <name>hbase.cluster.distributed</name>

                                <value>true</value>

                                <description>The mode the cluster will be in. Possible values are

                                false: standalone and pseudo-distributed setups with managed

                                Zookeeper true: fully-distributed with unmanaged Zookeeper

                                Quorum (see hbase-env.sh)

                                </description>

                </property>

                <property>

                                <name>hbase.zookeeper.property.clientPort</name>

                                <value>2222</value>

                                <description>Property from ZooKeeper's config zoo.cfg.

                                The port at which the clients will connect.

                                </description>

                </property>

                <property>

                <name>hbase.zookeeper.quorum</name>

                <value>hbase-master</value>

                <description>Comma separated list of servers in the ZooKeeper Quorum.

                                     For example,
                                     "host1.mydomain.com,host2.mydomain.com".

                                     By default this is set to localhost for local and
                                     pseudo-distributed modes of operation. For a
                                     fully-distributed setup, this should be set to a full

                                     list of ZooKeeper quorum servers. If
                                     HBASE_MANAGES_ZK is set in hbase-env.sh
                                     this is the list of servers which we will start/stop
                                     ZooKeeper on.

                </description>

                </property>

    </configuration>

Note:-

In our case, Zookeeper and hbase master both are running in same machine.

6. Open the file $HBASE_INSTALL_DIR/conf/hbase-env.sh and uncomment the following line:

                export HBASE_MANAGES_ZK=true        

7. Open the file $HBASE_INSTALL_DIR/conf/regionservers and add all the regionserver machine names.

    hbase-regionserver1

    hbase-regionserver2

    hbase-master

Note: Add hbase-master machine name only if you are running a regionserver on hbase-master machine.

INSTALLING AND CONFIGURING HBASE REGIONSERVER

1. Download hbase-0.20.6.tar.gz from http://www.apache.org/dyn/closer.cgi/hbase/ and extract to some path in your computer. Now I am calling hbase installation root as $HBASE_INSTALL_DIR.

2. Edit the file /etc/hosts on the hbase-regionserver machine and add the following lines.

                192.168.41.53 hbase-master       hadoop-namenode

Note: In my case, Hbase-master and hadoop-namenode are running on same machine.

Note: Run the command “ping hbase-master”. This command is run to check whether the hbase-master machine ip is being resolved to actual ip not localhost ip.

3.We have needed to configure password less login from hbase-regionserver to hbase-master machine.

                2.1. Execute the following commands on hbase-server machine.

                $ssh-keygen -t rsa

                $scp .ssh/id_rsa.pub ilab@hbase-master:~ilab/.ssh/authorized_keys2

               

4. Open the file $HBASE_INSTALL_DIR/conf/hbase-env.sh and set the $JAVA_HOME.

export JAVA_HOME=/user/lib/jvm/java-6-sun

Note:  If you are using open jdk , then give the path of open jdk.

5. Open the file $HBASE_INSTALL_DIR/conf/hbase-site.xml and add the following properties.

    <?xml version="1.0"?>

    <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

    <configuration>

                <property>

                                <name>hbase.master</name>

                                <value>hbase-master:60000</value>

                                <description>The host and port that the HBase master runs at.

                                                     A value of 'local' runs the master and a regionserver
                                                     in a single process.

                                </description>

                </property>

                <property>

                                <name>hbase.rootdir</name>

                                <value>hdfs://hadoop-namenode:9000/hbase</value>

                                <description>The directory shared by region servers.</description>

                </property>

                <property>

                                <name>hbase.cluster.distributed</name>

                                <value>true</value>

                                <description>The mode the cluster will be in. Possible values are

                                false: standalone and pseudo-distributed setups with managed

                                Zookeeper true: fully-distributed with unmanaged Zookeeper

                                Quorum (see hbase-env.sh)

                                </description>

                </property>

                <property>

                                <name>hbase.zookeeper.property.clientPort</name>

                                <value>2222</value>

                <description>Property from ZooKeeper's config zoo.cfg.

                                The port at which the clients will connect.

                                </description>

                </property>

                <property>

                <name>hbase.zookeeper.quorum</name>

                <value>hbase-master</value>

                <description>Comma separated list of servers in the ZooKeeper Quorum.

                                For example, "host1.mydomain.com,host2.mydomain.com".

                                By default this is set to localhost for local and
                                pseudo-distributed modes of operation. For a fully-distributed
                                setup, this should be set to a ful list of ZooKeeper quorum
                                servers. If HBASE_MANAGES_ZK is set in hbase-env.sh

                                this is the list of servers which we will start/stop ZooKeeper on.

                 </description>

                 </property>

    </configuration>

6. Open the file $HBASE_INSTALL_DIR/conf/hbase-env.sh and uncomment the following line:

                export HBASE_MANAGES_ZK=true

Note:-

Above steps is required on all the datanode in the hadoop cluster.

START AND STOP HBASE CLUSTER

1. Starting the Hbase Cluster:-

we have need to start the daemons only on the hbase-master machine, it will start the daemons in all regionserver machines. Execute the following  command to start the hbase cluster.

                $HBASE_INSTALL_DIR/bin/start-hbase.sh

               

Note:-

           At this point, the following Java processes should run on hbase-master machine. 

               ilab@hbase-master:$jps

               14143 Jps

               14007 HQuorumPeer

               14066 HMaster

               

and the following java processes should run on hbase-regionserver machine.

                23026 HRegionServer

                23171 Jps

2. Starting the hbase shell:-

                $HBASE_INSTALL_DIR/bin/hbase shell

                HBase Shell; enter 'help<RETURN>' for list of supported commands.

                Version: 0.20.6, r965666, Mon Jul 19 16:54:48 PDT 2010

                hbase(main):001:0>

               

                Now,create table in hbase.

                hbase(main):001:0>create 't1','f1'

                0 row(s) in 1.2910 seconds

                hbase(main):002:0>

               

Note: - If table is created successfully, then everything is running fine.

3. Stoping the Hbase Cluster:-

    Execute the following command on hbase-master machine to stop the hbase cluster.

                $HBASE_INSTALL_DIR/bin/stop-hbase.sh